Home » SQL & PL/SQL » SQL & PL/SQL » Case & Decode (Oracle 10g)
Case & Decode [message #448787] Thu, 25 March 2010 04:04 Go to next message
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 #448794 is a reply to message #448787] Thu, 25 March 2010 04:21 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes you can but it would be complex to read and maintain.
Stay with CASE for complex conditions.

There is no difference in performances between the both when they are appropriately used (that is not DECODE for complex conditions).

Regards
Michel
Re: Case & Decode [message #448795 is a reply to message #448787] Thu, 25 March 2010 04:22 Go to previous messageGo to next message
knw15pwr
Messages: 134
Registered: March 2010
Senior Member
DECODE (gcp.goldn_copy_sent_to_subscrbr, 'Y',(DECODE(gcp.goldn_copy_rcvd_by_subscrbr,'Y',1,0)),0)


Hope this is what you are looking for. And yes, it would be better to use CASE in such scenario.

[Updated on: Thu, 25 March 2010 05:06]

Report message to a moderator

Re: Case & Decode [message #448815 is a reply to message #448794] Thu, 25 March 2010 05:20 Go to previous messageGo to next message
harshalonline22
Messages: 74
Registered: March 2007
Location: Pune
Member
Thanks very much.

Checked with explain plan.

No difference in plan for both DECODE & CASE.

CASE seems much better to use.

Best Regards,
Harshal
Re: Case & Decode [message #448822 is a reply to message #448815] Thu, 25 March 2010 05:27 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is never a difference in plan depending on CASE or DECODE choice (unless you defined an index on one of them).

If you are not used to write with DECODE, always use CASE and let DECODE to elders.

Regards
Michel
Re: Case & Decode [message #448824 is a reply to message #448822] Thu, 25 March 2010 05:33 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 #448860 is a reply to message #448857] Thu, 25 March 2010 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Some test cases have been posted here that showed any difference in performances.
To talk about performances you must have a clear environment to make performances tests, without it it is just a feeling.

Regards
Michel
Re: Case & Decode [message #448922 is a reply to message #448787] Thu, 25 March 2010 14:37 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
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
Re: Case & Decode [message #448936 is a reply to message #448857] Thu, 25 March 2010 22:09 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
http://www.orafaq.com/forum/mv/msg/153642/438111/136607/#msg_438111

sriram Smile
Previous Topic: carry value
Next Topic: NONCLUSTERD Index Creation
Goto Forum:
  


Current Time: Tue Sep 27 05:57:59 CDT 2016

Total time taken to generate the page: 0.14537 seconds