Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Indexes on a Select statement

Re: Indexes on a Select statement

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 1 Aug 2001 08:12:07 +0100
Message-ID: <996650533.16098.0.nnrp-01.9e984b29@news.demon.co.uk>

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
>
Received on Wed Aug 01 2001 - 02:12:07 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US