Re: Sort Order in Nested query
- From: Gints Plivna <gints.plivna@xxxxxxxxx>
- To: ftaheny@xxxxxxxxx
- Date: Fri, 1 Feb 2013 11:14:02 +0200
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@xxxxxxxxx>
>
> *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?*
> **
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
