Oracle Optimizer 拒絕使用我的索引

我有一個相對簡單的查詢，可以在表中選擇數據。

SELECT l.columnRecovered FROM schema.TABLE l
WHERE (column1 = :a or (:a is null AND column1 is null))
AND (column2 = :b or (:b is null AND column2 is null))
AND (column3 = :c or (:c is null AND column3 is null));

這會生成以下計劃：

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |   309 (100)|          |
|*  1 |  TABLE ACCESS FULL| TABLE     |     1 |    56 |   309   (1)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  1 - filter((("COLUMN2"=:3 OR ("COLUMN2" IS NULL AND :4 IS NULL)) AND
             ("COLUMN1"=:1 OR ("COLUMN1" IS NULL AND :2 IS NULL)) AND ((:6 IS NULL
             AND "COLUMN3" IS NULL) OR "COLUMN3"=:5)))

在這張表上，我有一個包含所有這 3 列的 UNIQUE INDEX。

但是優化器更喜歡在表上執行全掃描而不是使用索引。

事情變得奇怪的地方是，當我在沒有綁定但直接替換查詢中的值的情況下嘗試此查詢時。

當然 SQL_ID 會發生變化，但這次優化器決定使用 INDEX 並從 1104 變為 6（這當然是一個很好的優化）。

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    56 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE         |     1 |    56 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | TABLE_IDX     |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("COLUMN3"='DATA3' AND "COLUMN2"='DATA2' AND "COLUMN1"='DATA1')

統計數據是最新的，此表上沒有直方圖。

有什麼方法可以讓優化器在不修改查詢的情況下使用該索引？

我認為優化器認為可能會發生 3 個 NULL，所以這使他無法使用索引，對嗎？

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    56 |   199   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TABLE     |     1 |    56 |   199   (2)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("COLUMN2" IS NULL AND "COLUMN1" IS NULL AND "COLUMN3" IS
             NULL)

優化器不使用你的索引，不是因為它不想，而是因為它不能。對於這樣的查詢，索引並不涵蓋所有可能的結果。

當:a, :b,:c都是 NULL 並且您正在搜尋column1, column2,column3都是 NULL 的行時，這些行沒有被(column1, column2, column3)b-tree 索引索引，因此它不能用於檢索匹配的行。

添加NOT NULL約束可能會有所幫助，但是如果您有 NULL 值，那當然是不可能的（我假設您有 NULL 值，否則為什麼要構造這樣的查詢）。

您還可以嘗試創建一個包含常量值的索引，這樣即使這些行也會被索引，其中索引列是 NULL。因此(column1, column2, column3)，您可以嘗試創建索引，而不是(column1, column2, column3, 'A')索引。

然而，我認為最好的解決方案是在綁定變數未分配值時生成不同的查詢。像上面這樣的查詢是嘗試使用單個查詢處理多個案例的結果。這可能看起來優雅而緊湊，是的，我知道，根據參數的存在生成不同的查詢可能很麻煩，但我已經無數次看到這種懶惰導致嚴重的性能問題。

是的，Oracle 有一些技巧，例如 NVL 優化、OR 擴展來處理此類謂詞。這些在 Powerpoint 和培訓材料中完美地工作，帶有簡單的範例，但實際上，對於復雜的查詢，它們並不總是可以使用。

引用自：https://dba.stackexchange.com/questions/224277