RE: CASE WHEN or DECODE - any efficiency differences

> -----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)

I think CASE is preferable because the statement is easier to read, and you can have conditions with AND/OR, which you cannot do using DECODE. To answer your question about performance: an informal test I did on several large tables (millions of rows), comparing a statement with DECODE vs. a statement with CASE, I found that the difference was negligible. Of course I'm not a performance expert so my results may be misleading. Received on Tue Jan 08 2002 - 13:20:22 CST