Re: *DECODE* to implement IF condition???

From: Richard Kuhler <noone_at_nowhere.com>
Date: Fri, 02 Nov 2001 16:52:12 GMT
Message-ID: <0bAE7.14192$D5.4207676@typhoon.san.rr.com>

Doh! Sorry but I've mixed those benchmarks up. They should be:

> case: 2.5 seconds
> sign/decode: 14.46 seconds (ouch!)
> subquery: 1.9 seconds

The sign/decode solution is the real dog among them. That's mostly attributable to the 'least' function call. If I change it to:

select decode(col4,
  'ABC', decode(

     sign(sysdate - date1), 1, 'DEF YES', 
     sign(date2 - sysdate), 1, 'DEF YES', 
     'DEF NO'),
  'DEF', decode(
     sign(sysdate - date1), 1, 'DEF YES', 
     sign(date2 - sysdate), 1, 'DEF YES', 
     'DEF NO'),
     'NEITHER ABC NOR DEF') as x

from t1

the timing comes down to

sign/decode v2: 6.9 seconds (still ouch!)

That date arithmetic is just ridiculously slow.

Sorry,
Richard Received on Fri Nov 02 2001 - 10:52:12 CST