RE: Non-unique indexes guarantee order?

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Mon, 5 Mar 2007 14:01:52 -0500
Message-ID: <D1DC33E67722D54A93F05F702C99E2A9905A0A@usahm208.amer.corp.eds.com>

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.

Mark D Powell -- Phone (313) 592-5148

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

(%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


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

Received on Mon Mar 05 2007 - 13:01:52 CST