ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > 数据库开发技术 > 通用SQL优化经典等价改写【一】——case when 改造

通用SQL优化经典等价改写【一】——case when 改造

原创 数据库开发技术 作者：梁敬彬 时间：2025-04-23 21:52:33 0 删除 编辑

1. 引言

SQL优化的核心目标是减少访问路径。前面我们讨论了通过添加索引、分区表等物理设计改进性能的方法，这些属于无需修改SQL就能完成的优化手段。但在许多场景下，通过等价改写SQL也能显著提升性能。本文将重点探讨Case When改造这一强大的优化技巧。

SQL优化的本质可归纳为:

  • 减少访问路径

  • 避免外部因素影响(执行计划不稳定、资源不足等)

2. Case When改造详解

Case When是Oracle SQL中的一个重要语法结构，在SQL优化中，我们可以利用它将多个查询合并为一个，从而减少表的访问次数，显著提升查询性能。

2.1 问题场景

我们先来看一个典型的问题场景 - 多个子查询重复访问同一张表:

-- 创建测试环境
drop table t1 purge;
drop table t2 purge;
create table t1 as select * from dba_objects;
create table t2 as select * from dba_objects;
-- 准备测试数据
update t2 set status='INVALID' WHERE ROWNUM<=10000;
update t2 set generated='Y' WHERE ROWNUM<=10000;
update t2 set temporary='Y' WHERE ROWNUM<=10000;
update t2 set temporary='M' WHERE temporary<>'Y';
update t2 set temporary='Q' WHERE temporary<>'Y' or temporary<>'M';
COMMIT;

2.2 原始查询(低效写法)

set autotrace traceonly  
set linesize 1000
              
select t1.object_name,
       t1.object_id,
       (select count(*)
          from t2
         where temporary = 'Y'
           and t2.object_id = t1.object_id) cnt_temporary_y,
       (select count(*)
          from t2
         where created >=sysdate-365 
           and t2.object_id = t1.object_id) cnt_created_new,
       (select sum(object_id)
          from t2
         where status <> 'VALUD'
           and t2.object_id = t1.object_id) sum_objid_status_v,
       (select sum(object_id)
          from t2
         where generated = 'Y'
           and t2.object_id = t1.object_id) sum_objid_generated_y,
       (select sum(object_id)
          from t2
         where generated = 'M'
           and t2.object_id = t1.object_id) sum_objid_generated_m,
       (select sum(object_id)
          from t2
         where generated = 'Q'
           and t2.object_id = t1.object_id) sum_objid_generated_q 
  from t1
