Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Need a Fast Row Count
Hi
If there is a not null index in the table, you can try index hint SELECT /*+ index(xxx_index xxx_table) */ COUNT(*) from xxx_table;
or without hint:
SELECT COUNT(<indexes_col_name>) from xxx_table;
Kari Pannila
Oracle DBA (OCP)
TietoEnator Corp - Telecom
Delete X to get a correct mail address ( X is only for the SPAMMERS ).
Yuri McPhedran wrote:
> > >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.
-- Delete X to get working mail address...Received on Mon Mar 27 2000 - 00:00:00 CST