Re: Sort Order in Nested query

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Fri, 1 Feb 2013 11:14:02 +0200
Message-ID: <CAN2wOq3XRt0D6ApKk5KhcBfnR9AknAnXYmx=-ZUMQo7srJUzRA_at_mail.gmail.com>



Speeking about the working example below. Of course as per documentation "Without
an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order", on the other hand if you look at these two examples (differs only the last word desc, hint no_query_transformation to be sure that oracle builds the view for inline view):
1. select /*+ no_query_transformation */ a.* from (
 select user_id, created from dba_users
  order by created) a
order by created

2. select /*+ no_query_transformation */ a.* from (
 select user_id, created from dba_users
  order by created) a
order by created desc

Execution plan for the first one, the relevant part is (only ONE sort!):

   0 | SELECT STATEMENT             |
   1 |  VIEW                        |
   2 |   SORT ORDER BY              |
   3 |    VIEW                      | DBA_USERS

For the second one there are two sorts:

  0 | SELECT STATEMENT              |
  1 |  SORT ORDER BY                |
  2 |   VIEW                        |
  3 |    SORT ORDER BY              |
  4 |     VIEW                      | DBA_USERS

So it seems that for the first query oracle eliminated the second sort because it is sure that rows are returned in the correct order. On the other hand this is for Oracle Database 11g Release 11.2.0.1.0. and (most probably) noone knows what will be in the future.

Gints

2013/1/31 Fergal Taheny <ftaheny_at_gmail.com>

>
> *This seems to work*
>
> select seq.nextval, v.* from (select USER_ID,CREATED from dba_users order
> by CREATED) v;
>
> NEXTVAL USER_ID CREATED
> ---------- ---------- ---------------
> 1 5 29-DEC-06 20:17
> 2 0 29-DEC-06 20:17
> 3 11 29-DEC-06 20:18
> 4 19 29-DEC-06 20:29
> 5 21 29-DEC-06 20:46
> 6 24 29-DEC-06 20:55
> 7 25 29-DEC-06 21:00
> 8 34 29-DEC-06 21:20
> 9 35 29-DEC-06 21:21
> *But can I rely on this always sorting on the created column (in different
> versions)? Or is this just a fluke?*
> *Is there a better way to do this?*
> **
>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Feb 01 2013 - 10:14:02 CET

Original text of this message