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 或>=进行过滤。
分页查询优化要点:
分页查询的排序是高代价操作,如果不能避免排序,则需要所有结果集查询完毕后进行排序操作后,才能进行分页选择。如果能够避免排序,则可以充分使用到ORACLE分页查询的COUNT STOPKEY算法,比如找前100行,则只要找到100行整条语