转：Oracle9i 的查询优化

Oracle9i 的查询优化

执行概要

本文描述了 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 <SEMIJOIN> 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 O_ORDERKEY = L_ORDERKEY AND O_ORDERDATE = L_SHIPDATE AND O_ORDERDATE BETWEEN '1-JAN-2002'  AND '31-JAN-2002' AND L_SHIPDATE BETWEEN '1-JAN-2002'  AND '31-JAN-2002'

新谓词的存在可以减少要联接的数据量，并能够利用另外的索引。

消除通用子表达

如果同样的子表达或计算在一个查询中出现多次，Oracle 对每一行只评估一次该表达式。

请看以下查询，找出 Dallas 身为副总裁或工资超过 100000 的所有员工。

SELECT * FROM EMP, DEPT WHERE (EMP.DEPTNO = DEPT.DEPTNO AND LOC = 'DALLAS'  AND SAL > 100000) OR (EMP.DEPTNO = DEPT.DEPTNO AND LOC = 'DALLAS'  AND JOB_TITLE ='VICE PRESIDENT'}

优化程序明白如果转换成如下语句时，该查询将更为高效：

SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND LOC = ‘DALLAS’ AND (SAL > 100000 OR JOB_TITLE = 'VICE PRESIDENT');

使用此转换后的查询，联接谓词和 LOC 的谓词对 DEPT 的每行仅评估一次，而不是两次。

谓词下推和上移

复杂查询往往包含多个视图与子查询，在这些视图和子查询中包含多个谓词。Oracle 可将谓词移入或移出视图，以产生新的高效查询。

可使用单一表视图来说明谓词下推：

CREATE VIEW EMP_AGG AS

SELECT

DEPTNO,

AVG(SAL) AVG_SAL,

FROM EMP

GROUP BY DEPTNO;

假设进行下列查询：

SELECT DEPTNO, AVG_SAL FROM EMP_AGG  WHERE DEPTNO = 10;

Oracle 会将谓词 DEPTNO=10 推进该视图，将查询转换为下列 SQL：

SELECT DEPTNO, AVG(SAL)5 FROM EMP  WHERE DEPTNO = 10 GROUP BY DEPTNO;

此已转换的查询的好处是谓词 DEPTNO=10 在 GROUP-BY 操作前进行，从而大大减少了要处理的数据量。

Oracle 还有许多其他优良技术来将 WHERE 子句的条件下推到外来的查询块中，将条件从查询块中提出，并且将条件在联接的查询块间平移。一旦有 WHERE 子句条件出现，就有机会筛选出一些行并减少早期阶段要处理的数据量。这样，后续的操作，比如说联接或 GROUP-BY，就可以只处理小得多的数据集，从而提高了执行效率。还有，谓词下推和上移通过产生新的访问路径以提高执行性能，这在没有增加新的谓词前不可能做到。

用于 “CUBE” 查询的分组修整

SQL CUBE 表达式是 SQL group-by 运算符的扩展，使得可以用单个 SQL 语句就可以检索多个集合。对于包含带有 CUBE 表达式的视图的查询，有时可以减少评估该查询所需要的数据量。例如，请看以下查询：

SELECT MONTH, REGION, DEPARTMENT FROM

(SELECT MONTH, REGION, DEPARTMENT, SUM(SALES_AMOUNT) AS REVENUE FROM SALES GROUP BY CUBE (MONTH, REGION, DEPT))

WHERE MONTH = ‘JAN-2001’;

该查询可以转换为下列 SQL：

SELECT MONTH, REGION, DEPARTMENT FROM

(SELECT MONTH, REGION, DEPARTMENT, SUM(SALES_AMOUNT) AS REVENUE FROM SALES WHERE MONTH = ‘JAN-2001’ GROUP BY MONTH, CUBE(REGION, DEPT))

WHERE MONTH = ‘JAN-2001’;

该转换后的 SQL 处理的数据集合大大减少，因为要计算的数据大大缩减了（只需计算 2001 年一月的数据），并且需合计的数量也减少了。对开发分析类应用程序的 SQL 程序员而言这是重要的一种转换，因为这些工具可能要对逻辑上的立方体进行查询，这些立方体在包含有 CUBE 运算符的视图中定义。

外联接到内联接的转换

在某些情况下，能够确定查询中的一个外联接能产生与内联接相同的结果。在这类情况下，优化程序会将外联接转变为内联接。这种转换让 Oracle 能够进一步合并视图或选用新的联接顺序，查询是一个外联接时就做不到这一点。

