Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Complex ORDER BY clauses
"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
news:1068937317.663013_at_yasure...
| Matthias Wirtz wrote:
|
| > "Daniel Morgan" <damorgan_at_x.washington.edu> schrieb im Newsbeitrag
| > news:1068849441.169822_at_yasure...
| >
| >>mcstock wrote:
| >
| >
| >>>| SELECT test_col
| >>>| FROM (
| >>>| SELECT test_col
| >>>| FROM t
| >>>| ORDER BY test_col)
| >>>| ORDER BY DECODE(test_col, 'B', 1, 'E', 2, 99);
| >
| >
| >>>why the double order by?
| >
| >
| >>The original wanted everything ordered with B and E pulled out. The
| >>entire set must be ordered first. The outer query only changes the
| >>value of the two selected values.
| >
| >
| > I understand what you are trying with this inner query, but this is not
| > working for me.
| >
| > I get the same result when I skip the inner query. B and E are fine but
the
| > rest is not sorted alphabetically.
| >
| > Explain plan states clearly the two different plans: with the above
query I
| > get two sort operations. If I skip the inner query I get only one sort
| > operation. But the results are looking the same. Doesn't make sense to
me.
| > --
| > Matthias Wirtz - Norfolk, USA
|
| Because most likely you entered ordered information.
| Enter it disordered. Or more like the real world ...
| enter is disordered and then perform multiple updates.
|
| Then try the statements. The ORDER BY is essential.
|
| Or at least some operation that implicitly orders such
| as a GROUP BY clause.
| --
| Daniel Morgan
| http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
| http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
| damorgan_at_x.washington.edu
| (replace 'x' with a 'u' to reply)
|
daniel, the confusion that you've introduced is you are advocating a subquery with an order by, then a second order by in the outer query, and relying on oracle not to change the order of rows that meet the default case in the DECODE order by ....
you're doing two order by's (two sort operations) when one will suffice, for no apparent advantage...
SQL> SELECT test_col
2 FROM (
3 SELECT test_col 4 FROM t 5 ORDER BY test_col)
T
-
B
E
A
C
D
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (ORDER BY)
2 1 VIEW 3 2 SORT (ORDER BY) 4 3 TABLE ACCESS (FULL) OF 'T'
Statistics
0 recursive calls 4 db block gets 1 consistent gets 0 physical reads 0 redo size 518 bytes sent via SQL*Net to client 425 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 5 rows processed
SQL> select test_col
2 from t
3 order by DECODE(test_col, 'B', 1, 'E', 2, 99), test_col;
...
T
-
B
E
A
C
D
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 TABLE ACCESS (FULL) OF 'T'
Statistics
0 recursive calls 4 db block gets 1 consistent gets 0 physical reads 0 redo size 518 bytes sent via SQL*Net to client 425 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 5 rows processedReceived on Sat Nov 15 2003 - 18:59:48 CST