RE: CASE WHEN or DECODE - any efficiency differences

From: Ron Rogers <RROGERS_at_galottery.org>
Date: Tue, 08 Jan 2002 12:47:08 -0800
Message-ID: <F001.003EA301.20020108122600@fatcity.com>

Using the supplied query on a Linuxbox RH7.1 Oracle 8.1.7 provided the following:

CASE Elapsed: 00:00:00.10

Statistics

---

          0  recursive calls
          8  db block gets
       6010  consistent gets
        283  physical reads
          0  redo size
     156280  bytes sent via SQL*Net to client
      42823  bytes received via SQL*Net from client
        384  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       5735  rows processed
*-------------------------------------------------------------------------------------

DECODE Elapsed: 00:00:00.40

Statistics

---

          7  recursive calls
          8  db block gets
       6013  consistent gets
        278  physical reads
          0  redo size
     156280  bytes sent via SQL*Net to client
      43219  bytes received via SQL*Net from client
        384  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       5735  rows processed
*----------------------------------------------------------------

There appears to be a large difference between the two. Thanks for the query and ability to test. ROR mª¿ªm

>>> Jacques.Kilchoer_at_quest.com 01/08/02 02:38PM >>>

> -----Original Message-----
> From: Nicoll, Iain (Calanais) [mailto:iain.nicoll_at_calanais.com]
>
> I've just been asked whether there are any efficiency
> differences between
> CASE WHEN and DECODE. I'd imagined that they would use the
> same underlying
> code but perhaps not. Does anyone know which is more
> efficient (I realise
> that CASE is SQL-92 compliant and allows use of IN but
> excluding this is
> there any efficiency difference)

This is not the test I originally ran, but you could try this in your database (uses dba_objects). I just ran this script on one of our development databases with a large number of objects. The version with case took approx. 27 seconds, the version with decode approx. 24 seconds (time displayed by "set timing on").

set timing on
set autotrace traceonly statistics
select

   case

     when substr (object_name, 1, 1) = 'A'
       then 'Object Name starts with A'
     when substr (object_name, 1, 1) = 'B'
      then 'Object Name starts with B'
     when substr (object_name, 1, 1) = 'C'
      then 'Object Name starts with C'
     when substr (object_name, 1, 1) = 'D'
      then 'Object Name starts with D'
     when substr (object_name, 1, 1) = 'E'
      then 'Object Name starts with E'
     when substr (object_name, 1, 1) = 'F'
      then 'Object Name starts with F'
     when substr (object_name, 1, 1) = 'G'
      then 'Object Name starts with G'
     when substr (object_name, 1, 1) = 'H'
      then 'Object Name starts with H'
     when substr (object_name, 1, 1) = 'I'
      then 'Object Name starts with I'
     when substr (object_name, 1, 1) = 'J'
      then 'Object Name starts with J'
     when substr (object_name, 1, 1) = 'K'
      then 'Object Name starts with K'
     when substr (object_name, 1, 1) = 'L'
      then 'Object Name starts with L'
     when substr (object_name, 1, 1) = 'M'
      then 'Object Name starts with M'
     when substr (object_name, 1, 1) = 'N'
      then 'Object Name starts with N'
     when substr (object_name, 1, 1) = 'O'
      then 'Object Name starts with O'
     when substr (object_name, 1, 1) = 'P'
      then 'Object Name starts with P'
     when substr (object_name, 1, 1) = 'Q'
      then 'Object Name starts with Q'
     when substr (object_name, 1, 1) = 'R'
      then 'Object Name starts with R'
     when substr (object_name, 1, 1) = 'S'
      then 'Object Name starts with S'
     when substr (object_name, 1, 1) = 'T'
      then 'Object Name starts with T'
     when substr (object_name, 1, 1) = 'U'
      then 'Object Name starts with U'
     when substr (object_name, 1, 1) = 'V'
      then 'Object Name starts with V'
     when substr (object_name, 1, 1) = 'W'
      then 'Object Name starts with W'
     when substr (object_name, 1, 1) = 'X'
      then 'Object Name starts with X'
     when substr (object_name, 1, 1) = 'Y'
      then 'Object Name starts with Y'
     when substr (object_name, 1, 1) = 'Z'
      then 'Object Name starts with Z'
     else 'Object Name doesn''t start with capital letter'

   end as object_name_first_letter
from dba_objects ;
/
select

   decode (substr (object_name, 1, 1),

           'A', 'Object Name starts with A',
           'B', 'Object Name starts with B',
           'C', 'Object Name starts with C',
           'D', 'Object Name starts with D',
           'E', 'Object Name starts with E',
           'F', 'Object Name starts with F',
           'G', 'Object Name starts with G',
           'H', 'Object Name starts with H',
           'I', 'Object Name starts with I',
           'J', 'Object Name starts with J',
           'K', 'Object Name starts with K',
           'L', 'Object Name starts with L',
           'M', 'Object Name starts with M',
           'N', 'Object Name starts with N',
           'O', 'Object Name starts with O',
           'P', 'Object Name starts with P',
           'Q', 'Object Name starts with Q',
           'R', 'Object Name starts with R',
           'S', 'Object Name starts with S',
           'T', 'Object Name starts with T',
           'U', 'Object Name starts with U',
           'V', 'Object Name starts with V',
           'W', 'Object Name starts with W',
           'X', 'Object Name starts with X',
           'Y', 'Object Name starts with Y',
           'Z', 'Object Name starts with Z',
           'Object Name doesn''t start with capital letter'
          ) as object_name_first_letter

from dba_objects ;
/

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ron Rogers
  INET: RROGERS_at_galottery.org

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Jan 08 2002 - 14:47:08 CST