详细大纲版:oracle优化培训之<SQL写法与改写>第二期

预告: 下一次培训主要面向DBA, 讲一下ora工具的用法与用途,大概需要一天时间.

第一期的SQL写法与改写培训已经过去近5个月,相信很多学员已经掌握了大部分精髓,课后大家在微信群又分享和讨论了一些案例,加深了培训内容的理解。第一期侧重点是OLTP系统的SQL。

我的业务不是只有培训,对客户生产系统做性能分析和优化是我的主业。多年大量客户生产系统的优化过程,包含了大量的经典案例,很多学员希望我能够把这些案例体现在培训中。其实在之前的几个理论专题的培训中, 已经加入了大量的生产真实案例。为了能够让大家更好的理解这些综合案例,第二期的SQL写法与改写(侧重点是OLAP系统的SQL)培训就要先给大家讲完。

索引专题(两天线上)+SQL写法与改写专题(两期线上),基本上就是SQL优化的精华了(以后还会增加hint专题)。

区间检索:

严格区间检索,网上介绍的优化方法有什么问题:

使用降序索引又是什么问题?

匹配不到区间时,执行效率为什么会那么差?

学习终极写法的同时,还能深入理解索引扫描

关联更新:

update的错误写法,update的正确写法,update的高效写法,高效改进写法; merge语法介绍,merge写法,merge改进写法, merge 修改关联字段的写法; CTAS实现关联更新的写法

分析函数:

row_number/rank/dense_rank的区别和用途; 用dense_rank优化改写生产案例;ratio_to_report用法; lag/lead用法介绍及几个写法需求的实现; sum分析函数带order by的注意事项; 用lag/sum/dense_rank共同实现的写法综合案例; dense_rank keep及注意事项; first_value/last_value实现相同逻辑;

with as的几种用途及实战案例:

造测试数据

提高代码可读性

materialize --生产案例

递归

内嵌函数

connect by层级查询:

自顶向下查/自底向上查

展开连续区间写法

拆分逗号分隔字符串为多行

拆分表字段字符分隔字符串的两种方法

顺便讲一下合并:

多行记录合并为一行listagg

listagg 19c前如何去重?

配合使用in还是使用exists?--生产案例

不能push_pred怎么办?--生产案例

分页查询不能做nested loop被驱动怎么办?--生产案例

被强制使用低效view merge怎么办?--生产案例

批处理(row by row)还是单SQL(set based):

一个包含多个sql的loop循环存储过程,用一条sql实现

in 列表元素过多:

in (1,2,3,4) / in (1,3,4,6,8) / in (1,......,1000) 这类sql,使用绑定变量没有意义,用两种方法解决这种问题

大数据导出到文本文件:

用网上提供的工具? 用原始的spool ? 还是java代码写文件?

直接用oracle提供的utl_file做成函数, 并行快速导出

可以在应用代码调用,也可直接shell脚本调用

文本文件导入到表:

sqlldr? 外部表?

pivot行转列:

不会行转列,多次union all,效率低下, 生产案例真实感受一下

不用pivot, case when也能实现相同功能

unpivot列转行:

不会列转行,多次union all,效率很差, 生产案例给你练习,看看效果

多列转行, 书上介绍的方法很复杂, 不要被误导

Model:

model写法比较偏门, 给两个实际需求案例了解一些就行了

综合案例分析:

1. 网上流传的计算哪个时间段在线人数最多?再引申一下,把具体的时间点列出来

2. unpivot 实现组合统计: pc平台购买,ios平台购买,Android平台购买,pc+ios,pc+android,ios+andriod,pc+ios+andriod不同组合的统计

3. 生产案例:SQL使用or,大结果集做filter, 25秒; 改写后5毫秒以内; 能不能不改写达到效率提高? 用到上期写法知识点

4. 生产案例:txn_dt||txn_tm>=:1 and txn_dt||txn_tm<=:2 这种写法的常规改写? 结合生产实际情况的实际改写.

5. 一个SQL因为结果集错误拿出来讨论,可以有多种优化写法,提高效率的同时, 原来的结果集错误问题也可以避免.