自己對oracle sql的一些優化總結自己也記錄下來也希望對大家有幫助
一使用where少使用having;
二查兩張以上表時把記錄少的放在右邊;
三減少對表的訪問次數;
四有where子查詢時子查詢放在最前;
五select語句中盡量避免使用*(執行時會把*依次轉換為列名);
六盡量多的使用commit;
七Decode可以避免重復掃描相同的記錄或重復連接相同的表;
八通過內部函數也可提高sql效率;
九連接多個表時使用別名並把別名前綴於每個字段上;
十用exists代替in
十一not exists代替 not in(not in 字句將執行一個內部的排序和合並任何情況下not in是最低效的子查詢中全表掃描了為了避免使用not in可以改寫成outer joins或not exists);
十二表連接比exists更高效;
十三用exists替換distinct
例
低 高
select distinct dept_no dept_name select dept_no dept_name
from dept d emp e from dept d
where ddept_no = edept_no; where exists (select from emp e where edept_no = ddept_no);
十四使用TKPROF工具來查詢sql性能狀態;
十五用索引提高效率(代價是索引需要空間而且定期重構索引很有必要ALTER INDEX REBUILD
先介紹下索引的原理方便接下來對索引的優化的理解
通過索引找到rowid然後通過rowid訪問表但如果查詢的列包括在index中將不在執行第二部操作因為檢索數據保存在索引中單單訪問索引就可以完全滿足查詢要求
前提提要在十六例中LODGING列有唯一索引;MANAGER列上有非唯一性索引
十六索引范圍查詢(INDEX RANGE SACEN):
適用於兩種情況
)基於一個范圍的查詢
SELECT LODGING FROM LODGING WHERE LODGING LIKE M%
(where字句條件包括一系列的值oracle將通過索引范圍查詢方式查詢LODGING_PK)
) 基於非唯一性索引的檢索
SELECT LODGING FROM LODGING WHERE MANAGER = LI;
(此查詢分兩步LODGING$MANAGER的索引范圍查詢得到所有符合條件記錄的rowid然後通過rowid訪問表得到LODGING列的值該索引為非唯一性索引數據庫不能對它執行索引唯一掃描)
where字句中如果索引列所對應的值的第一個字符由通配符開始索引將不被采用而會全表掃描如 SELECT WHERE MANAGER LIKE %LI
十七基礎表的選擇
基礎表最先訪問的表(通常以全表掃描的方式被訪問)
根據優化器的不同SQL語句中基礎表的選擇是不一樣的
如果使用CBO優化器會檢查SQL語句中的每個表的物理大小索引的狀態然後選用話費最低的路徑
如果使用RBO並且所有的連接條件都有索引對應這種情況下基礎表就是FROM字句中列在最後的表
例
SELECT ANAME BMANAGER FROM WOKER A LODGING B WHERE ALODGING = BLODGING;
由於LODGING列上有一個索引而且WORKER表中沒有相比較的索引WORKER表將被作為查詢基礎表
十八多個平等的索引
當SQL語句的執行路徑可以使用分布在多個表上的多個索引時oracle會同事使用多個索引並在運行時對它們的記錄合並檢索僅對全部索引有效的記錄
oracle選擇執行路徑是唯一索引等級高於非唯一索引只有當where字句中索引列和常量比較才有效如果索引列和其它表的索引列相比較這種字句在優化器中等級非常低;
如果不同表中兩個相同等級的索引將被引用根據FROM字句中表的順序決定哪個先被使用FROM字句中最後的表索引優先級高如果相同表中兩個相同等級的索引將被引用where字句中最先被引用的索引將有最高的優先級
例DEPTNO上有非唯一性索引EMP_CAT也有非唯一性索引
SELECT ENAME FROM EMP WHERE DEPT_NO = AND EMP_CAT = A;
DEPTNO索引將被先檢索然後同EMP_CAT索引檢索出的結果合並執行路徑如下
TABLE ACCESS BY ROWID ON EMP
AND _EQUAL
INDEX RANGE SCAN ON DEPT_IDX
INDEX RANGE SCAN ON CAT_IDX
十九等式比較與范圍比較
先上例子
SELECT ENAME FROM EMP WHERE DEPT_NO > AND EMP_CAT = A;
(在兩個非唯一性索引前提下)此時范圍索引不被使用通過EMP_CAT索引查詢出記錄再與DEPT_NO條件進行比較
注意唯一性所以做范圍比較時等級要比非唯一性索引的等式比較低;
二十強制索引失效
如果兩個或兩個以上索引具有相同的等級可以強制命令oracle優化器使用其中的一個 那何時使用此種策略呢?如果一個索引已接近於唯一而另一索引有很多重復的值排序與合並反而會成為負擔此時可以屏蔽後者使其索引失效
(失效方式對索引列加入計算+或||"");
From:http://tw.wingwit.com/Article/program/Oracle/201311/19042.html
