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
