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

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 Received on Thu Dec 20 2001 - 15:33:15 CST