Oracle SQL最佳化總結

***  SQL 最佳化也做了一段時間了，特此總結本人在工作中遇到的各個點，見笑了~

一、縮小範圍    減少額外io/cpu/物理/邏輯等效能消耗

①、縮小範圍(全掃能否加過濾條件’最好是索引條件’)；

②、分割槽表儘量加分割槽條件

執行計劃區別：(PARTITION RANGE ALL分割槽全掃)  (PARTITION RANGE SINGLE分割槽範圍)”

③、是否存在沒意義的關聯條件
例如1：A.id in (Select id from a1) ① and A.id in (Select id from a1 where id=1)②

      此時可以註釋掉①部分避免沒必要的關聯和查詢。

例如2：業務功能類似，功能1 需要範圍abcde5張表，功能2範圍abc3張表；

排除的表/欄位都等需求都相同，開發可能為了省事改寫，直接將功能 2 sql直接使用功能1 的sql；

最終最外層將不需要欄位篩選掉。

④、left join 確認能否改成join

left join 關聯方式 存在選錯驅動表被驅動表,某些時候可能還會存在量小應該走索引時不走索引。

且產生大量不必要的中間結果，產生大量物理/邏輯讀；Join可以過濾資料(過濾條件)；

二、執行計劃

①、小表/小分割槽 變大表/大分割槽，不統計引起的 執行計劃錯誤問題；

②、特殊情況可加hint （driving_site、full、no_index、use_hash ..）語句加hint需要和dba協商

資料庫統計資訊沒問題時，大多數沒特殊情況都是不需要加hint指定執行計劃的；

③、善於用繫結變數；

④、使用標量子查詢要量力而行；

⑤、能傳入定值部分儘量傳入定值

若某欄位傳一定值，凡是用到這個傳定值欄位的部分都儘量直接傳值 (例如：關聯條件，group by等)

oracle在生成執行計劃時要透過關聯條件等衡量2個表哪個驅動被驅動表，若直接寫定值，

資料庫會更準確的選擇最好的執行計劃去資料庫中讀取資料，group by 排序部分為了減少資源消耗。

執行計劃出現如下情況需要嚴重注意的：

①、CARTESIAN，笛卡爾乘積，需要確認是否有落下的表沒有和其他表做關聯；

②、filter，驅動表量越大越會引起效能問題；

③、Nest loop 驅動表或被驅動表全掃時
最糟糕的情況：驅動表和被驅動表都是全掃，且隨著量的增加後續會存在效能問題..

大表做迴圈--&gt加索引

選擇性很差的索引迴圈--&gt刪索引

--&gt2大表做nest loop迴圈時，需要衡量使用頻繁度且儘量和dba協商，來適當加索引；

--&gtnest loop是選擇性很差(distinct key 很小)的索引(且執行時間較慢)時，需要dba分析該索引；

若刪除索引，建議做個統計確認這幾個月是否真的沒人用再進行刪除；

參考部落格：http://blog.itpub.net/28602568/viewspace-1362044/

三、經驗點

①、update 和merge的選擇參考 Oracle 如何選擇update、merge 

②、去重 distinct、group by 的選擇；

子查詢裡包含group by 可能存在子查詢外層資料量很小傳不到子查詢內層的情況；參考Oracle 針對子查詢裡有group by 表全掃的最佳化 

③、不要使用(id = 1 or in 子查詢 )形式

原因：執行計劃會選擇filter，驅動表過濾後條目很多的話，被驅動表會產生熱點塊;

部落格參考：Oracle 'or exists/in'結合使用引起的filter執行計劃 的最佳化

④、exists 和 in 的選擇(避免filter)

exists 比 in 產生filter執行計劃機率大，所以當sql慢，且按F5發現執行計劃中有filter 可以試著改成in，
若出現in 子查詢裡表全掃或者還存在慢的情況，可以考慮將in (子查詢) A與外層B做join關聯，
若還存在問題，確認是否存在本文提到的情況，針對整體衡量去最佳化。
補充：[not] exists 子查詢裡不存在與外層關聯的條件，最終結果有無資料的現象   
參考部落格：http://blog.itpub.net/28602568/viewspace-1666675/

⑤、分頁：rownum和row_number的選擇

部落格參考：Oracle rownum 分頁引起的效率問題及最佳化思路

⑥、適當選擇使用rowid...

部落格參考：Oracle 透過rowid秒優SQL

⑦、儘量不寫is null 等會引起不走索引的條件，“索引不記錄空值”

若欄位有nvl函式索引可以寫NVL(欄位, 0)）=0來當做is null判斷，可走函式索引

⑧、程式碼If count >0.. 做判斷時，若rownum=1能查出資料就滿足count>0
就不必全部取出再判斷count>0...所以這樣的判斷可以統一加 rownum=1
全部做更改後dba需要跟蹤sql確認是否有存在rownum缺點引起的問題’
Oracle rownum 分頁引起的效率問題及最佳化思路
mysql 使用limit 1，oracle 使用rownum=1 來取資料中一行。

⑨、 合理使用臨時表解決大量下載功能

 Oracle SQL最佳化了解業務多重要 http://blog.itpub.net/28602568/viewspace-1685600/

⑩、 臨時用的表： 臨時屬性表？普通表？

臨時表：不可統計，create/insert大量後，再呼叫臨時表可能存在選擇索引情況引起效能問題可能；

普通表：create as select ... 不統計，再呼叫時，資料庫會使用動態取樣去產生正確的執行計劃；若是insert需要及時統計，否則可能會存在和臨時表的問題。

  【源於本人筆記】 若有書寫錯誤，表達錯誤，請指正...