Re: select count(*) optimization

Thomas J. Kyte wrote:
>
>
> if, for example, the primary key was a number > 0, you could:
>
> select count(*) from blah where pk_column > 0;
>
> this would cause an index range scan. You can use this with other datatypes and
> values as well.... Use a where clause on the column and this will typically

Well I'm going to search back through the Oracle documentation. This doesn't jive with what I remember reading. I recently had an argument with a co-worker about whether we should use count(*) or count(<primary key column(s)>). Now according to Thomas we need to use a where clause to get an index. Seems like neither myself or my co-worker were correct.

Anyway I vaguely remember that somewhere in that vast Oracle documentation a claim was made that if you did a:

 select count(*) from blah;

Oracle would use an unique index from blah if one was available. Oh well, caveat emptor with Oracle documentation.

-- 

Ed Bruce
<Bruce_at_ha.hac.com>
<ebruce_at_iquest.com>