`
sjk2013
  • 浏览: 2166933 次
文章分类
社区版块
存档分类
最新评论

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

 
阅读更多

在oracle世界,你可以使用:

1)case表达式 或者

2)decode函数

来实现逻辑判断。OracleDECODE函数功能很强,灵活运用的话可以避免多次扫描,从而提高查询的性能。而CASE9i以后提供的语法,这个语法更加的灵活,提供了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的性能比较

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


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

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

测试结果确实是CASE的简单表达式写法效率最高,然后是CASE的另一种写法,DECODE效率最低。但是对于600W的记录,最终结果只有0.010.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还低。

根据上面的测试可以得出结论,无论是DECODE还是CASE方式的两种写法,执行效率没有明显的差别。

分享到:
评论

相关推荐

    Oracle-Decode()函数和CASE语句的比较

    本文讲述了Oracle-Decode()函数和CASE语句的比较。

    Oracle用decode函数或CASE-WHEN实现自定义排序

    主要介绍了Oracle用decode函数或CASE-WHEN实现自定义排序功能,本文通过实例代码给大家介绍的非常详细,具有一定的参考借鉴价值,需要的朋友可以参考下

    ORACLE DECODE函数在中国式报表统计查询中的组合条件实现

    使用DECODE实现统计,是比较常用的。 但是在遇到需要组合条件进行统计时,有时却不如case when 或者另建视图好用。 可是有时就想用DECODE,咋办? 这里给大家介绍一下DECODE如何实现组合条件查询,一句SQL查询一张...

    Oracle_Database_11g完全参考手册.part2

    原书名: Oracle Database 11g The Complete Reference 原出版社: McGraw-Hill Osborne Media ... 第16章 decode 和case:sql中的if-then-else  第17章 创建和管理表、视图、索引、群集和序列  第18章 分区

    oracle database 10g 完整参考手册part1

    第16章 DECODE和CASE SQL中的if-then-else 第17章 创建和管理表、视图、索引、群集和序列 第18章 Oracle基本安全 第Ⅲ部分 高级内容 第19章 高级安全性—— 虚拟专用数据库 第20章 使用表空间 第21章 用SQL*Loader...

    Oracle_Database_11g完全参考手册.part3/3

    第16章 DECODE和CASE.SQL中的if-fhen-else 第17章 创建和管理表、视图、索引、群集和序列 第18章 分区 第19章 Oracle基本安全 第Ⅲ部分 高级主题 第20章 高级安全性-虚拟专用数据库 第21章 高级安全性:透明数据加密...

    Oracle_Database_11g完全参考手册.part2/3

    第16章 DECODE和CASE.SQL中的if-fhen-else 第17章 创建和管理表、视图、索引、群集和序列 第18章 分区 第19章 Oracle基本安全 第Ⅲ部分 高级主题 第20章 高级安全性-虚拟专用数据库 第21章 高级安全性:透明数据加密...

    Oracle高级sql学习与练习

    6、DECODE函数和行列互换 7、CASE表达式 8、ROWNUM-TOP-N分析 9、相关子查询和非相关子查询 10、增强GROUP BY 11、分析函数(ANALYTICAL FUNCTIONS) 12、ROWID的使用 13、ORACLE 10G正则表达式 14、使用HINT 15、...

    Oracle_Database_11g完全参考手册.part5

    原书名: Oracle Database 11g The Complete Reference 原出版社: McGraw-Hill Osborne Media ... 第16章 decode 和case:sql中的if-then-else  第17章 创建和管理表、视图、索引、群集和序列  第18章 分区

    Oracle的列转行问题

    网络上流传了很多将行数据转换称列数据的方法和应用实例,一般通过decode或者case函数与聚合函数联合实现功能,这里就不再重复。 日前本人在一个偶然的应用中用到了一个需要将列数据转换为行数据的问题。搜索了很久...

    oracle database 11g 完整参考手册中文高清完整版part3

     第16章 decode 和case:sql中的if-then-else  第17章 创建和管理表、视图、索引、群集和序列  第18章 分区 . 第19章 oracle 基本安全 第ⅲ部分 高 级 主 题  第20章 高级安全性——虚拟专用数据库  第21章 ...

    oracle database 11g 高清完整中文版part2

     第16章 decode 和case:sql中的if-then-else  第17章 创建和管理表、视图、索引、群集和序列  第18章 分区 . 第19章 oracle 基本安全 第ⅲ部分 高 级 主 题  第20章 高级安全性——虚拟专用数据库  第21章 ...

    Oracle_Database_11g完全参考手册.part3

    基本信息 原书名: Oracle Database 11g The Complete Reference 原出版社: McGraw-Hill Osborne ... 第16章 decode 和case:sql中的if-then-else  第17章 创建和管理表、视图、索引、群集和序列  第18章 分区

    Oracle_Database_11g完全参考手册.part4

    原书名: Oracle Database 11g The Complete Reference 原出版社: McGraw-Hill Osborne Media ... 第16章 decode 和case:sql中的if-then-else  第17章 创建和管理表、视图、索引、群集和序列  第18章 分区

    oracle常用知识

    多年来的一点oracle知识积累decode替代case,Drop含有特殊字符的表,Oracle的物化视图的建立方法,动态查询语句返回结果集,利用ORACLE的MINUS函数和OVER函数,直接通过视图实现两个记录集的比较,在oracle中生成...

    racle_Database_11g完全参考手册.part1

    原书名: Oracle Database 11g The Complete Reference 原出版社: McGraw-Hill Osborne Media ... 第16章 decode 和case:sql中的if-then-else  第17章 创建和管理表、视图、索引、群集和序列  第18章 分区

    oracle database 11g完全参考手册 高清完整版part1 共3部分

     第16章 decode 和case:sql中的if-then-else  第17章 创建和管理表、视图、索引、群集和序列  第18章 分区 . 第19章 oracle 基本安全 第ⅲ部分 高 级 主 题  第20章 高级安全性——虚拟专用数据库  第21章 ...

    oracle获取上一旬的开始时间和结束时间的实现函数

    代码如下: — 获取上旬开始... select case decode(trunc((to_char(v_rq, ‘dd’) – 1) / 10), 0, ‘上旬’, 1, ‘中旬’, ‘下旬’) when ‘上旬’ then –返回上个月的下旬 to_char(add_months(v_rq, -1), ‘yyyyMM

Global site tag (gtag.js) - Google Analytics