Re: Indexes on a Select statement

Will is correct that Oracle can do a
'sort by nosort' given those conditions.

I can't recall version numbers where this became possible - however, I do know that some time around 8.1.5, you can even drop the requirement (in this specific example) to include (col1 and col2) in the order by clause.

--
Jonathan Lewis

Host to The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

Seminars on getting the best out of Oracle
See http://www.jlcomp.demon.co.uk/seminar.html

Screensaver or Lifesaver: http://www.ud.com
Use spare CPU to assist in cancer research.




Daniel A. Morgan wrote in message <3B66F62D.54231B09_at_attws.com>...



>Will wrote:

>

>>

>> You probably want a composite index on COL1, COL2, COL3, COL4, and

>> COL5.  That's one index comprised of 5 columns.  You may also need to

>> modify your sort to include COL's 1 and 2.  So it would look like

>> this...

>>

>> create index your-index on your-table (col1, col2, col3, col4, col5);

>>

>> select *

>> from   your-table

>> where  col1 = :col1

>>  and   col2 = :col2

>> order by col1, col2, col3, col4, col5;

>>

>> If you do it that way, Oracle will use the index and won't sort the

>> result.

>>

> Clear?

>

>Clear. And you may be right about the ORDER BY though I'd like to see a

 confirmation from Tom Kyte or


>someone with knowledge of Oracle internals. But I can not agree that "most

 queries hit the primary


>key". Most queries may incorporate the primary key but that is only a

 starting point.


>

>Daniel A. Morgan

>