Re: Alternative to select count(*) from table ?

You are correct select count(*) or select count(1) will take the same amount of time etc.
Is there a column that is sequential? You could make it unique and put a constraint to limit to a particular number. Might not work if a sequence gets lost (e.g. uses a sequence and the transaction gets rolled back) Jim
"johan" <jox123_at_hotmail.com> wrote in message news:3C8F7841.BE5ADA3D_at_hotmail.com...
>
>
> Adam Spindler wrote:
>
> > Johan wrote:
> > > Hi all,
> > >
> > > Hope that someone can help me with this question.
> > >
> > > I have a table where I need to keep track of the number of rows it
> > > contains, because there is a limit on the maximum number of rows
> > > allowed in it. Now I do a select count(*) from table; before every
> > > insert. With 5 million rows in the table the count takes around 10
> > > seconds. (I can see from the execution plan that it is counting the
> > > number of rows in the primary index of the table).
> > >
> > > So, I was wondering if there is a better (and faster) alternative to
> > > my select count(*) available? I would rather not maintain a counter by
> > > myself in another table for example. The table in question could
> > > contain up to millions of rows.
> > >
> > > Is the number of rows in a table to be found as metadata from
> > > somewhere for example?
> > >
> > > I'm using Oracle 8.1.7 Standard Edition on Solaris 8.
> > >
> > > Any help is greatly appreciated.
> > >
> > > /Johan
> >
> > I believe "select count(*)" has to retrieve the whole row into the SGA,
> > so using "count(1)" may be faster, if it's not an IOT (and is not likly
> > to become one) try using "select count(rowid)" - if it works let me know
 ...
>
> Thanks for your quick reply.
>
> I made a test and the only difference I notice over several runs is that
 select
> count(rowid) is slightly slower. (See transcript below). And no, it is not
 an
> IOT. Would that make it faster? (I'm not sure what Cost, Card, and Bytes
> actually mean but the values differ only for select count(rowid).
>
> As I understand it select count(*) or select count(1) should make no
 difference
> in Oracle!? (at least not in versions over 8)
>
> ---
>
> SQL> select count(*) from users;
>
> COUNT(*)
> ----------
> 5000001
>
> Elapsed: 00:00:10.79
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1670 Card=1)
> 1 0 SORT (AGGREGATE)
> 2 1 INDEX (FAST FULL SCAN) OF 'PK_USERS' (UNIQUE) (Cost=1670
> Card=4994490)
>
>
>
>
> SQL> select count(1) from users;
>
> COUNT(1)
> ----------
> 5000001
>
> Elapsed: 00:00:11.01
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1670 Card=1)
> 1 0 SORT (AGGREGATE)
> 2 1 INDEX (FAST FULL SCAN) OF 'PK_USERS' (UNIQUE) (Cost=1670
> Card=4994490)
>
>
>
>
> SQL> select count(rowid) from users;
>
> COUNT(ROWID)
> ------------
> 5000001
>
> Elapsed: 00:00:13.79
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1670 Card=1 Bytes=7)
> 1 0 SORT (AGGREGATE)
> 2 1 INDEX (FAST FULL SCAN) OF 'PK_USERS' (UNIQUE) (Cost=1670
> Card=4994490 Bytes=34961430)
>
>
>
>
Received on Thu Mar 14 2002 - 03:21:00 CET