Re: all table names with row counts?

The question was not limited to one schema only (*all_tables*). On the other hand,

statement

begin
dbms_utility.analyze_database('ESTIMATE'); end;

is not that fast.

esiyuri_at_my-dejanews.com wrote:

> 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 - 12:42:33 CST