Re: USING INDEXES

A copy of this was sent to "Mark Ragan" <mark.ragan_at_dial.pipex.com> (if that email address didn't require changing) On Mon, 15 Nov 1999 13:54:17 -0000, you wrote:

>I have a table which contains many fields, one of which is "OrderNo".
>
>This "OrderNo" WILL have duplicate entries.
>
>I have created an index on this field.
>
>When doing a "Select * from Filename ORDER BY OrderNo" Oracle sorts each
>record before outputting.
>
>How can I force Oracle to use the index?
>
>Why doesn't it use this index by default?.
>

is orderNo by nullable? if so, the index cannot be used as nulls are not indexed (in B*Trees anyway).

Try

select * from filename where orderNo > 0 order by orderno;

That does 2 things

• it removes the possibility that orderNo in the result set could be null, this allows the use of the index.
• it tells us that maybe we won't get every row in the table anyway, lets use an index range scan to get the ones we asked for.

This'll work nicely in the rule based optimizer but the CBO might not take this path. It will know that every row will be read anyway and might avoid the index again.

>TIA
>
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Nov 15 1999 - 09:13:55 CST