`
woshixushigang
  • 浏览: 576337 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类

case when和decode

 
阅读更多

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

本人转载：http://yangtingkun.itpub.net/post/468/493022 

分享到：
| ClassLoader(转)
评论
发表评论

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

相关推荐

    decode函数与case when 的妙用

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

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

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

    Oracle_case_when_用法

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

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

    `DECODE`函数和`CASE-WHEN`语句都是实现这一目标的有效工具。根据实际的业务需求和性能考虑，可以选择合适的方法进行应用。在编写SQL时，确保理解并充分利用这些高级功能，可以提升代码的可读性和执行效率。希望本文...

    oracle case when 语句的用法详解

    CASE WHEN语句有两种主要形式：简单CASE和搜索CASE。 **1. 简单CASE函数** 简单CASE主要用于简单的等值比较。例如： ```sql CASE sex WHEN '1' THEN '男' WHEN '2' THEN '女' ELSE '其他' END ``` 这里，...

    [数据库] Oracle使用CASE判断解决多值问题1

    为了简化这一过程，我们可以利用Oracle SQL的内置函数，如DECODE和CASE WHEN，直接在数据库层面进行处理。 首先，尝试使用DECODE函数。DECODE函数允许我们根据指定的条件返回不同的值。例如，DECODE(ZY_TYPE, '国家...

    oracle的decode函数

    - **高效性**：在某些情况下，`DECODE`函数可能比等效的`CASE WHEN`结构执行得更快。 ##### 注意事项： - **NULL处理**：需要注意的是，默认情况下，如果`input_value`为`NULL`，那么`DECODE`函数将返回`NULL`，...

    ORACLE 列转行 DECODE函数用法

    在实际工作中，你可能还需要结合其他SQL函数，如CASE WHEN或NVL，来进一步增强DECODE的功能。例如，当你需要处理可能的NULL值时，NVL函数可以提供一个默认值，防止因NULL而导致的结果异常。 总之，Oracle的DECODE...

    数据库基础 高级sql编程

    在高级 SQL 编程中，Case 与 Decode 是两个非常有用的函数，它们能够极大地提高数据处理能力和查询效率。 高级 SQL 编程在数据库开发技术中扮演着非常重要的角色，它能够极大地提高数据库的查询效率和数据处理能力...

    oracle sql 行列转换

    无论是使用CASE语句、DECODE函数还是PIVOT操作符，关键在于理解数据结构和转换需求，选择最合适的工具来实现转换。这些技能对于高效处理和分析复杂数据集至关重要，尤其是在IT行业中，掌握这些技能将大大提高数据...

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

    然而，在面对复杂的多条件组合统计需求时，`DECODE`函数的应用可能会变得相对繁琐，这时往往更倾向于使用`CASE WHEN`语句或创建视图的方法来解决。不过，如果出于特定原因需要坚持使用`DECODE`函数，那么了解如何...

    一道java面试题 行列转换

    SELECT @sql = @sql + ', [' + cid + '] = SUM(CASE cid WHEN ''' + cid + ''' THEN mark ELSE NULL END) ' FROM (SELECT DISTINCT cid FROM sc) AS sc1; -- 添加总分、平均分和最高分的计算语句 SET @sql = @sql +...

    mysql仿oracle的decode效果查询

    然而，MySQL 并没有内置的 `DECODE` 函数，但我们可以使用 `CASE WHEN` 语句来达到类似的效果。下面我们将详细探讨如何在 MySQL 中实现类似于 Oracle 的 `DECODE` 查询。 首先，让我们回顾一下 Oracle 中 `DECODE` ...

    orcale数据库 行列互换

    在Oracle中，可以使用SQL的CASE语句或者DECODE函数来实现行转列。例如，在提供的描述中，我们看到一个例子是将学生的语文和数学分数从行转换为了列。 ```sql SELECT student AS '姓名', SUM(CASE name WHEN '语文'...

    SQL学习笔记.docx

    CASE WHEN 函数的语法为 CASE WHEN condition THEN result [ WHEN condition THEN result ... ] ELSE result END，其中 condition 是条件，result 是执行的结果。 SIGN 函数 SIGN 函数是一个数学函数，用于返回一...

    在employees表中查询出员工的工资，并计算应交税款

    根据提供的文件信息，本文将详细解释如何在...使用CASE语句和DECODE函数都是解决这类问题的好方法，具体选择哪一种取决于实际需求和个人喜好。希望这篇文章能帮助你更好地理解和掌握如何在Oracle数据库中处理此类问题。

    SQL经典面试题及答案

    - `SUM(CASE WHEN shengfu = '胜' THEN 1 ELSE 0 END)` 这部分使用了`CASE`语句对每行数据进行判断，如果是“胜”则返回1，否则返回0。然后通过`SUM()`函数对所有1求和，从而得到“胜”的总数。 - 同样的逻辑也适用...

    oracle练习_面试题

    Oracle 提供了两种函数来实现条件选择，即 CASE 函数和 DECODE 函数。CASE 函数可以根据条件选择不同的值，DECODE 函数可以根据条件选择不同的值或表达式。 示例： ```sql SELECT t.ename, CASE t.deptno WHEN ...

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