sql查询中，case和decode的比较

最新推荐文章于 2023-03-27 16:24:18 发布
最新推荐文章于 2023-03-27 16:24:18 发布
阅读量788 收藏
点赞数
分类专栏： db 文章标签： case和decode比较
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Carino_U/article/details/79976962
版权
 

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

Carino_U
关注
专栏目录
SQL查询常用的字段学习(decode、case、nvl、（+）、substr)
Tz
05-07 731
sql查询常用的几种条件函数解释 decode函数 语法 decode(条件，值1，返回值1，值2，返回值2，...值n,返回值n，缺省值) 含义 IF条件=值1THEN RETURN(返回值1) ELSIF条件=值2THEN RETURN(返回值2) ...... ELSIF条件=值nTHEN RETURN(返回值n) ELSE RETURN(缺省值) ENDIF 举例 decode(X，...
Oracle中的case when以及decode
wuChiSha的博客
11-12 2209
一、两种形式的case when 形式一： 　　　　case 列名 　　　　when 条件值1 then 符合条件1后的值 　　　　when 条件值2 then 符合条件2后的值 …… 　　　　else 默认值 end eg: 如下，gender 值为1时，显示男，2显示女，否则显示其他 SELECT id,NAME, CASE gender WHEN gender = '1' THEN '男...
decode函数与case when 的妙用
03-24
NULL 博文链接：https://hcty31.iteye.com/blog/1119556
DECODE和CASE的性能比较
luobina的专栏
11-27 1362
 Oracle的DECODE函数功能很强，灵活运用的话可以避免多次扫描，从而提高查询的性能。而CASE是9i以后提供的语法，这个语法更加的灵活，提供了IF THEN ELSE的功能。 对于很多情况，DECODE和CASE都能解决问题，个人更倾向于使用DECODE，一方面是从8i保留下来的习惯，另一方面是DECODE的语法更加的简洁，代码量要小一些。不过今天在看Oracle9i的数据
【SQL】CASE与DECODE
Just Do IT
05-09 697
1. case..when case..when语句用于按照条件返回查询结果，如当我们想把emp表的工资按照多少分成几个不同的级别，并分别统计各个级别的员工数。SQL语句如下： select (case          when sal           '1'          when sal > 1000 and sal           '2'          wh
Oracle中case when与decode的比较
最新发布
limts的博客
03-27 597
注：case when语句优先级是从上至下的，满足一个条件后就会输出值，不会继续再向下执行；适用场景：case when使用比较灵活，比较适合复杂的条件；性能比较：比两个语句的执行计划，这个场景下的执行计划相同；
case与decode的区别
季节更替
03-20 261
decode的用法： SELECT DECODE ( '持久层技术缩写', j, 'jdbc', h, 'hibernate', i, 'ibatis', j, 'JPA' 'other' ) AS '具体持久方式' FROM 'java开发'; 可以清楚的看出decode是一个一对一译码过程，这也是...
Oracle-Decode()函数和CASE语句的比较
03-03
Oracle数据库中的`Decode()`函数和`CASE`语句都是用于条件判断和数据转换的重要工具，它们各有特点，适用场景也有所不同。以下是对这两个功能的详细比较。 首先，`Decode()`函数是一个简化的条件表达式，它允许你在...
SQL中的 CASE 表达式用法
傲慢的沙扬娜拉
04-01 1431
** SQL中的 CASE 表达式妙用这么多，你知道吗？ ** 前言 历史考试选择题：黄花岗起义第一枪谁开的? A宋教仁 B孙中山 C黄兴 D徐锡麟，考生选C。 又看第二题：黄花岗起义第二枪谁开的? 考生傻了，就选了个B。 接着看第三题：黄花岗起义中，第三枪谁开的? 考生疯了，胡乱选了A。 考试出来就去找出卷老师。老师拿出课本说：黄兴连开三枪，揭开了黄花岗起义的序幕。考生：… CASE表达式 之概念 相信大家都用过CASE表达式，尤其是做一些统计功能的时候，用的特别多，可真要说什么是 CASE表达式，我估计
CASE/DECODE条件表达式
Yangll29的博客
03-31 2286
撰写时间：2019年03月27日 在 SQL 语句中使用IF-THEN-ELSE 逻辑 两种方法: —— CASE 表达式 —— DECODE 函数 这两种方法各有优点，使用方法也差不多，多掌握一点知识不吃亏。 ①使用 DECODE 函数 : 格式：DECODE(value,comparison1,return1,comparison2,return2…,else)， 其中 value表示表中任意...
7- Decode和Case
pengpenglin的专栏
08-14 1749
 1．DECODE, NVL, and NVL2简介：These functions are used to make decisions based on data values within an SQL statement without resorting(求助) to a procedural language like PL/SQL DECODE和NVL、NVL2函数语
DECODE函数和CASE WHEN 比较
no pay no gay
03-14 3454
一，DECODE函数   其基本语法为： Sql代码   DECODE(value, if1, then1, if2, then2, if3, then3,...ifn, thenn, else)   DECODE(value, if1, then1, if2, then2, if3, then3,...ifn, thenn, else) 表示如果value等于i
case表达式和decode函数
ytempest的博客
12-09 606
1. 用case表达式做等值判断 格式： case 表达式 when 值1 then 返回值1 when 值2 then 返回值2 …… [else 默认返回值]end 示例： 查询员工编号，姓名，部门编号，部门名称； 部门名称： =30  SALE =20  ETC =10  JXB 其他    ICSS SELECT
decode与case when比较
andy
08-19 943
decode只能做等值的，case when可以做区间的，decode能做到的，case when也能做到。 效率方面，个人觉得两者效率差不多。 但decode的语法要简单些，但它只能做等值的比较 case when end 能做条件的判断 select case when b.total_fee is null then 0 else b.total_fee end from ...
case表达式 与decode函数
qq_33856123的博客
12-31 838
1、语法如下: DECODE(value, if1, then1, if2, then2, if3, then3,...ifn, thenn, default)   decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值) 表示如果value等于if1时，DECODE函数的结果返回then1,...,如果不等于任何一个if值，则返回else。亦即： C
case 和decode的区别
weixin_30787531的博客
11-24 243
区别： decode是pl/sql语法,只能在oracle中使用,case when是标准SQL的语法,哪儿都能用,也就是说移植性更强. decode像是case when的精简版,当要实现的功能比较简单时,用decode方便,简洁. decode只能做等值的，case when可以做区间的，使用范围来进行条件区分，decode能做到的，case when也能做到；效率方面，个人觉得...
case when和decode比较
shinyprince的专栏
09-23 1832
含义解释： decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值) case语法一 case 条件 when 值1 then 翻译值1                 when 值2 then 翻译值2                 …                 Else 缺省值 end Case语法二 case when 条件1 then 翻
decode与case的区别
zhoubo200的专栏
03-02 609
本文讲述了Oracle-Decode()函数和CASE语句的比较。 首先，举2个简单的例子，简单对比一下这2者的区别。 1.CASE语句：SELECT CASE SIGN(5 - 5) WHEN 1 THEN Is Positive WHEN -1 THEN Is Negative
case when then与decode分析比较
weixin_34262482的博客
09-10 364
case when 在不同条件需要有不同返回值的情况下使用非常方便，可以在给变量赋值时使用，也可以在select查询语句中使用(有一个满足，就立刻退出)--搜索格式，case后无内容str:=casewhen i=1 then 'a'when i=2 then 'b'else 'c'end; --此处赋值，为表达，不用end case。若不赋值给str，则需要用end ...
SQL集合操作符与TRANSLATE、DECODE及CASE函数解析
`CASE`表达式允许在SQL查询中构建更复杂的条件分支，其灵活性和可读性通常优于`DECODE()`，因此在新代码中应优先考虑使用`CASE`。 在实际应用中，理解和熟练掌握这些集合操作符和函数，能够帮助我们写出更高效、更...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值