Ordering

Here’s a recent question from comp.databases.oracle.server:

Is there any documentation available on the default query result ordering in the absence of an ORDER BY clause? I looked through the Oracle docs and could not find much. I do not plan to depend on this behavior but I am curious on how it works.

If you want the data to come out in a particular order your SQL has to include an order by clause (and it’s up to the optimizer to decide how to make that ordering happen); but this is an interesting question and it’s good to be (a little bit) curious, so I thought I’d run up a two-table demonstration in 9i showing how the order of the result set can be affected by the join mechanism used to join the tables.

rem
rem     Script:         join_effect.sql
rem     Author:         Jonathan Lewis
rem     Dated:          March 2007
rem
rem     Last tested:
rem             21.3.0.0
rem             19.11.0.0
rem             9.2.0.8
rem

create table t1
as
select
        rownum                  id,
        mod(rownum + 2,4)       n1,
        lpad(rownum,10,'0')     small_vc,
        rpad('x',100)           padding
from
        all_objects
where   rownum <= 4 -- > comment to avoid wordpress format issue
;

/*
        ID         N1 SMALL_VC   PADDING
---------- ---------- ---------- -------
         1          3 0000000001 x
         2          0 0000000002 x
         3          1 0000000003 x
         4          2 0000000004 x
*/

create table t2
as
select * from t1
union all
select * from t1
union all
select * from t1
union all
select * from t1
;

/*
        ID         N1 SMALL_VC   PADDING
---------- ---------- ---------- -------
         1          3 0000000001 x
         2          0 0000000002 x
         3          1 0000000003 x
         4          2 0000000004 x
         1          3 0000000001 x
         2          0 0000000002 x
         3          1 0000000003 x
         4          2 0000000004 x
         1          3 0000000001 x
         2          0 0000000002 x
         3          1 0000000003 x
         4          2 0000000004 x
         1          3 0000000001 x
         2          0 0000000002 x
         3          1 0000000003 x
         4          2 0000000004 x
*/

-- collect statistics here

select
        /*+ ordered use_nl(t2) */
        /*+ ordered use_hash(t2) */
        /*+ ordered use_merge(t2) */
        t1.id   t1_id,
        t1.n1   t1_n1,
        t2.id   t2_id,
        t2.n1   t2_n1
from
        t1, t2
where
        t2.n1 = t1.n1
;

Obviously you should only implement one of the three sets of hints at a time. The three sets of output are as follows.

=========== 
Nested loop 
===========     

     T1_ID      T1_N1      T2_ID      T2_N1 
---------- ---------- ---------- ---------- 
         1          3          1          3 
         1          3          1          3 
         1          3          1          3 
         1          3          1          3 
         2          0          2          0 
         2          0          2          0 
         2          0          2          0 
         2          0          2          0 
         3          1          3          1 
         3          1          3          1 
         3          1          3          1 
         3          1          3          1 
         4          2          4          2 
         4          2          4          2 
         4          2          4          2 
         4          2          4          2     

16 rows selected.     

=========== 
Hash join 
===========     

     T1_ID      T1_N1      T2_ID      T2_N1 
---------- ---------- ---------- ---------- 
         1          3          1          3 
         2          0          2          0 
         3          1          3          1 
         4          2          4          2 
         1          3          1          3 
         2          0          2          0 
         3          1          3          1 
         4          2          4          2 
         1          3          1          3 
         2          0          2          0 
         3          1          3          1 
         4          2          4          2 
         1          3          1          3 
         2          0          2          0 
         3          1          3          1 
         4          2          4          2     

16 rows selected.     

=========== 
Merge join 
===========     

     T1_ID      T1_N1      T2_ID      T2_N1 
---------- ---------- ---------- ---------- 
         2          0          2          0 
         2          0          2          0 
         2          0          2          0 
         2          0          2          0 
         3          1          3          1 
         3          1          3          1 
         3          1          3          1 
         3          1          3          1 
         4          2          4          2 
         4          2          4          2 
         4          2          4          2 
         4          2          4          2 
         1          3          1          3 
         1          3          1          3 
         1          3          1          3 
         1          3          1          3     

16 rows selected. 

Note how the ordering for the nested loop is basically driven by the first table in the join; the ordering for the hash join is driven by the second table; and the order for the merge join is driven by the sort order of the join column(s).

If you think back to my notes on filter subqueries, you will realise that the change in join order could easily affect the number of times a filter subquery gets called.

Update Oct 2021

This came to my attention thanks to a question on Oracle-L where enabling adaptive plans in 19c resulted in one query take longer to start returning rows, and much longer to return the entire result set. Investigation of the SQL Monitor report suggested very strongly that:

• the delay in starting to return rows (the latency) was due to a nested loop join “adapting” to a hash join that took a couple of minutes to build a large hash table
• the increased time to return the final result set was due to the fact that the order of the rowsource produced by the hash join was different from the order produced by the nested loop, resulting in a much greater degree of randomness in the access to a table that appeared in a nested loop join several steps later in the plan – resulting in more “self-flushing” of table blocks compared to the “self-induced caching” that had appeared when the query had been using nested loops all down the plan.

I’ve rerun this little model on 19.11.0.0 and 21.3.0.0: the effect hasn’t changed.