RE: Non-unique indexes guarantee order?
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: <rjoralist@xxxxxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 5 Mar 2007 14:35:41 -0800
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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?
--
//www.freelists.org/webpage/oracle-l
Other related posts:
