Re: Will Index be Used - ORDER BY MYFIELD DESC?

The PK/UK issue could well be to do with null columns. A Unique key index need not hold a reference to every row as unique key constraints allow for NULL columns.

Rerun the experiment and define a column in the UK to be NOT NULL, and I would expect you to see a difference. In fact, with recent versions of Oracle, any index with a non-null column in it should be viable for the type of path.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

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

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

Brian Tkatch wrote in message <3c2251e1.2702186328_at_news.alt.net>...


>On 13 Dec 2001 19:46:08 -0800, delphidev2000_at_yahoo.com (Delphi Dev

>2000) wrote:

>

>>Will the index be used in the following scenario on Oracle 8i?  I have

>>browsed through the news groups and it appears that there is no clear

>>answer to this.  I have tried to do some speed benchmarks myself.

>>Sometimes it seems that the query takes longer while other times it is

>>quicker; I suspect that there may be some caching going on.

>>

>>In any event, can you traverse backwards on indexes that are not

>>created with the "DESC" keyword?

>>

>>Create Index MYINDEX ON MYTABLE(MYFIELD);

>>SELECT MYFIELD FROM MYTABLE WHERE MYFIELD <= 'XXXXXX' ORDER BY MYFIELD

>>DESC;

>>

>>

>>Thanks, Mark

>

>If I understand correctly, it depends on whether the index is used to

>retrieve data, and whether the optimizer does a FULL SCAN or a FAST

>FULL SCAN.

>

>A FULL SCAN can be used to sort, whilst a FAST FULL SCAN cannot. Do an

>EXPLAIN PLAN on the statement, and see if the index is used at all.

>

>For DESCENDING, the Optimizer uses a DECENDING on the FULL SCAN.

>

>Here's what I did:

>

>CREATE TABLE Moo (Cow NUMBER PRIMARY KEY);

>

>SELECT * FROM Moo ORDER BY Cow;

>

>This used an INDEX FULL SCAN.

>

>SELECT * FROM Moo ORDER BY Cow DESC

>

>This used an INDEX FULL SCAN DESCENDING.

>

>At a closer look, it looks as if it would only use the index for the

>sort if it was PRIMARY KEY. Even a UNIQUE key/index was not used.

>

>Brian