Re: Top N in Oracle 8.0.5

In article <58qU8.7531$68.204409_at_news4.srv.hcvlny.cv.net>, "Anurag says...
>
>True. Oracle does state that distinct is not guaranteed
>to return ordered result sets.
>A case would be when you are using the PQO.
>At least in pre-8i, for non-PQO, distinct does return
>an ordered resultset since Oracle does a *sort* to find
>the distinct rows.
>

but -- it doesn't have to sort it (never did).

Also, when it does sort it -- who is to say that it'll sort it in the way YOU want it sorted (eg: select distinct x, y -- will that be sorted by x and then y or y and then x??)

Case in point:

ops$tkyte_at_ORA806.WORLD> set autotrace on explain ops$tkyte_at_ORA806.WORLD> select distinct x, y from t;

         X Y
---------- ----------

         5          1
         4          2
         3          3
         2          4
         1          5

Execution Plan

   0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=4 Card=5 Bytes=45)    1 0 SORT (UNIQUE NOSORT) (Cost=4 Card=5 Bytes=45) 2 1 INDEX (FULL SCAN) OF 'SYS_C00665' (UNIQUE) (Cost=1 Card=5 Bytes=45)

ops$tkyte_at_ORA806.WORLD> select distinct y, x from t;

         Y X
---------- ----------

         1          5
         2          4
         3          3
         4          2
         5          1

Execution Plan

   0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=4 Card=5 Bytes=45)    1 0 SORT (UNIQUE NOSORT) (Cost=4 Card=5 Bytes=45) 2 1 INDEX (FULL SCAN) OF 'SYS_C00665' (UNIQUE) (Cost=1 Card=5 Bytes=45)

ops$tkyte_at_ORA806.WORLD> set autotrace off

SORT (unique nosort) ......

>Having said that .. :) I agree completely with Niall.
>I should know better than suggesting a solution which
>might fail in later Oracle releases or in case of a configuration change...
>
>Niall, I think the INDEX hint has the exact same pitfall with PQO. It too
>does not
>guarantee an ordered resultset (correct me if wrong?).
>
>Anurag
>
>
>"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message
>news:3d221b5e$0$8506$cc9e4d1f_at_news.dial.pipex.com...
>> "Anurag Varma" <avdbi_at_hotmail.com> wrote in message
>> news:PMjU8.4622$68.82651_at_news4.srv.hcvlny.cv.net...
>> > I might be wrong ... and cannot test this .. but I remember using
>distinct
>> > clause to sort the subquery results.
>>
>> Distinct doesn't guarantee an ordered resultset ONLY order by does. I'll
>> agree that generally you'll get one but if you don't its just tough and
>that
>> way unresolvable bugs lie.
>>
>>
>> --
>> Niall Litchfield
>> Oracle DBA
>> Audit Commission UK
>>
>>
>>
>>
>
>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp