CODEWITHSUNDEEP
sqloracle-databaseindexingnull
StilesCrisis
StilesCrisis

Reputation: 16290

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

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.

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 is not used in the data.) The query worked, but didn't hit the index; it was a full scan.

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

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.

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

Upvotes: 2

Views: 1036

Answers (1)

Alex Poole
Alex Poole

Reputation: 191285

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)

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.

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

Upvotes: 3

Related Questions