Re: select count(*) optimization

If you are interested in seeing what the Oracle optimizer really does, we will have soon a functional demo for our TunaSQL product that helps to explain these little surprises. You can see the page at: www.cardume.com (We will have the demo by the end of the month).

Ed Bruce <bruce_at_ha.hac.com> wrote in article <3331BCA2.794B_at_ha.hac.com>...
> Thomas J. Kyte wrote:
> >
> 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.
>

I think the problem is that some different versions (including changes in minor releases) have huge changes in the optimizer.

Fernando Pereira
CardumeSoftware
www.cardume.com Received on Sat Mar 22 1997 - 00:00:00 CST