开发者

CASE vs. DECODE

Referring t开发者_开发知识库o a previous question, i was wondering if its always possible to replace DECODE by CASE and which one is better for performance?


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.


From performance perspective, In Oracle decode and CASE does not make any difference.

But in Exadata , Decode is faster than CASE.

The Decode operation is done at storage Server level where the data is present BUT CASE is done at DB Instance level which receives data from DB storage Level.

Though that network transfer of data between Storage and DB server is less (Infiniband connection), that transfer is avoided when you use decode statment


CASE is a statement and DECODE is a function We can use the CASE in the where clause and can not use the DECODE in the where clause. 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.

You can find more: http://www.oraclegeneration.com/2014/01/sql-query-interview-questions.html


An old thread, I know but another interesting comparison between CASE and DECODE... pick your poison.

SELECT CASE 1 WHEN '1' THEN 'A' ELSE 2 END result 

FROM DUAL 

/*
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*/

;

SELECT DECODE(1, '1', 'A', 2) result

FROM DUAL

/*
A
*/

;


select (DECODE(NULL, NULL, 1, 0)) from dual;

select (CASE
    WHEN NULL IS NULL THEN 1
    ELSE 0

END
)
from dual;

both return 1


Both the NULL to NULL Comparison returns the value as 1 instead of DECODE returning the value of 1 and CASE returning as 0.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