0

I want a query that selects the number of rows in each table but they are NOT updated statistically .So such query will not be accurate:

select table_name, num_rows from user_tables

i want to select several schema and each schema has minimum 500 table some of them contain a lot of columns . it will took for me days if i want to update them .

from the site ask tom he suggest a function includes this query

'select count(*) from ' || p_tname INTO l_columnValue;

such query with count(*) is really slow and it will not give me fast results.
Is there a query that can give me how many rows are in table in a fast way ?

MMD
  • 115
  • 2
  • 1
    With the restrictions and requirements you've provided I'm going to have to say no. If this system is used by thousands of people and each one is updating data in various schema's there's no one table with this information unless you create a view/table but the second you populate it, it could be out of date. Ref: http://stackoverflow.com/questions/14125731/oracle-row-count-of-table-by-count-vs-num-rows-from-dba-tables – xQbert May 13 '13 at 17:34
  • 1
    Do I dare ask WHY you need an instant + exact count of rows in any/all tables at any given time? Reasonably updated stats should give you a reasonable (if not exact) answer, no? – tbone May 13 '13 at 17:45
  • xqbert thanks for you comment @tbone i want to delete the empty tables, so i have to be exact with the results to make sure not deleting wrong tables. and excat = alot of time. – MMD May 13 '13 at 17:50
  • counting the rows in all tables is more or less what oracle does when you gather stats. so, if you already count everything 1. don't you want to save the stats ? and 2. don't you think oracle does it faster ? – haki May 13 '13 at 18:15
  • You want to do exact counts on any/all tables before you delete ALL of the rows, so you can make sure that Oracle correctly deleted all of the rows??? Much easier to just do: truncate table x; rather than: select count(1) from x; delete from x; (make sure counts match); commit; – tbone May 13 '13 at 18:20
  • @haki - Oracle can estimate the stats so they aren't necessarily ever completely accurate, and they go stale; might still be good enough to indicate if a table it used at all, though the staleness still seems to be an issue. – Alex Poole May 13 '13 at 18:25
  • @tbone - I think that's a terminology error; I think the OP is trying to check the table is empty before *dropping*, not deleting. That's how I interpreted that comment anyway, we'll see how my answer gets on 8-) – Alex Poole May 13 '13 at 18:27
  • @AlexPoole ahh, maybe so. But still very weird logic. "I want to DROP a table called "abc" (I think that's its name), but I KNOW it has 3,255,605 rows, so let me do a count on "abc" and if I get the expected count, then its ok to drop that sucker". I'd like to see that script get send to DBAs to run in production! (surely a dailywtf entry) – tbone May 13 '13 at 18:36
  • @tbone - I think the row count isn't known, and if it's empty then it's assumed to be the right table, so It'll be dropped; so only zero is the right answer. It might be worse than that, it sounds like we're going to drop all empty tables in the schema, assuming nothing is expecting them to be populated later. Agree about weirdness, and hope it doesn't make it as far a production, or any env I care about really... – Alex Poole May 13 '13 at 18:59

3 Answers3

2

Full Disclosure: I had originally suggested a query that specifically counts a column that's (a) indexed and (b) not null. @AlexPoole and @JustinCave pointed out (please see their comments below) that Oracle will optimize a COUNT(*) to do this anyway. As such, this answer has been altered significantly.

There's a good explanation here for why User_Tables shouldn't be used for accurate row counts, even when statistics are up to date.

If your tables have indexes which can be used to speed up the count by doing an index scan rather than a table scan, Oracle will use them. This will make the counts faster, though not by any means instantaneous. That said, this is the only way I know to get an accurate count.

To check for empty (zero row) tables, please use the answer posted by Alex Poole.

Community
  • 1
  • 1
