Oracle查询优化

Oracle9i 的查询优化 Oracle 白皮书 2002 年 2 月 Oracle9i 的查询优化 执行概要 ..............................................................................................4 简介......................................................................................................4 什么是查询优化程序？ ..............................................................4 Oracle 在查询优化方面提供了什么? ......................................4 SQL 语句转换 ....................................................................................5 试探查询转换 ..............................................................................6 简单视图合并......................................................................6 复杂视图合并......................................................................6 子查询“展平”.......................................................................7 传递谓词生成......................................................................8 消除通用子表达..................................................................9 谓词下推和上移..................................................................9 用于 “CUBE” 查询的分组修整 ......................................10 外联接到内联接的转换 .................................................... 11 基于开销的查询转换 ................................................................ 11 实体化视图重写................................................................ 11 OR 扩展 ............................................................................12 星型转换............................................................................12 外联接视图的谓词下推 ....................................................14 选择访问路径 ....................................................................................14 联接排序 ....................................................................................15 适应式查找策略................................................................15 多重初始排序试探 ............................................................16 位图索引 ....................................................................................16 位图联接索引 ............................................................................18 域索引及扩展性 ........................................................................18 快速全索引扫描 ........................................................................18 索引联接 ....................................................................................19 索引跳扫 ....................................................................................19 分区优化 ....................................................................................19 智能化分区联接，分组聚合及排序 ........................................20 消除排序 ....................................................................................20 OLAP 优化 ...............................................................................20 并行执行 ....................................................................................21 提示............................................................................................21 Oracle9i 的查询优化 第 2 页 开销模型和统计 ................................................................................22 优化程序统计 ............................................................................22 对象级统计........................................................................22 系统统计............................................................................23 用户定义的统计................................................................23 统计管理 ....................................................................................23 并行取样............................................................................23 监视....................................................................................24 自动确定直方图................................................................24 动态取样 ....................................................................................24 优化成本类型 ............................................................................25 动态运行时间优化 ............................................................................25 动态并行程度 ............................................................................26 动态内存分配 ............................................................................26 数据库资源管理程序 ................................................................27 结论....................................................................................................28 Oracle9i 的查询优化 第 3 页 Oracle9i 的查询优化 执行概要 本文描述了 Oracle 的查询优化程序，它是数据库的关键组件，能 让 Oracle 的用户获得极佳的执行性能。Oracle 的查询优化技术在 功能上无与伦比，本文详细讨论了查询优化的所有重要领域。 简介 什么是查询优化程序？ 查询优化对于关系数据库的性能，特别是对于执行复杂 SQL 语句 的性能而言至关重要。查询优化程序确定执行每一次查询的最佳 策略。 例如，查询优化程序选择对于指定的查询是否使用索引，以及在 联接多个表时采用哪一种联接技术。这类决策对 SQL 语句的执行 性能有很大的影响，查询优化对于每一种应用程序都是关键技术， 应用程序涉及的范围从操作系统到数据仓库，从分析系统到内容 管理系统。查询优化程序对于应用程序和最终用户是完全透明的。 由于应用程序可能生成非常复杂的 SQL 语句, 查询优化程序必 须精心构建、功能强大，以保障良好的执行性能。例如，查询优 化程序可转换 SQL 语句，使复杂的语句转换成为等价的但执行性 能更好的 SQL 语句。查询优化程序的典型特征是基于开销。在基 于开销的优化策略中，对于给定查询生成多个执行，然后对 每个计划估算开销。查询优化程序选用估算开销最低的计划。 Oracle 在查询优化方面提供了什么? Oracle 的优化程序可称是业界最成功的优化程序。基于开销的优 化程序自 1992 年随 Oracle7 推出后，通过 10 年的丰富的实际用 户经验，不断得到提高和改进。好的查询优化程序不是基于纯粹 的理论假设及谓词在实验室中开发出来的，而是通过适合实际用 户需求开发和磨合出来的。 Oracle 的查询优化程序比任何其他查询优化程序在数据库应用程 序的应用都要多，而且 Oracle 的优化程序一直由于实际应用的反 馈而得到改进。 Oracle9i 的查询优化 第 4 页 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 语句。 Oracle9i 的查询优化 第 5 页 所有的这些转换对应用程序及最终用户完全透明。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 为合并这类复杂视图提供了一些高级 技术。 Oracle9i 的查询优化 第 6 页 请看以下带有 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 记录。通常，这不是一种高效的执行策略。然而， 查询转换可以实现效率更高的计划。 Oracle9i 的查询优化 第 7 页 该查询的可能计划之一是将查询作为“半联接”来执行。 “半联 接”是一种特殊类型的联接，它消除了联接中来自内表的冗余值 （这实际上就是该子查询的原本的语义）。在该示例中，优化程 序选择了一个散列半联接，尽管 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' Oracle9i 的查询优化 第 8 页 利用传递性，该 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; Oracle9i 的查询优化 第 9 页 假设进行下列查询： 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 运算符的视图中定义。 Oracle9i 的查询优化 第 10 页 外联接到内联接的转换 在某些情况下，能够确定查询中的一个外联接能产生与内联接相 同的结果。在这类情况下，优化程序会将外联接转变为内联接。 这种转换让 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 可以允许生成维的元数据对象，描述 维内的层次关系。该层次化元数据用来支持更为复杂的实体化视 图查询重写。例如，只要存在描述月与季度之间的层次关系的时 间维，一个包含月销售数据的实体化视图就能支持对季度销售数 据的查询。 Oracle9i 的查询优化 第 11 页 注意，使用实体化视图的转换后的查询并不总是比原查询效率高。 因为，就算实体化视图比其基于的表小，但这些基础表可以有更 好的索引，从而能够被更快地访问。选择优化执行计划的唯一途 径是对使用和未使用实体化视图的执行计划进行计算并比较其开 销。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 中添加新的子查询） 来提高星型查询的效率。 Oracle9i 的查询优化 第 12 页 这些新的子查询允许利用位图索引更有效地访问事实表。 通过查看示例可以更好地理解这种星型转换。请看以下查询，它 返回各州 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 独家拥有的专利技术。其他厂商也有 类似的对星型查询的查询转换能力，但是没有一家厂商能将该技 术与静态位图索引以及智能联接回功能结合在一起。 Oracle9i 的查询优化 第 13 页 外联接视图的谓词下推 通常，当查询包含一个联接到其他表的视图时，该视图可以被合 并，以实现更好的查询优化。但是，如果一个视图是用外联接方 式联接的，则该视图就不能被合并。在这种情况下，Oracle 有一 个特别的谓词下推操作，使联接谓词可以下推到该视图中，该转 换使通过该视图中的某个表的索引执行外联接成为可能。这种转 换是基于开销的，因为索引访问可能并不是最有效的。 选择访问路径 选择访问路径的目标是决定查询中联接表的次序、使用何种联接 方法以及如何访问每个表中的数据。对于给定的查询，可以使用 Oracle 的 EXPLAIN PLAN 工具或 Oracle 的 v$sql_plan 视窗查 看所有这些信息。 Oracle 的选择访问路径算法特别高深，因为 Oracle 提供了特别 丰富的一套数据库结构和查询评估技术。Oracle 的选择访问路径 和开销模型形成了对每一特性的全面理解，以能最佳地使用每个 特性。 Oracle 的数据库结构包括： 表结构 表（缺省） 索引编排表 嵌套表 簇 散列簇 索引结构 B 树索引 位图索引 位图联接索引 反向关键字 B 树索引 基于功能的 B 树索引 基于功能的位图索引 域索引 分区技术 范围分区 散列分区 组合范围散列分区 列表分区 组合范围列表分区 Oracle9i 的查询优化 第 14 页 Oracle 的访问技术包括： 索引访问技术 索引唯一关键字查找 索引最大/最小查找 索引范围扫描 索引降序范围扫描 索引全扫描 索引快速全扫描 索引跳扫 索引 and-equal 处理 索引联接 索引 B 树位图变换 位图索引 AND/OR 处理 位图索引范围处理 位图索引 MINUS (NOT) 处理 位图索引 COUNT 处理 联接方法 嵌套循环内联接、外联接、半联接以及反联接 排序合并内联接、外联接、半联接以及反联接 散列内联接、外联接、半联接以及反联接 智能化分区联接 本文不会就所有这些处理技术展开讨论，下面将讨论 Oracle 访问 路径选择的一些关键属性。 联接排序 如果联接大量的表，所有可能的执行计划将占用很大的空间，优 化程序要需要花费很长的时间来探究这些空间消耗。例如，对 5 个表的查询有 120 种可能的联接次序，基于各种索引、访问方法 和联接技术的组合，每种联接次序又可能有几十种可能的执行计 划。因此对 5 个表的查询的执行计划共有几千种，优化程序有可 能选出最可能的执行计划。但是，对于有 10 个表的联接，存在 逾 3 百万种的联接次序，通常就会有 1 亿多种可能的执行计划。 因此，在寻找可能的执行计划时优化程序需要智取而非力夺。 适应式查找策略 Oracle 优化程序使用许多技术来智能地减少查找空间。值得关注 的一种技术是 Oracle 采用了适应式查找策略。如果查询可在一秒 钟内完成，用十秒种来进行查询优化就太浪费了。相反，如果一 个查询多半要运行几分钟或是几小时，则为了找到一种更好的计 划而在优化阶段花上几秒钟甚至几分钟也是值得的。Oracle 使用 适应式优化算法确保在对复杂查询需要投入额外的优化时间时， 优化时间仅是该查询预期执行时间的很少一部分（百分之几）。 Oracle9i 的查询优化 第 15 页 有些数据库系统允许 DBA 定义一个“优化等级”，以控制用于 查询优化的时间量。但是，相较于控制优化的系统级参数而言， 适应式优化策略是更有效的一种技术。使用系统级参数时，DBA 不加区分地决定所有查询的优化等级，而 Oracle 的适应式优化策 略为每个查询分别决定优化等级。 多重初始排序试探 Oracle 优化程序的另一个查找算法是创新性的“多重初始排序试 探”。如果优化程序在查找过程的早期就找到最优的计划，优化 程序会提前结束工作。这样，该试探借助高深的方法能即刻在查 找空间中找到一些近于最优的、或至少是非常好的执行计划。优 化程序从这些计划开始，而不是从随机生成的计划开始。该试探 对于高效执行查询优化十分关键，因为它可大大减少查询优化所 需要的时间。 位图索引 Oracle 创新性的位图索引（已获得专利）获得了广泛的应用，特 别是在数据仓库应用方面。其他厂商提供的是“动态”位图索引， 而 Oracle 支持真正的位图索引（除动态位图索引外）。真正的位 图索引是一种索引结构，在数据库中按该结构存储压缩的用位图 表示的索引，而动态位图索引是在查询处理过程中将数据库的 B 树索引转换成位图结构。真正的位图索引的好处显而易见，因为 较之常规的 B 树结构，它们可以节约大量空间。对空间的节约又 可以转化为性能上的好处，表现为磁盘 I/O 减少。真正的位图索 引在处理许多查询时，速度能提高 10 倍，而使用的索引存储空 间却为原来的十分之一。有关量化位图索引的好处的更多信息， 请参见性能白皮书“Oracle9i 的关键数据仓库特性：比较分析”。 位图索引在评估结合 AND 和 OR 运算的多重谓词时特别有效。 此外，位图索引使用 Oracle 的标准的一致性模型，以便在带有位 图索引的表上与查询同步执行 DML 操作（插入、修改、删除） 时，能完全保持数据的一致性。 Oracle 最丰富的位图索引功能为查询优化程序提供了许多新的执 行策略。Oracle 的查询优化程序能够生成执行计划，这些计划包 含位图运算复杂的树，这些位操作组合了对应于 WHERE 子句中 的 AND、OR 和 NOT 条件的多种索引，不论是真正的位图索引， 还是动态位图索引。这些位图的布尔运算非常快速，所以，只要 查询能利用位图运算的巨大好处，通常情况下都能很好地执行。 Oracle9i 的查询优化 第 16 页 此外，Oracle 支持对位图索引查询的“仅用索引”的访问方式。 该“仅用索引”访问采用“AND（与）”位图的精确算法。这种 精确性与那些采用动态位图索引并依赖对交叉行标识列表进行散 列操作的数据库系统形成了鲜明的对比（参见 http://as400bks.rochester.ibm.com/cgi-bin/bookmgr/BOOKS/EZ30X B00/2.4.1）。即便使用动态位图索引，为保证正确的查询结果， 这种系统也避免不了对表的访问。与之相反，Oracle 能在不访问 表的情况下评估各种各样的查询。例如，假设有一家银行想要知 道其在加利福尼亚州有多少已婚客户。 SELECT COUNT(*) FROM CUSTOMER WHERE STATE = 'CA' AND MARITAL_STATUS = 'MARRIED' 假设有关于州和婚姻状态的位图索引，Oracle 就能简单地执行一 个关于该位图的对‘CA’和 ‘MARRIED’的位级的‘与’运算， 计算出结果位图中 1 的数目，这是一个非常快速高效的操作，因 为整个查询的执行仅需访问两个高度压缩的索引结构。如果数据 库系统不能仅通过索引得出该查询的结果，它就可能不得不去访 问表中的上百万行，从而导至执行速度会慢许多。 Oracle 真正的位图索引的精确性（较之动态位图索引而言）对于 Oracle 的星型转换和位图联接索引的联接回减少特性而言，也是 至关重要的。没有精确位图操作功能的数据库系统永远需要在联 接回阶段联接所有的维表，而 Oracle 仅需要联接最少量的维表。 与其他类型的压缩索引结构相比，Oracle 的位图索引具有重要的 优势。例如，在使用 Oracle 的位图索引访问一定范围的值时，查 询优化程序将建立起始关键字和停止关键字，以保证只访问部分 索引结构。这与有些索引形成了鲜明的对比，例如“编码向量索 引”，在该方式下要想执行一个查询，会从头至尾扫描每个索引 一 一种极浪费的做法。 Oracle 的查询优化程序能够将多种类型的索引组合起来访问同一 个表。例如，一个位图索引可以与域索引和一个 B 树索引组合， 访问一个给定的表。通过动态位图索引技术访问该域索引和 B 树 索引用，以使它们能与位图索引组合在一起。 Oracle9i 的查询优化 第 17 页 位图联接索引 “联接索引”是一种索引结构，能够跨越多个表，改善对这些表 联接的执行性能。 联接索引是一种索引，其中索引列以及行标识/位图的表示涉及多 个不同的表。因此，位图联接索引的一部分定义还将包括联接条 件，指定如何匹配表中的行。通常，会给事实表产生一个联接索 引，索引的列属于一个维表。例如，给定一个事实表，即销售表， 一个维表，即产品表，于是就可以对销售表生成一个位图联接索 引，但索引列是产品表中的产品类别，这是由于一个联接条件是 产品标识。使用这样的索引，就可以直接从索引中找到每个产品 目录里的销售行，而不需要进行联接。位图联接索引有两个主要 优点： 1. 减少基数：多数情况下产品类别数会比产品标识少。所以，位 图联接索引的基数会比任何基于事实表中的联接列的索引的基数 少得多，对位图索引就变得更小。 2. 消除联接回：在许多情况下，采用位图联接索引可以消除查询 中的联接。 可以通过组合多种索引的位图技术，在同一访问路径中一起使用 位图联接索引与非联接索引。通常，由于星型转换与位图联接索 引联合工作，因此会采用其他索引。 域索引及扩展性 Oracle 支持域索引，以高效地访问自定义的复杂数据类型（如文 档、空间数据、图像和视频片段）。这类索引与内置的 Oracle 索 引有所不同，但其属性可以在 Oracle 中登记。Oracle 优化程序是 可扩展的，所以域索引和用户定义的功能可以有相应的统计数据 和开销功能。它们和 Oracle 内置的索引功能有着同样的开销模型 和查找空间，甚至能够通过 Oracle 位图技术，在同一访问路径中 与常规的 Oracle 索引组合在一起。 快速全索引扫描 索引的快速全扫描是指能够将索引当作表来扫描，而不是像在索 引中那样以树型顺序扫描。当索引中包含所有需要的表列时，就 可以这样做，而不需要对表进行访问。在需要对大量数据进行存 取时快速全扫描是很有用的，因为它们能够最大程度地利用多块 磁盘 I/O 的优点，同时在并行化方面比范围扫描更好。因为索引 通常比表小得多，扫描索引一般比扫描表本身要划算。 Oracle9i 的查询优化 第 18 页 索引联接 在没有包含全部所需列的单一索引的情况下，索引联接能够用多 个索引重新建立表列的一个子集。如果一组索引一起包含了全部 所需的表列，优化程序就可能利用这些索引，通过标识扫描或全 扫描避免可能的成本高昂的