最好的查询命中Oracle索引绑定和空值

Question

我有一个表，在多个列上有一个索引，其中许多列是可空的。

CREATE UNIQUE INDEX 
    UX_MYTABLE_A_B_C_D_E
    ON MYTABLE
    ("A", "B", "C", "D", "E")

现在从c++代码中，我试图检查这个表并精确地命中索引。对于每个检查，列的不同组合可能为NULL。

我第一次尝试使用NVL，但这导致Oracle忽略索引:

SELECT * FROM MYTABLE 
        WHERE NVL(A,0)=:1 
          AND NVL(B,0)=:2 
          AND NVL(C,0)=:3
          AND NVL(D,0)=:4 
          AND NVL(E,0)=:5 

(数据中不使用0)查询工作了，但没有命中索引;

接下来，我编写了自定义c++代码，每次根据搜索条件重建查询，为每列填充IS NULL或=:x:

SELECT * FROM MYTABLE 
        WHERE A IS NULL
          AND B=:1
          AND C IS NULL
          AND D=:2
          AND E=:3

这也会命中索引，但需要一堆自定义代码，并迫使Oracle解析相同基本查询的一堆不同类型。如果每次都必须手工组装查询的话，感觉这就忽略了绑定变量的意义。如果我只有一个查询，那就更简洁了。

是否有一个单一的查询，我可以写它将与NULL s的任何组合工作，而不需要添加一个新的索引，并且仍然总是命中索引?(我意识到我可以在NVL(A,0), NVL(B,0)等上添加一个函数索引，但是对于应该很简单的东西来说，这感觉太脏了!我试图重用我现有的索引，而不是创建一个新的。)

Answer 1

可以比较列和值是否都为空;或者两者都非空且相等:

SELECT * FROM MYTABLE 
WHERE ((A is null and :1 is null) or A = :1) 
  AND ((B is null and :2 is null) or B = :2) 
  AND ((C is null and :3 is null) or C = :3) 
  AND ((D is null and :4 is null) or D = :4) 
  AND ((E is null and :5 is null) or E = :5) 

这不是非常漂亮，但应该工作。正如您已经知道的那样，您不能将值与null进行相等比较，只能使用is操作符。

根据你的客户端软件，你可以使用命名绑定变量来避免重复绑定;如果没有，您可以使用子查询或CTE，它们接受绑定，然后在主查询中使用它们。比如:

WITH CTE AS (
  SELECT :1 AS val_1, :2 AS val_2, :3 AS val_3, :4 AS val_4, :5 AS val_5
  FROM DUAL
)
SELECT MT.*
FROM CTE
JOIN MYTABLE MT
  ON ((MT.A is null and CTE.val_1 is null) or MT.A = CTE.val_1) 
 AND ((MT.B is null and CTE.val_2 is null) or MT.B = CTE.val_2) 
 AND ((MT.C is null and CTE.val_3 is null) or MT.C = CTE.val_3) 
 AND ((MT.D is null and CTE.val_4 is null) or MT.D = CTE.val_4) 
 AND ((MT.E is null and CTE.val_5 is null) or MT.E = CTE.val_5) 

Gordon的基于函数的索引方法可能更可靠，也更容易理解，只要您真的不允许任何列具有神奇的值0。(我也错过了你问题中的那一行，没有意识到你已经忽略了这一点!)