Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, sudhir.

Asked: February 14, 2011 - 8:45 am UTC

Last updated: February 16, 2011 - 8:39 am UTC

Version: 10.0.1

Viewed 50K+ times! This question is

You Asked

Hi Tom,
Can you please explain which performs better - case or decode?


and Tom said...

hmmm, how do you think I would answer this...

With a small benchmark perhaps?



ops$tkyte%ORA11GR2> create table t as select mod(rownum,10) id, a.* from all_objects a;

Table created.


select decode( id, 1, 'x', 2, 'y', 3, 'z', 4, 'a', 5, 'b', 6, 'c', 7, 'd', 8, 'e', 9, 'f', 0, 'g' ) from t

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     4834      0.03       0.17       1063       5837          0       72487
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     4836      0.03       0.17       1063       5838          0       72487
********************************************************************************
select case id when 1 then 'x' when 2 then 'y' when 3 then 'z' when 4 then 'a' when 5 then 'b' when 6 then 'c'
       when 7 then 'd' when 8 then 'e' when 9 then 'f' when 0 then 'g' end from t

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     4834      0.03       0.15       1063       5837          0       72487
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     4836      0.03       0.15       1063       5838          0       72487
********************************************************************************
select case
       when id=1 then 'x' when id=2 then 'y' when id=3 then 'z' when id=4 then 'a' when id=5 then 'b' 
       when id=6 then 'c' when id=7 then 'd' when id=8 then 'e' when id=9 then 'f' when id=0 then 'g'
       end from t

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     4834      0.04       0.16       1063       5837          0       72487
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     4836      0.04       0.16       1063       5838          0       72487


they are about the same...

Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Jignesh Vyas, February 15, 2011 - 6:16 am UTC

Hi Tom,

In order to test the performance, what if we loop those SELECTs say for 100,000 times or more ? will it give a better idea on what is better CASE or DECODE ?

Thanks,
Jignesh
Tom Kyte
February 15, 2011 - 8:40 am UTC

go for it??? It sure doesn't look like it would make a tiny bit of difference. Do you really expect to have a tight loop that is executed 72,000,000 times?

Why not

BillC, February 15, 2011 - 7:53 am UTC

I don't want to put words in anyone's mouth but I suspect that Tom would say to try it and then report the results in this thread.
Tom Kyte
February 15, 2011 - 8:42 am UTC

bingo....

Hmmm..

Enrique Aviles, February 15, 2011 - 8:13 am UTC

I thought Tom was going to give the answer that is correct 99% of the time: "it depends". :D

Already shown ?

Tim M, February 15, 2011 - 12:20 pm UTC

Did not Tom's case already show this repeated for a high number? His example brings back 72,487 rows which would require the DECODE / CASE portion of the statement to be executed at least this many times - correct?
In a previous post, Tom has shown that both DECODE and CASE will use short-circuit logic (as opposed to NVL and NVL2):
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:926029357278
(which would have had the potential for a difference in performance should either CASE or DECODE use short circuit logic and the other not)
In my opinion, one should use either CASE or DECODE based on which tends to look better for the given circumstance, which one the developer is more familiar with and your personal preference. For instance with inequality or range - I think CASE is much easier to read. Simple A or B - I tend to like DECODE.
I believe it has been shown here that performance should not be a consideration when trying to decide between CASE or DECODE.


Tom Kyte
February 15, 2011 - 12:28 pm UTC

I sort of like decode in some cases because of the way it deals with null...


decode( x, 1, 'one', 2, 'two', null, 'null' )


it makes null = null :)


I sort of like case in most cases because it is much easier to read and supports complex predicates in the expression - using or's and and's and the like.

Thanks

sudhir, February 15, 2011 - 11:51 pm UTC

Thanks for the response with example.

Jignesh, February 15, 2011 - 11:59 pm UTC

Tom, Thanks a lot for the feedback.

I have tested it on a table of 2 million rows on version 10.2.0.4.0

What i am not able to understand is : while autotrace ON, the statistics are almost similar in both - CASE as well as DECODE, still difference in performance.May be this behavior of poor performance by DECODE is due to 10g which is different than that of 11g (short circuit)?

I guess the trace table presented by you in your first follow-up in this thread is generated by tkprof. Is there a way to generate it through SQL prompt?

