Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Non-unique indexes guarantee order?
Oracle is retrieving the data via an index that matches the order by
clause so therefore the data is in the requested sorted order allowing
the optimizer to eliminate a separate sort step. What is not guaranteed
is that without the order by clause that the data will be returned in
the desired order since the CBO could choose to solve this query
differently if the statistics indicate that a different plan would be a
better solution. The order by clause guarantees the order in which the
data is returned not how Oracle goes about arranging the data into
order.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rich Jesse
Sent: Monday, March 05, 2007 12:21 PM
To: oracle-l_at_freelists.org
Subject: Non-unique indexes guarantee order?
Hey all,
Went live with new ERP system this month on 10.1.0.5. As I'm sweeping
for potential DB issues, I see this statement that's being run a
bazillion time
(approximately):
SELECT *
FROM mytable
WHERE ( col1 = :key1
AND col2 = :key2 )
ORDER BY col1 ASC, col2 ASC, col3 ASC;
The table has a non-unique index created with columns col1, col2, and col3, which matches the ORDER BY clause exactly. The explain plan for the above statement is:
| Id | Operation | Name | Rows | Bytes | Cost
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).
Thoughts? I know my caffeine intake is a little low, so bear with me if I'm off in la-la land here. And I've yet to tackle a 10053 (ever) to see if that gives any insight into situations like this.
Thanks!
Rich
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Mon Mar 05 2007 - 13:01:52 CST