RE: CASE WHEN or DECODE - any efficiency differences

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Tue, 08 Jan 2002 11:48:54 -0800
Message-ID: <F001.003EA164.20020108113822@fatcity.com>

> -----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 ;
/ Received on Tue Jan 08 2002 - 13:48:54 CST