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

I'm not sure I understood your reasoning behind not using a sequence number.
why not just create a sequence, start it at whatever the current count of your table is, and increment it with each row that is inserted, through the use of a trigger. have a job (or manually) check that counter every little bit to see what the current value is so you know the number of rows at any given time. maybe crosscheck the sequence value occasionally against the current count of the rows, to make sure they are the same (eg. deletes would have deletorious effect on your accuracy between the two). If they have different values, recreate the counter with the new value.

Try different things, see what works.

jox123_at_hotmail.com (Johan) wrote in message news:<7257f2d6.0203130700.24eb027a_at_posting.google.com>...
> 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
Received on Wed Mar 13 2002 - 20:37:04 CET