Re: case short circuits?

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Tue, 21 Jun 2005 00:02:00 +0100
Message-ID: <e0ieb150m9rpn6tlse4jni3fjd69js8po9@4ax.com>

On 20 Jun 2005 14:27:46 -0700, "tq" <tomm_quinn_at_yahoo.com> wrote:

>Question about this case statement below - is it actually evaluating
>the 3=3 or does it short ciruit? I'm trying to judge decode versus case
>in terms of potential performance differences.
>
>case when 1=2 and 3=3 then 0 else 1 end

 It's documented behaviour for PL/SQL that Oracle does short-circuit evaluation on logical expressions. It's not explicitly stated for CASE within SQL as far as I can see, however, it appears to also be true.

 You can demonstrate this by setting up functions with side effects, in this case using dbms_output to dump some text out whenever it runs.

SQL> create or replace function f1 return number as   2 begin

  3      dbms_output.put_line('f1');
  4      return 1;

  5 end;
  6 /

Function created

SQL> create or replace function f2 return number as   2 begin

  3      dbms_output.put_line('f2');
  4      return 2;

  5 end;
  6 /

Function created

SQL> select case when 2=f1() and 2=f2() then 0 else 1 end from dual;

CASEWHEN2=F1()AND2=F2()THEN0EL

---

                             1

f1

 So, f2() is not executed in the statement, since it's short-circuited out by 2=f1() being false.

[1]
http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10807/02_funds.htm#sthref223

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

Received on Mon Jun 20 2005 - 18:02:00 CDT