Oracle9i 的查询优化

 本文来自：http://bbs.51cto.com/thread-49356-1-1.html

本文描述了Oracle 的查询优化程序，它是数据库的关键组件，能让Oracle 的用户获得极佳的执行性能。Oracle 的查询优化技术在功能上无与伦比，本文详细讨论了查询优化的所有重要领域。

　　 简介

　　 什么是查询优化程序？

　　 查询优化对于关系数据库的性能，特别是对于执行复杂SQL 语句的性能而言至关重要。查询优化程序确定执行每一次查询的最佳策略。

　　 例如，查询优化程序选择对于指定的查询是否使用索引，以及在联接多个表时采用哪一种联接技术。这类决策对SQL 语句的执行性能有很大的影响，查询优化对于每一种应用程序都是关键技术，应用程序涉及的范围从操作系统到数据仓库，从分析系统到内容管理系统。查询优化程序对于应用程序和最终用户是完全透明的。

　　 由于应用程序可能生成非常复杂的SQL 语句, 查询优化程序必须精心构建、功能强大，以保障良好的执行性能。例如，查询优化程序可转换SQL 语句，使复杂的语句转换成为等价的但执行性能更好的SQL 语句。查询优化程序的典型特征是基于开销。在基于开销的优化策略中，对于给定查询生成多个执行计划，然后对每个计划估算开销。查询优化程序选用估算开销最低的计划。

　　 Oracle 在查询优化方面提供了什么？

　　 Oracle 的优化程序可称是业界最成功的优化程序。基于开销的优化程序自1992 年随Oracle7 推出后，通过10 年的丰富的实际用户经验，不断得到提高和改进。好的查询优化程序不是基于纯粹的理论假设及谓词在实验室中开发出来的，而是通过适合实际用户需求开发和磨合出来的。

　　 Oracle 的查询优化程序比任何其他查询优化程序在数据库应用程序的应用都要多，而且Oracle 的优化程序一直由于实际应用的反馈而得到改进。

　　 Oracle 的优化程序包含4 大主要部分（本文将在以下章节详细讨论这些部分）：

　　 SQL 语句转换：在查询优化中Oracle 使用一系列精深技术对SQL 语句进行转换。查询优化的这一步骤的目的是将原有的SQL 语句转换成为语义相同而处理效率更高的SQL 语句。

　　 执行计划选择：对于每个SQL 语句, 优化程序选择一个执行计划（可使用Oracle 的EXPLAIN PLAN 工具或通过Oracle 的“v$sql_plan” 视图查看）。执行计划描述了执行SQL 时的所有步骤，如访问表的顺序；如何将这些表联接在一起；以及是否通过索引来访问这些表。优化程序为每个SQL 语句设计许多可能的执行计划，并选出最好的一个。

　　 开销模型与统计：Oracle 的优化程序依赖于执行SQL 语句的所有单个操作的开销估算。想要优化程序能选出最好的执行计划，需要最好的开销估算方法。开销估算需要详细了解某些知识，这些知识包括：明白每个查询所需的I/O、CPU 和内存资源以及数据库对象相关的统计信息（表、索引和物化视图），还有有关硬件服务器平台的性能信息。收集这些统计和性能信息的过程应高效并且高度自动化。

　　 动态运行时间优化：并不是SQL 执行的每个方面都可以事先进行优化。Oracle 因此要根据当前数据库负载对查询处理策略进行动态调整。该动态优化的目标是获得优化的执行性能，即使每个查询可能不能够获得理想的CPU 或内存资源。Oracle 另有一个原来的优化程序，即基于规则的优化程序。该优化程序仅向后兼容，在Oracle 的下个版本将不再得到支持。绝大多数Oracle 用户目前使用基于开销的优化程序。所有主要的应用程序供应商（如Oracle 应用程序、SAP 和Peoplesoft，仅列出这几家）以及大量近来开发的客户应用程序都使用基于开销的优化程序来获得优良的执行性能，故本文仅讲述基于开销的优化程序。

　　 SQL 语句转换

　　 使用SQL 语句表示复杂查询可以有多种方式。提交到数据库的SQL 语句类型通常是最终用户或应用程序可以最简单的方式生成的SQL 类型。但是这些人工编写或机器生成的查询公式不一定是执行查询最高效的SQL 语句。例如，由应用程序生成的查询通常含有一些无关紧要的条件，这些条件可以去掉。或者，有些从某查询谓词出的附加条件应当添加到该SQL 语句中。SQL 转换语句的目的是将给定的SQL 语句转换成语义相同（即返回相同结果的SQL 语句）并且性能更好的SQL 语句。

　　 所有的这些转换对应用程序及最终用户完全透明。SQL 语句转换在查询优化过程中自动实现。

　　 Oracle 实现了多种SQL 语句转换。这些转换大概可分成两类：

　　 试探查询转换：在可能的情况下对进来的SQL 语句都会进行这种转换。这种转换能够提供相同或较好的查询性能，所以Oracle 知道实施这种转换不会降低执行性能。 基于开销的查询转换：Oracle 使用基于开销的方法进行几类查询转换。借助这种方法，转换后的查询会与原查询相比较，然后Oracle 的优化程序从中选出最佳执行策略。

　　 以下部分将讨论Oracle 转换技术的几个示例。这些示例并非是权威的，仅用于帮助读者理解该关键转换技术及其益处。

　　 试探查询转换

　　 简单视图合并

　　 可能最简单的查询转换是视图合并。对于包含视图的查询，通常可以通过把视图定义与查询“合并”来将视图从查询中去掉。例如，请看下面的非常简单的视图及查询。

