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

On Thu, 20 Dec 2001 21:46:32 -0000, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>
>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

How correct you are. I figured I would miss something obvious. (Maybe now that I bought your book, I should actually reads it too. :-) )

CREATE TABLE Moo (Cow NUMBER NOT NULL);
CREATE INDEX Cow ON Moo(Cow);

SELECT * FROM Moo ORDER BY Cow;
Uses an INDEX FULL SCAN.

SELECT * FROM Moo ORDER BY Cow DESC;
Uses an INDEX FULL SCAN DESCENDING.

DROP INDEX Cow;
CREATE INDEX Cow ON Moo(Cow DESC);

SELECT * FROM Moo ORDER BY Cow;
SELECT * FROM Moo ORDER BY Cow DESC;

Both use TABLE ACCESS FULL even when I try

SELECT /*+ INDEX (Moo Cow) */ * FROM Moo ORDER BY Cow DESC;

Hmm.. even

SELECT /*+ INDEX (Moo Cow) */ Cow FROM Moo;

goes to the table rather than using the index.

<time passes>

Aha! Found it on the CREATE INDEX documentation.

<<
Oracle treats descending indexes as if they were function-based indexes. You do not need the QUERY REWRITE or GLOBAL QUERY REWRITE privileges to create them, as you do with other function-based indexes. However, as with other function-based indexes, Oracle does not use descending indexes until you first analyze the index and the table on which the index is defined. See the column_expression clause of this statement.
<<

ANALYZE TABLE Moo COMPUTE STATISTICS;
ANALYZE INDEX Cow COMPUTE STATISTICS;

Still both queries

SELECT * FROM Moo ORDER BY Cow;
SELECT * FROM Moo ORDER BY Cow DESC;

use a full table scan.

COMPATIBLE is set to 8.1.0. So, I do not know why it doesn't use it. I guess I need to read more.

Brian Received on Thu Dec 20 2001 - 16:24:29 CST