RE: is it possible in pl/sql?
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <psinger1@xxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 15 Feb 2005 19:34:01 +0100
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.
---------------------------------------------
Visit my website at http://www.naturaljoin.nl
---------------------------------------------
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Singer, Phillip (P.W.)
Sent: Tuesday, February 15, 2005 19:00
To: oracle-l@xxxxxxxxxxxxx
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
