使用decode函数
Decode函数使用：
Oracle 的decode函数蛮有意思，是oracle独有的，国际标准SQL中并没有decode函数。
语法
DECODE(col|expression, search1, result1
[, search2, result2,...,]
[, default])
例子
SELECT product_id,
DECODE (warehouse_id, 1, 'Southlake',
2, 'San Francisco',
3, 'New Jersey',
4, 'Seattle',
'Non domestic') "Location"
FROM inventories
WHERE product_id < 1775
ORDER BY product_id, "Location";
从上面简单的语法和例子中可以看出decode函数也可以做判断，可以实现case…when…then…else..end 和 if..then..else..end if 同样的功能。
Decode函数优点：
1、 使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表，从而减少数据处理时间
例如：想要统计scott用户下emp表中部门20和部门30各有多少员工，每个部门工资成本。
SELECT COUNT(*),
SUM(SAL) FROM EMP WHERE DEPTNO = 20;
SELECT COUNT(*),
SUM(SAL) FROM EMP WHERE DEPTNO = 30;
上面的语句可以合并成一条可以达到同样的目的
SELECT COUNT(DECODE(DEPTNO,20,'X',NULL)) D20_COUNT,
COUNT(DECODE(DEPTNO,30,'X',NULL)) D30_COUNT,
SUM(DECODE(DEPTNO,20,SAL,NULL)) D20_SAL,
SUM(DECODE(DEPTNO,30,SAL,NULL)) D30_SAL
FROM EMP；
2、 简化了代码
上面的例子如果使用case when 来实现写起来会多写一些代码
SELECT product_id,
Case warehouse_id
When 1 then 'Southlake'
When 2 then 'San Francisco'
When 3 then 'New Jersey'
When 4 then 'Seattle'
Else 'Non domestic'
End as "Location"
FROM inventories
WHERE product_id < 1775
ORDER BY product_id, "Location";
建议：如果只是简单的判断使用decode函数简单明了
Decode函数性能对比case when性能
If..then..end if是一种落后的判断方式，这里不做对比了。
经常在网上看到说使用某个函数或者某种写法效率怎么怎么高，执行效率如何还是得具体测试：
SQL> CREATE TABLE T AS
2 SELECT A.*
3 FROM DBA_OBJECTS A, DBA_MVIEWS;
Table created.
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
6075760
下面检查DECODE和两种CASE语句的效率：
SQL> SET ARRAY 1000
SQL> SET TIMING ON
SQL> SET AUTOT TRACE
SQL> SELECT DECODE(OWNER, 'SYSTEM', 'SYSTEM', 'SYS', 'SYSTEM', 'USER')
2 FROM T;
6075760 rows selected.
Elapsed: 00:00:07.24
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4245K| 68M| 13828 (1)| 00:03:14 |
| 1 | TABLE ACCESS FULL| T | 4245K| 68M| 13828 (1)| 00:03:14 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
47551 consistent gets
0 physical reads
0 redo size
46288564 bytes sent via SQL*Net to client
67317 bytes received via SQL*Net from client
6077 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6075760 rows processed
SQL> SELECT CASE OWNER WHEN 'SYSTEM' THEN 'SYSTEM'
2 WHEN 'SYS' THEN 'SYSTEM'
3 ELSE 'USER' END
4 FROM T;
6075760 rows selected.
Elapsed: 00:00:07.22
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4245K| 68M| 13828 (1)| 00:03:14 |
| 1 | TABLE ACCESS FULL| T | 4245K| 68M| 13828 (1)| 00:03:14 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
47551 consistent gets
0 physical reads
0 redo size
46288578 bytes sent via SQL*Net to client
67317 bytes received via SQL*Net from client
6077 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6075760 rows processed
SQL> SELECT CASE WHEN OWNER = 'SYSTEM' THEN 'SYSTEM'
2 WHEN OWNER = 'SYS' THEN 'SYSTEM'
3 ELSE 'USER' END
4 FROM T;
6075760 rows selected.
Elapsed: 00:00:07.23
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4245K| 68M| 13828 (1)| 00:03:14 |
| 1 | TABLE ACCESS FULL| T | 4245K| 68M| 13828 (1)| 00:03:14 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
47551 consistent gets
0 physical reads
0 redo size
46288585 bytes sent via SQL*Net to client
67317 bytes received via SQL*Net from client
6077 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6075760 rows processed
测试结果是CASE的简单表达式写法效率最高，然后是CASE的另一种写法，DECODE效率最低。但是对于600W的记录，最终结果只有0.01到0.02秒的查询，实在没有办法得出上面的结论，因为这个差别实在是太小，以至于任何其他的一些影响都足以改变测试结果，如要一定要得出结论，那么结论就是3种方式的效率基本相同。
不过由于CASE表达式更加灵活，使得以前DECODE必须运用的一些技巧得以简化，这时使用CASE方式，确实可以得到一些性能上的提高，比如：
SQL> SELECT DECODE(SIGN(OBJECT_ID), 1, '+', -1, '-', '0')
2 FROM T;
6075760 rows selected.
Elapsed: 00:00:04.94
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4245K| 52M| 13840 (1)| 00:03:14 |
| 1 | TABLE ACCESS FULL| T | 4245K| 52M| 13840 (1)| 00:03:14 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
47551 consistent gets
0 physical reads
0 redo size
31491431 bytes sent via SQL*Net to client
67317 bytes received via SQL*Net from client
6077 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6075760 rows processed
SQL> SELECT CASE WHEN OBJECT_ID > 0 THEN '+'
2 WHEN OBJECT_ID < 0 THEN '-'
3 ELSE '0' END
4 FROM T;
6075760 rows selected.
Elapsed: 00:00:04.60
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4245K| 52M| 13840 (1)| 00:03:14 |
| 1 | TABLE ACCESS FULL| T | 4245K| 52M| 13840 (1)| 00:03:14 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
47551 consistent gets
0 physical reads
0 redo size
31491449 bytes sent via SQL*Net to client
67317 bytes received via SQL*Net from client
6077 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6075760 rows processed
这里CASE带来性能提升的主要原因实际上是CASE避免了SIGN函数的调用，而并不是CASE本身的性能要高于DECODE，事实上如果这里使用SIGN并利用CASE的所谓高效语法：
SQL> SELECT CASE SIGN(OBJECT_ID) WHEN 1 THEN '+'
2 WHEN -1 THEN '-'
3 ELSE '0' END
4 FROM T;
6075760 rows selected.
Elapsed: 00:00:04.97
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4245K| 52M| 13840 (1)| 00:03:14 |
| 1 | TABLE ACCESS FULL| T | 4245K| 52M| 13840 (1)| 00:03:14 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
47551 consistent gets
0 physical reads
0 redo size
31491445 bytes sent via SQL*Net to client
67317 bytes received via SQL*Net from client
6077 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6075760 rows processed
可以看到，这时效率比DECODE还低。
总结
1、 case语句和decode函数执行效率方面
无论是DECODE还是CASE方式的两种写法，执行效率没有明显的差别。
2、 代码实现方面
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表，从而减少数据处理时间。
如果只是简单的判断使用decode函数简单明了。
以上文档测试并整理自互联网
官方参考文档：
Oracle® Database
SQL Language Reference
11g Release 2 (11.2)
E17118-04
5 FUNCTIONSàDECODE
