SQL优化的本质就是减少访问路径,前面的章节中我们已经学到了很多减少访问路径的思路,比如增加索引从全表扫描转换成索引范围扫描,比如表改造成分区表从全表扫描转化成局部分区扫描,这些都属于不需要改写SQL就完成的减少访问路径的思路。当然,在很多场景下,我们还是必须要完成有些等价改写的,比如case when改造、rownum分页改写等等。
除了减少访问路径外,我们还要注意避免外因的影响。比如一些执行计划的不稳定、所在环境的资源不足等等,这些也是我们需要注意的。
总体思路如下:
1 设法减少访问路径
接下来我们从Case When、Rownum、Hint直接路径改造、只取所需列、避免递归调用、rowid优化这6个案例展开,向读者展现不同写法前后的优化效果:
1.1 Case When改造
Case when是Oracle一个重要的SQL语法,在SQL优化中笔者经常利用该语法将SQL的冗余写法进行合并,由此来减少表的访问次数,等同于减少了访问路径,从而大幅度提升SQL优化性能。
请看笔者构造的例子,如下:
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;
set autotrace traceonly
set linesize 1000
select t1.object_name,
t1.object_id,
(s