Re: Would Oracle use index on count(*)?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 14 Apr 1998 13:23:55 GMT
Message-ID: <01bd67a7$5b5c31d0$294b989e@WORKSTATION>

Eugen Nyffeler <eugen.nyffeler_at_ubs.com> wrote in article <35331BA9.86E49A6E_at_ubs.com>...
>
> If you use a count(*) the full table scan is coosen. If you use
> count(primary key field)
> an index scan shoud be used. I don't know the exact Oracle
internals for
> this, but
> i think one reason is to make the codeing simple (e.g. what do you
do
> when there
> are several indexes but none is unique, or even indexes with null
value
> ??)
>

Not strictly true, although there are plenty of variations dependent upon
Oracle version.

For instance, if you have a bitmap index (in 8.0.3 at least) then select count(*) will almost certainly use the index to do the count (partly because, contrary to btree indexes) null are stored in the index.

I believe that given suitable cost factors, count(*) can be converted to a count using the PK index if such exists.

Jonathan Lewis Received on Tue Apr 14 1998 - 08:23:55 CDT