index full scan (min/max) and descending index

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_at_flash.net
214.954.1781

--
http://www.freelists.org/webpage/oracle-l