9i與11g在SQL中有使用disticnt的處理方式不同

因為Database的版本由9i升級為11g,在測試的過程中發現某些報表在9i與11g顯示出來的排序不一樣。
但是報表使用的SQL及程式都是一樣的,在回報顧問之後,得知SQL中有使用distinct後,但是9i與11g在處理上有不一樣的地方。
而11g有一個參數optimizer_features_enable可以改為9i的處理方式,但是多少會影響效能,測試步驟如下:

11g預設的值
SQL> select name,value from v$parameter where name='optimizer_features_enable';

NAME                                VALUE
----------------------------------- --------------------
optimizer_features_enable           11.2.0.4


執行下列SQL後可以發現資料並未排序,執行計劃可以知道11g使用HASH (UNIQUE)
SQL> select distinct a from tmp_ddd where rownum<=10;

A
------------------------------------------------------------------------------
703-264
703-272
703-273
703-438
703-398
703-265
703-262
703-263
703-437
703-274

10 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=10 Bytes=80
          )

   1    0   HASH (UNIQUE) (Cost=4 Card=10 Bytes=80)
   2    1     COUNT (STOPKEY)
   3    2       TABLE ACCESS (FULL) OF 'TMP_DDD' (TABLE) (Cost=3 Card=
          337 Bytes=2696)

修改optimizer_features_enable為9.2.0
SQL> alter system set optimizer_features_enable='9.2.0';

System altered.

查詢設定是否成功
SQL> select name,value from v$parameter where name='optimizer_features_enable';

NAME                                VALUE
----------------------------------- --------------------
optimizer_features_enable           9.2.0

再執行相同的SQL,可以發現資料已自動排序,執行計畫顯示為SORT (UNIQUE)
SQL> select distinct a from tmp_ddd where rownum<=10;

A
-------------------------------------------------------------------------------
703-262
703-263
703-264
703-265
703-272
703-273
703-274
703-398
703-437
703-438

10 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=10 Bytes=80)
   1    0   SORT (UNIQUE) (Cost=49 Card=10 Bytes=80)
   2    1     COUNT (STOPKEY)
   3    2       TABLE ACCESS (FULL) OF 'TMP_DDD' (TABLE) (Cost=2 Card=
          337 Bytes=2696)

Oracle DB的listener.log清理