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: Charles Hooper on 31 Mar 2008 08:46 On Mar 30, 5:59 pm, Arthernan <arther...(a)hotmail.com> wrote: > 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. Let's test. Here is the setup for the test run: CREATE TABLE T1( UPP_FIRST VARCHAR2(6), UPP_LAST VARCHAR2(6), MMDDOB DATE, AA NUMBER(5), BB NUMBER(5)); INSERT INTO T1 VALUES( 'JOHN', 'SMITH', NULL, 1, NULL); INSERT INTO T1 VALUES( 'JOHN', 'SMITH', TO_DATE('01012008','MMDDYYYY'), 1, NULL); INSERT INTO T1 VALUES( 'JOHN', 'SMITH', NULL, 1, 2); INSERT INTO T1 VALUES( 'JOHN', 'SMITH', TO_DATE('01012008','MMDDYYYY'), 1, 2); COMMIT; SELECT * FROM T1; UPP_FI UPP_LA MMDDOB AA BB ------ ------ --------- ---------- ---------- JOHN SMITH 1 JOHN SMITH 01-JAN-08 1 JOHN SMITH 1 2 JOHN SMITH 01-JAN-08 1 2 We have 4 rows in the table, two with a value for MMDDOB, and two with a value for BB. Now, a simple select using the first two criteria that you specified in the SQL statement that you posted: SELECT * FROM T1 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; You want 3 rows to be returned, but Oracle returns: UPP_FI UPP_LA MMDDOB AA BB ------ ------ --------- ---------- ---------- JOHN SMITH 1 2 JOHN SMITH 01-JAN-08 1 2 Now, what happens when we switch to using bind variables, do we see the third row in the result? A simple VB program using ADO to find out: Dim comData As ADODB.Command Dim snpData As ADODB.Recordset Dim strSQL As String Dim intRows As Integer Set comData = New ADODB.Command Set snpData = New ADODB.Recordset strSQL = "SELECT" & vbCrLf strSQL = strSQL & " *" & vbCrLf strSQL = strSQL & "FROM" & vbCrLf strSQL = strSQL & " T1" & vbCrLf strSQL = strSQL & "WHERE" & vbCrLf strSQL = strSQL & " UPP_FIRST= ? and UPP_LAST= ? and MMDDOB= ? and AA= ?" & vbCrLf strSQL = strSQL & " or UPP_FIRST= ? and UPP_LAST= ? and BB= ?" With comData 'Set up the command properties .CommandText = strSQL .CommandType = adCmdText .CommandTimeout = 30 .ActiveConnection = dbMyDB 'Add the bind variables .Parameters.Append .CreateParameter("first1", adVarChar, adParamInput, 6, "JOHN") .Parameters.Append .CreateParameter("last1", adVarChar, adParamInput, 6, "SMITH") .Parameters.Append .CreateParameter("dob1", adDate, adParamInput, 8, Null) .Parameters.Append .CreateParameter("aa", adNumeric, adParamInput, 8, 1) .Parameters.Append .CreateParameter("first2", adVarChar, adParamInput, 6, "JOHN") .Parameters.Append .CreateParameter("last2", adVarChar, adParamInput, 6, "SMITH") .Parameters.Append .CreateParameter("bb", adNumeric, adParamInput, 8, 2) End With Set snpData = comData.Execute intRows = 0 Do While Not (snpData.EOF) intRows = intRows + 1 snpData.MoveNext Loop Debug.Print "Rows Retrieved "; intRows The output is: Rows Retrieved 2 {the same result as our SQL query} If we change the third parameter as follows: .Parameters.Append .CreateParameter("dob1", adDate, adParamInput, 8, CDate("01/01/2008")) The output is Rows Retrieved 3 {the number of rows that were expected} If we reset the third parameter back to NULL and also specify the last parameter (bb) as NULL: .Parameters.Append .CreateParameter("first1", adVarChar, adParamInput, 6, "JOHN") .Parameters.Append .CreateParameter("last1", adVarChar, adParamInput, 6, "SMITH") .Parameters.Append .CreateParameter("dob1", adDate, adParamInput, 8, Null) .Parameters.Append .CreateParameter("aa", adNumeric, adParamInput, 8, 1) .Parameters.Append .CreateParameter("first2", adVarChar, adParamInput, 6, "JOHN") .Parameters.Append .CreateParameter("last2", adVarChar, adParamInput, 6, "SMITH") .Parameters.Append .CreateParameter("bb", adNumeric, adParamInput, 8, Null) The output is: Rows Retrieved 0 {MMDDOB=NULL OR BB=NULL returns 0 rows} I recognize that not everyone programs in VB, but perhaps someone will post a simple example using bind variables in another language. Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.
From: Shakespeare on 31 Mar 2008 15:38 "Arthernan" <arthernan(a)hotmail.com> schreef in bericht news:4b2f54b6-f801-42bf-9040-47cc2d3f2abc(a)t54g2000hsg.googlegroups.com... > 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 I'm sorry, I give up. I've read your post several times and still don't get it. Shakespeare.
From: joel garry on 31 Mar 2008 16:43 On Mar 31, 12:38 pm, "Shakespeare" <what...(a)xs4all.nl> wrote: > "Arthernan" <arther...(a)hotmail.com> schreef in berichtnews:4b2f54b6-f801-42bf-9040-47cc2d3f2abc(a)t54g2000hsg.googlegroups.com... > > > > > > > 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 > > I'm sorry, I give up. I've read your post several times and still don't get > it. > > Shakespeare I think he is looking for something like Tom talks about starting around page 714 here: http://books.google.com/books?id=8_WChqD3nc4C&pg=PA723&lpg=PA723&dq=%22function+dump_fixed_width%22&source=web&ots=S_h46hJRTS&sig=SWr1tWWISLiX2YAhH9ZxOf7qQbc&hl=en#PPA707,M1 (If that doesn't work, the reference is to Expert One-On-One Oracle by Tom Kyte. I'm sure there are other examples online, and perhaps in other books). jg -- @home.com is bogus. "Eat my voltage!" - Chargers graphic on the back of a Prius.
From: Shakespeare on 29 Mar 2008 08:14 "Arthernan" <arthernan(a)hotmail.com> schreef in bericht news: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
From: Charles Hooper on 29 Mar 2008 13:43
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. |