`
java-mans
  • 浏览: 11727736 次
文章分类
社区版块
存档分类
最新评论

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

--------------------------------------------------------------------------
| 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方式的两种写法，执行效率没有明显的差别。

分享到：
| Objective-C新特性
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

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

    Oracle数据库中的`Decode()`函数和`CASE`语句都是用于条件判断和数据转换的重要工具，它们各有特点，适用场景也有所不同。以下是对这两个功能的详细比较。 首先，`Decode()`函数是一个简化的条件表达式，它允许你在...

    Oracle 中 decode 函数用法

    Oracle中的DECODE函数是一种非常实用的工具，它允许你在SQL查询中执行简单的条件判断和返回相应的值。这个函数类似于IF-THEN-ELSE语句的简写形式，减少了编写复杂逻辑的代码量，使得SQL语句更加简洁易读。 DECODE...

    ORACLE 列转行 DECODE函数用法

    在Oracle数据库中，DECODE函数是一个非常实用的工具，它允许你在SQL查询中进行条件判断，将特定的值转换为其他值。...在实际应用中，不断实践和探索DECODE函数的不同用法，将会使你在处理数据库数据时更加得心应手。

    Oracle中Decode()函数使用技巧

    Oracle中的Decode()函数是数据库查询时非常实用的一个工具，它允许你在SQL语句中实现简单的条件判断，类似于IF-THEN-ELSE逻辑。...在理解其基本用法的同时，也需要根据具体需求和性能考虑来选择合适的应用场景。

    ORACLE函数及其用法

    ### ORACLE函数及其用法详解 #### 概述 Oracle是一种广泛使用的数据库管理系统，它提供了丰富的内置函数，这些函数能够帮助开发者和数据库管理员更高效地处理数据。本文将详细介绍Oracle中的一些常用函数及其应用...

    oracle 函数大全 参考函数 手册 速查 chm格式

    Oracle函数是数据库操作中的核心组成部分，它们允许开发人员和DBA执行各种计算、转换、查询和数据处理任务。手册中可能涵盖了以下几类函数： 1. 数学函数：如ABS（取绝对值）、MOD（取模）、ROUND（四舍五入）等，...

    decode函数与case when 的妙用

    在数据库管理和SQL编程中，`DECODE`函数和`CASE WHEN`语句是两种非常重要的逻辑判断工具，它们常用于处理条件分支和数据转换。在这篇文章中，我们将深入探讨这两种方法的用途、区别以及如何巧妙地运用它们。 首先，...

    Oracle_case_when_用法

    `CASE WHEN` 与 `DECODE()` 函数相似，但因其符合 ANSI 标准而更受推荐使用。 #### 二、基本结构 `CASE WHEN` 语句的基本结构如下： ```sql CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ...

    oracle的decode函数

    - **性能考量**：虽然`DECODE`函数在简单场景下非常有效，但在复杂逻辑或大量数据的情况下，使用`CASE WHEN`或其他方式可能会更加高效。 总之，`DECODE`函数是Oracle数据库中一个非常有用的工具，尤其适用于需要...

    ORACLE函数大全与整理

    "函数整理.doc"和"ORACLE函数大全.doc"这两个文档应该包含了Oracle函数的详细说明、示例和应用场景，是学习和查阅Oracle函数的宝贵资料。通过深入学习这些函数，开发者可以更高效地编写SQL查询，优化数据库操作，...

    oracle性能优化、函数大全、傻瓜問題

    5. 条件函数：如CASE、DECODE、NVL等进行条件判断和缺失值处理。 三、Oracle数据库技术 1. PL/SQL：Oracle的编程语言，用于编写存储过程、触发器、游标等，提升数据库的业务处理能力。 2. 视图：创建虚拟表，简化...

    Oracle函数速查手册

    "Oracle函数速查手册"中详尽列举了这些函数的用法和示例，便于查阅和学习。通过深入研究这个文档，用户不仅可以快速查找所需函数，还能深入理解Oracle数据库的强大功能，从而提升数据库管理与开发的技能。

    oracle函数大全

    在实际编程中，我们需要注意区分Oracle函数的大小写敏感性，因为在PL/SQL中函数是不区分大小写的，但在SQL语句中则可能需要按照实际的大小写来编写函数名。此外，熟悉并正确使用这些函数将有助于我们编写出更高效、...

    ORACLE函数大全CHM

    本资源“ORACLE函数大全CHM”显然是一个针对Oracle数据库中的各种函数的参考手册，旨在帮助开发者和DBA深入理解和使用Oracle的内置函数。CHM文件是一种常见的Windows帮助文档格式，通常包含丰富的文本、索引和搜索...

    Oracle函数及其用法

    以上只是Oracle函数中的一部分，实际使用中还有更多其他函数，如连接函数、系统信息函数、转换函数等。熟练掌握这些函数，可以极大地提高你在数据库操作中的效率和灵活性。通过实际操作和练习，你会发现自己能更好地...

    oracle函数速查手册

    总的来说，Oracle函数速查手册是数据库管理员、开发人员和数据分析者的得力助手，通过学习和查阅，可以快速解决在处理Oracle数据库时遇到的问题。无论是进行数据查询、更新，还是进行复杂的数据分析，掌握Oracle函数...

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

    Oracle的`DECODE`函数是一种条件判断函数，它可以用于对数据进行简单的多值比较和返回。在自定义排序场景中，`DECODE`函数可以将不同的字段值映射到对应的排序值。以下是一个例子： ```sql SELECT * FROM table_...

    oracle函数大全中文

    数值型函数主要用于处理数字数据，如`ROUND`用于四舍五入，`TRUNC`用于截断小数部分，`MOD`计算余数，`POWER`进行指数运算，以及`DECODE`和`CASE`用于条件判断和数值转换。 2. 字符型函数： 字符型函数处理字符串...

    oracle内置函数学习

    在逻辑和比较操作中，我们有IFNULL()或NVL()用于处理NULL值，DECODE()可以实现简单的条件判断，而CASE表达式则提供了更复杂的逻辑判断。这些函数在处理数据查询和更新时非常有用，特别是在避免因NULL值导致的错误上...

Magicbox
Global site tag (gtag.js) - Google Analytics