Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Need a Fast Row Count

Re: Need a Fast Row Count

From: <jcollins_at_sasquatch.com>
Date: 2000/03/25
Message-ID: <Pine.NEB.4.21.0003250846480.4807-100000@seashell.sasquatch.com>#1/1

if you analyze your schema for cost-based optimizer then the row-count as of last time you analyzed is in user_tables. not totally up-to-date info but harmless query.

On Fri, 24 Mar 2000, Vitalij Serdakovskij wrote:

> 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 Sat Mar 25 2000 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US