I cannot disagree with this .. can I.
Thanks Kyte for setting it straight.
Anurag
"Thomas Kyte" <tkyte_at_oracle.com> wrote in message
news:afuqbf0s4f_at_drn.newsguy.com...
> 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
>
Received on Wed Jul 03 2002 - 08:59:50 CDT
