sql查询中,case和decode的比较

原创 2018年04月17日 16:37:56
 

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 WNER = 'SYSTEM' THEN 'SYSTEM' 
  2     WHEN WNER = '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方式的两种写法,执行效率没有明显的差别。

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/Carino_U/article/details/79976962

SQL Server 2016基础操作与查询(一)

本课程基于微软最新的SQL Server 2016,真正的零基础带你进入数据库的大门,从数据库的安装配置开始,逐步讲述了创建数据库、数据表,基础查询、高级查询、子查询、数据库完整性、视图、索引、存储过程、游标、函数、触发器、数据库备份恢复、自动化作业、数据库安全等,比较全面的讲解了SQL Server2016的基本操作和进阶操作,知识点涵盖日常工作中90%以上所用到的数据库技术。
  • 2016年09月29日 16:50

case when和decode的用法与区别

一、case when case when 类似我们的if ...else ,判断语句 语法如下: CASE expr WHEN expr1 THEN return_expr1        ...
  • hollo_hhy
  • hollo_hhy
  • 2014-04-08 00:17:21
  • 9662

SQL/PLSQL:在where条件中写case when和decode

代码示例: SELECT fp.ka,fp.* FROM BJRC_CKFP FP WHERE 1 = 1 AND fp.ka = ( CASE WHEN '&口岸' IS NOT N...
  • crzzyracing
  • crzzyracing
  • 2017-03-14 14:21:19
  • 1397

Oracle--decode函数、(case when then else end )和(update set replace)的用法

Oracle--(case when then else end )和(update set replace)的用法
  • oChangWen
  • oChangWen
  • 2016-04-14 17:50:45
  • 2071

DECODE ROUND case when

写了两周i的sql,真是醉了,但是收获也挺大的,认识了很多函数,所以就赶紧积累下来 写个例子,废话不多说 select count(1) as n1 ,--总人数 SUM(DEC...
  • u011592166
  • u011592166
  • 2016-09-19 19:50:59
  • 359

SQL高级查询--decode()函数

decode(value,search_value,result,default_value)对value与search_value进行比较。如果这两个值相等,decode()返回result,否则返...
  • langkeziju
  • langkeziju
  • 2013-11-02 11:20:19
  • 1205

oracle或达梦--使用decode行转列、oracle行转列、case when行转列

目前很多行转列都使用了 oracle 11g新增的pivot ,但是我用达梦国产数据库不支持该函数,所以使用case when的方式 首先,我们需要构造一个两列的数据,也就是查询结果 select...
  • a740220116
  • a740220116
  • 2016-04-02 17:07:07
  • 1269

sqlserver中查询语句case when判断的用法

今天帮同事统计表格,给我了个sql语句,又给我了份excel模板,但是sql语句查询出来的结果和模板对不上,这导致还得人工一条一条的找这些数据,然后手动填写结果...这太tm麻烦了... excel模...
  • CGS_______
  • CGS_______
  • 2017-05-15 15:26:32
  • 1930

sql语句中日期型转成字符型,日期比较常出现错误总结和case when的使用

工作了一天,把今天所做的一些东西,以及所接触到和所学到的新知识记录下来。 sql语句中对日期类型,将其转成指定的字符型格式...
  • scqdscy1994
  • scqdscy1994
  • 2016-11-07 19:59:23
  • 1562

case when和decode比较

含义解释: decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值) case语法一 case 条件 when 值1 then 翻译值1              ...
  • shinyprince
  • shinyprince
  • 2014-09-23 17:39:19
  • 312
收藏助手
不良信息举报
您举报文章:sql查询中,case和decode的比较
举报原因:
原因补充:

(最多只允许输入30个字)