************************************************************************************************************
SQL*Plus: Release 8.1.7.0.0 - Production on Wed Feb 16 10:32:36 2011

(c) Copyright 2000 Oracle Corporation. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set timing on

==> First execution of CASE : 7.92 seconds
SQL> SELECT COUNT(1)
2 FROM policy_register
3 WHERE 'ALL' = CASE preg_underwrite_company WHEN 'ALL' THEN 'ALL' ELSE 'ALL' END
4 AND 'ALL' = CASE preg_product_code WHEN 'ALL' THEN 'ALL' ELSE 'ALL' END
5 AND 'ALL' = CASE preg_state_code WHEN 'ALL' THEN 'ALL' ELSE 'ALL' END
6 AND 'ALL' = CASE preg_bill_type WHEN 'ALL' THEN 'ALL' ELSE 'ALL' END
7 AND 'ALL' = CASE preg_broker WHEN 'ALL' THEN 'ALL' ELSE 'ALL' END
8 AND 'ALL' = CASE DECODE(preg_inception_date, NULL, 'NEW', 'REN') WHEN 'ALL' THEN 'ALL' ELSE 'ALL' END
9 AND 'ALL' = CASE preg_payment_plan WHEN 'ALL' THEN 'ALL' ELSE 'ALL' END ;

COUNT(1)
----------
2061577

Elapsed: 00:00:07.92

==> Second execution of CASE : 0.82 seconds
SQL> SELECT COUNT(1)
2 FROM policy_register
3 WHERE 'ALL' = CASE preg_underwrite_company WHEN 'ALL' THEN 'ALL' ELSE 'ALL' END
4 AND 'ALL' = CASE preg_product_code WHEN 'ALL' THEN 'ALL' ELSE 'ALL' END
5 AND 'ALL' = CASE preg_state_code WHEN 'ALL' THEN 'ALL' ELSE 'ALL' END
6 AND 'ALL' = CASE preg_bill_type WHEN 'ALL' THEN 'ALL' ELSE 'ALL' END
7 AND 'ALL' = CASE preg_broker WHEN 'ALL' THEN 'ALL' ELSE 'ALL' END
8 AND 'ALL' = CASE DECODE(preg_inception_date, NULL, 'NEW', 'REN') WHEN 'ALL' THEN 'ALL' ELSE 'ALL' END
9 AND 'ALL' = CASE preg_payment_plan WHEN 'ALL' THEN 'ALL' ELSE 'ALL' END ;

COUNT(1)
----------
2061577

Elapsed: 00:00:00.82


==> First execution of DECODE : 7.05 seconds
SQL> SELECT COUNT(1)
2 FROM policy_register
3 WHERE 'ALL' = DECODE(preg_underwrite_company,'ALL','ALL' ,'ALL')
4 AND 'ALL' = DECODE(preg_product_code,'ALL','ALL' ,'ALL')
5 AND 'ALL' = DECODE(preg_state_code,'ALL','ALL' ,'ALL')
6 AND 'ALL' = DECODE(preg_bill_type,'ALL','ALL' ,'ALL')
7 AND 'ALL' = DECODE(preg_broker,'ALL','ALL' ,'ALL')
8 AND 'ALL' = DECODE(DECODE(preg_inception_date, NULL, 'NEW', 'REN') ,'ALL','ALL' ,'ALL')
9 AND 'ALL' = DECODE(preg_payment_plan,'ALL','ALL' ,'ALL');

COUNT(1)
----------
2061577

Elapsed: 00:00:07.05

==> Second execution of DECODE : 6.39 seconds
SQL> SELECT COUNT(1)
2 FROM policy_register
3 WHERE 'ALL' = DECODE(preg_underwrite_company,'ALL','ALL' ,'ALL')
4 AND 'ALL' = DECODE(preg_product_code,'ALL','ALL' ,'ALL')
5 AND 'ALL' = DECODE(preg_state_code,'ALL','ALL' ,'ALL')
6 AND 'ALL' = DECODE(preg_bill_type,'ALL','ALL' ,'ALL')
7 AND 'ALL' = DECODE(preg_broker,'ALL','ALL' ,'ALL')
8 AND 'ALL' = DECODE(DECODE(preg_inception_date, NULL, 'NEW', 'REN') ,'ALL','ALL' ,'ALL')
9 AND 'ALL' = DECODE(preg_payment_plan,'ALL','ALL' ,'ALL');

