Re: What is Select count(*) from ... doing?

Marty Himmelstein <marty.himmelstein_at_valley.net> wrote:

>I don't understand the behaviour of select count(*). I create a table with 1.5
>million rows and commit the insert. Sometime later, whether minutes or hours, I do
>a select count(*) from the table. With Oracle's Server Manager window open, I can
>see that the tablespace with the newly created table (and no other concurrent
>activity) is being accessed with twice as many writes as reads. Furthermore, the
>select count(*) takes a very long time.
 

>Does anybody know what Oracle is doing? Also, why doesn't Oracle maintain a row
>count?

Marty,

No idea about why you get writes as well as reads from the table, but I beleive Oracle keeps a row count within the index on the table. If you do a select count(*) from {this} where {primary_key} is not null, it should improve performance as it can now use the index. Hope this helps. Received on Wed Jul 26 1995 - 00:00:00 CEST