oracle函数之case和decode的用法区别及性能比较

   在oracle世界，你可以使用：

    1）case表达式      或者

    2）decode函数

    来实现逻辑判断。Oracle的DECODE函数功能很强，灵活运用的话可以避免多次扫描，从而提高查询的性能。而CASE是9i以后提供的语法，这个语法更加的灵活，提供了IF THEN ELSE的功能。   

    case表达式

    case表达式，可分两种，简单和搜索，简单case后接表达式，如：

    对于简单的case需要几点注意：

    1）寻找when的优先级：从上到下

    2）再多的when，也只有一个出口，即其中有一个满足了expr就马上退出case

    3）不能把return_expr和else_expr指定为null，而且，expr、comparison_expr和return_expr的数据类型必须相同。

    搜索case：

    CASE WHEN condition THEN return_expr

              [WHEN condition THEN return_expr]

              ...

    ELSE else_expr

    END

    例子：

    用decode可以违反第3NF（行不可再分，列不可再分，列不可重复）：列重复

    一个字段，decode函数可以完全改写简单case；

    多个字段，需要复杂的case，方可。

    语法：
    DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value等于if1时，DECODE函数的结果返then1,...,如果不等于任何一个if值，则返回else。可以用函数或表达式来替代value，if，then，else从而作出一些更有用的比较。

    来看看具体的运用：
    1 假设我们想给百度职员加工资，其标准是：工资在8000元以下的将加20％；工资在8000元以上的加15％
    则：

    select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary) "revised_salary" from employee

    2 表table_subject，有subject_name列。要求按照：语、数、外的顺序进行排序
    则：

    select * from table_subject order by decode(subject_name, '语文', 1, '数学', 2, , '外语',3)

    decode和简单case的性能比较

Oracle的DECODE函数功能很强，灵活运用的话可以避免多次扫描，从而提高查询的性能。而CASE是9i以后提供的语法，这个语法更加的灵活，提供了IF THEN ELSE的功能。

对于很多情况，DECODE和CASE都能解决问题，个人更倾向于使用DECODE，一方面是从8i保留下来的习惯，另一方面是DECODE的语法更加的简洁，代码量要小一些。

不过今天在看Oracle9i的数据仓库手册时发现，Oracle在文档中提到CASE语句的效率会更高一些，尤其是CASE表达式 WHEN 常量 THEN的语法，效率要比CASE WHEN表达式 THEN的语法更高一些。对于后面这种说法倒是没有太多的疑问，对于CASE比DECODE效率高这种说法倒是第一次看到，印象中DECODE效率很高，应该不会比CASE的效率差。

到底效率如何，还是要具体的实例来说：

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