Re: DECODE .vs IF THEN ELSE (or ELSIF)

From: Martin Burbridge <pobox002_at_bebub.com>
Date: Wed, 31 Mar 2004 02:26:37 GMT
Message-ID: <Xns94BCD9EFDC92Fpobox002bebubcom_at_204.127.199.17>

Moshe_t_at_yahoo.com (moshe) wrote in
news:616be6f6.0403300906.34626f35_at_posting.google.com:

> Hello,
>
> I have simple function that get char value and according its value
> return char value.
> The function can looks like :
> IF (InputValue = '00') THEN RETURN 'xxx'
> ELSIF (InputValue = '01') THEN RETURN 'yyy'
> ELSIF (InputValue = '02') THEN RETURN 'zzz'
> and so on...
>
> Or I can use DECODE like :
> Result := DECODE('00','xxx','01','yyy','02','zzz' .... )
>
> My question is consider only to performance - What is the best way ?
> Where I will get the highest performance ?
>
>
> Thanks.

If you only care about performance the answer is,

3. Don't use a function, use a view.

Benchmarking only takes a few minutes and is an easy way to answer these kinds of questions.

This test shows, that elsif outperforms decode by 3 to 1, primarily because decode cannot be used in a simple assignment, it has to be selected from dual.

The view then outperforms elsif, again by about 3 to 1.

SQL> create table t as

  2      select to_char(mod(object_id,3),'fm00') a
  3      from all_objects

  4 /

Table created.

SQL> create or replace function f1 (p_str varchar2)   2 return varchar2 as
  3 begin

  4      if p_str = '00' then
  5          return 'xxx';
  6      elsif p_str = '01' then
  7          return 'yyy';
  8      elsif p_str = '02' then
  9          return 'zzz';
 10      end if;
 11      return null;

 12 end;
 13 /

Function created.

SQL> create or replace function f2 (p_str varchar2)   2 return varchar2 as
  3 l_str varchar2(3);
  4 begin

  5      select decode(p_str, '00', 'xxx', '01', 'yyy', '02', 'zzz')
  6      into l_str from dual;
  7      return l_str;

  8 end;
  9 /

Function created.

SQL> create or replace view v as

  2      select decode(a, '00', 'xxx', '01', 'yyy', '02', 'zzz') f
  3      from t

  4 /

View created.

SQL> set timing on
SQL> set autotrace traceonly stat
SQL> select f1(a) f from t

  2 /

40601 rows selected.

Elapsed: 00:00:01.13

Statistics

---

         48  recursive calls
          0  db block gets
       2837  consistent gets
         62  physical reads
          0  redo size
     544453  bytes sent via SQL*Net to client
      30274  bytes received via SQL*Net from client
       2708  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      40601  rows processed

SQL> select f2(a) f from t
  2 /

40601 rows selected.

Elapsed: 00:00:03.36

Statistics

---

      40606  recursive calls
          0  db block gets
       2833  consistent gets
          0  physical reads
          0  redo size
     544453  bytes sent via SQL*Net to client
      30274  bytes received via SQL*Net from client
       2708  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      40601  rows processed

SQL> select f from v
  2 /

40601 rows selected.

Elapsed: 00:00:00.41

Statistics

---

         13  recursive calls
          0  db block gets
       2835  consistent gets
          0  physical reads
          0  redo size
     544453  bytes sent via SQL*Net to client
      30274  bytes received via SQL*Net from client
       2708  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      40601  rows processed

Hth

-- 
Martin Burbridge
adress shown is now 003

Received on Wed Mar 31 2004 - 04:26:37 CEST