基于ORACLE的SQL优化案例_PART1

  CBO( Cost Based Optimizer)优化器是目前ORACLE默认使用的优化器，它使用统计信息、查询转换等计算各种可能的访问路径成本，并生成多种备选执行计划，最终ORACLE选择成本最低的作为最终执行计划。与旧的RBO(Rule Based Optimizer)相比，更加灵活，可根据实际情况选择最佳执行路径。

  但是，由于其自身非常复杂，CBO的限制以及存在的BUG非常多，这时，作为SQL开发和优化人员，应该根据CBO特性，编写高效语句，以避免踩坑CBO优化器。

本文以两类常见的SQL优化问题来探讨基于ORACLE的高效SQL编写和优化。

1. ORACLE分页查询优化之道

ORACLE中主要使用ROWNUM来实现TOP-N分页查询，分页SQL编写有如下规则：

1）分页查询一般需要排序，内层查询需要先ORDER BY。

2）如果查询TOP–N行，需要两层嵌套：内层先排序，在外层嵌套查询ROWNUM，并且同一层按照WHERE ROWNUM <或<=进行过滤。

3）如果查询第M行到第N行，需要三层嵌套：内层先排序，之后外层嵌套查询ROWNUM并且将ROWNUM取别名，比如取别名为RN，且同一层按照WHERE ROWNUM <或<=进行过滤，之后最外层RN按照WHERE rn >或>=进行过滤。

分页查询优化要点：
  分页查询的排序是高代价操作，如果不能避免排序，则需要所有结果集查询完毕后进行排序操作后，才能进行分页选择。如果能够避免排序，则可以充分使用到ORACLE分页查询的COUNT STOPKEY算法，比如找前100行，则只要找到100行整条语句则可结束计算，这样就可以提升分页查询效率了。很显然，高效分页查询必须做到：

1）避免排序：通过创建索引

2）执行计划使用COUNT STOPKEY算法，进行分页裁剪。

1.1 错误的分页SQL写法

  分页SQL编写必须遵守查询前面说的3个规则，如下例是错误的分页语句写法：

  这条语句查询前20行，应该使用两层嵌套规则：最内层排序，外层查询rownum，并且在同一层用where rownum<或<=进行过滤。仔细分析这条语句，发现是两层嵌套，但是不符合“并且在同一层用where rownum<或<=进行过滤”这个条件，此语句查询rownum之后取了别名rn,在最外层进行rn过滤，可以从执行计划看到，走了全表扫描：

 

  这里的表TM_TESTX_TEMP的列DONE_DATE有索引，但是因为使用了错误的分页SQL写法，导致执行计划无法使用COUNT STOPKEY进行裁剪（执行计划中未出现COUNT STOPKEY），这样ORACLE需要按照条件查询所有的结果集，从而走索引COST更大，最终走了全表扫描。

1.2 正确的分页SQL写法

  如果按照规则进行SQL编写，则可以完美进行高效分页，<=分页只需要2层嵌套，done_date列有索引，根据条件done_date>to_date(‘20150916’,‘YYYYMMDD’)，只获取前20行，可高效利用索引和COUNT STOPKEY算法，改写完成后使用索引降序扫描，执行时间从1.72s到0.01s,逻辑IO 从42648到59，效率提升百倍。如下所示：

  语句改写为外层取rownum的同时按照WHERE ROWNUM <= 20进行过滤，而不是原来的在最外层进行过滤，符合分页SQL编写规则，执行计划变为：

  修改完后，可以看到根据ORDER BY DONE_DATE DESC，执行计划走了索引降序扫描，这样避免了排序，并且使用到了COUNT STOPKEY算法，找到前20行，则SQL运算结束，从而提高效率。

1.3 表关联SQL分页优化

  以上只是单表分页查询的高效SQL编写和优化思路，如果是多表关联SQL分页，也需要遵循分页SQL编写规则，优化方式同样是利用索引消除排序，并且能够使用COUNT STOPKEY算法，很显然，要做到这些，必须以ORDER BY列所在表为驱动表，JOIN方式为NESTED LOOPS，这样可全部走索引并且可使用STOPKEY算法进行结果集裁剪，提高效率。如下例：

  这条语句是test1和test2进行半连接，并按照test1的object_id列进行降序排列，最终返回test1的前10行数据。从子查询关联条件上看，按照object_id和object_name列关联，没有额外的过滤条件，从语句结构上看执行计划应该中表test1应该是全表扫描，如下所示：

 

  很显然，这不是最佳执行计划：没有消除排序，两表都是全表扫描，所有结果集返回后，才进行STOPKEY（SORT ORDER BY STOPKEY）。前面已经说过，对于表关联的分页查询，应该用排序键所在的表为驱动表，JOIN方式为NESTED LOOPS，并且消除排序，根据这个思想，应该在两表的object_id列分别建立索引即可：

create index idx_test1 on test1(object_id);

create index idx_test2 on test2(object_id);

索引创建完毕后的执行计划如下：

  现在的执行计划完全符合多表关联分页查询优化思路，以test1表为驱动表，消除排序，test1和test2之间走NESTED LOOPS，可以从执行计划上看出，ID=4的步骤虽然E-ROWS估算为69444行，但是实际只找到10行，也就结束了，最终逻辑读从原先的2184降低到15，大幅度提升了效率。

  因为分页查询要利用到STOPKEY算法，就算除关联条件外没有额外的过滤条件，也可以通过索引来提升效率。

  未完待续，见PART2：http://blog.chinaunix.net/uid-7655508-id-5835014.html