Re: sort without order by

A copy of this was sent to Norris <jcheong_at_cooper.com.hk> (if that email address didn't require changing) On 21 Nov 1999 09:52:13 GMT, you wrote:

>Will it run faster if we force the use of the index?
>

the original question was "i want the 3 min values". using the index to order the table and fetching the first 3 rows will be infinitely faster then not using an index, scanning the entire table, sorting it and then getting the first three rows.

>Greg Stark <greg-spare-1_at_mit.edu> wrote:
>
>> Norris <jcheong_at_cooper.com.hk> writes:
>
>>> What is the purpose of having "where x > -99999999999999"?
>
>>> > tkyte_at_8.0> select x from t where x > -99999999999999 order by x;
>
>> To force the use of the index I think. actually in this case without it Oracle

correct -- since X could be NULL in my example, unless we use a predicate that precludes X from being null in the query (since nulls are *not* indexed), we would not be able to use an index regardless of the optimizer mode (cost or rule).

using the predicate with a range like that will bias the optimizer towards using the index (the rule based one). Using the CBO tho, it might know that all values of x match the predicate and hence using the index would be slower then scanning/sort and it might by pass the index in which case a FIRST_ROWS hint would help alot.

>> would not be able to use the index at all because x could be NULL. If x were
>> NOT NULL and the table is analyzed Oracle would probably but not necessarily
>> use the index anyways.
>
>> --
>> greg

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Nov 21 1999 - 09:24:55 CST