Oracle查询优化改写技巧与案例2.0

《Oracle查询优化改写技巧与案例2.0》不讲具体语法，只是以案例的形式介绍各种查询语句的用法。第1～4章是基础部分，讲述了常用的各种基础语句，以及常见的错误和正确语句的写法，应熟练掌握这部分内容，因为在日常查询和优化改写中都要用到；第5～12章是提高部分，讲解了正则表达式、分析函数、树形查询及汇总函数的用法，这部分内容常用于一些复杂需求的实现及优化改写；最后两章介绍日常的优化改写案例，这部分内容是前面所学知识的扩展应用。

如果您是开发人员，经常与Oracle打交道，那么《Oracle查询优化改写技巧与案例2.0》可以帮助您处理复杂的需求，写出高性能的语句。如果您是运维人员，则本书可以帮助您更快地完成慢语句的改写优化。

师庆栋，网名有教无类。SWOUG（西南Oracle用户组）成员，资深Oracle开发DBA，道森培训高级讲师。拥有超过17年的IT从业经验，擅长Oracle数据库开发，对于数据库架构设计、性能优化拥有丰富的实战经验！通过大量的报表优化改写，积累了丰富的优化改写经验，擅长对Oracle查询语句的优化改写。

罗炳森，网名落落。擅长SQL优化，对Oracle优化器有较深的理解。从2007年至今一直专注于SQL优化、表设计优化、SQL查询改写。经常热心帮助网友解决SQL性能问题，累计SQL优化案例上千个。

适读人群 ：如果您是开发人员，经常与Oracle打交道，那么本书可以帮助您处理复杂的需求，写出高性能的语句。如果您是运维人员，则本书可以帮助您更快地完成慢语句的改写优化。

字典式写作手法：不讲具体语法，没有简单知识堆砌，直接以案例形式讲技巧与案例

大量优化实战方法：将主要SQL优化点一一剖析，分享大量SQL优化的实际工作经验

50余改写调优案例：覆盖大多数DBA日常工作场景，具有相当大的实用价值

技巧+案例：可以作为DBA的参考手册，也可以作为开发人员编写SQL的速查字典

《Oracle查询优化改写技巧与案例2.0》在《Oracle查询优化改写技巧与案例》的基础上进行了如下更新。

√所有代码都重新执行了一遍，以减少谬误。

√为了提高清晰度，尽量删除了图片，改用文本方式展示案例结果。

√为了提高阅读效率，删除了平时较少用到的内容。

√为了提高可读性，大部分案例都改用了SAMPLE中的数据，这样读者可以更容易地验证代码及思路。

序1

白鳝

国内知名DBA专家

作为一个有二十多年信息系统工作经历的老DBA，我最近这十多年的主要工作是系统优化，从最初的Oracle数据库优化到现在的信息系统整体优化、架构优化，这十多年来已经做过上百个案例。虽然做了十多年的优化，但老实说，SQL优化一直是我最不擅长、也最不愿意去做的工作，虽然这些年我也被逼着做了不少SQL优化的工作，但我认为自己在这方面还存在缺陷。这些年和我搭档做优化的老熊、老储都是SQL优化方面的高手，在SQL优化方面，他们的能力和经验都远高于我。我也曾和老熊、老储他们针对SQL优化工作做过交流，问他们能否写一本这方面的书。他们虽然做过近十年的SQL优化，但也仅限于见招拆招，对于写一本这方面的书，他们都觉得难度很大。SQL优化的难度很大，主要有以下方面原因。

首先是SQL优化工作十分繁杂，在一个维护得比较好的系统中，需要优化的SQL往往都是业务逻辑十分复杂的SQL语句，而不是简单地加一个索引就能解决问题，甚至有些SQL语句要打印十多页纸，想要理解这样的SQL语句的逻辑含义往往需要花上一段时间。

其次是SQL优化的方法与手段十分丰富，分析工具、分析方法及分析路径纷繁复杂，不同的SQL可能需要用不同的分析思路进行分析，很难找到一条放之四海而皆准的准则。要想获得这些方法只有两个途径：一个是通过自己的实践不断总结和积累经验；另一个是通过阅读相关的书籍，获得前人总结好的经验。

虽然现在市面上有很多关于Oracle SQL优化的书籍，但绝大多数书籍中介绍的SQL优化仅仅介绍了SQL优化的工具如何使用，执行计划如何解读，以及SQL优化从大方向上该如何去做，所举的例子也往往过于经典，不一定适用于我们的生产环境。特别是到目前为止，还没有一本十分系统地介绍SQL改写技巧的书籍，而SQL改写却又是SQL优化中最难的一种工作，也是最实用的一种技术。

