Re: Need a Fast Row Count

> >Is there any other way than "select count(*) from <table>" to return
> >number of rows in an Oracle table? This takes forever to run (>3
> >minutes) on a table that has over 3 million rows.
 

> If you do an "analyze table XXX estimate statistics" every night or
> even twice a day on the table in question, you could do:
>
> select num_rows from user_tables where table_name = 'XXX';
>
> That would return the approximate row count as of the last analyze
> (stored in the Last_Analyzed column in the same dictionary view,
> User_Tables).
>
> Chris
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Christopher Hamilton
> Oracle DBA -- Sandbox.com
> chamilton_at_sandbox-inc.com
> http://www.sandbox.com/
>

Chris,

I also think that is the best way. Fast and fairly accurate, if the statistics are up to date. If you analyze the entire schema it also allows you to do "select table_name, num_rows from user_tables" which can be really useful.

Before anybody asks, the best way to analyze the current schema is of course...

   exec DBMS_UTILITY.analyze_schema(user,'ESTIMATE');

--
Regards
Yuri McPhedran


Sent via Deja.com http://www.deja.com/
Before you buy.