Re: order by question

<sbrkic_at_yahoo.com> wrote in message
news:371ad5d2-bd5f-4a41-8ef7-622122dec18a_at_k13g2000hse.googlegroups.com... 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.

There is discussion of this on ask tom. Tom shows how changing statistics (and changing Oracle versions) can change the ordered by accident to a different "order".
Jim Received on Fri Oct 24 2008 - 05:44:31 CDT