解决Oracle SQL语句性能问题——SQL语句改写（视图、标量子查询及update）

这篇具有很好参考价值的文章主要介绍了解决Oracle SQL语句性能问题——SQL语句改写（视图、标量子查询及update）。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

我们在前述文章中也已经提到，对于高版本的关系库，尤其是针对Oracle这样的关系库，绝大多数场景下，同一语义和结果的SQL语句的具体语法，不会成为SQL语句执行计划的影响因素，但在少数场景下，针对同一语义和结果的SQL语句的不同写法，数据库优化器最终会分别为其生成不同的执行计划。因此，当一条SQL语句的执行计划不理想时，为了改变该SQL语句的执行计划，我们会考虑改写相应SQL语句，从而达到调优目的。下面，具体介绍通过SQL语句改写进行调优的方法和相关内容。

1. 消除视图（view）

视图可以简化应用的研发和维护，还可以满足安全性方面的需求。但在有些场景中，视图会为应用埋下性能隐患，尤其在大数据量业务中，大量且多层次嵌套使用视图的场景。为了消除性能隐患和优化性能，有时需要我们改写包含视图的SQL语句，分解并消除其中的相关视图，具体改写方法如下所示。

--改写前

SQL> create view v_test as

select * from tab1 where col1=...;

SQL> select * from tab2 t2,tab3 t3,v_test v

where t2.col1=t3.col1

and t3.col1=v.col1

and v.col2=...;

--改写后

SQL> select * from tab2 t2,tab3 t3,tab1 t1

where t2.col1=t3.col1

and t3.col1=t1.col1

and t1.col2=...

and t1.col1=...;

--注：

1）消除视图时需要注意的事项。

是否符合视图分解和消除条件，有些场景的视图，是没办法分解和消除的。
要把和原视图相关的select list中相关项，更换为原视图相应基表的列名。
要把原视图内的where条件和主查询合并。
要把原主查询中视图相关的条件更换为视图相应基表列的相关条件。

2）消除视图的适用场景，包括但不限于如下场景。

原SQL语句主查询from中连接数据对象比较多，且原SQL语句存在性能问题。
原SQL语句的视图子查询from中连接数据对象比较多，且原SQL语句存在性能问题。
原SQL语句中视图嵌套层数比较多，且原SQL语句存在性能问题。

3）设计实验并做测试。

获取分解消除视图前后的执行计划，并比较思考分解消除视图前后的变化和利弊。
注意分解消除视图前后，执行计划中join相关节点（见本书第7章）的变化，执行计划的整体调整和变化。
如果分解消除视图前后的执行计划没有变化，大家思考下，没有发生变化的原因，以及导致可能发生变化的场景和因素。

2. 标量子查询改为外连接

包含标量子查询的SQL语句，看上去思路清晰、逻辑分明且易于理解，但在很多场景中，标量子查询会导致其相关SQL语句的严重性能问题。因此，为了解决相关SQL语句的性能问题，有时我们需要对SQL语句进行改写，并消除相关的标量子查询，以对相关SQL语句进行性能优化，具体改写方法如下所示。

--改写前

SQL> select t1.col1,

(

select col2

from

(

select t2.col1,sum(t2.col2) col2

from tab2 t2

group by t2.col1

) t3

where t3.col1=t1.col2

) col2_sum

from tab1 t1

where t1.col3=...;

--改写后

SQL> select t1.col1,t3.col2 col2_sum

from tab1 t1,

(

select t2.col1,sum(t2.col2) col2

from tab2 t2

group by t2.col1

) t3

where t1.col2=t3.col1(+)

and t1.col3=...;

--注：

1）标量子查询改写为内嵌视图时需要注意的事项。

是否符合改写和消除标量子查询的条件。

原标量子查询下沉为内嵌视图时，需要和原主查询表进行outer join。
原SQL语句中存在多个标量子查询时，应该分别改为相应内嵌视图并下沉，同时，分别和原主查询表进行outer join。
原SQL语句中存在多个标量子查询时，分别改为相应内嵌视图，下沉并进行outer join后，原主查询select-list中各标量子查询相关的列，改为相应内嵌视图的列。

2）标量子查询改写为内嵌视图的适用场景，包括但不限于如下场景。

原SQL语句中包含标量子查询，且原SQL语句存在性能问题。
原SQL语句中包含标量子查询，且原SQL语句消耗较多系统资源。

3）设计实验并做测试。

获取改写和消除标量子查询前后的执行计划，并比较分析改写和消除标量子查询前后的变化和利弊。
注意观察分析相关SQL语句改写前后，子查询执行次数的变化和执行计划的整体变化。

3. update改为merge into

update作为DML语句之一，现实工作中，被数据库及研发相关人员广泛使用。但在某些场景中，update语句会导致严重的性能问题。基于上述场景，为了解决update语句导致的性能问题，有时我们需要将update语句改写为merge into语句，以消除导致性能问题的因素，优化和提升相关应用性能。

2014年，某行业机构相关人员对线上产品某核心模块重构、测试后，按计划在生产环境中进行替换，不料，期间发生严重性能问题，导致整个应用系统异常，用户无法使用。本人接到技术支援求助后，连夜通过该数据库的相关信息进行问题跟踪、分析和诊断，确定该故障由重构后的新应用模块导致，并在各方相关人员的协助下，在线对生产环境中该应用模块进行了修改和替换，即将该模块中一个update语句修改为merge into语句。之后，经本人和相关各方人员跟踪和观察，之前性能问题消失，相关应用模块性能提升近千倍。

此外，除了解决性能问题，某些场景中，merge into语句也能很好的解决某些特殊的业务需求。2016年，通过merge into语句，本人帮助某行业机构产品研发团队成功解决了跨时段详单汇聚问题，这样，既避开了原计划在大数据量、高并发频繁操作的核心业务表上，建立触发器可能带来性能等多方面问题的风险，又大幅提高了相应模块的性能，简化了应用逻辑和实现方法，为今后系统的管理和维护打下了良好的基础。关于update语句改写为merge into语句，具体改写方法如下所示。

--改写前

SQL> update tab1 t1 set col2=

(

select col2

from tab2 t2

where t1.col1=t2.col1

)

where t1.col3=...;

--改写后

SQL> merge into tab1 t1

using tab2 t2

(