Reputation: 1743
Which is the better way to use CASE in Oracle?
I'm curious which is better in these queries (performance or anything).
SELECT some_column,
CASE case_column
WHEN 1 THEN 'a'
WHEN 2 THEN 'a'
WHEN 3 THEN 'a'
WHEN 5 THEN 'b'
WHEN 6 THEN 'b'
...
END AS case_column_str
FROM some_table ORDER BY case_column_str
or
SELECT some_column,
CASE
WHEN case_column=1 OR case_column=2 OR case_column=3 THEN 'a'
WHEN case_column=5 OR case_column=6 THEN 'b'
...
END AS case_column_str
FROM some_table ORDER BY case_column_str
Does either of these queries have advantages over the other? Are there any significant differences between the two except for the second being able to use other field for more filtering? What if I'm just going to filter a single column?
Upvotes: 0
Views: 123
Answers (5)
Reputation: 397
Here you have mentioned two kind of case statements. 1st example is of normal case statement. 2nd example is a searched case statement.
1st form is pretty much equivalent to decode function of oracle. So I don't see any particular use of this type of case.
Whereas 2nd form is like if else coding paradigm. Through this multiple conditions can be checked which otherwise cannot be checked in 1st form.
From performance perspective, there is no difference between two ways. When both queries are executed in SQL DEVELOPER, both queries are giving same cost value.
Hope this helps.!!
Upvotes: 0
Reputation: 146259
The two forms of CASE syntax allow us to use different conditions.
The first form is only relevant for filtering on a single column, but even then it is pretty rigid. Given your example rule I would still opt to use the second variant, but with a less verbose formulation ...
CASE
WHEN case_column in (1,2,3) THEN 'a'
WHEN case_column in (5,6) THEN 'b'
ELSE 'c'
END CASE
... or perhaps ...
CASE
WHEN case_column <= 3 THEN 'a'
WHEN case_column between 5 and 6 THEN 'b'
ELSE 'c'
END CASE
Upvotes: 1
Reputation: 10941
They are parsed identically.
13:41:48 SYSTEM@oars_sandbox> create table t as select mod(rownum,5) val from dual connect by rownum <= 1e5;
Table created.
Elapsed: 00:00:00.21
Notice "column projection information". First case:
13:43:51 SYSTEM@oars_sandbox> ed
Wrote file S:\\tools\buffer.sql
1 SELECT CASE val
2 WHEN 1 THEN 'a'
3 WHEN 2 THEN 'a'
4 WHEN 3 THEN 'a'
5 WHEN 5 THEN 'b'
6 END AS case_column_str
7 FROM t
8* ORDER BY case_column_str
13:44:32 SYSTEM@oars_sandbox> @xplan
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 961378228
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 114K| 1450K| | 591 (2)| 00:00:08 |
| 1 | SORT ORDER BY | | 114K| 1450K| 2256K| 591 (2)| 00:00:08 |
| 2 | TABLE ACCESS FULL| T | 114K| 1450K| | 44 (3)| 00:00:01 |
-----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) CASE "VAL" WHEN 1 THEN 'a' WHEN 2 THEN 'a' WHEN 3 THEN
'a' WHEN 5 THEN 'b' END [1]
2 - "VAL"[NUMBER,22]
Note
-----
- dynamic sampling used for this statement (level=2)
Second case:
13:44:36 SYSTEM@oars_sandbox> ed
Wrote file S:\\tools\buffer.sql
1 SELECT CASE WHEN val=1 OR val=2 OR val=3 THEN 'a'
2 WHEN val=5 OR val=6 THEN 'b'
3 END AS case_column_str
4 FROM t
5* ORDER BY case_column_str
13:45:53 6
13:45:55 SYSTEM@oars_sandbox> @xplan
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 961378228
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 114K| 1450K| | 591 (2)| 00:00:08 |
| 1 | SORT ORDER BY | | 114K| 1450K| 2256K| 591 (2)| 00:00:08 |
| 2 | TABLE ACCESS FULL| T | 114K| 1450K| | 44 (3)| 00:00:01 |
-----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) CASE "VAL" WHEN 1 THEN 'a' WHEN 2 THEN 'a' WHEN 3 THEN
'a' WHEN 5 THEN 'b' END [1]
2 - "VAL"[NUMBER,22]
Note
-----
- dynamic sampling used for this statement (level=2)
Explain plan query:
select * from table(dbms_xplan.display(null, null, 'all'));
Upvotes: 3
Reputation: 754520
Any decent optimizer will treat the two as essentially identical; it is very unlikely you'll be able to measure the difference in performance. Oracle has a decent enough optimizer that you'll be hard-pressed to measure the difference at all. You could look at the query plans, but don't be surprised if they're identical.
Upvotes: 3
Reputation: 2515
SELECT some_column,
CASE
WHEN case_column=1 or case_column=2 or case_column=3 THEN 'a'
WHEN case_column=5 or case_column=6 THEN 'b'
...
END as case_column_str
FROM some_table order by case_column_str
This is far better as if any one of them is true in the case, we dont have to check the rest of the conditions
That is not the case with the first option, over there we would have to pass through each individual condition one by one.
Upvotes: -1