oracle - A fast query that selects the number of rows in each table -

i want query selects number of rows in each table not updated statistically .so such query not accurate:

select table_name, num_rows user_tables

i want select several schema , each schema has minimum 500 table of them contain lot of columns . took me days if want update them .

from site ask tom suggest function includes query

'select count(*) ' || p_tname l_columnvalue;

such query count(*) slow , not give me fast results.
there query can give me how many rows in table in fast way ?

you said in comment want delete (drop?) empty tables. if don't want exact count want know if table empty can shortcut count:

select count(*) table_name rownum < 2; 

the optimiser stop when reaches first row - execution plan shows 'count stopkey' operation - fast. return 0 empty table, , 1 table data - have no idea how data, don't seem care.

you still have slight race condition between count , drop, of course.

this seems odd thing want - either application uses table, in case dropping break if it's empty; or doesn't, in case shouldn't matter whether has (presumably redundant) , can dropped regardless. if think there might confusion, sounds source (including ddl) control needs work, maybe?

---

to check if either table in 2 schemas have row, count both of them; either union:

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

... or greatest , 2 sub-selects, e.g.:

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

either return 1 if either table has rows, , return 0 f both empty.