2

Is there any possibility of avoiding full scans, when dealing with incoming NULL query parameters in a stored procedure? Suppose I have 4 parameters, that the user sends from a form and tries to look for an exact match in the table, like this:

SELECT *
FROM table1 t1
WHERE ((:qParam1 is null) OR (t1.col1 = :qParam1)) AND
((:qParam2 is null) OR (t1.col2 = :qParam2)) AND
((:qParam3 is null) OR (t1.col3 = :qParam3)) AND
((:qParam4 is null) OR (t1.col4 = :qParam4));

So when this part of the procedure executes, because of NULL check, it will do a FTS, since the procedure has already been compiled and the execution plan determined. It would need 2^4 different queries to be written inside the procedure in order to always use the most efficient plan considering the incoming query parameters (and considerably more if the input parameter number increases). My question is - is there any way, except for dynamic SQL, to avoid the FTS in these type of queries?

Dharman
  • 30,962
  • 25
  • 85
  • 135
Gillio23
  • 21
  • 2
  • `t1.col = nvl(:qParam1, t1.col)` usually works fine. Oracle uses a `FILTER` operation to build two separate subplans, one with a FTS and one with an index, and will pick the appropriate plan at run time. I'd mark this as a duplicate of [this question](http://stackoverflow.com/q/3649036/409172), except the accepted answer is wrong. – Jon Heller May 12 '15 at 03:45

1 Answers1

0

Maybe not. Oracle does not store nulls in the index so it cannot ever use an index when there's a possibility of null in the predicate. If your columns are nullable then no. Having said that, there's a good chance it's the best plan anyway - your query is so vague (ok - flexible) that it would be hard pressed to build a single, useful plan anyway. Oracle is quite smart with flexible plans but there's not really much to go on here.

If you do have nullable columns and an index you might be able to bodge it with 

t1.col1 = :qParam2 and t1.col1 is not null

in case it's not smart enough to work that out for itself.

LoztInSpace
  • 5,584
  • 1
  • 15
  • 27