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.

Now, if you want the data to come out in a particular order, you have to put in an order by clause (and leave it up to the optimizer to decide how to implement that order by) but it’s an interesting question and it’s good to be 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

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

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.