where t1.object_id <= 50;
执行计划分析
Plan hash value: 2340670826
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    47 |  1410 |   285   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS FULL| T2   |     1 |     7 |   286   (1)| 00:00:04 |
|   3 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  4 |   TABLE ACCESS FULL| T2   |     1 |    13 |   285   (1)| 00:00:04 |
|   5 |  SORT AGGREGATE    |      |     1 |    12 |            |          |
|*  6 |   TABLE ACCESS FULL| T2   |     1 |    12 |   286   (1)| 00:00:04 |
|   7 |  SORT AGGREGATE    |      |     1 |     7 |            |          |
|*  8 |   TABLE ACCESS FULL| T2   |     1 |     7 |   286   (1)| 00:00:04 |
|   9 |  SORT AGGREGATE    |      |     1 |     7 |            |          |
|* 10 |   TABLE ACCESS FULL| T2   |     1 |     7 |   286   (1)| 00:00:04 |
|  11 |  SORT AGGREGATE    |      |     1 |     7 |            |          |
|* 12 |   TABLE ACCESS FULL| T2   |     1 |     7 |   286   (1)| 00:00:04 |
|* 13 |  TABLE ACCESS FULL | T1   |    47 |  1410 |   285   (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T2"."OBJECT_ID"=:B1 AND "TEMPORARY"='Y')
   4 - filter("T2"."OBJECT_ID"=:B1 AND "CREATED">=SYSDATE@!-365)
   6 - filter("T2"."OBJECT_ID"=:B1 AND "STATUS"<>'VALUD')
   8 - filter("T2"."OBJECT_ID"=:B1 AND "GENERATED"='Y')
  10 - filter("T2"."OBJECT_ID"=:B1 AND "GENERATED"='M')
  12 - filter("T2"."OBJECT_ID"=:B1 AND "GENERATED"='Q')
  13 - filter("T1"."OBJECT_ID"<=50)
性能统计
统计信息
--------
          0  recursive calls
          0  db block gets
     300904  consistent gets
          0  physical reads
          0  redo size
       2379  bytes sent via SQL*Net to client
        449  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         49  rows processed

2.3 问题分析

从执行计划可以清晰看出，T2表被重复访问了6次！这6次访问分别对应6个子查询:

cnt_temporary_y
cnt_created_new
sum_objid_status_v
sum_objid_generated_y
sum_objid_generated_m
sum_objid_generated_q

由于这些子查询访问的是同一张表，并且写法非常相似，我们可以利用Case When语法将它们合并为一个查询，从而减少表的访问次数。

2.4 优化改写(使用Case When)

with w_t2 as 
(select 
       t2.object_id,
       count(case when t2.temporary='Y' then 1 end) CNT_TEMPORARY_Y,
       count(case when created >=sysdate-365 then 1 end) CNT_CREATED_NEW,
       sum(case when t2.status<>'VALID' then t2.object_id end) SUM_OBJID_STATUS_V, 
       sum(case when t2.generated = 'Y' then t2.object_id end) SUM_OBJID_GENERATED_Y,
       sum(case when t2.generated = 'M' then t2.object_id end) SUM_OBJID_GENERATED_M,
       sum(case when t2.generated = 'Q' then t2.object_id end) SUM_OBJID_GENERATED_Q
from  t2
group by t2.object_id)
select t1.object_name, t1.object_id, w_t2.* 
from t1, w_t2
where t1.object_id = w_t2.object_id 
and t1.object_id <= 50;
优化后执行计划
Plan hash value: 3226881135
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    47 |  4512 |   572   (1)| 00:00:07 |
|   1 |  HASH GROUP BY      |      |    47 |  4512 |   572   (1)| 00:00:07 |
|*  2 |   HASH JOIN         |      |    47 |  4512 |   571   (1)| 00:00:07 |
|*  3 |    TABLE ACCESS FULL| T1   |    47 |  3384 |   285   (1)| 00:00:04 |
|*  4 |    TABLE ACCESS FULL| T2   |    47 |  1128 |   285   (1)| 00:00:04 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   3 - filter("T1"."OBJECT_ID"<=50)
   4 - filter("T2"."OBJECT_ID"<=50)
优化后性能统计
统计信息
--------
          0  recursive calls
          0  db block gets
       2040  consistent gets
          0  physical reads
          0  redo size
       2633  bytes sent via SQL*Net to client
        449  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         49  rows processed

3. 性能对比分析

在这里插入图片描述

优化后查询的逻辑读从 300,904降至 2,040，性能提升了近 147倍！这种巨大的改进主要得益于减少了对T2表的重复访问。

4. Case When优化说明

4.1 Case When语法结构

Oracle支持两种Case When语法:

简单Case表达式:

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ...
  [ELSE else_result]
END

搜索Case表达式:

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  [ELSE else_result]
END

4.2 聚合函数与Case When结合

在SQL优化中，最常见的是将Case When与聚合函数结合使用:

4.2.1. 计数条件记录:

COUNT(CASE WHEN condition THEN 1 END)

4.2.2. 条件求和:

SUM(CASE WHEN condition THEN value END)

4.2.3. 条件平均值:

AVG(CASE WHEN condition THEN value END)

4.2.4. 最大/最小值:

MAX(CASE WHEN condition THEN value END)
MIN(CASE WHEN condition THEN value END)

4.3 优化技巧应用场景

Case When优化技术适用于以下场景:

5. 总结

Case When改造是SQL优化中一种强大的等价改写技术，能够显著减少表的访问次数，提升查询性能。通过本文的案例分析，我们可以清晰看到一个简单的Case When改写使查询性能提升了近147倍，这充分说明了该技术的强大效果。

  • 在实际工作中，我们应该:

  • 识别重复访问同一表的子查询模式

  • 使用Case When技术将多个子查询合并

  • 结合WITH子句提高SQL可读性

  • 验证改写后的性能提升

掌握并灵活运用Case When改造技术，能够帮助我们编写更高效、更优雅的SQL语句，从而显著提升数据库应用性能。


在这里插入图片描述


未完待续... 

    SQL优化经典等价改写2——rownum分页

公众号：收获不止数据库

微信：ljbyxl1213

系列回顾



来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/9091385/viewspace-3082247/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： Oracle普通表自动改造分区表（可借鉴到其他数据库）
下一篇： 通用SQL优化经典等价改写【二】——分页优化
请登录后发表评论 登录
全部评论
梁敬彬
专家姓名：梁敬彬
历任中电福富研究院副理事长、中盾安信研究院副院长、宁德时代技术顾问等职务，受聘电信集团专家和国家十四五课题负责人，著有《收获，不止Oracle》《收获，不止SQL优化》等多本畅销数据库技术书籍。

注册时间：2024-02-06

  • 博文量
    162
  • 访问量
    198116

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593