Re: all table names with row counts?

In article <36AF6683.8816B009_at_us.oracle.com>,   Vadim Tropasko <vtropash_at_us.oracle.com> wrote:
> I want to perform query like this
> select table_name, (select count(*) from table_name) from all_tables
> but cannot fugure out how to build join correctly. Any ideas?
> No procedural solution, please.

Well, if you just want a rough idea you can do this:

SQL> exec dbms_utility.analyze_schema(USER,'ESTIMATE');

SQL> select table_name, num_rows from user_tables;

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
ACCOUNT                              5658
ACCOUNT_BALANCE                     13150

Simple, fast (well, at least the select part is!) and has the added benefit of getting your statistics up-to-date.

Just one warning - if you have code that has been heavily tuned for the rule- based optimiser, the fact that you now have statistics may (under some circumstances) mean that you will (unintentionally) end up using the cost-based optimiser, which may actually *slow* your applications down. On the plus side, in most cases it will speed them up!

By the way, I see that you have an Oracle email address - doesn't that mean you should be *answering* rather than *asking* these sort of questions? ;-)

Regards
Yuri McPhedran

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Jan 28 1999 - 11:28:32 CST