第一次看到本书时，其目录让我眼前一亮，这不是一本简单堆砌知识的书籍，而是一本和大家分享工作经验的书。从目录结构就可看出作者对Oracle SQL执行内部机理的认知是十分深入的，同时有大量的SQL优化的实际工作经验。本书从单表访问路径的基础知识开始讲起，将主要的SQL语法中可能的优化点进行一一剖析，总结了大量的优化实战方法。特别值得一提的是，本书最后一章列举了近60个实战案例，内容覆盖大多数DBA的日常工作场景，具有相当大的实用价值。

本书的写作手法十分朴实，甚至可以说有些章节有点过于简练，但是瑕不掩瑜，书中实用的内容之多是十分值得肯定的。本书可以作为DBA的参考书籍，也可以作为开发人员编写SQL的指导书籍。作为DBA行业的一个老兵，我愿意向大家推荐本书，对优化有兴趣的DBA，确实有必要读一读本书。

序2

黄超（网名：道道）

道森教育集团负责人，资深Oracle培训人员

当教主告诉我他准备写一本有关SQL编程改写的书时，我非常高兴，感觉到将会有一大批开发人员可以借助这样一本书将自己的SQL水平提升一个层次。因为我知道这不会是一本SQL入门的书，也不是一本专门讲优化理论的SQL优化书籍，而是一本结合常见的开发场景介绍编程技巧的书籍。教主拥有多年的软件开发和SQL开发经验，从和他的技术交流中，我也学到了很多SQL技巧，更难得的是，他对同一个SQL有多种不同的写法，结合一些SQL优化的原理，很容易找到非常高效的写法。

教主为人低调，热心帮助他人，并且在博客上经常分享一些SQL技巧和相关知识。感谢他的无私奉献，同时期望教主能坚持下去，不断总结他丰富的SQL开发经验，并与我们一起沟通交流。

前言

因开办了Oracle优化改写的在线培训，在教学和答疑的过程中有很多读者希望我能推荐一些相关的学习书籍。说实话，有关Oracle的书籍非常多，但在给读者推荐书籍时我发现特意针对优化改写的书籍不好找，因为很多资料注重各种语法的实现，对优化方面的知识考虑得较少，而介绍优化知识的书籍对改写知识涉及得也不多。因此，和落落商量后，尝试编写了本书。

本书共分14章，各章的主要内容如下。

√第1章介绍初学者在NULL上常犯的错误、字符串中单引号的处理方式及模糊查询时对通配符的转义。

√第2章讲述了ORDER BY的用法及TRANSLATE的特殊用法。

√第3章是基础知识的重点内容，需要掌握好各种连接的写法及为什么要左联、右联，以及过滤条件错误地放在WHERE里会有什么影响；当数据有重复值时要直接关联还是分组汇总后再关联。

√第4章介绍了UPDATE语句的正确用法，以及什么时候UPDATE语句应改写为MERGE。

√第5章以案例的形式讲解了正则表达式的用法，对正则表达式的基础语法不熟悉的读者可以通过官方文档或我的博客来学习，这里面对字符串的拆分方法可以直接套用，而对字符串的分组处理难度稍高，不常处理类似数据的读者可以略过。

√第6章介绍了常用分析函数的几个案例，在大部分情况下使用分析函数会让查询速度得到很大提升。所以，如果想熟练地改写，就必须熟悉分析函数的应用。另外，本章还对很多人感到模糊的max() keep()语句进行了分析。

√第7、8章讲了DATE类型的常见用法。

√第9章仍然介绍分析函数，希望本章内容对范围的处理能给读者一些借鉴。

√第10章的重点是结果集的分页，要弄清楚如何分页，为什么Oracle的分页会写得那么复杂，等等。

√第11章讲述了行列转换函数，并对两个函数进行了剖析，理解了其中的原理就可以用UNPIVOT对UNION ALL做一定的优化。本章的另一个重点就是分组汇总小计的统计，熟练掌握ROLLUP及CUBE可以让你少写一些UNION ALL语句。

√第12章能帮助读者在写树形查询时减少不必要的错误，生成更准确的数据。

√第13章选取了部分网友的需求案例，希望读者能通过这些案例的启发找到实现自己需求的思路。

