使用绑定和空值来命中Oracle索引的最佳查询 [英] Best query to hit Oracle index with binds and null values

问题描述

我有一个包含多列索引的表，其中许多列都可以为空。

I've got a table with an index on multiple columns, many of which are nullable.

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

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

And now from within C++ code, I'm trying to check this table and precisely hit the index. For each check, a different combination of the columns might be NULL.

我第一次尝试执行此查询时使用 NVL ，但这导致Oracle忽略索引：

My first attempt to do this query used NVL, but this caused Oracle to ignore the index:

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。）查询工作，但没有达到指数;这是完全扫描。

(0 is not used in the data.) The query worked, but didn't hit the index; it was a full scan.

接下来，我编写了自定义C ++代码，每次根据搜索条件重建查询，填写 IS每列的空白或 =：x ：

Next, I wrote custom C++ code which rebuilds the query each time, based on the search criteria, filling in IS NULL or =:x for each column:

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

这也会触及索引，但需要一堆自定义代码并强制Oracle解析一堆不同品种的相同基本查询。如果我每次都必须手工组装查询，那么感觉这就错过了绑定变量的要点。如果我只有一个查询，它会更清晰。

This hits the index as well but requires a bunch of custom code and forces Oracle to parse a bunch of different varieties of the same basic query. It feels like this misses the point of bind variables, if I have to hand-assemble the query each time anyway. It would be much cleaner if I only had one query.

我可以编写一个查询，它可以与 NULL <的任意组合一起使用/ code> s与否，无需添加新索引，仍将始终命中索引？ （我意识到我可以在 NVL（A，0）， NVL（B，0）上添加功能索引，但是，对于真正应该简单的事情，这又感觉非常脏！我正在尝试重用我现有的索引，而不是创建一个新索引。）

Is there a single query I can write which will work with any combination of NULLs or not, without needing to add a new index, and will still always hit the index? (I realize I could add a functional index on NVL(A,0), NVL(B,0), etc., but again this feels horribly dirty for something which really ought to be simple! I'm trying to reuse my existing index, not create a new one.)

推荐答案

您可以比较列和值以查看两者是否为空;或者两者都不为空且等于：

You can compare the column and value to see if both are null; or both are not-null and equal:

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与等值的值进行比较，只有是运算符。

Which isn't terribly pretty but ought to work. As you already know you can't compare values against null with equality, only the is operator.

取决于您的客户端软件，您可以使用命名绑定变量，以避免重复绑定;如果不是，您可以使用子查询或CTE来获取绑定，然后在主查询中使用它们。类似于：

Depending on your client software you might be able to use named bind variables to avoid having to repeat the bindings; if not you could use a subquery or CTE which takes the binds and then use them in the main query. Something like:

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's function-based index approach might be more reliable and easier to understand, as long as you really can't ever have any columns with the magic value zero. (I'd missed that line in your question too and hadn't realised you'd already discounted that!)

这篇关于使用绑定和空值来命中Oracle索引的最佳查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！