| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: select count(*) optimization
Fernando Pereira <morgan_at_cardume.com> wrote in article <01bc36a4$b82c2ad0$861c41c2_at_tubarao>...
> > > 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.
The cost based optimizer can choose to use an index to do a count(*) if it calculates it will be more efficient. The decision will be based on table statistics (size of table, size of index), multi-block read setting, parallelism for the table and version of Oracle. In 7.3.3, the ability to perform parallel full fast index scans will probably result in the Cost based optimizer choosing the index over the table almost every time (I haven't checked this).
In the Rule based optimizer, you would have to specify a WHERE clause condition to promote the use of the index - or use an INDEX hint.
Regards,
Guy Harrison
 |
 |