Posted by We’ve (hopefully) all had it drummed into us enough times – you cannot assume the order of results from queries unless you explicitly include an ORDER BY statement.
Here’s another trivial example of this- I was doing a little demo script for an AskTom question.
Here’s the script running in 11.2.0.4
SQL> create table T
2 as
3 select
4 rownum c1,
5 mod(rownum,10) c2,
6 trunc(rownum/1000) c3,
7 mod(rownum,100) c4,
8 trunc(rownum/100) c5,
9 mod(rownum,1000) c6,
10 trunc(rownum/10) c7
11 from dual
12 connect by level <= 100;
Table created.
SQL>
SQL> begin
2 for i in 1 .. 7 loop
3 execute immediate 'create bitmap index IX'||i||' on T ( c'||i||')';
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL> select
2 index_name,
3 leaf_blocks,
4 avg_leaf_blocks_per_key,
5 avg_data_blocks_per_key
6 from user_indexes
7 where table_name = 'T';
INDEX_NAME LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
------------------------------ ----------- ----------------------- -----------------------
IX1 1 1 1
IX2 1 1 1
IX3 1 1 1
IX4 1 1 1
IX5 1 1 1
IX6 1 1 1
IX7 1 1 1
And here’s the same script running in 12.1.0.2
SQL> create table T
2 as
3 select
4 rownum c1,
5 mod(rownum,10) c2,
6 trunc(rownum/1000) c3,
7 mod(rownum,100) c4,
8 trunc(rownum/100) c5,
9 mod(rownum,1000) c6,
10 trunc(rownum/10) c7
11 from dual
12 connect by level <= 100;
Table created.
SQL>
SQL> begin
2 for i in 1 .. 7 loop
3 execute immediate 'create bitmap index IX'||i||' on T ( c'||i||')';
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL> select
2 index_name,
3 leaf_blocks,
4 avg_leaf_blocks_per_key,
5 avg_data_blocks_per_key
6 from user_indexes
7 where table_name = 'T';
INDEX_NAME LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
------------------------------ ----------- ----------------------- -----------------------
IX7 1 1 1
IX6 1 1 1
IX5 1 1 1
IX4 1 1 1
IX3 1 1 1
IX2 1 1 1
IX1 1 1 1
Learning is not a spectator sport 
Does selecting from an inner query which has an order by guarantee the final output to be sorted by the column in inner query?….
Ex-
Select * from ( select * from t order by n);
Yes, because that is a special optimization we used for sorted subsets
Of course UNION (but not UNION ALL), MINUS and INTERSECT are the three exceptions to needing an ORDER BY.
And, Connor, correct me if I’m wrong, but very technically Oracle *could* change the behavior of the query in Akash’s question, so it technically isn’t a “guarantee”.
None of UNION, MINUS and INTERSECT imply any sort of order. In particular, the ‘distinct’ operator that often gets used when evaluating these, could conceivably be a distinct-via-hash, and not distinct-via-sort.
(I’ll check up on the inline view order-by to see if we explicitly document it)
We’ll, the Oracle Education training materials (I teach from them) seem to imply differently… UNION, MINUS and INTERSECT are all always sorted by the first column by default unless you add an ORDER BY. I see this in practice, and in the materials. Are the materials wrong?