It all depends on the way the index is accessed. Unique and Range scans
will read the leaf blocks in the order of the index values. A Fast Full
scan that performs multiblock reads will read in order of block
arrangement. The FFS would then require a sort to present the rows in
the right order.
The ORDER BY will force the rows to be returned in the right order and
the optimizer is smart enough (usually) to know that a Unique/Range scan
of the values will be in the requested sorted order. If the plan changes
and uses an index FFS, it will need to sort.
--
Daniel Fink
Oracle Performance, Diagnosis and Training
OptimalDBA http://www.optimaldba.com
Oracle Blog http://optimaldba.blogspot.com
Join me at Miracle Scotland DB Forum!
http://www.miracleltd.com/index.asp?page=167&page2=343
Rich Jesse wrote:
> 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 - 12:25:19 CST
