Re: order by question

On 24 Okt, 10:28, Laurenz Albe <inv..._at_spam.to.invalid> wrote:
> sbr..._at_yahoo.com wrote:
> > I wonder if following result set would be sorted according to x.z. Or
> > is it possible that the optimizer for some reason, based on the outer
> > conditions, decides to get the result unsorted because it would be
> > more performant.
>
> > select sub.*
> >  from (select x from y order by x.z) sub
> > where ...
> >    and ...
> >    and ...
> >    and rownum < 10;
>
> > If this works the (select x from y order by x.z ) would be a view.
>
> > I have not been able to get a resultset which is unsorted acording to
> > x.z but need to know that this would not happen in a productive
> > environment.
>
> I couldn't find a guarantee for that in the documentation, and so I
> wouldn't rely on it.
>
> I would add an additional "order by" at the end of the statement.
>
> Of course there is reason to worry that there will be an additional sort
> operation which would negatively affect performance, so I looked at
> the explain plan for
>
> SELECT *
> FROM (SELECT vorname, nachname
>       FROM person
>       ORDER BY nachname) sub
> WHERE rownum < 4
> ORDER BY nachname
>
> on my system, and I got:
>
> ---------------------------------------------------------------------------­---
> | Id | Operation               | Name   |Rows |Bytes | Cost (%CPU)| Time     |
> ---------------------------------------------------------------------------­---
> |  0 | SELECT STATEMENT        |        |   3 |  372 |     4  (25)| 00:00:01 |
> |* 1 |  COUNT STOPKEY          |        |     |      |            |          |
> |  2 |   VIEW                  |        |   5 |  620 |     4  (25)| 00:00:01 |
> |* 3 |    SORT ORDER BY STOPKEY|        |   5 |   80 |     4  (25)| 00:00:01 |
> |  4 |     TABLE ACCESS FULL   | PERSON |   5 |   80 |     3   (0)| 00:00:01 |
> ---------------------------------------------------------------------------­---
>
> So it seems that the optimizer is smart enough to remember that the rows
> are already sorted and doesn't sort them again.
>
> Yours,
> Laurenz Albe- Dölj citerad text -
>
> - Visa citerad text -

My previous comment was not intended for you Laurenz. I have been looking at exactly this kind of stuff and been trying to different kind of test but have not been able to produce a "false" order. Thanks for your help. Received on Fri Oct 24 2008 - 03:59:21 CDT