COUNT(1)
----------
2061577

Elapsed: 00:00:06.39

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

SQL> set autotrace on

SQL> SELECT COUNT(1)
2 FROM policy_register
3 WHERE 'ALL' = DECODE(preg_underwrite_company,'ALL','ALL' ,'ALL')
4 AND 'ALL' = DECODE(preg_product_code,'ALL','ALL' ,'ALL')
5 AND 'ALL' = DECODE(preg_state_code,'ALL','ALL' ,'ALL')
6 AND 'ALL' = DECODE(preg_bill_type,'ALL','ALL' ,'ALL')
7 AND 'ALL' = DECODE(preg_broker,'ALL','ALL' ,'ALL')
8 AND 'ALL' = DECODE(DECODE(preg_inception_date, NULL, 'NEW', 'REN') ,'ALL','ALL' ,'ALL')
9 AND 'ALL' = DECODE(preg_payment_plan,'ALL','ALL' ,'ALL');

COUNT(1)
----------
2061577

Elapsed: 00:00:06.02
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16260 Card=1 Bytes
=30)

1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'POLICY_REGISTER' (TABLE) (Cost=1
6260 Card=1 Bytes=30)





Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
89173 consistent gets
0 physical reads
0 redo size
203 bytes sent via SQL*Net to client
255 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed



SQL> SELECT COUNT(1)
2 FROM policy_register
3 WHERE 'ALL' = CASE preg_underwrite_company WHEN 'ALL' THEN 'ALL' ELSE 'ALL' END
4 AND 'ALL' = CASE preg_product_code WHEN 'ALL' THEN 'ALL' ELSE 'ALL' END
5 AND 'ALL' = CASE preg_state_code WHEN 'ALL' THEN 'ALL' ELSE 'ALL' END
6 AND 'ALL' = CASE preg_bill_type WHEN 'ALL' THEN 'ALL' ELSE 'ALL' END
7 AND 'ALL' = CASE preg_broker WHEN 'ALL' THEN 'ALL' ELSE 'ALL' END
8 AND 'ALL' = CASE DECODE(preg_inception_date, NULL, 'NEW', 'REN') WHEN 'ALL' THEN 'ALL' ELSE 'ALL' END
9 AND 'ALL' = CASE preg_payment_plan WHEN 'ALL' THEN 'ALL' ELSE 'ALL' END ;

COUNT(1)
----------
2061577

Elapsed: 00:00:00.13
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16221 Card=1 Bytes
=30)

1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'POLICY_REGISTER' (TABLE) (Cost=1
6221 Card=2061577 Bytes=61847310)





Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
89173 consistent gets
0 physical reads
0 redo size
221 bytes sent via SQL*Net to client
255 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


Tom Kyte
February 16, 2011 - 8:39 am UTC

if you would have used a more recent sqlplus, you would have seen "why" right away


If you were an optimizer and you saw:

when X then X else X end;

what might you optimizer that code into. I would optimize it into 'X'.

And if you saw

where 'x' = ... when 'x' then 'x' else 'x' end

what might you optimize that into?

[this space left intentionally blank]


ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * from dual where 'x' = case dummy when 'x' then 'x' else 'x' end;

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

ops$tkyte%ORA11GR2> select * from dual where 'x' = decode( dummy, 'x', 'x', 'x' );

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(DECODE("DUMMY",'x','x','x')='x')

ops$tkyte%ORA11GR2>



See the predicate information - optimized away in case 1, not so in case 2.

Now - do two things


a) set up more real world examples testing actual behavior :)
b) get up to date on sqlplus - 8.1.7 sqlplus means you are missing out on lots of stuff invented this century.

Oleksandr Alesinskyy, February 16, 2011 - 2:50 pm UTC

This
http://jonathanlewis.wordpress.com/2010/12/16/fbi-oddities/
may be of some interest - depending on what for CASE and DECODE are used.

Thanks

Jignesh, February 17, 2011 - 6:25 am UTC

Tom, Thanks a lot; Will certainly move to the higher version of sqlplus.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.