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

Morgan Skinner (morgan.skinner_at_fisons-ls.com) wrote:
: 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.

'...where {primary_key} is not null ' won't do it (full table scan) try '...where {primary_key} > 0 ' or something similar (index range scan)

--
--------------------------------------------------------------------
Thomas Keul, IVU GmbH                               tk_at_ivu-berlin.de
Bundesallee 129                                 tel +49 30 850006 91
12161 Berlin, Germany                           fax +49 30 850006 86