Menu
Referring to a previous question, i was wondering if its always possible to replace DECODE by CASE and which one is better for performance?
840
DECODE can check equality operators only where as CASE can support all relational operators DECODE can be used in sql only where as CASE can be used in SQL AND PL/SQL CASE is better than DECODE. -1 for "...and can not use the DECODE in the where clause." DECODE is a function and every function has a return value.
There is very little performance difference between CASE and DECODE on the same platform. One has to run 100's of 1000's of iterations to see a difference, and even then it is debatable of whether that difference is just due to the CASE vs DECODE.
The case statement you can use in PL SQL but as decode is function then you require to use only in SQL statements. You can also use in PLSQL but not like Case statement. You always need to use it in select statement.
DECODE compares the expression to each search value one by one. If expression is equal to a search, then the corresponding result is returned by the Oracle Database. If a match is not found, then default is returned. If default is omitted, then Oracle returns null.
There is one big difference between DECODE and CASE and it has to do with how NULLs are compared. DECODE will return "true" if you compare NULL to NULL. CASE will not. For example:
DECODE(NULL, NULL, 1, 0) will return '1'.
CASE NULL WHEN NULL THEN 1 ELSE 0 END will return '0'. You would have to write it as:
CASE WHEN NULL IS NULL THEN 1 ELSE 0 END As always with Oracle ... AskTom...
From this post...
Decode is somewhat obscure -- CASE is very very clear. Things that are easy to do in decode are easy to do in CASE, things that are hard or near impossible to do with decode are easy to do in CASE. CASE, logic wise, wins hands down.
From a performance point of view seems they are about the same, again above article mentions some speed differences but without benchmarking the particular statements it's hard to say.
40
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
