Non-unique indexes guarantee order?
- From: "Rich Jesse" <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 5 Mar 2007 11:21:26 -0600 (CST)
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 (%CPU)|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 15257 | 2 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| MYTABLE | 11 | 15257 | 2 (0)|
| 2 | INDEX RANGE SCAN | MYTABLE_IDX | 11 | | 3
(0)|
-------------------------------------------------------------------------------
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
