oracle資料庫sql的最佳化總結

 　　自己對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 d.dept_no = e.dept_no;                                                                                               where exists (select 1 from emp e where e.dept_no = d.dept_no);

　　十四：使用TKPROF工具來查詢sql效能狀態;

　　十五：用索引提高效率(代價是：索引需要空間，而且定期重構索引很有必要：ALTER INDEX REBUILD

　　先介紹下索引的原理，方便接下來對索引的最佳化的理解：

　　通過索引找到rowid，然後通過rowid訪問表。但如果查詢的列包括在index中，將不在執行第二部操作，因為檢索資料儲存在索引中，單單訪問索引就可以完全滿足查詢要求。

　　前提提要：在十六例中，LODGING列有唯一索引;MANAGER列上有非唯一性索引。

　　十六：索引範圍查詢(INDEX RANGE SACEN):

　　適用於兩種情況：

　　1)基於一個範圍的查詢：

　　SELECT LODGING FROM LODGING WHERE LODGING LIKE 'M%'

　　(where字句條件包括一系列的值，oracle將通過索引範圍查詢方式查詢LODGING_PK)

　　2) 基於非唯一性索引的檢索：

　　SELECT LODGING FROM LODGING WHERE MANAGER = 'LI';

　　(此查詢分兩步：LODGING$MANAGER的索引範圍查詢得到所有符合條件記錄的rowid，然後通過rowid訪問表得到LODGING列的值。該索引為非唯一性索引，資料庫不能對它執行索引唯一掃描)

　　where字句中，如果索引列所對應的值的第一個字元由萬用字元開始，索引將不被採用，而會全表掃描，如 SELECT..... WHERE MANAGER LIKE '%LI'

　　十七：基礎資料表的選擇：

　　基礎資料表：最先訪問的表(通常以全表掃描的方式被訪問)。

　　根據最佳化器的不同，SQL語句中基礎資料表的選擇是不一樣的：

　　如果使用CBO，最佳化器會檢查SQL語句中的每個表的物理大小，索引的狀態，然後選用話費最低的路徑。

　　如果使用RBO，並且所有的串連條件都有索引對應，這種情況下基礎資料表就是FROM字句中列在最後的表

　　例：

　　SELECT A.NAME, B.MANAGER FROM WOKER A, LODGING B WHERE A.LODGING = B.LODGING;

　　由於LODGING列上有一個索引，而且WORKER表中沒有相比較的索引，WORKER表將被作為查詢基礎資料表。

　　十八：多個平等的索引：

　　當SQL語句的執行路徑可以使用分布在多個表上的多個索引時，oracle會同事使用多個索引並在運行時對它們的記錄合并，檢索僅對全部索引有效記錄。

　　oracle選擇執行路徑是，唯一索引等級高於非唯一索引，只有當where字句中索引列和常量比較才有效。如果索引列和其它表的索引列相比較，這種字句在最佳化器中等級非常低;

　　如果不同表中兩個相同等級的索引將被引用，根據FROM字句中表的順序決定哪個先被使用。FROM字句中最後的表索引優先順序高。如果相同表中兩個相同等級的索引將被引用，where字句中最先被引用的索引將有最高的優先順序。

　　例：DEPTNO上有非唯一性索引，EMP_CAT也有非唯一性索引

　　SELECT ENAME FROM EMP WHERE DEPT_NO = 20 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 > 20 AND EMP_CAT = 'A';

　　(在兩個非唯一性索引前提下)此時範圍索引不被使用，通過EMP_CAT索引查詢出記錄再與DEPT_NO條件進行比較

　　注意：唯一性所以做範圍比較時，等級要比非唯一性索引的等式比較低;

　　二十：強制索引失效：

　　如果兩個或兩個以上索引具有相同的等級，可以強制命令oracle最佳化器使用其中的一個。 那何時使用此種策略呢?如果一個索引已接近於唯一，而另一索引有很多重複的值，排序與合并反而會成為負擔，此時可以屏蔽後者使其索引失效。

　　(失效方式：對索引列加入計算'+0'或'||""');