在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
--------------------------------------------------------------------------
| 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还低。
根据上面的测试可以得出结论,无论是DECODE还是CASE方式的两种写法,执行效率没有明显的差别。
分享到:
相关推荐
本文讲述了Oracle-Decode()函数和CASE语句的比较。
主要介绍了Oracle用decode函数或CASE-WHEN实现自定义排序功能,本文通过实例代码给大家介绍的非常详细,具有一定的参考借鉴价值,需要的朋友可以参考下
使用DECODE实现统计,是比较常用的。 但是在遇到需要组合条件进行统计时,有时却不如case when 或者另建视图好用。 可是有时就想用DECODE,咋办? 这里给大家介绍一下DECODE如何实现组合条件查询,一句SQL查询一张...
原书名: Oracle Database 11g The Complete Reference 原出版社: McGraw-Hill Osborne Media ... 第16章 decode 和case:sql中的if-then-else 第17章 创建和管理表、视图、索引、群集和序列 第18章 分区
第16章 DECODE和CASE SQL中的if-then-else 第17章 创建和管理表、视图、索引、群集和序列 第18章 Oracle基本安全 第Ⅲ部分 高级内容 第19章 高级安全性—— 虚拟专用数据库 第20章 使用表空间 第21章 用SQL*Loader...
第16章 DECODE和CASE.SQL中的if-fhen-else 第17章 创建和管理表、视图、索引、群集和序列 第18章 分区 第19章 Oracle基本安全 第Ⅲ部分 高级主题 第20章 高级安全性-虚拟专用数据库 第21章 高级安全性:透明数据加密...
第16章 DECODE和CASE.SQL中的if-fhen-else 第17章 创建和管理表、视图、索引、群集和序列 第18章 分区 第19章 Oracle基本安全 第Ⅲ部分 高级主题 第20章 高级安全性-虚拟专用数据库 第21章 高级安全性:透明数据加密...
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 The Complete Reference 原出版社: McGraw-Hill Osborne Media ... 第16章 decode 和case:sql中的if-then-else 第17章 创建和管理表、视图、索引、群集和序列 第18章 分区
网络上流传了很多将行数据转换称列数据的方法和应用实例,一般通过decode或者case函数与聚合函数联合实现功能,这里就不再重复。 日前本人在一个偶然的应用中用到了一个需要将列数据转换为行数据的问题。搜索了很久...
第16章 decode 和case:sql中的if-then-else 第17章 创建和管理表、视图、索引、群集和序列 第18章 分区 . 第19章 oracle 基本安全 第ⅲ部分 高 级 主 题 第20章 高级安全性——虚拟专用数据库 第21章 ...
第16章 decode 和case:sql中的if-then-else 第17章 创建和管理表、视图、索引、群集和序列 第18章 分区 . 第19章 oracle 基本安全 第ⅲ部分 高 级 主 题 第20章 高级安全性——虚拟专用数据库 第21章 ...
基本信息 原书名: Oracle Database 11g The Complete Reference 原出版社: McGraw-Hill Osborne ... 第16章 decode 和case:sql中的if-then-else 第17章 创建和管理表、视图、索引、群集和序列 第18章 分区
原书名: Oracle Database 11g The Complete Reference 原出版社: McGraw-Hill Osborne Media ... 第16章 decode 和case:sql中的if-then-else 第17章 创建和管理表、视图、索引、群集和序列 第18章 分区
多年来的一点oracle知识积累decode替代case,Drop含有特殊字符的表,Oracle的物化视图的建立方法,动态查询语句返回结果集,利用ORACLE的MINUS函数和OVER函数,直接通过视图实现两个记录集的比较,在oracle中生成...
原书名: Oracle Database 11g The Complete Reference 原出版社: McGraw-Hill Osborne Media ... 第16章 decode 和case:sql中的if-then-else 第17章 创建和管理表、视图、索引、群集和序列 第18章 分区
第16章 decode 和case:sql中的if-then-else 第17章 创建和管理表、视图、索引、群集和序列 第18章 分区 . 第19章 oracle 基本安全 第ⅲ部分 高 级 主 题 第20章 高级安全性——虚拟专用数据库 第21章 ...
代码如下: — 获取上旬开始... select case decode(trunc((to_char(v_rq, ‘dd’) – 1) / 10), 0, ‘上旬’, 1, ‘中旬’, ‘下旬’) when ‘上旬’ then –返回上个月的下旬 to_char(add_months(v_rq, -1), ‘yyyyMM