Re: Do all the legs of an "OR" with null comparisons get executed?

On Mar 28, 11:41 am, "fitzjarr...@xxxxxxx" <fitzjarr...@xxxxxxx>
wrote:
On Mar 28, 10:53 am, Arthernan <arther...@xxxxxxxxxxx> wrote:





If I do an explain plan of the statement below I can see that the
"and" clauses that have MMDDOB=NULL do not get executed

select * from v_search where
UPP_FIRST='JOHN' and UPP_LAST='SMITH' and MMDDOB=NULL and AA=1 or
UPP_FIRST='JOHN' and UPP_LAST='SMITH' and BB=2 or
UPP_FIRST='JOHN' and UPP_LAST='SMITH' and CC='CA' or
UPP_FIRST='JOHN' and MMDDOB=NULL and AA=1 and BB=2 or
UPP_FIRST='JOHN' and MMDDOB=NULL and AA=1 and CC='CA' or
UPP_FIRST='JOHN' and BB=2 and CC='CA' or
UPP_LAST='SMITH' and MMDDOB=NULL and AA=1 and BB=2 or
UPP_LAST='SMITH' and MMDDOB=NULL and AA=1 and CC='CA' or
UPP_LAST='SMITH' and BB=2 and CC='CA' or
MMDDOB=NULL and AA=1 and BB=2 and CC='CA'

My problem is how to know if it does the same thing when I parametize
the query as

select * from v_search where
UPP_FIRST=:UPP_FIRST and UPP_LAST=:UPP_LAST and MMDDOB=:MMDD and
AA=:AA or
UPP_FIRST=:UPP_FIRST and UPP_LAST=:UPP_LAST and BB=:BB or
UPP_FIRST=:UPP_FIRST and UPP_LAST=:UPP_LAST and CC=:CC or
UPP_FIRST=:UPP_FIRST and MMDDOB=:MMDD and AA=:AA and BB=:BB or
UPP_FIRST=:UPP_FIRST and MMDDOB=:MMDD and AA=:AA and CC=:CC or
UPP_FIRST=:UPP_FIRST and BB=:BB and CC=:CC or
UPP_LAST=:UPP_LAST and MMDDOB=:MMDD and AA=:AA and BB=:BB or
UPP_LAST=:UPP_LAST and MMDDOB=:MMDD and AA=:AA and CC=:CC or
UPP_LAST=:UPP_LAST and BB=:BB and CC=:CC or
MMDDOB=:MMDD and AA=:AA and BB=:BB and CC=:CC

Any ideas?

Nothing equals NULL, so possibly you should change your logic to use
the nvl() function to provide some 'usable' value when the NULLs
appear.

As  to  your other question I'll wait until you make the code changes
and report on the results of those modifications.

David Fitzjarrell

That is an alternative. I do think the logic is correct thought. Take
the first comparison for example:

UPP_FIRST='JOHN' and UPP_LAST='SMITH' and MMDDOB=NULL and AA=1

I do not want to get any rows back if MMDDOB was NULL. In this case I
want Oracle to skip that comparison at execution. And it does do that
when it is not parametized. I just don't know if it will when I
parametize it.

The code is very simple and straight forward as it is. And I do
believe it is also correct. I don't want to make it complicated if it
not necessary.

Arturo Hernandez
.