Re: Non-unique indexes guarantee order?

At 10:21 AM 3/5/2007, Rich Jesse wrote:
>Hey all,
>Huh? Where's the SORT operation that would be required for the ORDER BY
>clause? My knee jerk is that Oracle has assumed that the index guarantees
>the order and will not resort. I have it etched in my cold-plagued gray
>matter that an index does not guarantee order, but I can't find conclusive
>evidence of this in docs nor Metalink (aside from GROUP BY without ORDER BY
>not guaranteeing order in 10g, but that's a different case).

Where did you get the impression that an index does not guarantee order. It does. An IFF (Index Fast Full scan) does not - because it just reads all the blocks ignoring branch and root blocks thus not following the index order(!), but an index range scan does. That is one of the options the optimizer checks, for example, when doing a merge-join, if it can avoid the sort by accessing one of the tables by a suitable index.

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

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