使用绑定和空值命中Oracle索引的最佳查询

小编典典

使用绑定和空值命中Oracle索引的最佳查询

sql

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

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解析同一基本查询的许多不同变体。如果我无论如何每次都要手工组装查询，感觉就像错过了绑定变量的要点。如果我只有一个查询，那会干净得多。

我是否可以编写一个查询，该查询可以与NULLs的任何组合一起使用，也可以不与s的任何组合一起使用，而无需添加新索引，并且仍然会一直命中该索引？（我意识到我可以在NVL(A,0)，NVL(B,0)等上添加一个功能索引，但是对于那些实际上应该很简单的东西来说，这又太可怕了！我试图重用现有的索引，而不是创建一个新的索引。）

阅读 193

2021-04-22

共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)

只要您确实无法拥有任何魔法值为零的列，戈登的基于函数的索引方法可能会更可靠且更易于理解。（我也错过了您的问题中的那一行，并且没有意识到您已经对此轻描淡写了！）

2021-04-22