Kbase P128428: Oracle 10G R2 Sorts in Different Order than Oracle 9i
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
7/2/2008 |
|
Status: Verified
SYMPTOM(s):
Oracle 10G sorts in different order than Oracle 9i
Oracle 10G R2 sorts record set by "order by" clause
Oracle 7.8 and 9i sorts record sets by the first field of primary index of the table
The sorting differences are affecting query display order
Application code that depend on Oracle's sorting behavior displays wrong order
FACT(s) (Environment):
All Supported Operating Systems
Oracle DataServer
Progress 9.1E
OpenEdge 10.1x
Oracle 10g
CAUSE:
This is Oracle issue. Oracle changed sorting behavior between Oracle 7, 8, 9 and Oracle 10
FIX:
To revert to old version behavior, use any of following approach:
All switches can be set either at session level or instance level
_newsort_enabled = false OR _gby_hash_aggregation_enabled = false (minimun version Oracle 10GR2)
OR
optimizer_features_enabled = 9.2.0 (for Oracle 10G)
Use "alter session set .." for session level setting, use send-sql for data server sessions
For example, session can be altered in SQLplus like following
SQL> alter session set "_newsort_enabled"=false;
SQL> alter session set "_newsort_enabled"=true;
If changes are made in parameter file, instance needs to be restarted