RE: Evaluating both sides of OR?

From: Kenneth Naim <kennaim_at_gmail.com>
Date: Thu, 16 Apr 2009 12:13:36 -0400
Message-ID: <780ED3FC819241BB94F0103B8637FA70_at_KenPC>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Steve Bradshaw
Sent: Thursday, April 16, 2009 11:26 AM
To: adar666_at_inter.net.il
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_at_inter.net.il> 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

--
http://www.freelists.org/webpage/oracle-l



 



--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 16 2009 - 11:13:36 CDT