Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Non-unique indexes guarantee order?

Re: Non-unique indexes guarantee order?

From: Rich Jesse <rjoralist_at_society.servebeer.com>
Date: Mon, 5 Mar 2007 14:25:54 -0600 (CST)
Message-ID: <59435.12.17.117.251.1173126354.squirrel@12.17.117.251>


This makes perfect sense now that the OTC drug-induced haze has been somewhat cleared by caffeine and McFood substitute.

I've been telling devs for years that an index by itself does not guarantee order of rows returned in a query -- and from the dev's point-of-view, I stand by that statement, since they would not (cannot?) take into consideration when Oracle could FFS the index. (similar to Mark's reply)

Thanks Wolfgang, Dan, and Mark for your replies!

Rich

> 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
Received on Mon Mar 05 2007 - 14:25:54 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US