　　 CREATE VIEW TEST_VIEW AS SELECT ENAME, DNAME, SAL FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;
　　 SELECT ENAME, DNAME FROM TEST_VIEW WHERE SAL > 10000;

　　 如果不加任何转换，处理该查询的唯一方法是将EMP 的所有行联接到DEPT 表的所有行，然后筛选有适当的SAL 的值的那些行。

　　 如果使用视图合并，上述查询可以转换为：

　　 SELECT ENAME, DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.SAL > 10000;

　　 处理该转换后的查询时，可以在联接EMP 和DEPT 表前使用谓词‘SAL>10000’。这一转换由于减少了联接的数据量而大大提高了查询的执行性能。即便在这样一个非常简单的示例里，查询转换的益处和重要性也显而易见。

　　 复杂视图合并

　　 许多视图合并操作都是直截了当的，如以上示例。但是，较复杂的视图，如包含GROUP BY 或DISTINCT 操作符的视图合并起来就不那么容易了。Oracle 为合并这类复杂视图提供了一些高级技术。

　　 请看以下带有GROUP BY 语句的视图。在该示例中，视图计算每个部门的平均工资。

　　 CREATE VIEW AVG_SAL_VIEW AS SELECT DEPTNO, AVG(SAL) AVG_SAL_DEPT FROM EMP GROUP BY DEPTNO

　　 查询的目的是要找出Oakland 每个部门的平均工资：

　　 SELECT DEPT.NAME, AVG_SAL_DEPT FROM DEPT, AVG_SAL_VIEW WHERE DEPT.DEPTNO = AVG_SAL_VIEW.DEPTNO AND DEPT.LOC = 'OAKLAND'

　　 可以转换为：

　　 SELECT DEPT.NAME, AVG(SAL) FROM DEPT, EMP WHERE DEPT.DEPTNO = EMP.DEPTNO AND DEPT.LOC = 'OAKLAND' GROUP BY DEPT.ROWID, DEPT.NAME

　　 该特殊转换的执行性能优点立即显现：该转换把EMP 数据在分组聚合前进行联接和筛选，而不是在联接前将EMP 表的所有数据分组聚合。

　　 子查询“展平”

　　 Oracle 有一些转换能将不同类型的子查询转变为联接、半联接或反联接。作为该领域内的技术示例，我们来看下面这个查询，找出有工资超过10000 的员工的那些部门：

　　 SELECT D.DNAME FROM DEPT D WHERE D.DEPTNO IN (SELECT E.DEPTNO FROM EMP E WHERE E.SAL > 10000)

　　 存在一系列可以优化本查询的执行计划。Oracle 会考虑这些可能的不同转换，基于开销选出最佳计划。

　　 如果不进行任何转换，这一查询的执行计划如下：

　　 OPERATION OBJECT_NAME OPTIONS
　　 SELECT STATEMENT
　　 FILTER
　　 TABLE ACCESS DEPT FULL
　　 TABLE ACCESS EMP FULL

　　 按照该执行计划，将扫描DEPT 表的每一行查找所有满足子查询条件的EMP 记录。通常，这不是一种高效的执行策略。然而，查询转换可以实现效率更高的计划。

　　 该查询的可能计划之一是将查询作为“半联接”来执行。“半联接”是一种特殊类型的联接，它消除了联接中来自内表的冗余值（这实际上就是该子查询的原本的语义）。在该示例中，优化程序选择了一个散列半联接，尽管Oracle 也支持排序-合并以及嵌套-循环半联接：

　　 OPERATION OBJECT_NAME OPTIONS
　　 SELECT STATEMENT
　　 HASH JOIN SEMI
　　 TABLE ACCESS DEPT FULL
　　 TABLE ACCESS EMP FULL

　　 由于SQL 没有用于半联接的直接语法，此转换过的查询不能使用标准的SQL 来表示。但是，转换后的伪SQL 将是：

　　 SELECT DNAME FROM EMP E, DEPT D WHERE D.DEPTNO E.DEPTNO AND E.SAL > 10000;

　　 另一个可能的计划是优化程序可以决定将DEPT 表作为联接的内表。在这种情况下，查询作为通常的联接来执行，但对EMP 表进行特别排序，以消除冗余的部门号：

　　 OPERATION OBJECT_NAME OPTIONS
　　 SELECT STATEMENT
　　 HASH JOIN
　　 SORT UNIQUE
　　 TABLE ACCESS EMP FULL
　　 TABLE ACCESS DEPT FULL

　　 转换后的SQL 语句为：

　　 SELECT D.DNAME FROM (SELECT DISTINCT DEPTNO FROM EMP) E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.SAL > 10000;

　　 与视图合并一样，子查询展平也是获得良好查询执行性能的基本优化办法。

　　 传递谓词生成

　　 在某些查询中，由于表间的联接关系，一个表中的谓词可以转化为另一个表中的谓词。Oracle 会以这种方式演绎出新的谓词，这类谓词被称为传递谓词。例如，来看一个查询，找出定货当天运出的所有商品：

　　 SELECT COUNT(DISTINCT O_ORDERKEY) FROM ORDER, LINEITEM WHERE O_ORDERKEY = L_ORDERKEY AND O_ORDERDATE = L_SHIPDATE AND O_ORDERDATE BETWEEN '1-JAN-2002' AND '31-JAN-2002'

　　 利用传递性，该ORDER 表中的谓词也可以用于LINEITEM 表：

　　 SELECT COUNT(DISTINCT O_ORDERKEY) FROM ORDER, LINEITEM WHERE