Re: order by question

From: Laurenz Albe <invite_at_spam.to.invalid>
Date: 24 Oct 2008 08:28:15 GMT
Message-ID: <1224836892.866728@proxy.dienste.wien.at>

sbrkic_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 Received on Fri Oct 24 2008 - 03:28:15 CDT