√第14章选取了能覆盖目前大部分改写方法的案例。读者需要在对前面内容熟悉的基础上来学习这些案例。各种改写方法能否提高速度都与对应的环境有关，所以掌握更多的优化知识和改写方法对优化有很大的帮助。

在此要特别感谢白鳝老师和我们的同事道道给本书作序，通过白鳝老师写的序可以看到，他认真阅读了本书并给出非常中肯的评价，能在百忙之中花费大量时间耐心地把我写的书看完，确实非常令人感动。另外，还要感谢出版社的各位编辑，有很多地方词不达意，是他们给我指出了错误的地方，并给出了改正意见。

《Oracle查询优化改写技巧与案例2.0》在《Oracle查询优化改写技巧与案例》的基础上进行了如下更新。

√所有代码都重新执行了一遍，以减少谬误。

√为了提高清晰度，尽量删除了图片，改用文本方式展示案例结果。

√为了提高阅读效率，删除了平时较少用到的内容。

√为了提高可读性，大部分案例都改用了SAMPLE中的数据，这样读者可以更容易地验证代码及思路。

√删除了实用性不高的实战案例，另增加了部分实战案例（详见最后两章）。

因水平有限，本书在编写过程中难免有错漏之处，恳请读者批评、指正。

作 者

第1章 单表查询 1

1.1 查询表中所有的行与列 1

1.2 从表中检索部分行 2

1.3 查找空值 3

1.4 空值与运算 3

1.5 处理空值 4

1.6 空值与函数 5

1.7 查找满足多个条件的行 6

1.8 从表中检索部分列 7

1.9 为列取有意义的名称 7

1.10 在WHERE子句中引用取别名的列 8

1.11 拼接列 8

1.12 在SELECT语句中使用条件逻辑 9

1.13 限制返回的行数 11

第2章 给查询结果排序 12

2.1 以指定的次序返回查询结果 12

2.2 按多个字段排序 14

2.3 按子串排序 15

2.4 从表中随机返回n条记录 16

2.5 TRANSLATE 18

2.6 按数字和字母混合字符串中的字母排序 19

2.7 处理排序空值 20

2.8 根据条件取不同列中的值来排序 21

第3章 操作多个表 23

3.1 UNION ALL与空字符串 23

3.2 UNION与OR 24

3.3 UNION与去重 26

3.4 组合相关的行 30

3.5 IN、EXISTS和INNER JOIN 30

3.6 INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL JOIN解析 32

3.7 外连接与过滤条件 36

3.8 自关联 39

3.9 NOT IN、NOT EXISTS和LEFT JOIN 40

3.10 检测两个表中的数据及对应数据的条数是否相同 43

3.11 聚集与内连接 44

第4章 插入、更新与删除 49

4.1 插入新记录 49

4.2 阻止对某几列插入 50

4.3 复制表的定义及数据 51

4.4 用WITH CHECK OPTION限制数据录入 51

4.5 多表插入语句 52

4.6 用其他表中的值更新 56

4.7 合并记录 62

4.8 删除违反参照完整性的记录 64

4.9 删除名称重复的记录 64

第5章 使用字符串 67

5.1 生成连续数值 67

5.2 遍历字符串 68

5.3 计算字符在字符串中出现的次数 69

5.4 从字符串中删除不需要的字符 71

5.5 将字符和数字数据分离 72

5.6 查询只包含字母或数字型的数据 73

5.7 提取姓名的大写首字母缩写 78

5.8 根据表中的行创建一个分隔列表 79

5.9 提取第n个分隔的子串 80

5.10 分解IP地址 82

5.11 将分隔数据转换为多值IN列表 82

5.12 组合去重 83

第6章 使用数字 86

6.1 常用聚集函数 86

6.2 列转行 88

6.3 行转列 89

6.4 生成累计和 91

6.5 累计与重复值 95

6.6 生成排名 97

6.7 返回最值对应信息 98

6.8 求总和的百分比 99

第7章 日期运算 103

7.1 日期类型 103

7.2 日期计算 105

7.3 时间间隔类型 105

7.4 日期计算函数 106

7.5 间隔月份 108

7.6 获取记录间的间隔时间 108

第8章 日期操作 112

8.1 提取日期中的信息 112

8.2 提取间隔类型中的信息 115

8.3 周的计算 117

8.4 计算一年中周内各日期的次数 119

8.5 确定一年是否为闰年 123

8.6 创建本月日历 123

8.7 全年日历 126

8.8 补充范围内丢失的值 129

8.9 识别重叠的日期范围 131

第9章 范围处理 134