Ed Gibbs
  • 25,924
  • 4
  • 46
  • 69
  • 1
    Oracle will happily optimize a `COUNT(*)` to do a scan of the primary key index if one exists. No need to `COUNT(indexed_notnull_column)` explicitly. – Justin Cave May 13 '13 at 17:39
  • 1
    Isn't Oracle likely to pick that index for the execution plan, even with `count(*)`? – Alex Poole May 13 '13 at 17:39
  • Thanks Justin and Alex - I didn't know that. A simple `COUNT` query with an `EXPLAIN PLAN` would have told me as much. At any rate, this means my answer is misleading so I'd like to delete it, but your comments are valuable and I don't want them to go away with the answer. Any suggestions? – Ed Gibbs May 13 '13 at 17:45
  • i prefer to keep them @EdGibbs i am really learning from your comments and answers. – MMD May 13 '13 at 17:51
  • Fair enough @user2373598, and thanks for the feedback. I'll adjust the answer accordingly and take out the misleading bits. – Ed Gibbs May 13 '13 at 17:54
2

You said in a comment that you want to delete (drop?) empty tables. If you don't want an exact count but only want to know if a table is empty you can do a shortcut count:

select count(*) from table_name where rownum < 2;

The optimiser will stop when it reaches the first row - the execution plan shows a 'count stopkey' operation - so it will be fast. It will return zero for an empty table, and one for a table with any data - you have no idea how much data, but you don't seem to care.

You still have a slight race condition between the count and the drop, of course.

This seems like a very odd thing to want to do - either your application uses the table, in which case dropping it will break something even if it's empty; or it doesn't, in which case it shouldn't matter whether it has (presumably redundant) and it can be dropped regardless. If you think there might be confusion, that sounds like your source (including DDL) control needs some work, maybe?

To check if either table in two schemas have a row, just count from both of them; either with a union:

select max(c) from (
    select count(*) as c from schema1.table_name where rownum < 2
    union all
    select count(*) as c from schema2.table_name where rownum < 2
);

... or with greatest and two sub-selects, e.g.:

select greatest(
    (select count(*) from schema1.table_name where rownum < 2),
    (select count(*) from schema2.table_name where rownum < 2)
) from dual;

Either would return one if either table has any rows, and would only return zero f they were both empty.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • i need to check that this table doesn't has columns in two schemas (exp: tab1 in schema1 has 0 column but tab1 in schema2 has coulumns it shouldnt be droped because there are relation between them) so can we adjust the querry that you mentioned for 2 schema ? and i prepared a dump file if some those empty tables has an effect on my application – MMD May 13 '13 at 18:12
  • @user2373598 - I don't understand what you mean. Do you mean rows (i.e. data) rather than columns? – Alex Poole May 13 '13 at 18:17
  • interesting so this will be fast because i am specifying to count only to two row num . but 'select count(*) from table_name' would mention the name of the table with the number of count right ? the query of schema is helpful thank you – MMD May 13 '13 at 18:40
  • @MMD - it won't show the table name with the count. If you want to run this over all your tables automatically then you'll need some form of dynamic SQL, as in the AskTom article you linked to but with a `where` clause added, which could also do the drop automatically (if you're feeling brave). Or you could generate the queries from one script and spool that into a file, and then execute that. – Alex Poole May 13 '13 at 18:55
  • thanks alot for your information. i will accept your answer and tomorrow morning i will test the query and if i have any question i will ask you here. – MMD May 13 '13 at 19:16
0

You could make a table to hold the counts of each table. Then, set a trigger to run on INSERT for each of the tables you're counting that updates the main table.

You'd also need to include a trigger for DELETE.

Lawson
  • 624
  • 1
  • 5
  • 19
  • 1
    Note, of course, that this would require that every `INSERT` and `DELETE` operation on a table would need to be serialized. That would dramatically affect the scalability of the system and would probably cause the whole system to grind to a halt unless it is a very small system. There is a very good reason that Oracle doesn't try to maintain this sort of data transactionally. – Justin Cave May 13 '13 at 17:38
  • 1
    But that will either cause two simultaneous updates to overwrite each other (leading to inaccurate counts again), or will serialise, which will slow things down... – Alex Poole May 13 '13 at 17:38