Re: SQL Query: Order by Question.

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 23 Jun 2001 10:09:08 +0100
Message-ID: <993287448.21436.0.nnrp-13.9e984b29@news.demon.co.uk>

There is no absolute answer to the question - the Cost Based Optimizer will make a choice based on the known statistics of the situation.

It is possible for Oracle to identify that a query has an execution path that allows an (ascending) index to be used in descending order to bypass the need to sort. There is also a hint (index_desc) that can be used to direct Oracle towards this option.

--
Jonathan Lewis

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
See http://www.jlcomp.demon.co.uk/book_rev.html

Seminars on getting the best out of Oracle
See http://www.jlcomp.demon.co.uk/seminar.html






Atul Pinge wrote in message <3B33B99C.A89DCDA7_at_carl.org>...


>Hello everybody,

>

>I need to read the records using an sql query and read them in reverse

>order on an index (The index is not a descending index).  I can use

>order by clause to do it.  But are the records sorted after they are

>read from the database on the index?  Then it'll be really expensive for

>big tables, right?  Or is Oracle query optimizer intelligent enough to

>read the data in reverse order on an index?  Does anybody know?  Thanks

>in advance...

>

>Atul

>

>

Received on Sat Jun 23 2001 - 04:09:08 CDT