[SQL Tune] 用 Oracle Constraint 改善 Query 效能

多數人在 tune SQL 採用的方式多從 Index 或 Join的方式著手. Oracle Optimizer在決定一個 Execution Plan時會有以下的考量:

1. The query to optimize
2. All available database object statistics
3. System statistics(CPU, I/O,..)
4. Initialization parameters
5. Constraints

看到第五項嗎? 原來Constraint也是Oracle在決定Execution Plan時的考慮因素之ㄧ. Constraint對多數人而言是確保資料一致性的最後一道關卡, 但對SQL效能而言也有一定幫助, 且看以下說明:

Constraint --> Check

當SQL遇到 Check constraint時, 若 Check有限制某欄位的值而查詢條件又恰好有該值, 這時Oracle就會根據 Check的限制調整實際SQL的 Execution Plan.

實際範例可參閱Oracle 雜誌 5/6月 Asktom的文章.

http://www.oracle.com/technology/oramag/oracle/09-may/o39asktom.html

這部分在真實狀況下發生的機會較低, 因此不多說明.

Constraint --> NOT NULL

這個情況比較清楚, 就是當某欄位有Index, 但允許空值, 此時空值部份不會被存入 Index中. 但若是該欄位設定為 Not Null, 則當此類SQL(Select Count(*) from Table1 t) 時, Oracle 會很聰明的選取"Index Fast Full Scan"而不用"Full Table Scan", 效能就有一定的提升, 因為該欄位的所有值都對被存入 Index中. 有關"Index Fast Full Scan", 可參閱以下文章.

http://oracle-wei.blogspot.com/2009/03/sql-tune-index-access-methods.html

Constraint --> Primary Key/Foreign Key

這部份對效能有更顯著的提升, 因為 Oracle做的是 Table Elimination, 就是將執行 SQL所需的 Table刪除. 甚麼? 我也是第一次聽到耶! 比較實際的例子用以下說明:

Master Table --> M1 (M_Key, M_Value)

Detail Table --> D1 (M_Key)

有一個SQL如下:
select Sum(M_Value)
from M1, D1
where M1.M_Key=D1.M_Key

可能 Oracle的 Execution Plan是 "Hash Join"就是將 Master和 Detail做 Join後去得到結果.

但如果將 M_Key 加成 D1對M1的 Foreign Key, 而且 M_key為 M1的 Primary Key. 則方才 SQL的 Execution Plan可能會換成只對M1的存取. 因為以實際而言, Ｄ1 的存取並不需要, 只是 Constraint確定了這一件事情. 讓 Oracle 放手去做.

不好意思, 偷懶一下這次沒有用自己跑的實例說明, AskTom的說明應該夠清楚也有說服力.