Never rely on an assumed order

February 16, 2016

1–2 minutes

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

5 responses to “Never rely on an assumed order”

Akash Jain

December 23, 2018 at 1:53 am

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);

Reply
Connor McDonald

December 23, 2018 at 6:56 am

Yes, because that is a special optimization we used for sorted subsets

Reply
Rich Soule

February 17, 2020 at 10:58 pm

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”.

Reply
1. Connor McDonald
  
  February 17, 2020 at 11:11 pm
  
  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)
  
  Reply
  1. Rich Soule
    
    February 18, 2020 at 1:34 pm
    
    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?
    
    Reply