Case & Decode [message #448787] |
Thu, 25 March 2010 04:04  |
harshalonline22
Messages: 74 Registered: March 2007 Location: Pune
|
Member |
|
|
Hi,
Please consider the following CASE Statment :
sum(CASE WHEN gcp.goldn_copy_sent_to_subscrbr = 'Y'
THEN 1
ELSE 0
END) AS sent_to_subscrbr_cnt
Above CASE can be replced by DECODE as follows :
DECODE(gcp.goldn_copy_sent_to_subscrbr, 'Y' , 1 , 0) AS sent_to_subscrbr_cnt
But if we have two conditions in CASE statment like follow :
sum(CASE WHEN gcp.goldn_copy_sent_to_subscrbr = 'Y'
AND gcp.goldn_copy_rcvd_by_subscrbr = 'Y'
THEN 1
ELSE 0
END) AS sec_published_cnt
Then in this case how can we replace it with decode?
Also performace wise which is good to use CASE or DECODE ?
Best Regards,
Harshal
|
|
|
|
|
|
|
Re: Case & Decode [message #448824 is a reply to message #448822] |
Thu, 25 March 2010 05:33   |
harshalonline22
Messages: 74 Registered: March 2007 Location: Pune
|
Member |
|
|
Index is not be defined on CASE or DECODE. It is to be on table.
It is not elders who can only use DECODE, depending on the scenario of code complexity we can decide!!!!
|
|
|
Re: Case & Decode [message #448826 is a reply to message #448824] |
Thu, 25 March 2010 05:38   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote:Index is not be defined on CASE or DECODE. It is to be on table.
What I mean is:
create index i on mytable decode(col,1,col2,col3);
Quote:It is not elders who can only use DECODE, depending on the scenario of code complexity we can decide!!!!
Why would youngers use something they more hardly and have to learn to understand when CASE is immediate? CASE is learned in school (and is immediate to one that can read english), DECODE is not.
You can use it if you want, it was just an advice and as any advice can be ignored.
You ask, I answer.
Regards
Michel
|
|
|
Re: Case & Decode [message #448857 is a reply to message #448787] |
Thu, 25 March 2010 06:58   |
maheshmhs
Messages: 93 Registered: October 2008 Location: Nepal
|
Member |
|
|
By my experience, I have found out that decode is much more faster than case especially when the records are in tens of millions and we have to apply it for many columns.
This is totally out of my experience.
And you could use something like
DECODE (gcp.goldn_copy_sent_to_subscrbr||gcp.goldn_copy_rcvd_by_subscrbr,'YY',1,0)
[Updated on: Thu, 25 March 2010 07:23] Report message to a moderator
|
|
|
|
Re: Case & Decode [message #448922 is a reply to message #448787] |
Thu, 25 March 2010 14:37   |
 |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
I presume that in 99.99% of the cases, CASE and DECODE are CPU focused code components. This simple test (not necessarily a very good one), suggests that there is little difference between the two:
SQL> select cpu_time,executions,round(cpu_time/executions) avg_cpu_time,sql_text from v$sqlarea where sql_text like 'select count(%dba_source%'
2 /
CPU_TIME EXECUTIONS AVG_CPU_TIME SQL_TEXT
---------- ---------- ------------ ----------------------------------------------------------------------------------------------------------------------
10642462 5 2128492 select count(case when text is null then 1 else null end) not_compressed ,count(case when text is
11052294 5 2210459 select count(decode(text,null,1,null)) not_compressed ,count(decode(text,null,null,1)) compressed
2 rows selected.
Notice the almost inpercetable difference. OK, as I said this was a quick and dirty but seems to me there would need to be millions of rows before you saw any difference at all. Of course this was a test of a simple situation. More complex expressions might give different results. But I would need to see such situations first before I accepted that there was a very big difference.
So I suggest that you use whatever expression makes your code more readable. Indeed, even if there was a difference between the two, I would still say use the expression that makes your code more readable and forget about the performance difference.
Here was my test code in case anyone cares to retry it.
select count(case when text is null then 1 else null end) not_compressed
,count(case when text is not null then 1 else null end) compressed
from dba_source
/
select count(decode(text,null,1,null)) not_compressed
,count(decode(text,null,null,1)) compressed
from dba_source
/
select cpu_time,executions,round(cpu_time/executions) avg_cpu_time,sql_text from v$sqlarea where sql_text like 'select count(%dba_source%'
/
Kevin
|
|
|
|