Home » SQL & PL/SQL » SQL & PL/SQL » Case and Decode (Oracle 9i)
Case and Decode [message #437357] Mon, 04 January 2010 00:36 Go to next message
prakashaa
Messages: 31
Registered: November 2009
Location: Bangalore
Member
Hi Goodmorning all of you
What is the difference Between CASE and DECODE in SQL(not in PL/SQL) and also which one is faster what is the reason please explain me?

Thanks in advance.
Re: Case and Decode [message #437359 is a reply to message #437357] Mon, 04 January 2010 00:46 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Always search Before Posting

Good luck Smile

Sriram Smile
Re: Case and Decode [message #437360 is a reply to message #437357] Mon, 04 January 2010 00:47 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10795/adfns_po.htm#1006214
Re: Case and Decode [message #438039 is a reply to message #437357] Wed, 06 January 2010 22:36 Go to previous messageGo to next message
srikkanthms86
Messages: 6
Registered: January 2010
Location: chennai
Junior Member
Hai.
In case uses expression where as in decode we do not use expression.So decode executes fast than case.
Re: Case and Decode [message #438064 is a reply to message #437357] Thu, 07 January 2010 00:44 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
@srikkanthms86
Are you sure your that your statement is true.....
can you prove it....benchmark it....

Regards,
Pointers
Re: Case and Decode [message #438069 is a reply to message #438039] Thu, 07 January 2010 00:56 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
srikkanthms86 wrote on Thu, 07 January 2010 10:06
Hai.
In case uses expression where as in decode we do not use expression.So decode executes fast than case.


Decode syntax

CASE Expressions

And Read this

sriram Smile
Re: Case and Decode [message #438111 is a reply to message #438069] Thu, 07 January 2010 03:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, I'm going to be horribly unfashionable here, and actually post information rather than links, so please forgive me.

There is little functional difference between CASE and DECODE in SQL statements - there is little that you can do with a CASE that you can't do with Decode (or usually several nested decodes).

CASE's big advantage is that it is MUCH easier to read, and much easier to do range comparisons or complex conditions with - with CASE, a range comparison is simple, wheras with DECODE, you need to nest multiple Decode statements,

As to Speed, the results may suprise you: Here's a test case comparing Decode, searched and non-searched case statements:

The results I get are these:
Test 1: Total: 750 Avg: .75 stddev: .853
Test 2: Total: 699 Avg: .699 stddev: .846
Test 3: Total: 717 Avg: .717 stddev: .848
, showing that the Decode is roughly 5-6% slower than the Case

create or replace type ty_timing_table as table of number;
/

create table test_127 (col_1  varchar2(1));

insert into test_127 select chr(97+mod(level,26)) from dual connect by level <= 10000;

commit;

declare
  
  t_timing    ty_timing_table := ty_timing_table();
  
  v_iter      pls_integer := 1000;
  v_time      pls_integer;
  v_avg       number;
  v_total     pls_integer;
  v_stddev    number;
  
  v_result    varchar2(20);
  
begin
-- pre load data
  for rec in (select col_1 from test_127) loop
    null;
  end loop;
  
  t_timing.delete;
  t_timing.extend(v_iter);

  for i in 1..v_iter loop
    v_time := dbms_utility.get_time;
    
    for rec in (select decode(col_1,'a','A','b','B','Other') from test_127) loop
      null;
    end loop;
    
    t_timing(i) := dbms_utility.get_time - v_time;
  end loop;
  
  select avg(column_value)
        ,stddev(column_value)
        ,sum(column_value)
  into   v_avg,v_stddev,v_total
  from   table(t_timing);
  
  dbms_output.put_line('Test 1: Total: '||v_total||' Avg: '||round(v_avg,3-trunc(log(10,v_avg)))||' stddev: '||round(v_stddev,3));

  t_timing.delete;
  t_timing.extend(v_iter);

  for i in 1..v_iter loop
    v_time := dbms_utility.get_time;
    
    for rec in (select case when col_1 = 'a' then 'A'
                            when col_1 = 'b' then 'B'
                       else 'Other'
                       end from test_127) loop
      null;
    end loop;
    
    t_timing(i) := dbms_utility.get_time - v_time;
  end loop;
  
  select avg(column_value)
        ,stddev(column_value)
        ,sum(column_value)
  into   v_avg,v_stddev,v_total
  from   table(t_timing);
  
  dbms_output.put_line('Test 2: Total: '||v_total||' Avg: '||round(v_avg,3-trunc(log(10,v_avg)))||' stddev: '||round(v_stddev,3));

  t_timing.delete;
  t_timing.extend(v_iter);

  for i in 1..v_iter loop
    v_time := dbms_utility.get_time;
    
    for rec in (select case col_1 when 'a' then 'A'
                                  when 'b' then 'B'
                       else 'Other'
                       end from test_127) loop
      null;
    end loop;
    
    t_timing(i) := dbms_utility.get_time - v_time;
  end loop;
  
  select avg(column_value)
        ,stddev(column_value)
        ,sum(column_value)
  into   v_avg,v_stddev,v_total
  from   table(t_timing);
  
  dbms_output.put_line('Test 3: Total: '||v_total||' Avg: '||round(v_avg,3-trunc(log(10,v_avg)))||' stddev: '||round(v_stddev,3));

  
end;

Re: Case and Decode [message #438115 is a reply to message #438111] Thu, 07 January 2010 03:26 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
./fa/1581/0/ JRowbottom sir.

regards,
Delna
Re: Case and Decode [message #438126 is a reply to message #438111] Thu, 07 January 2010 03:57 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Jrowbottom
But TKPROF stats does not looks supportive enough
Can you please explain this behaviour
SELECT DECODE(COL_1,'a','A','b','B','Other') 
FROM
 TEST_127


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute   1000      0.01       0.01          0          0          0           0
Fetch   101000      6.21       6.20          0     120000          0    10000000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   102001      6.23       6.21          0     120001          0    10000000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61  (AYUSH)   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
10000000  TABLE ACCESS FULL TEST_127 (cr=120000 pr=0 pw=0 time=30745086 us)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
10000000   TABLE ACCESS (FULL) OF 'TEST_127' (TABLE)

********************************************************************************

SELECT CASE WHEN COL_1 = 'a' THEN 'A' WHEN COL_1 = 'b' THEN 'B' ELSE 'Other' 
  END 
FROM
 TEST_127


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute   1000      0.00       0.03          0          0          0           0
Fetch   101000      6.68       6.90          0     120000          0    10000000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   102001      6.68       6.93          0     120001          0    10000000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61  (AYUSH)   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
10000000  TABLE ACCESS FULL TEST_127 (cr=120000 pr=0 pw=0 time=39966674 us)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
10000000   TABLE ACCESS (FULL) OF 'TEST_127' (TABLE)

********************************************************************************

SELECT CASE COL_1 WHEN 'a' THEN 'A' WHEN 'b' THEN 'B' ELSE 'Other' END 
FROM
 TEST_127


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute   1000      0.03       0.02          0          0          0           0
Fetch   101000      6.53       6.95          0     120000          0    10000000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   102001      6.56       6.97          0     120001          0    10000000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61  (AYUSH)   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
10000000  TABLE ACCESS FULL TEST_127 (cr=120000 pr=0 pw=0 time=38900448 us)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
10000000   TABLE ACCESS (FULL) OF 'TEST_127' (TABLE)

********************************************************************************
Re: Case and Decode [message #438140 is a reply to message #438126] Thu, 07 January 2010 04:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
1)What version of the Db are you using - I'm on 10.2.0.4

2() What results do you get when you run my timing test case?
Re: Case and Decode [message #438142 is a reply to message #438140] Thu, 07 January 2010 04:21 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
SQL> @ r
Test 1: Total: 708 Avg: .708 stddev: .863
Test 2: Total: 685 Avg: .685 stddev: .848
Test 3: Total: 675 Avg: .675 stddev: .84

PL/SQL procedure successfully completed.
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
Re: Case and Decode [message #438154 is a reply to message #438142] Thu, 07 January 2010 04:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Curious - which to trust?

Anyone got any thoughts on the matter?
Re: Case and Decode [message #438155 is a reply to message #438154] Thu, 07 January 2010 04:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With such a standard deviation, we can say that all results are the same ones, there is no difference (statistically speaking).

Regards
Michel
Re: Case and Decode [message #438163 is a reply to message #438155] Thu, 07 January 2010 05:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Good catch.

The problem was that the test data set was so small that processing it all took in the order of 0-2 units of time (100'ths of a second,I think)..
If you repeat the test with 100* as many rows in the source table, and 30 iterations, then you get results like these:
Test 1: Total: 2253 Avg: 75.1 stddev: 1.155
Test 2: Total: 2118 Avg: 70.6 stddev: 1.329
Test 3: Total: 2174 Avg: 72.47 stddev: 2.177
Re: Case and Decode [message #438170 is a reply to message #438163] Thu, 07 January 2010 05:47 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
But the problem is in fetch phase of the TKPROF as for DECODE it takes lesser time than a CASE.So how do you explain this behaviour.
Re: Case and Decode [message #438171 is a reply to message #438170] Thu, 07 January 2010 05:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Too few difference to say it is significative.
For me it is the same there are so many things that can explain the differences outside the query execution itself.

Regards
Michel
Re: Case and Decode [message #438172 is a reply to message #438170] Thu, 07 January 2010 05:56 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I don't know.

What I do know is that when you measure the time takes to execute those queries from the client, the Decode is coming out slower.

Try re-running your trace with the new size of table - possibly we were getting a similar granularity problem.
Previous Topic: blob data in oracle 10g
Next Topic: oracle 10g
Goto Forum:
  


Current Time: Mon Dec 05 18:56:32 CST 2016

Total time taken to generate the page: 0.05231 seconds