index full scan (min/max) and descending index
- From: "Larry Elkins" <elkinsl@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 28 Jun 2005 06:02:30 -0500
Listers,
9.2.
SELECT MAX(COL1) FROM FOO
COL1 has been created as a DESCENDING index. An index fast full scan is used
to resolve the query. If *not* defined as a DESCENDING index, then the
"index full scan (min/max)" optimization is used. And don't ask me why it is
defined as a descending index, it's a canned application. Maybe to make the
above query "faster", which it seems to prevent by not allowing the "index
full scan (min/max)" optimization to be used, instead dropping into an index
fast full scan ;-)
So, is the "index full scan (min/max)" optimization not available when the
index is defined as a descending index? Limited testing in 9iR2 and 10G
repeats the behavior of not using the optimization when it is defined as a
descending index, but it's hard to say it will never be used in the case of
a descending index. I came across a Usenet post from 1999 by Jonathan Lewis
where he noted this behavior as well.
This raises another question. Between the docs, googling, Metalink, etc, I
can't find a good "official" definition of this optimization. Oracle docs
mention it under RBO Access Paths but says "Oracle performs a full index
scan". My understanding is that it reads the first or last index entry. And
actually that's how Julian Dyke's site defines it -- "Returns the first or
last entry in the index".
Regards,
Larry G. Elkins
elkinsl@xxxxxxxxx
214.954.1781
--
//www.freelists.org/webpage/oracle-l
Other related posts:
