SQL优化的本质就是减少访问路径。相信大家都有学到了很多减少访问路径的思路，比如增加索引从全表扫描转换成索引范围扫描，比如把表改造成分区表从而从全表扫描转化成局部分区扫描，这些都属于不需要改写SQL就能完成的减少访问路径的思路。当然，在很多场景下，我们必须要完成一些等价改写，比如Case When改造、Rownum分页改写等等。

除了减少访问路径外，还要注意避免外因的影响，比如一些执行计划不稳定、所在环境的资源不足等等，这些也是我们需要注意的。

总体思路如下图：

接下来我们从Case When、Rownum、Hint直接路径改造、只取所需列、避免递归调用、rowid优化这6个案例展开，向读者展现不同写法前后的优化效果。

这里精心构造一个经典案例，其中CNT_TEMPORARY_Y、CNT_CREATED_NEW、SUM_ OBJID_STATUS_V、SUM_OBJID_GENERATED_、SUM_OBJID_GENERATED_M、SUM_OBJID_GENERATED_Q这6处是构造的列，作为结果集要展现出来。实战写法中T表被访问了多次，具体如下：

环境构造：

写法1：

对应的执行计划：

脚本（让表被访问多次的低效写法）

这里通过执行计划，可以看出T2表被访问了6次，该SQL语句的逻辑读高达300904！来，看看Case When改造的写法2，如下：

写法2执行计划如下：

脚本（Case When改造后的高效写法）

观察写法2的执行计划，我们发现T2表的访问次数仅1次。而且逻辑读为2040！差异如此巨大的本质原因就是写法2通过Case When的经典写法，将这些查询进行了合并，减少了表访问次数，自然就大幅度提升了性能！

写法1：

脚本（Rownum分页的普通写法）

写法2：

脚本（Rownum分页的高效写法）      

写法1的Buffers是1723，而写法2是5，为啥会有如此巨大的差异！认真看执行计划就能明白其中的原委。写法1的执行计划ID=2处的Operation对应的关键字是COUNT，而写法1的执行计划ID=2处的Operation对应的关键字是COUNT STOPKEY。写法2对应的A-ROWS是10，这意味着该SQL仅扫描10条记录，显然是局部扫描。而写法1是111K，这意味着全表每条记录都扫描了。

构造环境：

脚本（Hint直接路径改造的环境构造 ）   

比较两表插入的速度：

脚本（普通插入与直接路径写的性能差异）

插入T1表和T2表的速度差异如此之大，说明绕过SGA的直接路径访问性能提升非常明显，值得在特定的场合下使用。

（1）只取所需列，访问视图变快

环境准备，建一个视图v_t1_join_t2：

执行select * from v_t1_join_t2语句，必须要访问T2和T1表，如下：

但是有时会出现这样的情况：开发人员实际只需要取object_id、object_name两个列，但是他们为了简单，直接使用select * from先把所有列取回本地，再过滤object_id、object_name这两列，这时其实select * from v_t1_join_t2语句是等同于select object_id，object_name  from v_t1_join_t2的。那我们看看这个语句执行后是啥情况，如下：

脚本（普通访问视图的写法 ）    

我们惊奇地发现，这个语句只访问了T2表。这是为啥呢？因为object_id、object_name这两个列全部来自T2表，而且这个视图中的T1和T2表有主外键关联，确保了只取T2表记录且不会取错。这样Oracle既保障了高效又确保了记录的准确。

（2）只取所需列，索引无须回表

场景1，一个普通的利用索引查询的SQL语句，如下：

该语句通过IDX_OBJ_ID定位到了rowid，然后通过TABLE ACCESS BY INDEX ROWID回到表中，获取到了object_id列为*的信息，然后将结果展现。不过假如这里的情况和上述的情况类似，开发人员只是通过select * 把所有列都取回来，再取object_id、object_type两列，实际和select object_id,object_type from t where object_id=28等价。但是这么写性能可不一样了，由于object_type列的信息在索引中已经有了，所以就无须回表了。如下：

脚本（只取部分列后，访问视图有变化）

（3）避免或者减少递归调用

避免或者减少函数调用，这一个是非常重要的优化要点，也是一个非常常用的优化手段，现实中类似的案例场景也非常常见。一般来说，能避免函数调用就选择避免，一般是将函数调用改写成表连接的模式。但是有的时候函数调用不可避免，比如一些非常复杂的逻辑封装在函数中，一般人员实现起来比较困难，且使用面又广，那就只有使用函数调用。不过遇到这个场景，我们依然可以优化，那就是通过写法的优化，将函数调用的次数降低。

首先我们看看函数调用避免的手法和对应案例。

脚本(函数调用性能研究的环境准备)

以下两种写法是等价的，都是为了查询people表信息，同时通过sex表，获取人员的性别信息。