9.1 定位连续值的范围 134

9.2 合并连续区间 136

9.3 合并重叠区间 140

9.4 用WITH进行范围分组 143

第10章 高级查找 147

10.1 给结果集分页 147

10.2 使用管道函数把全表查询改为增量查询 149

10.3 内联视图与错误数据 153

10.4 正确使用分析函数 155

10.5 找到包含最大值和最小值的记录 157

10.6 提取维度信息 159

第11章 报表和数据仓库运算 162

11.1 行转列子句 162

11.2 列转行子句 165

11.3 将结果集反向转置为一列 168

11.4 打印小票与行列转换 169

11.5 数据分组 172

11.6 计算简单的小计 173

11.7 判别非小计的行 175

11.8 对不同组/分区同时实现聚集 177

11.9 移动范围取值分析 179

11.10 计算本期、新增、累加 182

11.11 listagg与小九九 186

第12章 分层查询 188

12.1 简单的树形查询 188

12.2 根节点、分支节点、叶子节点 190

12.3 sys_connect_by_path 191

12.4 树形查询中的排序 192

12.5 树形查询中的WHERE 193

12.6 查询树形的一个分支 195

12.7 剪去一个分支 196

12.8 多行字符串的拆分 196

第13章 应用案例实现 199

13.1 解析简单公式 199

13.2 匹配汉字 200

13.3 多表全外连接的问题 202

13.4 根据传入条件返回不同列中的数据 203

13.5 拆分字符串进行连接 205

13.6 用“行转列”来得到隐含信息 206

13.7 用隐藏数据进行行转列 208

13.8 用正则表达式提取clob里的文本格式记录集 210

第14章 改写调优案例分享 212

14.1 为什么不建议使用标量子查询 212

14.2 用LEFT JOIN优化标量子查询 215

14.3 用LEFT JOIN优化标量子查询之聚合改写 216

14.4 用LEFT JOIN及行转列优化标量子查询 217

14.5 标量中有ROWNUM=1 218

14.6 ROWNUM=1引起的逻辑问题 219

14.7 标量中有不等关联时改写的问题 222

14.8 标量中有聚合函数时改写的问题 226

14.9 用分析函数优化标量子查询（一） 229

14.10 用分析函数优化标量子查询（二） 231

14.11 用分析函数优化标量子查询（三） 233

14.12 标量中的树形查询 235

14.13 使用标量子查询优化取最值语句 238

14.14 用MERGE改写优化UPDATE 240

14.15 UPDATE中有ROWNUM=1 243

14.16 用MERGE使用左联 245

14.17 用MERGE改写UPDATE之多个子查询 249

14.18 将UPDATE改写为MERGE时遇到的问题 251

14.19 通过分页方式优化最值语句 254

14.20 让分页语句走正确的PLAN 256

14.21 去掉分页查询中的DISTINCT 257

14.22 优化有GROUP BY的分页语句 261

14.23 用WITH语句优化查询 264

14.24 用WITH辅助把OR改为UNION 265

14.25 错误的WITH改写 269

14.26 错误的分析函数用法 272

14.27 用LEFT JOIN优化多个子查询（一） 274

14.28 用LEFT JOIN优化多个子查询（二） 278

14.29 用LEFT JOIN优化多个子查询（三） 280

14.30 去掉由EXISTS引起的FILTER 283

14.31 巧改驱动表提升效率 284

14.32 用分析函数更改反连接 286

14.33 集合判断 288

14.34 相等集合判断 289

14.35 用分析函数改写最值过滤条件 291

14.36 用树形查询找指定级别的数据 292

14.37 行转列与列转行 293

14.38 UPDATE、ROW_NUMBER与MERGE 295

14.39 一个UPDATE语句的优化 298

14.40 自定义函数对速度的影响 302

14.41 纠结的MERGE语句 305

14.42 用CASE WHEN去掉UNION ALL 308

14.43 不恰当的WITH及标量子查询 320

14.44 用分析函数加“行转列”来优化标量子查询 323

14.45 用分析函数处理问题 326

14.46 用列转行改写A表多列关联B表同列 329

14.47 用分析函数改写最值语句 332

14.48 多列关联的半连接与索引 334

14.49 巧用分析函数优化自关联 335

14.50 纠结的UPDATE语句 340

14.51 巧用JOIN条件合并UNION ALL语句 342

14.52 用分析函数去掉NOT IN 345

14.53 读懂查询中的需求之裁剪语句 349

14.54 去掉FILTER里的EXISTS之活学活用 350