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

Sometimes optimizer features need a little time to catch up with other features.

create index i1 on t1(n1 desc);
select n1 from t1 order by n1 desc;

does not seem to be able to use just the index, even with exact hinting when using 8.1.7.0

Doing the same with 9.0.1.1.1 results in an index full scan and a nosort order by.

--
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 <3c22611a.2706083406_at_news.alt.net>...



>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

>

>