Oracle sql 最佳化の常用方式

標籤：

　　1、不要用 ‘*‘ 代替所有列名，特別是欄位比較多的情況下

　　使用select * 可以列出某個表的所有列名，但是這樣的寫法對於Oracle來說會存在動態解析問題。Oracle系統通過查詢資料字典將 ‘*‘ 轉換成表的所有列名，這自然會消耗系統時間。

　　2、進行全表刪除時，用truncate代替delete

　　當用delete刪除表的資料行時，Oracle會使用撤銷資料表空間(UNDO tablespace)來存放恢複的資訊。在這期間，如果使用者沒有發出commit語句，而是發出rollback語句，Oracle系統會將資料恢複到刪除之前的狀態，當使用者使用truncate對錶的資料進行刪除時，系統不會將被刪除的資料寫到復原段（撤銷資料表空間）裡，速度自然快些。

　　3、在確保完整性的情況下多使用commit

　　在PL/SQL塊中，經常將幾個相互聯絡的DML語句寫到一個BEGIN...END塊中，建議在每個DML語句結尾加上commit，這樣可以使用對象DML語句及時提交，同時也釋放事務所佔用的資源。

　　4、盡量減少表的查詢次數

　　在含有多個子查詢的sql中，要注意減少對錶的查詢。
　　樣本：

    --Inefficient sql    select tab_name1,tab_name2 from table_name     where tab_name1 = (select tab_n1 from table_n)       and tab_name2 = (select tab_n2 from table_n)    --Efficient sql    select tab_name1,tab_name2 from table_name     where (tab_name1,tab_name2)= (select tab_n1,tab_n2 from table_n)

　　5、使用exists代替in，使用not exists代替not in

　　在子查詢中，in使用一個內部的排序和合并，無論在哪種情況下，not in都是最低效的，因為它對子查詢中的表執行了全表遍曆，為了避免使用not in，我們可以把它改寫為外串連(outer join)或者是not exists。但並不代表not in 完全不可用，我們一分為二來看待此問題：若子查詢返回的資料集較複雜，避免使用not in；當子查詢返回的資料集較簡單或者可枚舉，not in也可以使用。

　　exists和in使用同理。

　　6、合理使用函數

　　不合理的函數使用方式會對資料庫造成嚴重的效能問題，即使一個非常簡單的函數，因為其使用不當，都可能造成嚴重的後果。

　　樣本：

    --Inefficient sql    for i in 0...10 LOOP      select count(*) into i_cnt from race_results      where horse_name = format_name(‘xiaobai‘);    end LOOP;    --Efficient sql    for i in 0...10 LOOP      select count(*) into i_cnt from race_results      where horse_name = (select format_name(‘xiaobai‘) from dual);    end LOOP;

　　當函數在where子句中使用時，查詢結果得到的每一行記錄均會調用該函數一次。

　　7、正確選擇from表順序

　　在基於規則的最佳化器(Rule-based Optimizer)下，Oracle解析器按照從右至左的順序處理from後的表名，因此寫在from中最後的表將會被先處理，先處理的表也稱為驅動表，在from子句中包含多個表的情況下，建議選擇資料量最少的表作為驅動表。原因：當Oracle處理多個表時，會運用排序或合并的方式串連它們。首先，系統掃描from中最後的表，並對該表的資料進行排序；然後，掃描from中倒數第二個表；最後，將所有從第二個表中檢索出來的記錄和第一個表中的合適記錄進行合并，取交集。

　　如果有三個以上的表串連，則建議使用交叉表(intersection table)作為驅動表，所謂交叉表指的是被其他表所引用的表。

　　8、where子句條件的串連順序

　　Oracle採用自下而上順序解析where子句，按照這個原理，表串連的條件必須寫在其他條件之前，那些能過濾掉最大記錄的條件必須寫在where子句的末尾，也就是表在串連操作以前，能過濾的資料量越大越好。

Oracle sql 最佳化の常用方式