基于开销的查询转换

实体化视图重写

以实体化视图的形式预先处理和存储常用数据能够大大加速查询处理。Oracle 能对 SQL 查询进行转换，使查询中对一个或多个表的引用被对一个实体化视图的引用所取代。如果该实体化视图小于原来要引用的表，或比原来要引用的表有更好的访问路径，则该转换后的 SQL 语句比原查询的执行速度会快得多。

例如，请看以下实体化视图：

CREATE MATERIALIZED VIEW SALES_SUMMARY AS SELECT SALES.CUST_ID, TIME.MONTH, SUM(SALES_AMOUNT) AMT FROM SALES, TIME WHERE SALES.TIME_ID = TIME.TIME_ID GROUP BY SALES.CUST_ID, TIME.MONTH;

该实体化视图能够用于优化以下查询：

SELECT CUSTOMER.CUST_NAME, TIME.MONTH, SUM(SALES.SALES_AMOUNT) FROM SALES, CUSTOMER, TIME WHERE SALES.CUST_ID = CUST.CUST_ID AND SALES.TIME_ID = TIME.TIME_ID GROUP BY CUSTOMER.CUST_NAME, TIME.MONTH;

重写后的查询为：

SELECT CUSTOMER.CUST_NAME, SALES_SUMMARY.MONTH, SALES_SUMMARY.AMT FROM CUSTOMER, SALES_SUMMARY WHERE CUSTOMER.CUST_ID = SALES_SUMMARY.CUST_ID;

在该示例中，转换后的查询速度可能快得多的原因是：销售汇总表可能比销售表小得多，同时转换后的查询少一次联接，而且无须计总。

Oracle 具有一系列强有力的实体化视图重写技术，允许每个实体化视图能被用在尽可能多的查询类型中。

Oracle 实体化视图的另一个显著特点是与 Oracle 数据库中的声明式维集成在一起。Oracle 可以允许生成维的元数据对象，描述维内的层次关系。该层次化元数据用来支持更为复杂的实体化视图查询重写。例如，只要存在描述月与季度之间的层次关系的时间维，一个包含月销售数据的实体化视图就能支持对季度销售数据的查询。

注意，使用实体化视图的转换后的查询并不总是比原查询效率高。因为，就算实体化视图比其基于的表小，但这些基础表可以有更好的索引，从而能够被更快地访问。选择优化执行计划的唯一途径是对使用和未使用实体化视图的执行计划进行计算并比较其开销。Oracle 正是这样做的，所以实体化视图重写是基于开销的查询转换范例。（有关实体化视图的更多信息，参见白皮书“Oracle9i 实体化视图”）。

OR 扩展

该技术把在 WHERE 子句中带有 OR 的查询转换成一个包含多个不带 OR 的查询的 UNION ALL。当 OR 表示的是对不同表的限制条件时，这是大有好处的。请看以下查询，找出所有进出 Oakland 的运货：

SELECT * FROM SHIPMENT, PORT P1, PORT P2 WHERE SHIPMENT.SOURCE_PORT_ID = P1.PORT_ID AND SHIPMENT.DESTINATION_PORT_ID = P2.PORT_ID AND (P1.PORT_NAME = 'OAKLAND'  OR P2.PORT_NAME = 'OAKLAND')

该查询可以转换为：

SELECT * FROM SHIPMENT, PORT P1, PORT P2 WHERE SHIPMENT.SOURCE_PORT_ID = P1.PORT_ID AND SHIPMENT.DESTINATION_PORT_ID = P2.PORT_ID AND P1.PORT_NAME = 'OAKLAND' UNION ALL SELECT * FROM SHIPMENT, PORT P1, PORT P2 WHERE SHIPMENT.SOURCE_PORT_ID = P1.PORT_ID AND SHIPMENT.DESTINATION_PORT_ID = P2.PORT_ID AND P2.PORT_NAME = 'OAKLAND'  AND P1.PORT_NAME <> 'OAKLAND'

注意，每个 UNION ALL 分支可以有不同的优化的联接顺序。在第一个分支里，Oracle 可以利用对 P1 的限制，从该表开始联接。而在第二个分支里，Oracle 可以从 P2 开始。产生的计划可能比原来的查询快好几个数量级，这取决于表索引和其数据。这种查询转换必须是基于开销的，因为它并不保证每个查询的性能都会得到提高。

星型转换

