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

On Mar 28, 11:41 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Mar 28, 10:53 am, Arthernan <arther..._at_hotmail.com> 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 Received on Fri Mar 28 2008 - 14:03:29 CDT