Skip to Content

Every now and then a new version of the database software comes along.
Upgrades of Oracle or MaxDB often include major improvements concerning stability and performance. These improvements can and should be read as: things run different now.
A common example of how different things work and a “classic” issue in support is the following: “We upgraded our database software and now the data is not returned in the same order as before.”

Of course, at first sight, the new and presumely better version of the database software “forgot” how to sort things correctly. Therefore a support message is opened.
But look (and think) again:
If the Statement, that is now giving back data in a different order, does not include the ORDER BY clause, than the database has done nothing wrong.

In each and every database manual and also in the sql standard you’ll find a hint to this:
Unless ORDER BY is supplied, the database does NOT guarantee the order of returned rows.
If your application logic relies on the order, then use ORDER BY.
And really that’s it! Period.
So why can this happen at all?
The answer to this is (most often): a change of data access paths.
Let’s make an example (this time with Oracle 10g):

1. We create two tables SMALL and BIG:

 

create table small as (select object_name from dba_objects);
create table big as (select object_name, object_type from dba_objects);

repeat this a few times:
insert into big (select * from big);
Result: there’s now a 1:m relationship between the object_name in SMALL and the object_name in BIG.

2. Create index on BIG and collect cbo statistics:
create index i_big on big (object_name);
analyze table small compute statistics;
analyze table big compute statistics for table for all indexes;

 

3. Let’s see which order the data has right now:
select b.* from small s, big b where s.object_name = b.object_name;

 

OBJECT_NAME          OBJECT_TYPE
-------------------- -----------
I_COBJ#              INDEX
C_FILE#_BLOCK#       CLUSTER
SEG$                 TABLE
I_UNDO1              INDEX
I_COL1               INDEX
I_IND1               INDEX
I_COL3               INDEX
I_CDEF2              INDEX
I_USER#              INDEX
[...]

So, this is not ordered anyhow…

4. How has this been when the rule based optimizer was used? The data was sorted back then!
select /*+ rule */ b.* from small s, big b where s.object_name = b.object_name;

OBJECT_NAME          OBJECT_TYPE
-------------------- --------------------
I_COBJ#              INDEX
I_COBJ#              INDEX
I_COBJ#              INDEX
I_COBJ#              INDEX
I_COBJ#              INDEX
I_COBJ#              INDEX
I_COBJ#              INDEX
I_COBJ#              INDEX
I_COBJ#              INDEX
I_COBJ#              INDEX
I_COBJ#              INDEX
I_COBJ#              INDEX
I_COBJ#              INDEX
I_COBJ#              INDEX
I_COBJ#              INDEX
I_COBJ#              INDEX
C_FILE#_BLOCK#       CLUSTER
C_FILE#_BLOCK#       CLUSTER
C_FILE#_BLOCK#       CLUSTER
C_FILE#_BLOCK#       CLUSTER
C_FILE#_BLOCK#       CLUSTER
C_FILE#_BLOCK#       CLUSTER
[...]

 

5. So why is that ? It’s all about access paths !
explain plan for
select /*+ rule */ b.*
from small s, big b
where s.object_name = b.object_name;
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------
Plan hash value: 1302638698
----------------------------------------------
| Id  | Operation                    | Name  |
----------------------------------------------
|  0  | SELECT STATEMENT             |       |
|  1  |  TABLE ACCESS BY INDEX ROWID | BIG   |
|  2  |   NESTED LOOPS               |       |
|  3  |    TABLE ACCESS FULL         | SMALL |
|* 4  |    INDEX RANGE SCAN          | I_BIG |
----------------------------------------------

With the rulebased optimizer the available index has been used to make up the join. As a coincidence the data has been delivered the way it was picked up: sorted in the index sort manner.

When the RBO-Usage vanished with Oracle 10g (at the latest), other join strategies have been considered:

explain plan for select  b.* from small s, big b where s.object_name = b.object_name;

select * from table(dbms_xplan.display);
------------------------------------
| Id  | Operation          | Name  |
------------------------------------
|   0 | SELECT STATEMENT   |       |
|*  1 |  HASH JOIN         |       |
|   2 |   TABLE ACCESS FULL| SMALL |
|   3 |   TABLE ACCESS FULL| BIG   |
------------------------------------

*) I omitted some information here to make the comparisation easier.

As you can see, now the index is not used at all. The join is done via a HASH join. This hash join does not take care about the way the data is sorted, but is pretty quick for the join itself.
The result is a quicker join but the “order” of rows has completely vanished.

6. Now, what do you guess will happen to the access path if we ask for a sorted result?

Index access? Guess again!

explain plan for select  b.* from small s, big b where s.object_name = b.object_name order by b.object_name;
-------------------------------------
| Id  | Operation           | Name  |
-------------------------------------
|   0 | SELECT STATEMENT    |       |
|   1 |  MERGE JOIN         |       |
|   2 |   SORT JOIN         |       |
|   3 |    TABLE ACCESS FULL| SMALL |
|*  4 |   SORT JOIN         |       |
|   5 |    TABLE ACCESS FULL| BIG   |
-------------------------------------

A SORT-MERGE-Join is done. This join method does not only care about the sort order, it relies on it.

As these examples are valid for Oracle the same effects can be demonstrated with different versions/releases of MaxDB (former SAP DB).

For the SQL-User (usually the application or report developer) knowing this effect should lead to the conclusio:

IF THE DATA SHOULD BE SORTED, ASK FOR IT. USE ORDER BY.
NO ORDER BY – NO GUARANTEED ORDER.

Best regards,  Lars

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. Anonymous
    During the migration process problems have been reported for this blog. The blog content may look corrupt due to not supported HTML code on this platform. Please adjust the blog content manually before moving it to an official community.
    (0) 
  2. Anonymous
    But check this sentence, I find it confusing, although I do get what you mean.
    “Unless ORDER BY is not supplied, the database does NOT guarantee the order of returned rows.”
    (0) 
    1. Lars Breddemann Post author
      Hi Dries,

      you’re right. Actually the sentence is wrong.
      It should have been:
      “Unless ORDER BY is supplied, the database does NOT guarantee the order of returned rows.”

      Some errors are somehow invisible to the one who made same.

      KR Lars

      (0) 
  3. Kumud Singh
    Hi Lars,
    Why these beautiful learning blogs are not published in ABAP section as well….

    I could see in the blog that its there in Netweaver platform only…

    Regards,
    Kumud

    (0) 
    1. Lars Breddemann Post author
      Hi Kumud,

      well, I guess I was rather db-focussed when I wrote this. Also, some of the former blog sections had been renamed/removed meanwhile.

      I’ll see what I can do to assign the ABAP section to those where it applies.

      Thanks for pointing out and best regards,
      Lars

      (0) 

Leave a Reply