脚本(函数调用和两表关联两个等价写法)

通过autotrace比较观察，发现两种写法在性能上存在巨大差异，首先跟踪写法1：

脚本(函数调用写法的性能)

接下来跟踪写法2：

脚本(两表关联写法的性能)

结论：这里的性能差异非常明显，究其本质原因，就是写法1进行了过多的函数调用。

前面说过了，很多时候函数调用不可避免的时候只有想办法降低函数调用次数。而降低函数调用次数有两个主要思路：

1. 尽量将函数写在聚合汇集结果集后而不是写在之前，显然聚合后调用会降低调用次数。

2. 当函数在取值条件的位置时，可以考虑函数索引来减少递归调用。

    

• 函数写法的位置

首先我们研究第一种场景，构造两个函数f_deal1和f_deal2。这两个函数其实是一模一样的，构造这个仅仅是为了说明函数索引可以减少递归调用，具体试验如下：

脚本(构造环境，建函数)

我们观察如下两个等价语句的性能差异，首先看写法1：

脚本(函数调用在汇集结果集之前)

接下来看写法2：

脚本(函数调用在汇集结果集之后)

• 用函数索引优化

当函数在where 条件下调用，且在没有建函数索引时，结果如下：

脚本(未建函数索引的函数调用)

当函数在where 条件下调用，且建了函数索引，结果如下：

脚本(建函数索引的函数调用)

当没有建函数索引时产生111111次递归调用，而建了后递归调用为0，性能得到大幅度提升。

（4）避免使用触发器

建表触发器：

脚本(建表触发器)

写法1，T表有触发器时，插入耗时46s：

脚本(触发器生效时的插入性能)

写法2，触发器失效的情况，插入耗时仅1s多：

脚本(触发器失效时的插入性能)

性能为何差异如此之大，很明显就在于：写法1每插入T1表一条记录，就调用触发器完成一次统计查询，从而产生了大量的递归调用；而写法2则是消除了触发器。这是现实中的一个案例，后续和开发人员确认该统计可以去掉，去掉后性能得以大幅度提升。

类似地，如果触发器调用的是更新语句，那么可以将其修改成程序批量完成，而非每条触发，性能也能大幅度提升。

（5）rowid优化应用

rowid是一个伪列，既是一个非用户定义的列，而又实际存储于数据库之中。每一个表都有一个rowid列，一个rowid值用于唯一确定数据库表中的一条记录。因此通过rowid方式来访问数据也是Oracle数据库访问数据的实现方式之一。由于Oracle rowid能够直接定位一条记录，因此使用rowid方式来访问数据，访问数据的效率非常高！

环境准备：

首先我们来看看最普通的访问方式，全表扫描访问：

脚本(全表扫描)

接下来我们来看看索引扫描方式：

脚本(索引扫描)

最后看看rowid扫描方式：

脚本1(rowid扫描)

全表扫描 consistent gets为1724，索引访问 consistent gets为4，而rowid访问的consistent gets为1，三者的性能差别巨大。TABLE ACCESS BY USER ROWID确实是最高效的访问方式，性能最高！

这里要特别注意使用rowid访问的应用场景。举个例子，我们通过索引找到了某条记录，然后要进行更新，如果不用rowid一般是update tab set …where索引条件，如果刚才在查询的过程中顺带获取到rowid，语句则改变成update tab set …where索引条件和rowid=xxx条件，有了这个rowid=xxx条件，更新性能将进一步提升。

不过值得一提的是，一般情况下rowid是不会变化的。当然，如果做了alter table move的动作，rowid显然就改变了。此外分区表的分区条件内的数据发生了分区转移（比如以地区为分区，厦门的数据转移到厦门来），rowid也会产生变化。

我们在前面的章节中描述过关于Hint改写的内容，主要从Hint、固定Outline、重新收集统计信息三个方面来左右执行计划，从而让不正确的执行计划变得正确。这里的Hint部分也算是对SQL进行特定的等价改写。具体的写法不再累述，略去。

原因看出来了，因为结果集ta里的tache表和外围的tache表先关联，但是之间却没有关联条件，从而导致出现了笛卡尔乘积，所以性能大幅度下降。

因此我们要避免结果集ta的tache表和外围的tache表先关联，就是说让结果集ta内部的三个表先完成关联，再往外关联。那如何实现呢？

改写如下：

原理：为保证Rownum值的准确性，保证该子查询不会移到外面去拿部分表和tache表先关联。

这个问题主要表现为三种可能：

1. 主要是体现在机器的配置比较低，内存CPU及IO的资源不够；

2. 外部某应用程序耗尽了主机的资源，导致主机资源不足；

3. 内部程序大量使用并行操作，资源争用导致所在的环境资源不足。