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

李艳桃

我有一个表，它在多个列上有一个索引，其中许多是空的。
CREATE UNIQUE INDEX     UX_MYTABLE_A_B_C_D_E    ON MYTABLE    ("A","B","C","D","E")现在，从C   在代码中，我试图检查表格并准确命中索引。对于每次检查，列表的不同组合可能是NULL。
我第一次尝试使用这个查询NVL，但这导致Oracle忽略索引：
( W5 ]0 |2 \7 W6 `" [1 aSELECT * FROM MYTABLE         WHERE NVL(A,0)=:1                                                                                                                                                                                                                                                                                                                                                                                                             AND NVL(B,0)=:2                                   AND NVL(C,0)=:3  3                                                                                                                                                                                                                                                                                                                               AND NVL(D,0)=:4                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       AND NVL(E,0)=:5(数据中未使用0。)查询有效，但未命中索引；这是全面扫描。
接下来，我写了自定义C   代码，每次根据搜索条件重新生成查询，并填写IS NULL或填充=:x每一列：
* N' d( d" H7 K5 k! z( V0 q+ ZSELECT * 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)等上加一个功能索引，但对于那些其实应该很简单的东西来说，太可怕了！我试图重用现有的索引，而不是创建新的索引。
/ P7 P. }1 {/ M* e& }/ q$ W                                                               
# \4 f' O5 ~0 r6 C    解决方案:                                                               
                                                                您可以比较列和值来检查两者是否为空；或者两者都不是空的和相等的：
. ], G! v( T" CSELECT * 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比较运算符。
4 h' ]7 C2 l6 N9 K4 u, B0 R根据客户端软件的不同，您可以使用命名绑定变量来避免重复绑定。如果没有，可以使用接受绑定的子查询或CTE，然后在主查询中使用。
, D, ]* u+ t' {5 R8 T* `9 sWITH 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 CTEJOIN 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)只要你真的没有任何魔法值为零的列，戈登基于函数的索引方法可能会更可靠、更容易理解。(我也错过了你的问题，没有意识到你已经轻描淡写了。！）