ORACLE SQL性能優化系列 （十三）

　　 用WHERE替代ORDER BY
　　ORDER BY 子句只在兩種嚴格的條件下使用索引
　　
　　ORDER BY中所有的列必須包含在相同的索引中並保持在索引中的排列順序
　　
　　ORDER BY中所有的列必須定義為非空
　　
　　WHERE子句使用的索引和ORDER BY子句中所使用的索引不能並列
　　
　　例如:
　　
　　　　　表DEPT包含以下列:
　　
　　　　　　DEPT_CODE PK NOT NULL
　　
　　　　　　DEPT_DESC NOT NULL
　　
　　　　　　DEPT_TYPE NULL
　　
　　　　　 非唯一性的索引(DEPT_TYPE)
　　
　　　　 低效: (索引不被使用)
　　
　　　　　　　　SELECT DEPT_CODE
　　
　　　　　　　　FROM DEPT
　　
　　　　　　　　ORDER BY DEPT_TYPE
　　
　　　　　EXPLAIN PLAN:
　　
　　　　　　　　SORT ORDER BY
　　
　　　　　　　　　　　TABLE ACCESS FULL
　　
　　　　 高效: (使用索引)
　　
　　　　　　　 SELECT DEPT_CODE
　　
　　　　　　　 FROM DEPT
　　
　　　　　　WHERE DEPT_TYPE >
　　
　　EXPLAIN PLAN:
　　
　　　　　TABLE ACCESS BY ROWID ON EMP
　　
　　　　　　　　 INDEX RANGE SCAN ON DEPT_IDX
　　
　　　　　ORDER BY 也能使用索引! 這的確是個容易被忽視的知識點 我們來驗證一下:
　　
　　SQL> select * from emp order by empno;
　　
　　Execution Plan
　　
　　
　　
　　　 SELECT STATEMENT Optimizer=CHOOSE
　　
　　　 TABLE ACCESS (BY INDEX ROWID) OF EMP
　　
　　　 INDEX (FULL SCAN) OF EMPNO (UNIQUE)
　　
　　 避免改變索引列的類型
　　當比較不同數據類型的數據時 ORACLE自動對列進行簡單的類型轉換
　　
　　假設 EMPNO是一個數值類型的索引列
　　
　　SELECT …
　　
　　FROM EMP
　　
　　WHERE EMPNO =
　　
　　實際上經過ORACLE類型轉換 語句轉化為:
　　
　　SELECT …
　　
　　FROM EMP
　　
　　WHERE EMPNO = TO_NUMBER()
　　
　　幸運的是類型轉換沒有發生在索引列上索引的用途沒有被改變
　　
　　現在假設EMP_TYPE是一個字符類型的索引列
　　
　　SELECT …
　　
　　FROM EMP
　　
　　WHERE EMP_TYPE =
　　
　　這個語句被ORACLE轉換為:
　　
　　SELECT …
　　
　　FROM EMP
　　
　　WHERE TO_NUMBER(EMP_TYPE)=
　　
　　因為內部發生的類型轉換 這個索引將不會被用到!
　　
　　為了避免ORACLE對你的SQL進行隱式的類型轉換 最好把類型轉換用顯式表現出來 注意當字符和數值比較時 ORACLE會優先轉換數值類型到字符類型
　　
　　 需要當心的WHERE子句
　　某些SELECT 語句中的WHERE子句不使用索引 這裡有一些例子
　　
　　在下面的例子裡 != 將不使用索引 記住 索引只能告訴你什麼存在於表中 而不能告訴你什麼不存在於表中
　　
　　不使用索引:
　　
　　SELECT ACCOUNT_NAME
　　
　　FROM TRANSACTION
　　
　　WHERE AMOUNT !=;
　　
　　使用索引:
　　
　　SELECT ACCOUNT_NAME
　　
　　FROM TRANSACTION
　　
　　WHERE AMOUNT >;
　　
　　下面的例子中 ||是字符連接函數 就象其他函數那樣 停用了索引
　　
　　不使用索引:
　　
　　SELECT ACCOUNT_NAMEAMOUNT
　　
　　FROM TRANSACTION
　　
　　WHERE ACCOUNT_NAME||ACCOUNT_TYPE=AMEXA;
　　
　　使用索引:
　　
　　SELECT ACCOUNT_NAMEAMOUNT
　　
　　FROM TRANSACTION
　　
　　WHERE ACCOUNT_NAME = AMEX
　　
　　AND ACCOUNT_TYPE= A;
　　
　　下面的例子中 +是數學函數 就象其他數學函數那樣 停用了索引
　　
　　不使用索引:
　　
　　SELECT ACCOUNT_NAME AMOUNT
　　
　　FROM TRANSACTION
　　
　　WHERE AMOUNT + >;
　　
　　使用索引:
　　
　　SELECT ACCOUNT_NAME AMOUNT
　　
　　FROM TRANSACTION
　　
　　WHERE AMOUNT > ;
　　
　　下面的例子中相同的索引列不能互相比較這將會啟用全表掃描
　　
　　不使用索引:
　　
　　SELECT ACCOUNT_NAME AMOUNT
　　
　　FROM TRANSACTION
　　
　　WHERE ACCOUNT_NAME = NVL(:ACC_NAMEACCOUNT_NAME);
　　
　　使用索引:
　　
　　SELECT ACCOUNT_NAME AMOUNT
　　
　　FROM TRANSACTION
　　
　　WHERE ACCOUNT_NAME LIKE NVL(:ACC_NAME%);
　　
　　如果一定要對使用函數的列啟用索引 ORACLE新的功能: 基於函數的索引(FunctionBased Index) 也許是一個較好的方案
　　
　　CREATE INDEX EMP_I ON EMP (UPPER(ename)); /*建立基於函數的索引*/
　　
　　SELECT * FROM emp WHERE UPPER(ename) = BLACKSNAIL; /*將使用索引*/
From:http://tw.wingwit.com/Article/program/Oracle/201311/17864.html