Non-unique indexes guarantee order?

From: Rich Jesse <rjoralist_at_society.servebeer.com>
Date: Mon, 5 Mar 2007 11:21:26 -0600 (CST)
Message-ID: <34833.12.17.117.251.1173115286.squirrel@12.17.117.251>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Mar 05 2007 - 11:21:26 CST