Re: sort without order by

If I use IOT, I need not force the use of the index, am I right?

Thomas Kyte <tkyte_at_us.oracle.com> wrote:
> 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 - 19:49:35 CST