Do all the legs of an "OR" with null comparisons get executed?
in [Oracle]
|
Prev: how to increase hardnofiles & softnofiles on SUSE SLES9
Next: dbms_stats.gather_schema_stats with the option gather_auto
From: Arthernan on 30 Mar 2008 17:53 On Mar 29, 7:14 am, "Shakespeare" <what...(a)xs4all.nl> wrote: > "Arthernan" <arther...(a)hotmail.com> schreef in berichtnews:e65e3d5b-d8be-4dec-a1fa-8c17a1e89bf2(a)b1g2000hsg.googlegroups.com... > On Mar 28, 11:41 am, "fitzjarr...(a)cox.net" <fitzjarr...(a)cox.net> > 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 > > I do not want to get any rows back if MMDDOB was NULL. In this case I > > In that case you should use > > WHERE MMDDOB is not NULL and (--- rest of your conditions). > > MMDDOB = NULL is ALWAYS false (or actually undefined but not true) so these > clauses dont need to be calculated. You could replace the whole part with > "0=1" > > > 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. > > No it's not, comparing with '=NULL' id definitely wrong. > > > I don't want to make it complicated if it > > not necessary. > > But you already did. > > > Arturo Hernandez > > Shakespeare Ohh my!! I do not think I'm comunicating here. If I write "where a=b" no parameters, no constants just database columns. Any row where "a has a null value" or "b has a null value" is filtered out. This is by design. Ther is nothing wrong in having null values in the "a column" or the "b column". I do not intend to write code that would read "where a=NULL" that is incorrect. What I do intend to write is a PARAMETIZED sql that reads "where a=:a" notice the colon after the variable name a. This is a standard wayt to parametize SQL statements. Normally I do not write such complex queries as the one above. But just like in a table there is no way to know ahead of time whether there will be non-null values in the "where a=b" example. I do not know if there could be NULL values in the parameters. I wrote the SQL with MMDDOB=NULL as an example, but that is not all. I wrote that because it's easy to run an explain plan on it. But my question is about the PARAMETIZED sql. here is my original question ==== 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
From: Arthernan on 30 Mar 2008 17:59 On Mar 29, 12:43 pm, Charles Hooper <hooperc2...(a)yahoo.com> wrote: > On Mar 28, 5:43 pm, Arthernan <arther...(a)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 > > > On Mar 28, 2:15 pm, Frank van Bortel <frank.van.bor...(a)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- Hide quoted text - > > > - Show quoted text - > > Look closely at the responses that Frank van Bortel provided to you. > The WHERE clause is not working the way you expect, and that is why > you receive too many rows when using MMDDOB IS NULL. > > Placing ( ) as Oracle would evaluate the WHERE clause: > (((((((((((((((((((((((((((((((((((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') > > Now, let's just replace MMDDOB=NULL with FALSE, as it can never be > TRUE (as indicated by several people in this thread): > (((((((((((((((((((((((((((((((((((UPP_FIRST='JOHN' and > UPP_LAST='SMITH') and FALSE) 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 FALSE) and > AA=1) and BB=2) or UPP_FIRST='JOHN') and FALSE) and AA=1) and CC='CA') > or {UPP_FIRST='JOHN') and BB=2) and CC='CA')} or UPP_LAST='SMITH') and > FALSE) and AA=1) and BB=2) or UPP_LAST='SMITH') and FALSE) and AA=1) > and CC='CA') or UPP_LAST='SMITH') and BB=2) and CC='CA') or FALSE) and > AA=1) and BB=2) and CC='CA') > > Now, let's go through the WHERE clause and set all other conditions to > TRUE, just to see what happens if those conditions were TRUE: > (((((((((((((((((((((((((((((((((((TRUE and TRUE) and FALSE) and TRUE) > or TRUE) and TRUE) and TRUE) or {TRUE) and TRUE) and TRUE)} or TRUE) > and FALSE) and TRUE) and TRUE) or TRUE) and FALSE) and TRUE) and TRUE) > or TRUE) and TRUE) and TRUE) or TRUE) and FALSE) and TRUE) and TRUE) > or TRUE) and FALSE) and TRUE) and TRUE) or TRUE) and TRUE) and TRUE) > or FALSE) and TRUE) and TRUE) and TRUE) > > In the above, look closely for the curly brackets { } (I did not > identify all such cases). Now, simplifying the above a little, we > start to see the problem - most of the WHERE clause evaluates to FALSE > - always > ((((((((((((((((((((((((((((((FALSE or TRUE) and TRUE) and TRUE) or > TRUE) and FALSE) and TRUE) and TRUE) or TRUE) and FALSE) and TRUE) and > TRUE) or TRUE) and TRUE) and TRUE) or TRUE) and FALSE) and TRUE) and > TRUE) or TRUE) and FALSE) and TRUE) and TRUE) or TRUE) and TRUE) and > TRUE) or FALSE) and TRUE) and TRUE) and TRUE) > > Simplifying again > (((((((((((((((((((((((((((TRUE) or TRUE) and FALSE) and TRUE) and > TRUE) or TRUE) and FALSE) and TRUE) and TRUE) or TRUE) and TRUE) and > TRUE) or TRUE) and FALSE) and TRUE) and TRUE) or TRUE) and FALSE) and > TRUE) and TRUE) or TRUE) and TRUE) and TRUE) or FALSE) and TRUE) and > TRUE) and TRUE) > > There are only a couple of conditions in the above that could possibly > evaluate to TRUE. > > The reason that the WHERE clause does not work with the MMDDOB IS NULL > condition is due to the order of evaluation. You did not specify the > evaluation order by using ( ) in the WHERE clause, so Oracle used the > default (note that the default evaluation order differs from some > programming languages, such as C). > > Charles Hooper > IT Manager/Oracle DBA > K&M Machine-Fabricating, Inc. I really appreciate your time, but I do not think my question is coming throught. I am going to parametize my query. So I am not really going to execute something that reads .... A=NULL... What I am going to have is ..... A=:A....notice the colon before the second A. I do not know if :A will have a NULL value. Just like I don't know if A will have one a NULL value. I understand Oracle execution enough to know the logic matches the intent. What I do not know is how will it be executed.
From: Shakespeare on 2 Apr 2008 05:39
Do you mean the part where he states: "Yes! Sorry about that. I need to work on describing things clearly." ??? Shakespeare |