In my original tests (I wish I had kept the numbers), trying different combinations on several different million-row tables, in a development database with no one else connected, on a development server otherwise unused, my results were that the majority of times CASE was faster, but only by 10% or less, and a few times DECODE was faster. Of course my test was only on "long"-running queries (5 minutes or more). Perhaps your "fast" query paints a more accurate picture.
> -----Original Message-----
> From: Ron Rogers [mailto:[EMAIL PROTECTED]]
>
> 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.
