Order Of Data Retrieval Differs after upgrading 9i To 10g

Applies to:
Oracle Server – Enterprise Edition – Version: 10.1.0.2 to 10.2.0.1
This problem can occur on any platform.

SQL output and the order of data

It is not unknown for SQL in 10g, using the same execution plan as previously in 9i, to return the data
in a different order for columns outside of the ORDER BY clause.
For instance , the following output is from SQL with an ORDER BY NAME clause :-

NAME SURNAME
—– ——-
George Bush
Tony Blair
Tony Book

It may well change its order to

NAME SURNAME
—– ——-
George Bush
Tony Book
Tony Blair

… after an upgrade. This can cause issues with customers and applications which were
reliant on the fixed order of SURNAME although it was not explicitly ordered.
Changes
Upgrade from 9.x to 10.x
Cause
This happens because Oracle conforms to the SQL STANDARD which states that only columns which have an explicit ORDER BY instruction are guaranteed to be correctly and consistently ordered.

This is also documented in the SQL Reference Manual:-

order_by_clause

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.

SOLUTIONS

It is important to realize that the behavior is not a BUG.
The solutions to the issue are:-

* Explicitly sort on columns using an ORDER BY to guarantee consistency across versions
* However, as a temporary workaround while the application is recoded correctly it may be possible to use the following parameter to revert to 9.x sorting behavior:

alter session set “_newsort_enabled”=false;

Advertisements

Leave a Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: