RE: Non-unique indexes guarantee order?

I have seen people write this type of query:

select /*+ index (my_table my_index) */ * from my_table where my_column = :b1 ;

and purposely omit the "order by", in an attempt at "optimizing" the SQL statement, expecting that the result set will be ordered by the index columns. Which is of course not guaranteed.

select /*+ index (my_table my_index) */ * from my_table where my_column = :b1
order by my_index_column_1, my_index_column_2 ;

should be the correct way to write it, and let the optimizer decide if the sort is superfluous.

Or did I misunderstand your question?

-----Message d'origine-----

De : oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] De la part de Rich Jesse

 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).

Thoughts?

--

http://www.freelists.org/webpage/oracle-l Received on Mon Mar 05 2007 - 16:35:41 CST