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

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 ... Received on Wed Mar 13 2002 - 16:25:28 CET