Oracle Scratchpad

March 5, 2007

Ordering

Filed under: CBO,Execution plans,Performance,Tuning — Jonathan Lewis @ 1:37 pm GMT Mar 5,2007

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.

 

18 Comments »

  1. Will this close forever the discussion about the use of ORDER BY? ;-)

    I think not, you have left out the test case with the GROUP BY, and somebody for sure will claim that Oracle always order the result with GROUP BY…

    Thanks a lot for your precious effort!!!

    Comment by Franco — March 5, 2007 @ 2:12 pm GMT Mar 5,2007 | Reply

  2. I’ve heard that if the query is done in parallel, that can affect the order the records are returned in as well. Is that true?

    Comment by Mike McAllister — March 5, 2007 @ 2:25 pm GMT Mar 5,2007 | Reply

  3. Don’t forget indexes, multiple partition access, different versions, HINTS – what else?

    Comment by Glenn — March 5, 2007 @ 2:51 pm GMT Mar 5,2007 | Reply

  4. Group by can or can NOT do order by.

    In Oracle 10 there’s a new group by mode (group by hash if I remember well) that made order by less sure…I’m sure about that :D

    Comment by Antonio — March 5, 2007 @ 3:40 pm GMT Mar 5,2007 | Reply

  5. Mike, parallel query was, indeed, one of the easy options for showing that you can’t guarantee a specific order without including an explicit order by.

    Antonio, just to make life interesting, 10g can even eliminate an order by in a non-mergeable view if the optimizer thinks it will not change the result set.

    Comment by Jonathan Lewis — March 5, 2007 @ 5:39 pm GMT Mar 5,2007 | Reply

  6. I believe there’s one exception, is there not? A sorted hash cluster will (if I remember the documentation well enough!) return rows in the order of its SORT BY clause, without an ORDER BY clause being mentioned at query time.

    Comment by dizwell — March 5, 2007 @ 9:55 pm GMT Mar 5,2007 | Reply

  7. Howard, there is some truth in that statement – although it may not be entirely safe. My memory of the marketing was that the structure of the sorted hash cluster was supposed to ensure that inserts and queries would operate very efficiently without needing to perform an explicit sort to retrieve the data in order.

    However my tests with a 10032, repeated a few moments ago, showed Oracle sorting data which was not in order to produce a sorted output whether or not I used an order by clause. So possibly there’s something a little underhand and dangerous going on there at present. I’d still put the clause in – whatever the marketingn literature might say.

    Comment by Jonathan Lewis — March 6, 2007 @ 1:05 am GMT Mar 6,2007 | Reply

  8. The Oracle manual documentation for the SELECT statement saids: Use the ORDER BY clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.

    So without an ORDER BY clause the order of the rows may change and is not specified. The only way to guarantee the order of the rows is by using the ORDER BY clause.

    Comment by DSanchez — March 6, 2007 @ 1:27 am GMT Mar 6,2007 | Reply

  9. DSanchez, I don’t think you have to convince anyone who knows SQL that an order by clause is necessary to guarantee correct ordering – but I believe Howard could have been thinking of this descriptive comment from the section about sorted hash clusters in the 10.2 Database Admin Guide B-14231-01, p. 19-4:

    Given the sort order of the data, the following query would return the call records for a specified hash key by oldest record first.

    SELECT * WHERE telephone_number = 6505551212;

    It’s always a little irritating when manuals contradict each other.

    Comment by Jonathan Lewis — March 6, 2007 @ 3:09 am GMT Mar 6,2007 | Reply

  10. I think it’s a good idea to include “select * ” from both tables at the beginning of your examples so that we could see the “raw order” of the rows. Although the raw order cann be guessed by the create statements, for me it would be good to see it – and it would make the point more clear.

    Comment by Todor Botev — March 6, 2007 @ 8:55 am GMT Mar 6,2007 | Reply

  11. Todor, I think you’re right – I’ve made the change, and also given the selected columns aliases to identify which table they cam from, and the visual impact is better.

    Comment by Jonathan Lewis — March 6, 2007 @ 10:32 am GMT Mar 6,2007 | Reply

  12. Thanks Jonathan – I think, too, that it’s better now.

    Comment by Todor Botev — March 6, 2007 @ 11:18 am GMT Mar 6,2007 | Reply

  13. I need to ask that :) – why did you add the other two columns – small_vc, padding – to the table t1? They seem to have nothing to do with the point. Just to simulate better a real situation – bacause we rarely have tables with only two columns in reality?

    Comment by Todor Botev — March 6, 2007 @ 11:49 am GMT Mar 6,2007 | Reply

  14. Todor, it’s a side effect of having a standard template for test cases. As you guessed, better simulation for general testing.

    Comment by Jonathan Lewis — March 6, 2007 @ 1:22 pm GMT Mar 6,2007 | Reply

  15. Jonathan, the entry about sorted hash clusters in the 10.2 Database Admin Guide B-14231-01, p. 19-4 has been reported as a documentation bug. See following discussion:

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:74320098178823#74643668232655

    Comment by DSanchez — March 6, 2007 @ 11:57 pm GMT Mar 6,2007 | Reply

  16. Todor/Jonathan,

    Even the concept of “raw order” seems dangerous, as that is often what people think they’re going to get from a single table FTS and what they think is equivalent to the order in which rows were inserted — I’m sure that someone could knock up an easy demonstration of this as fallacious reasoning, based on parallel query for example :D

    Comment by David Aldridge — March 9, 2007 @ 4:48 pm GMT Mar 9,2007 | Reply

  17. David,

    Here’s an example:

    create tablespace test 
    datafile 'E:\Oracle\Bases\MIK8\TESTS_01.DBF' size 10m,
             'E:\Oracle\Bases\MIK8\TESTS_02.DBF' size 10m
    default storage (initial 20k next 20k pctincrease 0);
    drop table test;
    create table test (id number, val char(2000)) parallel 3 tablespace test;
    alter table test allocate extent (datafile 'E:\Oracle\Bases\MIK8\TESTS_02.DBF');
    alter table test allocate extent (datafile 'E:\Oracle\Bases\MIK8\TESTS_01.DBF');
    alter table test allocate extent (datafile 'E:\Oracle\Bases\MIK8\TESTS_01.DBF');
    alter table test allocate extent (datafile 'E:\Oracle\Bases\MIK8\TESTS_02.DBF');
    desc test
    begin
      for i in reverse 0..25 loop
        insert into test values (i, chr(ascii('A')+i));
      end loop;
    end;
    /
    commit;
    
    SQL> select extent_id, file_id, block_id, blocks from dba_extents 
      2  where owner=user and segment_name='TEST';
     EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
    ---------- ---------- ---------- ----------
             0          7          2          5
             1          7          7          5
             2          6          2          5
             3          6          7          5
             4          7         12          5
             5          6         12          5
    
    6 rows selected.
    
    SQL> select id, 
      2         dbms_rowid.rowid_relative_fno(rowid) file#,
      3         dbms_rowid.rowid_block_number(rowid) block#,
      4         dbms_rowid.rowid_row_number(rowid) row#
      5  from test
      6  /
            ID      FILE#     BLOCK#       ROW#        ExtNb BlockNb BlocksInExtent
    ---------- ---------- ---------- ----------        ----- ------- --------------
            16          6          2          0     --   2      0          5
            14          6          4          0     --   2      2          5
            13          6          5          0     --   2      3          5
            22          7          6          0     --   0      4          5
            20          7          8          0     --   1      1          5
            10          6          8          0     --   3      2          5
            19          7          9          0     --   1      2          5
             7          6         11          0     --   3      4          5
             1          6         12          0     --   5      0          5
             0          6         13          0     --   5      1          5
             3          7         15          0     --   4      1          5
            25          7          3          0     --   0      1          5
            24          7          4          0     --   0      2          5
            23          7          5          0     --   0      3          5
            21          7          7          0     --   1      0          5
            18          7         10          0     --   1      3          5
             6          7         12          0     --   4      0          5
             5          7         13          0     --   4      1          5
             4          7         14          0     --   4      2          5
            15          6          3          0     --   2      1          5
            12          6          6          0     --   2      4          5
            11          6          7          0     --   3      0          5
             9          6          9          0     --   3      2          5
             8          6         10          0     --   3      3          5
            17          7         11          0     --   1      4          5
             2          7         16          0     --   4      4          5
    
    26 rows selected.
    
    SQL> /
            ID      FILE#     BLOCK#       ROW#
    ---------- ---------- ---------- ----------
            16          6          2          0
            14          6          4          0
            13          6          5          0
            21          7          7          0
             8          6         10          0
            17          7         11          0
             0          6         13          0
             2          7         16          0
            15          6          3          0
            12          6          6          0
            11          6          7          0
             9          6          9          0
            18          7         10          0
             6          7         12          0
             4          7         14          0
             3          7         15          0
            25          7          3          0
            24          7          4          0
            23          7          5          0
            22          7          6          0
            20          7          8          0
            10          6          8          0
            19          7          9          0
             7          6         11          0
             1          6         12          0
             5          7         13          0
    
    26 rows selected.
    
    SQL> /
            ID      FILE#     BLOCK#       ROW#
    ---------- ---------- ---------- ----------
            25          7          3          0
            24          7          4          0
            23          7          5          0
            20          7          8          0
             1          6         12          0
             0          6         13          0
            16          6          2          0
            13          6          5          0
            22          7          6          0
            21          7          7          0
             9          6          9          0
             7          6         11          0
            18          7         10          0
             6          7         12          0
             4          7         14          0
            15          6          3          0
            14          6          4          0
            12          6          6          0
            11          6          7          0
            10          6          8          0
             8          6         10          0
            19          7          9          0
            17          7         11          0
             5          7         13          0
             3          7         15          0
             2          7         16          0
    
    26 rows selected.
    

    Regards

    Comment by Michel Cadot — March 9, 2007 @ 7:20 pm GMT Mar 9,2007 | Reply

  18. […] answer to the second question could be in an observation I first published 14 years ago – and it could indicate a generic threat to adaptive […]

    Pingback by Adaptive Study | Oracle Scratchpad — October 11, 2021 @ 11:57 am BST Oct 11,2021 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

%d bloggers like this: