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

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)