`
woshixushigang
  • 浏览: 562871 次
  • 性别: 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 的妙用

    NULL 博文链接：https://hcty31.iteye.com/blog/1119556

    Oracle_case_when_用法

    详细讲解了case、when的用法.ASE表达式可以在SQL中实现if-then-else型的逻辑，而不必使用PL/SQL。CASE的工作方式与DECODE()类似，但应该使用CASE,因为它与ANSI兼容。

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

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

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

    但是在遇到需要组合条件进行统计时，有时却不如case when 或者另建视图好用。 可是有时就想用DECODE，咋办？ 这里给大家介绍一下DECODE如何实现组合条件查询，一句SQL查询一张中国式报表。 （本文的示例，在实际业务...

    mysql仿oracle的decode效果查询

    以下代码在MYSQL中测试通过，MSSQL... #mysql执行 select sum(case when c = ‘1’ then A else B end) from tmp #oracle执行 select sum(decode(c,’1′,a,b)) from tmp #普通联合查询 select sum(d) from ( select

    oracle sql of extracting table structure

    then all_c.data_type || decode(all_c.data_precision,0,'',NULL,'','(' || to_char(all_c.data_precision)) || decode(all_c.data_precision,0,'',NULL,'', decode(all_c.data_scale,0,'',NULL,'', ',' || to_...

    Oracle事例

    20.oracle8中扩充了group by rollup和cube的操作。有时候省了你好多功夫的。 下面的语句可以进行总计 select region_code,count(*) from aicbs.acc_woff_notify group by rollup(region_code); &lt;2&gt; 对第1个字段...

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

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

    Sakemail

    Some changes to the code.17/11/971.2.1- Now, all searches are made in case-insensitive, it could prevent some unexpected responses (no one reported, but...). Some changes to the code (again).20/11/...

    php.ini-development

    Directive names are *case sensitive* - foo=bar is different from FOO=bar. ; Directives are variables used to configure PHP or PHP extensions. ; There is no name validation. If ...

    EurekaLog_7.5.0.0_Enterprise

    6)....Fixed: Wrong code page was used to decode ANSI bug reports 7)....Fixed: Attaching .PAS files instead of .OBJ in C++ Builder 2006+ Pro/Trial EurekaLog 7.2 Hotfix 1 (7.2.1.0), 3-April-2015 1).......

    Bochs - The cross platform IA-32 (x86) emulator

    - Fixed Bochs crash when accessing the first byte above emulated memory size - Internal Debugger - Introduced range read/write physical watchpoints - Allow reloading of segment registers from ...

    SQL培训第一期

    1.8.2 case when 1.8.2.1 语法 select t.uuid, t.score, case when t.score &gt; 90 then '优秀' when t.score &gt; 60 then '及格' else '不及格' end from exam_user_exam t 1.8.3 decode 1.8.3.1 语法 select ...

    acpi控制笔记本风扇转速

    Optimized the Load ASL operator in the case where the source operand is an operation region. Simply map the operation region memory, instead of performing a bytewise read. (Region must be of type ...

    occam一维反演

    c zero out the model prejudice and weights in case inputm() and objmat() c do not take care of setting these. do 2 i = 1, np prewts(i) = 0.0 2 premod(i) = 0.0 c itmax = maxitr + nit c c input the ...

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