RE: CASE WHEN or DECODE - any efficiency differences

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:RROGERS_at_galottery.org]
>
> 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


> *-------------------------------------------------------------

> ------------------------

>           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


> *----------------------------------------------------------------