星型方式是普遍用于数据中心或数据仓库的建模策略。星型模式一般包含一个或多个非常大的表（叫做事实表），用来存储交易数据，另外还包含大量较小的查找表（叫做维表），以来存放描述性数据。

Oracle 支持一种用于评估星型模式查询的技术（叫做“星型转换”）。该技术通过进行转换（向原有的 SQL 中添加新的子查询）来提高星型查询的效率。

这些新的子查询允许利用位图索引更有效地访问事实表。

通过查看示例可以更好地理解这种星型转换。请看以下查询，它返回各州 2001 年三季度的饮料销售总量。事实表为销售。注意，此处的时间维是个“雪片”维，因为它包含 DAY 和 QUARTER 两个表。

SELECT STORE.STATE, SUM(SALES.AMOUNT) FROM SALES, DAY, QUARTER, PRODUCT, STORE WHERE SALES.DAY_ID = DAY.DAY_ID  AND DAY.QUARTER_ID =QUARTER.QUARTER_ID AND SALES.PRODUCT_ID = PRODUCT.PRODUCT_ID AND SALES.STORE_ID = STORE.STORE_ID AND PRODUCT.PRODUCT_CATEGORY = 'BEVERAGES' AND QUARTER.QUARTER_NAME = '2001Q3' GROUP BY STORE.STATE

转换后的查询为：

SELECT STORE.STATE, SUM(SALES.AMOUNT)  FROM SALES, STORE WHERE SALES.STORE_ID = STORE.STORE_ID AND SALES.DAY_ID IN (SELECT DAY.DAY_ID FROM DAY, QUARTER WHERE DAY.QUARTER_ID = QUARTER.QUARTER_ID AND QUARTER.QUARTER_NAME = '2001Q3') AND SALES.PRODUCT_ID IN (SELECT PRODUCT.PRODUCT_ID FROM PRODUCT WHERE PRODUCT.PRODUCT_CATEGORY = 'BEVERAGES') GROUP BY STORE.STATE

使用转换后的 SQL，该查询可以分为两个阶段有效处理。在第一阶段，借助位图索引从事实表中找出所有需要的行。在这种情况下，因为 DAY-ID 和 PRODUCT-ID 就是出现在子查询谓词中的那两列，所以按照它们产生的位图索引来访问事实表。

在查询的第二阶段（“联接回”阶段），维表被联接回到第一阶段的数据集。在该查询中，出现在选择列表中的唯一维表列是 store.state，因此 store 表是唯一一个需要联接的表。出现在查询第一阶段的包含 PRODUCT、DAY 和 QUARTER 的子查询消除了在第二阶段对这些表的联接的必要性，查询优化程序智能地去掉了这些联接。

星型转换是基于开销的查询转换，根据优化程序的开销估算决定是使用某个维的子查询开销较低还是查询重写比原有语句更好。

星型查询执行技术是 Oracle 独家拥有的专利技术。其他厂商也有类似的对星型查询的查询转换能力，但是没有一家厂商能将该技术与静态位图索引以及智能联接回功能结合在一起。

外联接视图的谓词下推

通常，当查询包含一个联接到其他表的视图时，该视图可以被合并，以实现更好的查询优化。但是，如果一个视图是用外联接方式联接的，则该视图就不能被合并。在这种情况下，Oracle 有一个特别的谓词下推操作，使联接谓词可以下推到该视图中，该转换使通过该视图中的某个表的索引执行外联接成为可能。这种转换是基于开销的，因为索引访问可能并不是最有效的。

选择访问路径

选择访问路径的目标是决定查询中联接表的次序、使用何种联接方法以及如何访问每个表中的数据。对于给定的查询，可以使用 Oracle 的 EXPLAIN PLAN 工具或 Oracle 的 v$sql_plan 视窗查看所有这些信息。

Oracle 的选择访问路径算法特别高深，因为 Oracle 提供了特别 丰富的一套数据库结构和查询评估技术。Oracle 的选择访问路径和开销模型形成了对每一特性的全面理解，以能最佳地使用每个特性。

Oracle 的数据库结构包括：

表结构 表（缺省） 索引编排表 嵌套表 簇 散列簇

索引结构 B 树索引 位图索引 位图联接索引 反向关键字 B 树索引 基于功能的 B 树索引 基于功能的位图索引 域索引

分区技术 范围分区 散列分区 组合范围散列分区 列表分区 组合范围列表分区

Oracle 的访问技术包括：

索引访问技术 索引唯一关键字查找 索引最大/最小查找