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

From: Arthernan <arthernan_at_hotmail.com>
Date: Fri, 28 Mar 2008 14:43:12 -0700 (PDT)
Message-ID: <78e5044b-f748-42e0-a772-cc086c1d522a@t54g2000hsg.googlegroups.com>

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

On Mar 28, 2:15 pm, Frank van Bortel <frank.van.bor..._at_gmail.com> wrote:
> Arthernan wrote:
> > 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
>
> MMDDOB=NULL should be "MMDDOB is NULL"
>
> And no, Oracle is smart enough to know boolean algebra; if
> a part of the boolean equation yields true (or false), the
> rest is no executed - it would not change the outcome.
>
> Now, you figure out when to OR or AND, and when TRUE or
> FALSE :)
> --
>
> Regards,
> Frank van Bortel
>
> Top-posting in UseNet newsgroups is one way to shut me up

But if I did "MMDDOB is NULL" instead of MMDDOB=NULL. I would get records back when I don't want them.

The final query will be parametized like this "MMDDOB=:MMDDOB". So I want to know if :MMDDOB happens to be NULL, will oracle be smart enougth to realize that whole "and clause" can go away. For example if I did MMDDOB=1231, whenever MMDDOB was NULL the row would be filtered out. Just look at my original SQL above and you will see what I mean.

Arturo Hernandez Received on Fri Mar 28 2008 - 16:43:12 CDT