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

Hi,
I would suggest that you maintain another table containing the number of rows.
You can maintain it with 'for each row' triggers on you base table.

e.g. for inserting you increase some value in the helper table, for deleting you decrease the value.

Yeah, seems quite simple.

But this could be a disaster if multiple users are inserting and deleting rows, because of locking issues.

In this case I would suggest that you helper table contains not just one row,
but say 127 rows. You should create it with INITRANS 127. You then could use a hash function on some key, so that different users, are updating different rows in that table.

e.g. if you have a primary key that is number, you could use update record_counts set r_count=r_count+1 where record_set_key=(primary_key_column mod 127).

Regards,
Mihail Daskalov
Brainbench MVP for Oracle Admin

P.S. If you ask why 127, my answer is:
I DO NOT KNOW. I JUST LIKE 127 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 Thu Mar 14 2002 - 16:39:40 CET