RE: Evaluating both sides of OR?
- From: "Kenneth Naim" <kennaim@xxxxxxxxx>
- To: <sjb1970@xxxxxxxxxxxxxx>, <adar666@xxxxxxxxxxxx>
- Date: Thu, 16 Apr 2009 12:13:36 -0400
From 10g (maybe 11g) oracle optimizes the OR function behind the scenes by
choosing which side is more likely to be true and evaluating it first and it
will not evaluate the second condition if the first condition is true.
Ken
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Steve Bradshaw
Sent: Thursday, April 16, 2009 11:26 AM
To: adar666@xxxxxxxxxxxx
Cc: ORACLE-L
Subject: Re: Evaluating both sides of OR?
Hi,
Just tried this out on Oracle XE - seems to depend on the order of the
statements.
If I did the ":v1 is null" check first, works fine, if I did the "f(:v1)"
the function is invoked (and hits the raise_application_error).
Steve
SQL> create table test_t
2 (a varchar2(10)
3 ) ;
Table created.
SQL> create or replace function TEST_F(P_1 varchar2) return varchar2 is
2 begin
3 if P_1 is null
4 then
5 RAISE_APPLICATION_ERROR(-20666, 'DOH!');
6 end if;
7 return(P_1);
8 end;
9 /
Function created.
SQL> insert into TEST_T values ('a') ;
1 row created.
SQL> insert into TEST_T values (null) ;
1 row created.
SQL> set serveroutput on
SQL> edit
Wrote file afiedt.buf
1 declare
2 L_VAR varchar2(10) := null;
3 begin
4 for each in (select NVL(A,'*') A
5 from TEST_T
6 where (L_VAR is null or TEST_F(L_VAR) is null)) loop
7 DBMS_OUTPUT.PUT_LINE(each.A);
8 end loop;
9* end;
SQL> /
a
*
PL/SQL procedure successfully completed.
SQL> edit
Wrote file afiedt.buf
1 declare
2 L_VAR varchar2(10) := null;
3 begin
4 for each in (select NVL(A,'*') A
5 from TEST_T
6 where (test_f(L_VAR) is null OR L_VAR is null)) loop
7 DBMS_OUTPUT.PUT_LINE(each.A);
8 end loop;
9* end;
SQL> /
declare
*
ERROR at line 1:
ORA-20666: DOH!
ORA-06512: at "TEST.TEST_F", line 5
ORA-06512: at line 4
SQL>
On Thu, Apr 16, 2009 at 2:52 PM, Yechiel Adar <adar666@xxxxxxxxxxxx> wrote:
I seem to remember that oracle is evaluating both sides of OR in where
clause.
select a,b from c where a =1 and (:v1 is null or function(:v1) = b);
Is the function activated when :v1 is null?
--
Adar Yechiel
Rechovot, Israel
--
//www.freelists.org/webpage/oracle-l
Other related posts:
