| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: is it possible in pl/sql?
a column with a unique index is not good enough --
otherwise it might be quicker, but also give different results ;-)
it must be an index on a NOT NULL column, and it is actually irrelevant
whether the index is unique; it's only the index *size* that counts.
kind regards,
Lex.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On
Behalf Of Singer, Phillip (P.W.)
Sent: Tuesday, February 15, 2005 19:00
To: oracle-l_at_freelists.org
Subject: RE: is it possible in pl/sql?
>Hmmm.. from "Oracle Performance Tuning 2nd edition" (Gurry and=20
>Corrigan,=20 O'Reilly Press, 1996) -- so were talking version 7 RBO
>here:
>
>"Contrary to popular belief, COUNT(*) is faster than COUNT(1). If
>the=20 rows are being returned via an index, counting the index
>column--for=20 example, COUNT(EMP_NO) is faster still. [followed with
>actual=20 test runs=20 including timings to prove this]"
>
>So, for the sake of historical accuracy (and not much else), there
>is=20 some evidence that a long time ago the count(*) performance=20
>issue existed.
>
>-Mark Bole
>
What is interesting is that it is contrasted with a select count() against a column on which a unique index exists - which is shown to be quicker still. Which suggests that (at that time at least) the optimizer was not smart enough to find and use a unique index without lots of help.
I also remember about that time finding advice on Metalink to do a count(*) on a table if one was getting ORA-01555's due to delayed block cleanout - this was supposed to be the 'best' way to force every block to be read.
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue Feb 15 2005 - 13:37:03 CST
 |
 |