Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: DECODE V/s CASE

Re: DECODE V/s CASE

From: <mjbox01_at_gmail.com>
Date: 15 Feb 2006 14:10:57 -0800
Message-ID: <1140041457.143129.170990@g14g2000cwa.googlegroups.com>


pankaj_wolfhunter_at_yahoo.co.in wrote:
> Thanks for the reply Hans. This means that whether we use DECODE or
> CASE its one and the same thing as far as performance is concerned. But
> while browing net, many places its given that "CASE executes faster
> than DECODE".
> In general, should we go with this statement?

No.

You should test and benchmark your own particualr requirements. If you say it is faster because you read it somewhere at some point you will be wrong. Guaranteed.

For example, at one point I believed there was no difference. Then I created a particular test which required DECODE to be combined with SIGN as the comparison was not simple equivalence.

In 9i CASE was much faster than the DECODE / SIGN combination. I thought CASE was faster. I told someone who asked how I knew. I mentioned the test I had created and they were interested in it. I recreated it and ran it in 10g. No difference. I guess teh Oracle developers had optimized away the difference.

In most situations, such as range testing, CASE is easier to write and easier to read. It can do everything DECODE can do, but if you have a straight if A then B, if C then D etc, DECODE can be simpler.

And if you are worried about performance test it in your environment. Received on Wed Feb 15 2006 - 16:10:57 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US