Oracle9i 的查询优化

执行概要

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

简介

什么是查询优化程序？

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

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

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

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

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

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

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

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

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

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

动态运行时间优化并不是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 EDEPTNO = DDEPTNO;

SELECT ENAME DNAME FROM TEST_VIEW WHERE SAL > ;

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

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

SELECT ENAME DNAME FROM EMP E DEPT D WHERE EDEPTNO = DDEPTNO AND ESAL > ;

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

复杂视图合并

许多视图合并操作都是直截了当的如以上示例但是较复杂的视图如包含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 DEPTNAME AVG_SAL_DEPT FROM DEPT AVG_SAL_VIEW WHERE DEPTDEPTNO = AVG_SAL_VIEWDEPTNO AND DEPTLOC = OAKLAND

可以转换为

SELECT DEPTNAME AVG(SAL) FROM DEPT EMP WHERE DEPTDEPTNO = EMPDEPTNO AND DEPTLOC = OAKLAND GROUP BY DEPTROWID DEPTNAME

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

子查询展平

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

SELECT DDNAME FROM DEPT D WHERE DDEPTNO IN (SELECT EDEPTNO FROM EMP E WHERE ESAL > )

存在一系列可以优化本查询的执行计划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 DDEPTNO EDEPTNO AND ESAL > ;

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

OPERATION OBJECT_NAME OPTIONS

SELECT STATEMENT

HASH JOIN

SORT UNIQUE

TABLE ACCESS EMP FULL

TABLE ACCESS DEPT FULL

转换后的SQL 语句为

SELECT DDNAME FROM (SELECT DISTINCT DEPTNO FROM EMP) E DEPT D WHERE EDEPTNO = DDEPTNO AND ESAL > ;

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

传递谓词生成

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

SELECT COUNT(DISTINCT O_ORDERKEY) FROM ORDER LINEITEM WHERE O_ORDERKEY = L_ORDERKEY AND O_ORDERDATE = L_SHIPDATE AND O_ORDERDATE BETWEEN JAN AND JAN

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

SELECT COUNT(DISTINCT O_ORDERKEY) FROM ORDER LINEITEM WHERE