Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Need a Fast Row Count
I have no benefit on my only processor. I used this script to check:
select to_char(sysdate,'sssss') from dual;
select count(*) from large_table;
select to_char(sysdate,'sssss') from dual;
alter table large_table (degree 4);
select to_char(sysdate,'sssss') from dual;
select count(*) from large_table;
select to_char(sysdate,'sssss') from dual;
Good luck!
"Mark Townsend" <mtownsen_at_us.oracle.com> wrote in:
news:38DA5A5B.71A006E8_at_us.oracle.com...
> Gary Brumfield wrote:
> >
> > Is there any other way than "select count(*) from <table>" to return the
> > number of rows in an Oracle table? This takes forever to run (>3
> > minutes) on a table that has over 3 million rows. I guess it has to do
> > a full table scan to determine the correct number of rows and in a read
> > consistent manner. It would be OK even if we got a count that was
> > fairly close.
> >
> > Any Ideas or Insight?
>
> Depending on your hardware, you may get some benefit from enabling
> parallelism - i.e
>
> SQL> select count(*) from sale_orders;
>
> COUNT(*)
> ----------
> 277500
>
> Elapsed: 00:00:00.91
>
> SQL> alter table sale_orders parallel 8;
>
> Table altered.
>
> Elapsed: 00:00:00.40
>
> SQL> select count(*) from sale_orders;
>
> COUNT(*)
> ----------
> 277500
>
> Elapsed: 00:00:00.30
>
> Note that this is on a single CPU, single drive NT laptop. On a machine
> with multiple CPU's, and sufficient I/O bandwidth, this can really fly.
>
> If you don't want an exact number, in Oracle8i you could try sampling
> i.e
>
> SQL> select count(*)*10 from sale_orders sample(10) block;
>
> COUNT(*)*10
> -----------
> 279010
>
> Elapsed: 00:00:00.31
>
> Alternatively, if statistics are reasonably up-to-date, you could just
> use the number of rows counted last time statistics were collected, i.e
>
> SQL> select table_name,num_rows from user_tables where table_name =
> 'SALE_ORDERS';
>
> TABLE_NAME NUM_ROWS
> ------------------------------ ----------
> SALE_ORDERS 274603
>
> Elapsed: 00:00:00.80
>
> --
> Regards,
>
>
> Mark Townsend
>
> Principal Product Manager Ph: (650) 633 5764
> Server Division Fx: (650) 506 7222
> Oracle Corporation Email: mtownsen_at_us.oracle.com
Received on Fri Mar 24 2000 - 00:00:00 CST