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

例子：

SELECT (CASE WHEN cust_credit_limit BETWEEN  0 AND 3999 THEN  ' 0 - 3999'
   WHEN cust_credit_limit BETWEEN  4000 AND 7999 THEN ' 4000 - 7999'
   WHEN cust_credit_limit BETWEEN  8000 AND 11999 THEN  ' 8000 - 11999'
   WHEN cust_credit_limit BETWEEN  12000 AND 16000 THEN '12000 - 16000' END)
  AS BUCKET, COUNT(*) AS Count_in_Group
FROM customers WHERE cust_city = 'Marshal' GROUP BY
 (CASE WHEN cust_credit_limit BETWEEN  0 AND 3999 THEN ' 0 - 3999'
 WHEN cust_credit_limit BETWEEN  4000 AND 7999 THEN ' 4000 - 7999'
 WHEN cust_credit_limit BETWEEN  8000 AND 11999 THEN  ' 8000 - 11999'
 WHEN cust_credit_limit BETWEEN  12000 AND 16000 THEN '12000 - 16000' END);

BUCKET        COUNT_IN_GROUP
------------- --------------
 0 - 3999                  8
 4000 - 7999               7
 8000 - 11999              7
12000 - 16000              1

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

hr@ORCL> select * from a;

        ID NAME
---------- ----------
         1 a
         2 b
         3 c
         1 a

hr@ORCL> select sum(decode(id,1,1,0)) think,
  2             sum(decode(id,2,2,0)) water,
  3             sum(decode(id,3,3,0)) linshuibin
  4        from a;

     THINK      WATER LINSHUIBIN
---------- ---------- ----------
         2          2          3

一个字段，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

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

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

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

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

还低。

根据上面的测试可以得出结论，无论是

DECODE

还是

CASE

方式的两种写法，执行效率没有明显的差别。

你可能也喜欢