如何說服 Oracle 在帶有綁定變數的 SQL 查詢中創建最佳執行計劃？
嘗試在 Oracle 中使用綁定變數和 OR :B1 IS NULL 或 NVL(:B1,col1) 在 WHERE 子句中參數化查詢會導致執行計劃不理想。
例如，我想這樣寫 SELECT：
SELECT * FROM MY_TABLE WHERE (COL1 = :B1 OR :B1 IS NULL) AND (COL2 = :B2 OR :B2 IS NULL) AND (COL3 = :B3 OR :B3 IS NULL);它的執行計劃應該取決於綁定變數的值，記住 Oracle CBO 會為每個變數創建最佳執行計劃。
如以下執行計劃範例所示，使用 OR FROM NULL 或 NVL 會產生表級過濾謂詞而不是索引級訪問謂詞。
有沒有其他方法可以在 Oracle 中參數化 SQL 查詢？
測試 1
select * from employees where employee_id = :B1; Plan hash value: 1833546154 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPLOYEE_ID"=TO_NUMBER(:B1))測試 2
select * from employees where (employee_id = :B1 OR :B1 IS NULL); Plan hash value: 1445457117 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 6 | 414 | 35 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMPLOYEES | 6 | 414 | 35 (0)| 00:00:01 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(:B1 IS NULL OR "EMPLOYEE_ID"=TO_NUMBER(:B1))測試 3
select * from employees where employee_id = NVL(:B1,employee_id); Plan hash value: 71496665 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 108 | 14364 | 36 (0)| 00:00:01 | | 1 | VIEW | VW_ORE_B4851255 | 108 | 14364 | 36 (0)| 00:00:01 | | 2 | UNION-ALL | | | | | | |* 3 | FILTER | | | | | | | 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 | |* 5 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 | |* 6 | FILTER | | | | | | | 7 | TABLE ACCESS FULL | EMPLOYEES | 107 | 7383 | 35 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(:B1 IS NOT NULL) 5 - access("EMPLOYEE_ID"=:B1) 6 - filter(:B1 IS NULL)
對於這個案例，測試 1 在邏輯上是不正確的。如果您提供 NULL 作為 的值
:B1，它將返回 0 行。測試 2 在邏輯上是正確的，但它的執行計劃並不理想，性能也是如此。在這種情況下，優化器無法使用索引，因為預設情況下 NULL 值不被索引。
測試 3 邏輯上是正確的，它的執行計劃是最優的，這應該用於這種簡單的情況。但是要非常小心。
employee_id = NVL(:B1,employee_id)被轉換為兩個分支的並集，基於employee_id = :B1和收集結果:B1 is null。這稱為 NVL 優化，在執行計劃中顯示為 OR 擴展 (ORE)。這是優化器的一個不錯的小技巧。問題是，只有當你有 1 個這樣的謂詞時，它才能正常工作。如果您有更多這樣的謂詞，優化器將不會對剩餘的謂詞執行此技巧，並且不會為 UNION 創建額外的分支。
像這樣的查詢：
SELECT * FROM MY_TABLE WHERE COL1 = NVL(:B1, COL1) AND COL2 = NVL(:B2, COL2) AND COL3 = NVL(:B3, COL3);不會為所有路徑生成分支。優化器將對 1 個謂詞執行 NVL 優化，然後將剩餘的謂詞放在第一個謂詞轉換創建的 2 個分支中。這意味著從這樣的查詢生成的計劃永遠不會是最佳的。這是導致我遇到的大部分 SQL 性能問題的原因，到目前為止，還沒有一個開發人員意識到這一點。如果您想獲得最佳的執行計劃和性能，您應該動態生成最簡單的 SQL 語句，而不是試圖用單個 SQL 語句覆蓋所有可能的場景。例如，如果沒有為 提供值
:B1，那麼就不要在 SQL 語句中包含該謂詞。