Dav_笔记6-The Query Optimizer

This part explains how to tune your SQL statements for optimal performance and discusses Oracle SQL-related performance tools.

The Query Optimizer 查询优化器

本章讨论SQL处理，优化方法以及查询优化器（通常称为优化器）如何选择特定计划来执行SQL。

本章包含以下部分：

■一：查询优化器概述

■二：优化器访问路径概述

■三：连接概述

■四：阅读和理解执行计划

■五：控制优化器行为

一：查询优化器概述

优化器是内置软件，可确定执行SQL语句的最有效方式。

本节包含以下主题：

■优化器操作

■查询优化器的组件

■绑定变量窥视

1：优化器操作

数据库可以通过多种方式执行SQL语句，例如全表扫描，索引扫描，嵌套循环和散列连接。优化程序在确定执行计划时会考虑与查询中的对象和条件相关的许多因素。这一决定是SQL处理中的一个重要步骤，可能会大大影响执行时间。

//注意：

优化器可能不会从一个版本的Oracle数据库到下一个版本做出相同的决定。 在最近的版本中，优化器可能会做出不同的决定，因为有更好的信息可用。//

当用户提交要执行的SQL语句时，优化器将执行以下步骤：

1.优化器根据可用的访问路径和提示为SQL语句生成一组潜在计划。

2.优化器根据数据字典中的统计数据估算每个计划的成本。统计信息包括关于由语句访问的表，索引和分区的数据分布和存储特性的信息。

成本是与执行特定计划声明所需的预期资源使用量成正比的估计值。优化器根据估计的计算机资源（包括I / O，CPU和内存）计算访问路径和联接订单的成本。

与成本较低的系列计划相比，成本较高的系列计划执行时间较长。使用并行计划时，资源使用与经过时间没有直接关系。

3.优化器比较计划并选择成本最低的计划。

优化器的输出是描述最佳执行方法的执行计划。这些计划显示了Oracle数据库用于执行SQL语句的步骤组合。每个步骤都可以从数据库中物理地检索行，或者为发布该语句的用户做好准备。

对于由Oracle数据库处理的任何SQL语句，优化器执行表11-1中列出的操作。

第四部分： IV Optimizing SQL Statements

sql语句优化：

This part explains how to tune your SQL statements for optimal performance and discusses Oracle SQL-related performance tools.

The chapters in this part include:

■Chapter 11, "The Query Optimizer"

■Chapter 12, "Using EXPLAIN PLAN"

■Chapter 13, "Managing Optimizer Statistics"

■Chapter 14, "Using Indexes and Clusters"

■Chapter 15, "Using SQL Plan Management"

■Chapter 16, "SQL Tuning Overview"

■Chapter 17, "Automatic SQL Tuning"

■Chapter 18, "SQL Access Advisor"

■Chapter 19, "Using Optimizer Hints"

■Chapter 20, "Using Plan Stability"

■Chapter 21, "Using Application Tracing Tools"

第11章 11The Query Optimizer 查询优化器

本章讨论SQL处理，优化方法以及查询优化器（通常称为优化器）如何选择特定计划来执行SQL。

本章包含以下部分：

■一：查询优化器概述

■二：优化器访问路径概述

■三：连接概述

■四：阅读和理解执行计划

■五：控制优化器行为

一：查询优化器概述

优化器是内置软件，可确定执行SQL语句的最有效方式。

本节包含以下主题：

■优化器操作

■查询优化器的组件

■绑定变量窥视

1：优化器操作

数据库可以通过多种方式执行SQL语句，例如全表扫描，索引扫描，嵌套循环和散列连接。优化程序在确定执行计划时会考虑与查询中的对象和条件相关的许多因素。这一决定是SQL处理中的一个重要步骤，可能会大大影响执行时间。

//注意：

优化器可能不会从一个版本的Oracle数据库到下一个版本做出相同的决定。 在最近的版本中，优化器可能会做出不同的决定，因为有更好的信息可用。//

当用户提交要执行的SQL语句时，优化器将执行以下步骤：

1.优化器根据可用的访问路径和提示为SQL语句生成一组潜在计划。

2.优化器根据数据字典中的统计数据估算每个计划的成本。统计信息包括关于由语句访问的表，索引和分区的数据分布和存储特性的信息。

成本是与执行特定计划声明所需的预期资源使用量成正比的估计值。优化器根据估计的计算机资源（包括I / O，CPU和内存）计算访问路径和联接订单的成本。

与成本较低的系列计划相比，成本较高的系列计划执行时间较长。使用并行计划时，资源使用与经过时间没有直接关系。

3.优化器比较计划并选择成本最低的计划。

优化器的输出是描述最佳执行方法的执行计划。这些计划显示了Oracle数据库用于执行SQL语句的步骤组合。每个步骤都可以从数据库中物理地检索行，或者为发布该语句的用户做好准备。

对于由Oracle数据库处理的任何SQL语句，优化器执行表11-1中列出的操作。

评估表达和条件:

优化器首先尽可能完整地评估包含常量的表达式和条件。

声明转换:

对于涉及（例如）相关子查询或视图的复杂语句，优化程序可能会将原始语句转换为等效的联接语句。

优化程序目标的选择:

优化器确定优化的目标。 请参阅第11-36页的“选择优化程序目标”。

访问路径的选择:

对于语句访问的每个表，优化器会选择一个或多个可用访问路径来获取表数据。 请参阅第11-12页的“优化器访问路径概述”。

加入订单的选择:

对于连接两个以上表的连接语句，优化器会选择首先连接哪对表，然后选择哪个表连接到结果，依此类推。 请参阅第11-22页的“查询优化器如何选择联接的执行计划”。

有时，您可能会获得关于特定应用程序数据的更多信息，而不是优化程序可用的信息。 在这种情况下，您可以在SQL语句中使用提示来指导优化程序如何执行语句。

2：Components of the Query Optimizer

查询优化器的组件

The query optimizer operations include:

■Query Transformation

■Estimation

■Plan Generation

查询优化器操作包括：

■查询转换

■估计

■计划生成

查询转换

语句的每个查询部分称为查询块。 查询转换器的输入是一个解析查询，它由一组查询块表示。

在以下示例中，SQL语句由两个查询块组成。 括号内的子查询是内部查询块。 外部查询块是SQL语句的其余部分，用于检索ID由子查询提供的部门中的员工名称。

SELECT first_name, last_name

FROM employees

WHERE department_id

IN (SELECT department_id FROM departments WHERE location_id = 1800);

查询表单确定查询块如何相互关联。 变换器确定将原始SQL语句重写为可以更高效地处理的语义等效SQL语句是否有利。

查询转换器使用多种查询转换技术，包括以下内容：

■View Merging 视图合并

■Predicate Pushing 谓语推送

Subquery Unnesting 子查询解嵌套

■Query Rewrite with Materialized Views 使用物化视图重写查询

这些转换的任何组合都可以应用于给定的查询。

视图合并

分析器将查询中引用的每个视图展开为一个单独的查询块。 该块基本上表示视图定义，并因此表示视图的结果。 优化器的一个选择是分别分析视图查询块并生成视图子计划。 优化器然后通过使用视图子计划来生成整个查询计划来处理查询的其余部分。 这种技术通常会导致次优的查询计划，因为视图是单独优化的。

在视图合并中，变换器将表示视图的查询块合并到包含的查询块中。 例如，假设您创建一个视图，如下所示：

CREATE VIEW employees_50_vw AS

SELECT employee_id, last_name, job_id, salary, commission_pct, department_id

FROM employees

WHERE department_id = 50;

You then query the view as follows:

SELECT employee_id

FROM employees_50_vw

WHERE employee_id > 150;

优化器可以使用视图合并将employees_50_vw的查询转换为以下等效查询：

SELECT employee_id

FROM employees

WHERE department_id = 50

AND employee_id > 150;

视图合并优化适用于仅包含选择，投影和连接的视图。 也就是说，可合并视图不包含集合运算符，聚合函数，DISTINCT，GROUP BY，CONNECT BY等等。

要使优化程序对用户发出的任何查询使用视图合并，您必须向用户授予MERGE ANY VIEW特权。 在特定视图上为用户授予MERGE VIEW特权，以使优化器能够使用视图合并来查看这些视图。 只有在特定条件下才需要这些权限，例如，由于安全检查失败而未合并视图。

谓词推动

在谓词推送中，优化器将来自包含查询块的相关谓词“推入”视图查询块。 对于未合并的视图，此技术改进了未合并视图的子计划，因为数据库可以使用推入谓词来访问索引或用作过滤器。

例如，假设您创建了一个引用两个员工表的视图。 该视图是使用UNION集合运算符的复合查询定义的，如下所示：

CREATE VIEW all_employees_vw AS

( SELECT employee_id, last_name, job_id, commission_pct, department_id

FROM employees )

UNION

( SELECT employee_id, last_name, job_id, commission_pct, department_id

FROM contract_workers );

You then query the view as follows:

SELECT last_name

FROM all_employees_vw

WHERE department_id = 50;

由于视图是复合查询，因此优化器无法将视图的查询合并到访问查询块中。 相反，优化器可以通过将谓词WHERE子句条件department_id = 50推入视图的复合查询来转换访问语句。 等效的转换查询如下所示：

SELECT last_name

FROM ( SELECT employee_id, last_name, job_id, commission_pct, department_id

FROM employees

WHERE department_id=50

UNION

SELECT employee_id, last_name, job_id, commission_pct, department_id

FROM contract_workers

WHERE department_id=50 );

子查询解嵌套

在子查询unnesting中，优化器将嵌套查询转换为等效的联接语句，然后优化联接。 这种转换使优化器能够利用连接优化器技术。 优化器只有在确保生成的连接语句与原始语句完全返回相同的行并且子查询不包含聚合函数（如AVG）时才能执行此转换。

例如，假设您以用户sh连接并执行以下查询：

SELECT *

FROM sales

WHERE cust_id IN ( SELECT cust_id FROM customers );

因为customers.cust_id列是主键，所以优化器可以将复杂查询转换为以下确保返回相同数据的联接语句：

SELECT sales.*

FROM sales, customers

WHERE sales.cust_id = customers.cust_id;

如果优化程序无法将复杂语句转换为连接语句，那么它将为父语句和子查询选择执行计划，就像它们是单独的语句一样。 优化器然后执行子查询并使用返回的行来执行父查询。 为了提高整个查询计划的执行速度，优化程序有效地订调用子计划。

使用物化视图进行查询重写

物化视图就像一个查询，其结果是数据库实现并存储在一个表中。 当数据库发现用户查询与与物化视图关联的查询兼容时，则数据库可以根据物化视图重写查询。 这种技术改进了查询执行，因为大部分查询结果都是预先计算好的。

查询转换器查找任何与用户查询兼容的物化视图，并选择一个或多个物化视图来重写用户查询。 使用物化视图重写查询是基于成本的。 也就是说，如果未使用实例化视图生成的计划比使用实例化视图生成的计划成本更低，则优化器不会重写查询。

考虑以下物化视图cal_month_sales_mv，它汇总了每月销售的美元数量：

CREATE MATERIALIZED VIEW cal_month_sales_mv

ENABLE QUERY REWRITE

AS

SELECT t.calendar_month_desc, SUM(s.amount_sold) AS dollars

FROM sales s, times t

WHERE s.time_id = t.time_id

GROUP BY t.calendar_month_desc;

假设典型月份的销售数量约为100万。 该视图具有针对每个月销售的美元数量的预计算总计。 考虑下面的查询，该查询要求每月销售金额的总和：

SELECT t.calendar_month_desc, SUM(s.amount_sold)

FROM sales s, times t

WHERE s.time_id = t.time_id

GROUP BY t.calendar_month_desc;

如果没有查询重写，数据库必须直接访问销售额并计算出售金额的总和。 这种方法涉及从销售中读取数百万行，这总会增加查询响应时间。 连接还会进一步减慢查询响应，因为数据库必须计算数百万行上的连接。 通过查询重写，优化器透明地重写查询，如下所示：

SELECT calendar_month, dollars

FROM cal_month_sales_mv;

估计

估算器确定给定执行计划的总成本。估算人员产生三种不同类型的措施来实现这一目标：

■选择性

此度量表示行集合中的一小部分行。选择性与查询谓词相关联，如last_name ='Smith'或谓词组合。

■基数

该度量表示行集合中的行数。

■成本

该度量表示所使用的工作单位或资源。查询优化器使用磁盘I / O，CPU使用率和内存使用量作为工作单位。

如果统计数据可用，则估算人员使用它们来计算这些度量。统计数据提高了措施的准确程度。

选择性

选择性表示行集合中的一小部分行。行集可以是基表，视图或连接或GROUP BY运算符的结果。选择性与查询谓词相关联，例如last_name ='Smith'，或谓词组合，例如last_name ='Smith'AND job_type ='Clerk'。

谓词过滤行集中特定数量的行。因此，谓词的选择性指示有多少行通过谓词测试。选择性范围从0.0到1.0。选择性为0.0意味着从行集合中不选择行，而选择性为1.0意味着所有行都被选中。当值接近1.0时，谓词变得更具选择性，而当值接近1.0时，谓词变得更少选择性（或更不具选择性）。

优化器根据是否有统计信息来估计选择性：

■统计数据不可用

根据OPTIMIZER_DYNAMIC_SAMPLING初始化参数的值，优化器可以使用动态统计或内部默认值。数据库根据谓词类型使用不同的内部默认值。例如，等式谓词（last_name ='Smith'）的内部缺省值比范围谓词（last_name>'Smith'）的内部缺省值低，因为等同谓词预计会返回一小部分行。请参阅第13-22页的“控制动态统计”。

■可用统计数据

当统计数据可用时，估算人员使用它们估算选择性。假设有150个不同的员工姓氏。对于相等谓词last_name ='Smith'，选择性是last_name的不同值的数量n的倒数，在本例中为.006，因为查询选择包含150个不同值中的1个的行。

如果直方图可用于last_name列，则估计器使用直方图而不是不同值的数量。柱状图捕获列中不同值的分布，因此它可以产生更好的选择性估计值，特别是对于包含倾斜数据的列。请参阅第13-28页的“查看直方图”。

基数

基数表示行集合中的行数。 在此上下文中，行集可以是基表，视图或联接或GROUP BY运算符的结果。

成本

成本代表作业中使用的工作单位或资源。优化器使用磁盘I / O，CPU使用率和内存使用量作为工作单位。该操作可以扫描表，通过使用索引访问表中的行，将两个表连接在一起或排序行集。成本是数据库执行查询并产生结果时预期会发生的工作单元数量。

访问路径决定了从基表获取数据所需的工作单元数量。访问路径可以是表扫描，快速全索引扫描或索引扫描。

■表扫描或快速全索引扫描

在表扫描或快速全索引扫描期间，数据库在单个I / O中从磁盘读取多个块。因此，扫描的成本取决于要扫描的块数和多块读取计数值。

■索引扫描

索引扫描的成本取决于B树中的级别，要扫描的索引叶块数量以及使用索引键中的rowid获取的行数。使用rowid获取行的成本取决于索引聚类因子。请参阅第11-16页的“评估块的I / O，而不是行”。

联合成本表示所连接的两个行集的单独访问成本加上联合操作的成本的组合。

计划生成

计划生成器通过尝试不同的访问路径，连接方法和连接顺序来探索查询块的各种计划。许多计划是可能的，因为数据库可以使用不同的访问路径，连接方法和连接顺序的各种组合来产生相同的结果。发电机的目的是选择成本最低的方案。

连接顺序 join order

连接顺序是不同的连接项（例如表）被访问并连接在一起的顺序。假定数据库连接了table1，table2和table3。连接顺序可能如下：

1.The database accesses table1.

2.The database accesses table2 and joins its rows to table1.

3.The database accesses table3 and joins its data to the result of the join between table1 and table2.

查询子计划

优化器通过单独的查询块表示每个嵌套子查询或未合并视图，并生成一个子计划。数据库分别从下往上分别优化查询块。因此，数据库首先优化最内层的查询块并为其生成一个子计划，然后最后生成代表整个查询的外层查询块。

查询块的可能计划数与FROM子句中的连接项数成正比。这个数字随着加入项目的数量呈指数增长。例如，五个表格连接的可能计划将显着高于两个表格连接的可能计划。

截止计划选择

计划生成器使用内部截断来减少在查找最低成本计划时尝试的计划数量。截止时间取决于当前最佳计划的成本。如果当前最好的成本很高，那么计划生成器会探索替代计划来找到一个更低成本的计划。如果当前的最佳成本很小，那么发电机会迅速结束搜索，因为进一步的成本改善不会很大。

如果计划生成器以初始连接顺序开始，生成成本接近最优的计划，则截断工作良好。找到一个好的初始连接顺序是一个难题。

3：Bind Variable Peeking

绑定变量窥视

在绑定变量窥视（也称为绑定窥视）中，当数据库对语句执行硬解析时，优化器将查看绑定变量中的值。

当查询使用文字时，优化器可以使用文字值来查找最佳方案。 但是，当查询使用绑定变量时，优化器必须选择最佳方案，而不在SQL文本中存在文字。 这项任务可能非常困难。 通过查看绑定值，优化器可以确定WHERE子句条件的选择性，就像文字已被使用一样，从而改进计划。

假设数据库中存在以下100,000行的emp表。 该表具有以下定义：

数据在deptno列中显着偏斜。 值99在99.9％的行中找到。 每个其他的deptno值（0到9）都在1％的行中找到。 您已收集表格的统计数据，从而在deptno列上生成直方图。 您可以使用绑定值9定义绑定变量并查询emp，如下所示：

VARIABLE deptno NUMBER

EXEC :deptno := 9

SELECT /*ACS_1*/ count(*), max(empno)

FROM emp

WHERE deptno = :deptno;

The query returns 10 rows:

COUNT(*) MAX(EMPNO)

---------- ----------

10 99

为了生成查询的执行计划，在硬解析期间数据库的值为9。 优化器生成选择性估计值，就好像用户执行了以下查询一样：

select /*ACS_1*/ count(*), max(empno)

from emp

where deptno = 9;

在选择计划时，优化器仅在硬解析期间查看绑定值。 这个计划对于所有可能的价值可能不是最佳的。

由于绑定变量巡视依赖于第一次巡视到的变量值，然后按照这个字面值生成了最优执行计划，一旦更换了变量值，可能还会按照第一次的执行计划执行，从而不一定最佳，所以有了下面的自适应游标共享的概念。

Adaptive Cursor Sharing

自适应游标共享

自适应光标共享功能使包含绑定变量的单个语句可以使用多个执行计划。 游标共享是“自适应的”，因为游标适应其行为，因此数据库并不总是对每次执行使用相同的计划或绑定变量值。

对于适当的查询，数据库监控随着时间的推移访问不同绑定值的数据，确保针对特定绑定值的光标的最佳选择。 例如，优化程序可能为绑定值9选择一个计划，为绑定值10选择另一个计划。游标共享是“自适应”的，因为游标会调整其行为，以便每次执行或绑定变量值时都不会使用相同的计划。

数据库默认启用了自适应光标共享，无法禁用。 请注意，自适应光标共享不适用于包含14个以上绑定变量的SQL语句。

注意：

自适应光标共享独立于CURSOR_SHARING初始化参数（请参阅第7-36页的“共享现有应用程序的光标”）。 自适应光标共享同样适用于包含用户定义的和系统生成的绑定变量的语句。

绑定敏感游标

绑定敏感游标是一个游标，其最佳计划可能取决于绑定变量的值。 数据库监视使用不同绑定值的绑定敏感游标的行为，以确定不同的计划是否有益。

优化器用来决定游标是否是绑定敏感的标准包括以下内容：

优化器偷看绑定值以生成选择性估计值。

■包含绑定值的列上存在直方图。

例11-2绑定敏感游标

在例11-1中，您使用deptno的绑定值9来查询emp表。 现在运行DBMS_XPLAN.DISPLAY_CURSOR函数来显示查询计划：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

The plan indicates that the optimizer chose an index range scan, which is expected because of the selectivity (only 1%) of the value 9. You can query V$SQL to view statistics about the cursor:

COL BIND_SENSI FORMAT a10

COL BIND_AWARE FORMAT a10

COL BIND_SHARE FORMAT a10

SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",

IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"

FROM V$SQL

WHERE SQL_TEXT LIKE 'select /*ACS_1%';

如以下输出所示，此语句存在一个子游标，并且已执行一次。 少量的缓冲区获取与子游标相关联。 由于deptno数据偏斜，数据库创建了一个直方图。 这个直方图导致数据库将光标标记为绑定敏感（IS_BIND_SENSITIVE为Y）。

对于每次执行具有新绑定值的查询，数据库都将记录新值的执行统计信息，并将它们与先前值的执行统计信息进行比较。 如果执行统计数据变化很大，那么数据库将标记游标绑定感知。

Bind-Aware Cursors

一个绑定感知游标是一个绑定敏感游标，可以为不同的绑定值使用不同的计划。 在游标进行绑定感知之后，优化程序将根据绑定值及其选择性估计值为将来的执行选择计划。

当执行带有绑定敏感游标的语句时，数据库将决定是否将游标绑定感知标记。 这个决定取决于游标是否为不同的绑定值产生显着不同的数据访问模式。 如果数据库标记了游标绑定感知，则下一次游标执行数据库时会执行以下操作：

■基于新的绑定值生成新计划。

■将为该语句生成的原始游标标记为不可共享（V $ SQL.IS_SHAREABLE为N）。 该游标不再可用，并且将成为共享SQL区域中第一批老化的游标。

例11-3绑定感知游标

在示例11-1中，您使用绑定值9查询了emp。现在使用绑定值10查询emp。查询返回包含值10的99,900行：

由于此语句的游标是绑定敏感的，因此优化程序假定游标可以共享。 因此，对于值9，优化器对值10使用相同的索引范围扫描。

V $ SQL输出显示第二次执行相同的绑定敏感游标（查询使用10）并且需要比第一次执行更多的缓冲区获取：

SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",

IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"

FROM V$SQL

WHERE SQL_TEXT LIKE 'select /*ACS_1%';

统计以前的执行情况并将光标标记为绑定感知。 在这种情况下，优化器决定新的计划是有保证的，所以它会对语句进行硬解析并生成新的计划。 新计划使用全表扫描而不是索引范围扫描：

V $ SQL的查询显示数据库创建了一个表示包含全表扫描的计划的额外子游标（子代号1）。 这个新的游标显示较少数量的缓冲区获取并且被标记为绑定感知：

SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",

IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"

FROM V$SQL

WHERE SQL_TEXT LIKE 'select /*ACS_1%';

执行查询两次后，值为10，再次使用更具选择性的值9执行该操作。由于自适应光标共享，优化程序会“调整”光标并选择索引范围扫描，而不是对此值执行全表扫描。

V $ SQL的查询表明数据库为查询的执行创建了一个新的子游标（子数字2）：

由于数据库现在使用自适应光标共享，因此数据库不再使用原始光标（子级0），该光标不具有绑定意识。 共享的SQL区域会使已停用的游标变老。

游标合并

如果优化器为绑定感知游标创建一个计划，并且该计划与现有游标相同，则优化器可以执行游标合并。 在这种情况下，数据库合并游标以节省共享SQL区域中的空间。 数据库增加了光标的选择性范围，以包含新绑定的选择性。

假设您执行的查询的绑定值不在现有游标的选择性范围内。 数据库执行硬解析并生成新计划和新游标。 如果此新计划与现有游标使用的计划相同，则数据库合并这两个游标并删除其中一个旧游标。

查看绑定相关的性能数据

您可以使用V $视图进行适应性光标共享，以查看选择性范围，光标信息（例如游标是否为绑定感知或绑定敏感）以及执行统计信息：

■V $ SQL显示游标是绑定敏感的还是绑定感知的

■V $ SQL_CS_HISTOGRAM显示跨三个执行历史记录直方图的执行计数分布

■如果选择性用于检查光标共享，则V $ SQL_CS_SELECTIVITY将显示为包含绑定变量的每个谓词存储的选择性范围

■V $ SQL_CS_STATISTICS总结优化器用于确定是否标记游标绑定感知的信息。

二：Overview of Optimizer Access Paths 优化器访问路径概述

访问路径是从数据库中检索数据的方式。通常，索引访问路径对于检索表格行的一小部分子集的语句很有用，而在访问大部分表格时全部扫描效率更高。联机事务处理（OLTP）应用程序由具有高选择性的短期运行SQL语句组成，通常以使用索引访问路径为特征。但是，决策支持系统倾向于使用分区表并对相关分区执行完整扫描。

本节介绍数据库可用于查找和检索任何表中的任何行的数据访问路径。

■全表扫描

■Rowid扫描

■索引扫描

■群集访问

■散列访问

■样本表扫描

■查询优化器如何选择访问路径

全表扫描

这种类型的扫描会读取表中的所有行，并过滤掉那些不符合选择条件的行。在全表扫描期间，扫描表中高位标记下的所有块。高水位标记表示已使用空间的数量或已格式化为接收数据的空间。检查每一行以确定它是否满足语句的WHERE子句。

当Oracle数据库执行全表扫描时，将按顺序读取这些块。由于这些块是相邻的，因此数据库可以使I / O调用大于单个块来加速该过程。读取调用的大小范围从一个块到由初始化参数DB_FILE_MULTIBLOCK_READ_COUNT指示的块数。使用多块读取，数据库可以非常有效地执行全表扫描。数据库只读取一次每个块。

示例11-14，第11-33页的“EXPLAIN PLAN输出”包含employees表上的全表扫描示例。

为什么全表扫描更快速地访问大量数据

访问表格中大部分块时，全表扫描比索引范围扫描便宜。全表扫描可以使用更大的I / O调用，并且减少大量I / O调用比调用更小的调用便宜。

优化程序使用全表扫描时

优化器在以下任何情况下都使用全表扫描：

缺乏索引

如果查询不能使用现有索引，则它使用全表扫描。例如，如果在查询中索引列上有一个函数，那么优化器不能使用索引，而是使用全表扫描。

如果您需要将索引用于区分大小写的搜索，则不要在搜索列中允许混合大小写数据，也不要在搜索列上创建基于函数的索引（如UPPER（last_name））。请参阅第14-7页的“使用基于功能的索引进行性能”。

大数据量

如果优化器认为查询需要表中的大部分块，那么即使索引可用，它也会使用全表扫描。

小表

如果一个表的数据库可以在单个I / O调用中读取的高水位标记下包含少于DB_FILE_MULTIBLOCK_READ_COUNT块，那么全表扫描可能比索引范围扫描便宜，无论正在访问的表的分数是多少还是索引存在。

平行度高

表格的高度并行性使优化器偏向范围扫描的全表扫描。检查ALL_TABLES中的DEGREE列以确定并行度。

全表扫描提示

使用提示FULL（表别名）来指示优化器使用全表扫描。有关FULL提示的更多信息，请参阅第19-3页的“访问路径的提示”。

您可以使用CACHE和NOCACHE提示来指示检索到的块放置在缓冲区缓存中的位置。 CACHE提示指示优化器在数据库执行全表扫描时，将检索到的块放在缓冲区高速缓存中最近使用的LRU列表末尾。

小表根据表11-2中的标准自动缓存。

对于使用CACHE属性创建或更改的表，禁用对小表的自动缓存。

并行查询执行

当需要全表扫描时，数据库可以通过使用多个并行执行服务器来缩短响应时间。 在某些情况下，当数据库具有大量内存时，数据库可以在SGA中缓存并行查询数据，而不是使用直接读取到PGA中。 通常，由于潜在的资源使用情况，并发查询会发生在低并发性数据仓库中。

Rowid扫描

行的rowid指定包含行和该块中行的位置的数据文件和数据块。通过指定rowid来查找行是检索单个行的最快方法，因为数据库中该行的确切位置是指定的。

要通过rowid访问表，Oracle数据库首先会从语句的WHERE子句或通过索引扫描一个或多个表索引来获取所选行的rowid。 Oracle数据库然后根据其rowid查找表中的每个选定行。

在示例11-14“解释计划输出”（第11-33页）中，计划包括对作业和部门表的索引扫描。数据库使用检索的rowid返回行。

优化器使用Rowid时

这通常是从索引中检索rowid之后的第二步。索引中不存在的任何列可能需要表访问权限。

通过rowid访问并不需要遵循每个索引扫描。如果索引包含该语句所需的所有列，则可能不会发生rowid表访问。

注意：

Rowid是数据库存储数据的内部表示。 Rowid可以在版本之间更改。 不建议访问基于位置的数据，因为由于行迁移和链接，导出和导入以及其他一些操作，行可以四处移动。 外键应该基于主键。 有关rowid的更多信息，请参阅Oracle数据库高级应用程序开发人员指南。

索引扫描

在此方法中，通过使用由语句指定的索引列值遍历索引来检索行。索引扫描根据索引中一个或多个列的值从索引中检索数据。要执行索引扫描，Oracle数据库将在索引中搜索由语句访问的索引列值。如果语句仅访问索引的列，则Oracle数据库直接从索引读取索引列值，而不是从表中读取索引列值。

索引不仅包含索引值，还包含表中具有该值的行的rowid。因此，如果语句除了索引列之外还访问其他列，那么Oracle数据库可以通过使用rowid访问的表或集群扫描来查找表中的行。

索引扫描可以是以下类型之一：

■评估块的I / O，而不是行

■索引唯一扫描

■索引范围扫描

■索引范围扫描降序

■索引跳过扫描

■完整扫描

■快速全索引扫描

■索引连接

■位图索引

评估块的I / O，而不是行

Oracle数据库按块执行I / O。因此，优化器使用全表扫描的决定受访问块百分比的影响，而不是行。这被称为索引聚类因子。如果块包含单个行，则访问的行和访问的块是相同的。

但是，大多数表格在每个块中都有多行。因此，所需数量的行可以聚集在几个块中或分布在更多数量的块上。

虽然聚类因子是索引的一个属性，但聚类因子实际上与表中数据块中类似索引列值的分布有关。较低的聚类因子表示单个行集中在表中较少的块内。相反，高聚类因子表示单个行在表中的块间更随机地分散。因此，高聚簇因子意味着使用范围扫描以rowid获取行花费更高，因为需要访问表中更多块以返回数据。例11-4显示了聚类因子如何影响成本。

例11-4聚类因子对成本的影响

假设以下情况：

■有9行的表格。

■表格的col1上有一个非唯一索引。

■c1列当前存储值A，B和C.

■表格只有三个数据块。

情况1：索引聚类因子在行中较低，因为它们排列在下图中。

这是因为c1具有相同索引列值的行位于表中相同的物理块内。 使用范围扫描返回所有具有值A的行的成本很低，因为只能读取表中的一个块。

情况2：如果表格中的相同行重新排列，以便索引值散布在表格块（而不是并置）上，则索引聚类因子更高。

这是因为必须读取表中的所有三个块才能检索col1中值为A的所有行。

索引唯一扫描

此扫描最多返回一个rowid。 如果语句包含保证仅访问单个行的UNIQUE或PRIMARY KEY约束，Oracle数据库将执行唯一扫描。

在第11-33页的“EXPLAIN PLAN输出”中，数据库分别使用job_id_pk和dept_id_pk索引对作业和部门表执行索引扫描。

优化程序使用索引唯一扫描时

当用户指定唯一（B-tree）索引的所有列或作为主键约束条件的结果创建的索引时，数据库使用此访问路径。

索引唯一扫描提示

一般来说，您不需要使用提示来执行独特的扫描。可能会出现表跨越数据库链接并从本地表访问的情况，或者表的表足够小以使优化器更喜欢全表扫描。

提示INDEX（别名index_name）指定要使用的索引，但不指定访问路径（范围扫描或唯一扫描）。有关INDEX提示的更多信息，请参阅第19-3页的“访问路径的提示”。

索引范围扫描

索引范围扫描是访问选择性数据的常用操作。它可以是有界的（两边都有界限），也可以是无界的（一边或两边）。数据以索引列的升序返回。具有相同值的多行按rowid升序排列。

如果您需要按顺序对数据进行排序，则使用ORDER BY子句，而不要依赖索引。如果索引可以满足ORDER BY子句，那么优化器将使用此选项并避免排序。

在示例11-5中，订单已从旧系统导入，并且您正在使用旧系统中使用的参考查询订单。假定这个参考是order_date。

例11-5索引范围扫描

SELECT order_status, order_id

FROM orders

WHERE order_date = :b1;

这应该是一个高度选择性的查询，并且您应该使用列上的索引查看查询以检索所需的行。返回的数据按order_date的rowid升序排列。由于索引列order_date对于此处选定的行是相同的，因此数据按rowid排序。

优化程序使用索引范围扫描时

优化程序在找到一个或多个在条件中指定的索引的前导列时使用范围扫描，如下所示：

■col1 =：b1

■col1

■col1>：b1

■索引中前导列的前述条件的组合

■col1就像'ASD％'通配符搜索不应该处于领先位置，否则像'％ASD'这样的条件col1不会导致范围扫描

范围扫描可以使用唯一索引或非唯一索引。 当索引列构成ORDER BY / GROUP BY子句时，范围扫描避免排序。

索引范围扫描提示

如果优化器选择其他索引或使用全表扫描，则可能需要提示。 提示INDEX（table_alias index_name）指示优化器使用特定的索引。 有关INDEX提示的更多信息，请参阅第19-3页的“访问路径的提示”。

Index Range Scans Descending 索引范围扫描降序

索引范围扫描降序与索引范围扫描相同，只是数据以降序返回。默认情况下，索引按升序存储。通常，数据库在按降序排序数据时首先使用此扫描来返回最新数据，或者在查找小于指定值的值时使用此扫描。

优化程序使用索引范围扫描下降时

当索引可以通过降序子句满足顺序时，优化器使用索引范围扫描降序。

索引范围扫描降序提示

对此访问路径使用提示INDEX_DESC（table_alias index_name）。有关INDEX_DESC提示的更多信息，请参阅第19-3页的“访问路径的提示”。

索引跳过扫描 Index Skip Scans

索引跳过扫描可以改善非前缀列的索引扫描。通常，扫描索引块比扫描表数据块快。

跳过扫描可以将复合索引逻辑分割为更小的子索引。在跳过扫描中，查询中未指定组合索引的初始列。换句话说，它被跳过。

数据库根据初始列中不同值的数量确定逻辑子索引的数量。当组合索引的前导列中有很少不同的值并且索引的非导向键中有许多不同的值时，跳过扫描是有利的。

当查询谓词中没有指定组合索引的前导列时，数据库可以选择索引跳过扫描。例如，假定您在sh.customers表中为客户运行以下查询：

SELECT * FROM sh.customers WHERE cust_email ='Abbey@company.com';

customers表中有一个cust_gender列，其值为M或F.假设在列（cust_gender，cust_email）上存在一个组合索引，其创建方式如下：

CREATE INDEX customers_gender_email ON sh.customers（cust_gender，cust_email）;

Example 11–6 Composite Index Entries

F,Wolf@company.com,rowid

F,Wolsey@company.com,rowid

F,Wood@company.com,rowid

F,Woodman@company.com,rowid

F,Yang@company.com,rowid

F,Zimmerman@company.com,rowid

M,Abbassi@company.com,rowid

M,Abbey@company.com,rowid

即使未在WHERE子句中指定cust_gender，数据库也可以使用此索引的跳过扫描。

在跳过扫描中，逻辑子索引的数量由前导列中不同值的数量决定。 在例11-6中，前导列有两个可能的值。 数据库在逻辑上用索引F将索引拆分成一个子索引，用索引M将索引拆分成第二个子索引。

在搜索电子邮件为Abbey@company.com的客户的记录时，数据库将使用值F first搜索子索引，然后使用值M搜索子索引。从概念上讲，数据库按如下方式处理查询：

SELECT * FROM sh.customers WHERE cust_gender = 'F'

AND cust_email = 'Abbey@company.com'

UNION ALL

SELECT * FROM sh.customers WHERE cust_gender = 'M'

AND cust_email = 'Abbey@company.com';

full scans 全面扫描

完整索引扫描消除了排序操作，因为数据是通过索引键排序的。它单独读取块。在以下任何情况下，Oracle数据库可能会使用完整扫描：

■查询中存在符合以下要求的ORDER BY子句：

- ORDER BY子句中的所有列必须位于索引中。

- ORDER BY子句中列的顺序必须与前导索引列的顺序相匹配。

ORDER BY子句可以包含索引中的所有列或索引中列的子集。

■查询需要排序合并连接。数据库可以执行完整索引扫描，而不是执行全表扫描，然后在查询满足以下要求时进行排序：

- 查询中引用的所有列必须位于索引中。

- 查询中引用的列的顺序必须与前导索引列的顺序相匹配。

查询可以包含索引中的所有列或索引中列的子集。

■查询中存在GROUP BY子句，并且GROUP BY子句中的列存在于索引中。列不需要在索引和GROUP BY子句中具有相同的顺序。 GROUP BY子句可以包含索引中的所有列或索引中列的子集。

Fast Full Index Scans

快速全索引扫描

当索引包含查询所需的所有列时，快速全索引扫描是全表扫描的替代方法，并且索引键中至少有一列具有NOT NULL约束。快速全扫描访问索引本身中的数据，而不访问表格。数据库无法使用此扫描消除排序操作，因为数据不是由索引键排序的。与完整索引扫描不同，数据库使用多块读取读取整个索引，并且可以并行扫描。

您可以使用初始化参数OPTIMIZER_FEATURES_ENABLE或INDEX_FFS提示指定快速完整索引扫描。快速全扫描比正常的全索引扫描更快，因为它可以使用多块I / O并且可以像表扫描一样并行运行。

快速全索引扫描提示

快速全扫描具有特殊的索引提示INDEX_FFS，它具有与常规INDEX提示相同的格式和参数。有关INDEX_FFS提示的更多信息，请参阅第19-3页的“访问路径的提示”。

索引连接

索引连接是几个索引的散列连接，它们一起包含查询中引用的所有表列。如果数据库使用索引连接，则不需要表访问，因为数据库可以从索引检索所有相关的列值。数据库不能使用索引连接来消除排序操作。

索引加入提示

您可以使用INDEX_JOIN提示指定索引连接。有关INDEX_JOIN提示的更多信息，请参阅第19-3页的“访问路径的提示”。

位图索引

位图连接使用位图的键值和映射函数将每个位的位置转换为rowid。位图可以高效地合并对应于WHERE子句中几个条件的索引，并使用布尔运算来解析AND和OR条件。

注意：

位图索引和位图连接索引仅在Oracle企业版中可用。

Cluster Access 集群访问

数据库使用群集扫描从存储在索引群集中的表中检索具有相同群集键值的所有行。在索引集群中，数据库将具有相同集群键值的所有行存储在同一个数据块中。要执行群集扫描，Oracle数据库首先通过扫描群集索引来获取所选行之一的rowid。 Oracle数据库然后根据此rowid查找行。

Hash Access 哈希访问

数据库使用散列扫描根据散列值在散列群集中查找行。在散列簇中，具有相同散列值的所有行都存储在同一个数据块中。要执行散列扫描，Oracle数据库首先通过将散列函数应用于由语句指定的群集键值来获取散列值。 Oracle数据库然后扫描包含具有该散列值的行的数据块。

Sample Table Scans 示例表扫描

示例表扫描从简单表或复杂SELECT语句中检索随机数据样本，如涉及连接和视图的语句。当语句的FROM子句包含SAMPLE子句或SAMPLE BLOCK子句时，数据库将使用此访问路径。要在使用SAMPLE子句按行进行采样时执行示例表扫描，数据库将读取表中指定百分比的行。要在使用SAMPLE BLOCK子句按块进行采样时执行示例表扫描，数据库将读取指定百分比的表块。

示例11-7使用示例表扫描访问雇员表的1％，按块进行抽样。

例11-7样本表扫描

SELECT *

FROM employees SAMPLE BLOCK (1);

查询优化器如何选择访问路径

查询优化器根据以下因素选择访问路径：

■语句的可用访问路径

■执行语句的估计成本，使用每个访问路径或路径组合

要选择访问路径，优化程序首先通过检查语句的WHERE子句及其FROM子句中的条件来确定哪些访问路径可用。然后优化器使用可用的访问路径生成一组可能的执行计划，并使用该语句可访问的索引，列和表的统计信息来估计每个计划的成本。最后，优化器选择具有最低估计成本的执行计划。

在选择访问路径时，查询优化器受以下因素影响：

■优化器提示

您可以指示优化器使用提示使用特定的访问路径，除非语句的FROM子句包含SAMPLE或SAMPLE BLOCK。

旧统计

例如，如果一个表自创建之后还没有被分析过，并且它在高水位标记下的DB_FILE_MULTIBLOCK_READ_COUNT块数少于一个，那么优化器认为该表很小并且使用全表扫描。查看ALL_TABLES表中的LAST_ANALYZED和BLOCKS列以检查统计信息。

三：连接概述

连接是从多个表中检索数据的语句。一个连接的特点是在FROM子句中有多个表。 WHERE子句中存在连接条件定义了表之间的关系。在一个连接中，一个行集称为inner，另一个称为outer。

本节讨论：

■查询优化器如何执行联接语句

■查询优化器如何为连接选择执行计划

■嵌套循环连接

■哈希连接

■排序合并联接

■笛卡尔联接

■外连接

查询优化器如何执行联接语句

为了选择加入语句的执行计划，优化器必须做出这些相关的决定：

■访问路径

至于简单的语句，优化器必须选择一个访问路径来从连接语句中的每个表检索数据。

■加入方法

要加入每对行源，Oracle数据库必须执行连接操作。连接方法包括嵌套循环，排序合并，笛卡尔和哈希连接。

■Join Order

要执行连接两个以上表的语句，Oracle数据库将连接两个表，然后将生成的行源连接到下一个表。这个过程一直持续到所有表都被加入到结果中。

查询优化器如何为连接选择执行计划

查询优化器在选择执行计划时会考虑以下内容：

■优化程序首先确定连接两个或更多表是否肯定会导致包含至多一行的行源。优化器根据表上的UNIQUE和PRIMARY KEY约束来识别这种情况。如果存在这种情况，那么优化器首先将这些表放入连接顺序中。优化器然后优化剩下的一组表的连接。

■对于具有外部连接条件的连接语句，具有外部连接运算符的表必须位于连接顺序中条件中的另一个表之后。优化器不考虑违反此规则的连接命令。同样，当子查询已转换为反连接或半连接时，子查询中的表必须位于它们连接或关联的外部查询块中的那些表之后。但是，在某些情况下，哈希反连接和半连接能够覆盖此排序条件。

通过查询优化器，优化器根据可能的连接顺序，连接方法和可用访问路径生成一组执行计划。优化器然后估计每个计划的成本并选择成本最低的那个。优化器通过以下方式估算成本：

■嵌套循环操作的成本基于将外部表的每个选定行及其内部表的每个匹配行读入内存的成本。优化器使用数据字典中的统计信息估算这些成本。

■排序合并连接的成本很大程度上取决于将所有资源读入内存并对其进行排序的成本。

■散列连接的成本很大程度上取决于在连接的其中一个输入侧上构建散列表的成本，并使用来自另一连接的行来探测它。

优化程序在确定每个操作的成本时也会考虑其他因素。例如：

■较小的排序区域大小可能会增加排序合并连接的成本，因为排序会在更小的排序区域中占用更多的CPU时间和I / O。请参阅第7-39页的“PGA内存管理”，了解如何调整SQL工作区的大小。

■较大的多块读取计数可能会降低排序合并连接相对于嵌套循环连接的成本。 如果数据库可以在单个I / O中从磁盘读取大量连续块，那么嵌套循环连接的内部表上的索引不太可能提高整个表扫描的性能。 多块读取计数由初始化参数DB_FILE_MULTIBLOCK_READ_COUNT指定。

您可以使用ORDERED提示覆盖优化器对连接订单的选择。 如果ORDERED提示指定了违反外连接规则的连接顺序，那么优化器会忽略该提示并选择顺序。 此外，您可以使用提示覆盖优化器对连接方法的选择。

嵌套循环连接

满足以下条件时，嵌套循环联接很有用：

■数据库连接数据的小部分。

■连接条件是访问第二个表的有效方法。

确保内表从外表驱动（依赖于）是很重要的。如果内部表的访问路径与外部表无关，那么对于外部循环的每次迭代都会检索相同的行，从而大大降低性能。在这种情况下，连接两个独立行源的散列连接表现更好。

嵌套循环连接涉及以下步骤：

1.优化器确定驱动表并将其指定为外部表。

2.另一个表被指定为内部表。

3.对于外部表中的每一行，Oracle数据库都会访问内部表中的所有行。外循环用于外表中的每一行，内循环用于内表中的每一行。外循环出现在执行计划的内循环之前，如下所示：

NESTED LOOPS

outer_loop

inner_loop

嵌套循环连接的原始和新实现

Oracle数据库11g为嵌套循环连接引入了一个新的实现。 因此，包含嵌套循环的执行计划可能会与先前版本的Oracle数据库中的执行计划不同。 在Oracle数据库11g中，新实现和嵌套循环联接的原始实现都是可能的。 因此，在分析执行计划时，理解NESTED LOOPS连接行源的数量可能会有所不同。

嵌套循环联接的原始实现

考虑以下查询：

SELECT e.first_name, e.last_name, e.salary, d.department_name

FROM hr.employees e, hr.departments d

WHERE d.department_name IN ('Marketing', 'Sales')

AND e.department_id = d.department_id;

在Oracle数据库11g之前，此查询的执行计划可能与以下执行计划类似：

在此示例中，联接的外侧包含对hr.departments表的扫描，该表返回匹配条件department_name IN（'Marketing'，'Sales'）的行。 内部循环检索与这些部门关联的hr.employees表中的雇员。

嵌套循环连接的新实现

Oracle数据库11g为嵌套循环连接引入了一个新的实现，以减少物理I / O的总延迟。当需要物理I / O时。 Oracle数据库11g可以批量处理多个物理I / O请求，并使用向量I / O处理它们，而不是一次处理一个请求。

作为嵌套循环联接的新实现的一部分，两个NESTED LOOPS联接行源可能出现在执行计划中，其中只有一个会在先前版本中出现。在这种情况下，Oracle数据库分配一个NESTED LOOPS连接行源以连接外部表中的值与内部索引连接。分配第二行来连接第一个连接的结果，其中包括存储在索引中的rowid，连接内侧的表。

考虑第11-24页的“嵌套循环联接的原始实现”中的查询。在Oracle数据库11g中，对于嵌套循环联接的新实现，此查询的执行计划可能与以下执行计划类似：

在这种情况下，hr.departments表中的行构成第一个连接的外侧。第一个连接的内部是索引emp_department_ix。第一个连接的结果构成第二个连接的外侧，其中hr.employees表为内侧。

在某些情况下，第二个连接行源未分配，并且执行计划看起来与之前版本中的相同。以下列表描述了这些情况：

■连接内部所需的所有列都存在于索引中，并且不需要表访问权限。在这种情况下，Oracle数据库只分配一个连接行源。

■返回的行的顺序可能与以前版本中的顺序不同。因此，当Oracle数据库尝试保留行的特定顺序时（例如为了消除对ORDER BY排序的需要），Oracle数据库可能会将原始实现用于嵌套循环联接。

■OPTIMIZER_FEATURES_ENABLE初始化参数在Oracle数据库11g之前设置为发行版。在这种情况下，Oracle数据库使用嵌套循环联接的原始实现。

优化程序使用嵌套循环联接时

优化器在连接少量行时使用嵌套循环连接，两个表之间具有良好的驱动条件。你从外循环驱动到内循环，所以执行计划中表的顺序很重要。

外环是驱动行源。它生成一组行来驱动连接条件。行源可以是使用索引扫描或全表扫描来访问的表格。此外，行可以从任何其他操作中生成。例如，嵌套循环连接的输出可以作为另一个嵌套循环连接的行源。

内循环针对从外循环返回的每一行进行迭代，理想情况下通过索引扫描。如果内部循环的访问路径不依赖于外部循环，那么你可以得到一个笛卡尔积;对于外部循环的每次迭代，内部循环产生相同的一组行。因此，当两个独立的行源联合在一起时，应该使用其他联接方法。

嵌套循环加入提示

如果优化器选择使用其他联接方法，那么可以使用USE_NL（table1 table2）提示，其中table1和table2是要联接的表的别名。

对于某些SQL示例，数据足够小，优化器可以选择全表扫描并使用散列连接。第11-26页的示例11-8“哈希联接”中显示的SQL示例就是这种情况。但是，您可以添加USE_NL来指示优化程序将联接方法更改为嵌套循环。有关USE_NL提示的更多信息，请参阅第19-4页的“加入操作的提示”。

嵌套循环

嵌套循环的外层循环本身可以是嵌套循环。您可以嵌套两个或更多个外部循环以根据需要连接多个表。每个循环都是一种数据访问方法，如下所示：

Hash Joins

数据库使用散列连接来连接大型数据集。 优化器使用两个表或数据源中较小的一个在内存中的连接键上构建一个散列表。 然后它扫描较大的表，探测哈希表以查找连接的行。

当较小的表适合可用内存时，此方法是最好的。 然后成本被限制为对两个表的数据的单次读取通过。

优化程序使用哈希联接时

优化程序使用散列连接来连接两个表，如果它们使用equijoin进行连接，并且满足以下任一条件，则：

■必须连接大量数据。

■大部分小表必须连接。

在例11-8中，数据库使用表顺序来构建哈希表。 数据库稍后将扫描较大的order_items。

例11-8哈希连接

SELECT o.customer_id, l.unit_price * l.quantity

FROM orders o ,order_items l WHERE l.order_id = o.order_id;

散列连接提示

将USE_HASH提示应用于指示优化器在将两个表连接在一起时使用散列连接。 请参阅第7-39页的“PGA内存管理”，了解如何调整SQL工作区的大小。 请参阅第19-4页的“加入操作提示”以了解USE_HASH提示。

select /*+ USE_HASH */ o.customer_id, l.unit_price * l.quantity

FROM orders o ,order_items l WHERE l.order_id = o.order_id;

Sort Merge Joins

排序合并连接可以连接来自两个独立源的行。散列连接通常比排序合并连接更好。但是，如果存在以下两个条件，则排序合并连接可以比散列连接执行得更好：

■行源已排序。

■排序操作不必完成。

但是，如果排序合并连接涉及选择较慢的访问方法（索引扫描而不是全表扫描），那么使用排序合并的好处可能会丢失。

当两个表之间的连接条件是不等式条件（如，或> =）时，排序合并连接非常有用。对于大型数据集，排序合并连接的执行效果要好于嵌套循环连接。除非存在相等条件，否则不能使用散列连接。

在合并连接中，没有驾驶台的概念。该联合由两个步骤组成：

1.排序连接操作：两个输入都按连接键排序。

2.合并连接操作：将已排序的列表合并在一起。

如果输入按连接列排序，则不对该行源执行排序连接操作。但是，排序合并连接总是为连接的右侧创建一个可定位的排序缓冲区，以便在重复连接键值从连接左侧出来的情况下，它可以回溯到最后一个匹配。

优化程序使用排序合并联接时

如果满足以下任一条件，则优化器可以通过散列连接选择排序合并连接以加入大量数据：

■两个表之间的连接条件不是等值连接。

■由于其他操作需要的排序，优化器发现使用排序合并比散列连接更便宜。

排序合并加入提示

要指示优化器使用排序合并连接，请应用USE_MERGE提示。您可能还需要提示强制访问路径。

有些情况下，使用USE_MERGE提示覆盖优化器是有意义的。例如，优化器可以选择对表进行全面扫描，并避免查询中的排序操作。但是，由于通过索引和单个块读取来访问大型表，而不是通过全表扫描来更快地访问，所以成本增加。

有关USE_MERGE提示的更多信息，请参阅第19-4页的“加入操作的提示”。

笛卡尔联接

当一个或多个表没有与语句中的任何其他表的联接条件时，数据库使用笛卡尔联接。优化器将来自一个数据源的每一行与来自另一个数据源的每一行进行连接，从而创建两个集合的笛卡尔乘积。

优化程序使用笛卡尔联接时

优化程序在要求连接两个没有连接条件的表时使用笛卡尔联接。在某些情况下，优化器可能会将两个表之间的通用筛选条件视为可能的连接条件。在其他情况下，优化程序可能会决定生成两个非常小的表的笛卡尔积，这两个表都连接到同一个大表。

笛卡尔加入提示

应用ORDERED提示，指示优化器使用笛卡尔连接。通过在指定连接表之前指定表，优化器将执行笛卡尔连接。

外连接

外部联接扩展了简单联接的结果。外连接返回满足连接条件的所有行，并且还从一个表中返回部分或全部行，其中没有行满足连接条件。

嵌套循环外连接

数据库使用此操作来遍历两个表之间的外部联接。即使内部（可选）表中没有相应的行，外部联接也会返回外部（保留的）表中的行。

在常规外部联接中，优化器根据成本选择表（驱动和驱动）的顺序。但是，在嵌套循环外部联接中，联接条件确定表的顺序。数据库使用外部表（保留行）驱动到内部表。

优化器在以下情况下使用嵌套循环联接来处理外部联接：

■可以从外表驱动到内表。

■数据量足够低以使嵌套循环方法有效。

对于嵌套循环外连接的示例，可以将USE_NL提示添加到示例11-9，以指示优化器使用嵌套循环。例如：

SELECT /*+ USE_NL(c o) */ cust_last_name, SUM(NVL2(o.customer_id,0,1)) "Count"

FROM customers c, orders o

WHERE c.credit_limit > 1000

AND c.customer_id = o.customer_id(+)

GROUP BY cust_last_name;

哈希连接外连接

在下列情况下，优化器使用散列连接来处理外连接：

■数据量足够大以使散列联接方法有效。

■不可能从外表驱动到内表。

表格的顺序由成本决定。 外部表（包括保留行）可用于构建散列表，或者可用于探测一个散列表。

例11-9显示了一个使用散列连接外连接的典型查询。 本示例查询信用额度大于1000的所有客户。需要外部联接以避免缺少订单的客户。

例11-9哈希连接外连接

SELECT cust_last_name, SUM(NVL2(o.customer_id,0,1)) "Count"

FROM customers c, orders o

WHERE c.credit_limit > 1000

AND c.customer_id = o.customer_id(+)

GROUP BY cust_last_name;

该查询查找满足各种条件的客户。 当外部联接在内部表中找不到任何对应的行时，外部联接会为内部表列和外部（保留的）表行返回NULL。 此操作查找所有没有任何订单行的客户行。

在这种情况下，外部连接条件如下所示：

customers.customer_id = orders.customer_id（+）

这种情况的组成部分如下所示：

■外表是客户。

■内部表格是订单。

■加入会保留客户行，包括那些没有订单中相应行的行。

您可以使用NOT EXISTS子查询来返回行。 但是，因为您正在查询表中的所有行，散列连接的性能会更好（除非NOT EXISTS子查询未嵌套）。

在例11-10中，外连接是一个多视图。 优化器不能像普通连接一样进入视图或推入谓词，因此它会构建视图的整个行集。

例11-10外部连接到一个多视图

SELECT c.cust_last_name, sum(revenue)

FROM customers c, v_orders o

WHERE c.credit_limit > 2000

AND o.customer_id(+) = c.customer_id

GROUP BY c.cust_last_name;

排序合并外连接

当外部连接无法从外部（保留）表驱动到内部（可选）表时，它不能使用散列连接或嵌套循环连接。然后它使用排序合并外连接来执行连接操作。

优化器对外连接使用排序合并：

■如果嵌套循环连接效率低下。由于数据量的原因，嵌套循环连接可能效率低下。

■优化器发现，由于其他操作所需的排序，对散列连接使用排序合并更便宜。

全外连接

完整的外连接就像左外连接和右外连接的组合。除了内部联接之外，两个表中的行还没有被返回

内部连接被保留并用空值扩展。换句话说，完整的外连接允许您将表连接在一起，但仍显示在连接的表中没有对应行的行。

示例11-11中的查询检索每个部门中的所有部门和所有员工，但还包括：

■没有部门的任何员工

■任何部门没有员工

例11-11全外连接

SELECT d.department_id, e.employee_id

FROM employees e

FULL OUTER JOIN departments d

ON e.department_id = d.department_id

ORDER BY d.department_id;

从Oracle数据库11g开始，Oracle数据库会自动使用基于哈希联接的本机执行方法来尽可能执行完整的外部联接。 当数据库使用新方法执行完整外连接时，查询的执行计划包含HASH JOIN FULL OUTER。 例11-12显示了例11-11中查询的执行计划。

例11-12全外连接的执行计划

注意HASH JOIN FULL OUTER包含在计划中。 因此，查询使用哈希完全外连接执行方法。 通常，当两个表之间的完整外连接条件是等连接时，哈希完全外连接执行方法是可能的，Oracle数据库将自动使用它。

要指示优化器考虑使用哈希完全外连接执行方法，请应用NATIVE_FULL_OUTER_JOIN提示。 要指示优化器不考虑使用哈希完整外连接执行方法，请应用NO_NATIVE_FULL_OUTER_JOIN提示。 NO_NATIVE_FULL_OUTER_JOIN提示指示优化器在连接每个指定的表时排除本机执行方法。 相反，完整的外连接是作为左外连接和反连接的联合来执行的。

四：阅读和理解执行计划

要执行SQL语句，Oracle数据库可能需要执行许多步骤。每个步骤都从数据库中物理地检索数据行，或者以某种方式为发布该语句的用户准备它们。 Oracle数据库用于执行语句的步骤组合是一个执行计划。执行计划包括语句访问的每个表的访问路径以及使用适当的连接方法对表（连接顺序）的排序。

EXPLAIN PLAN概述

您可以使用EXPLAIN PLAN语句检查优化程序为SQL语句选择的执行计划。当语句发布时，优化器会选择一个执行计划，然后将描述该计划的数据插入到数据库表中。只需发出EXPLAIN PLAN语句，然后查询输出表。

这些是使用EXPLAIN PLAN语句的基础知识：

■使用SQL脚本CATPLAN.SQL在模式中创建一个名为PLAN_TABLE的示例输出表。请参阅第12-4页的“PLAN_TABLE输出表”。

■在SQL语句之前包含EXPLAIN PLAN FOR子句。请参阅第12-4页上的“运行EXPLAIN PLAN”。

■发出EXPLAIN PLAN语句后，使用Oracle数据库提供的其中一个脚本或程序包来显示最新的计划表输出。请参阅第12-5页的“显示PLAN_TABLE输出”。

■EXPLAIN PLAN输出中的执行顺序以最右侧的行开始。下一步是该行的父母。如果两行同等缩进，则首行通常首先执行。

注意：

■本章中的EXPLAIN PLAN输出表用utlxpls.sql脚本显示。

■本章中EXPLAIN PLAN输出中的步骤可能与您的系统不同。 优化器可以根据数据库配置选择不同的执行计划。

示例11-13使用EXPLAIN PLAN检查为ID小于103的员工选择employee_id，job_title，salary和department_name的SQL语句。

例11-13使用EXPLAIN PLAN

EXPLAIN PLAN FOR

SELECT e.employee_id, j.job_title, e.salary, d.department_name

FROM employees e, jobs j, departments d

WHERE e.employee_id < 103

AND e.job_id = j.job_id

AND e.department_id = d.department_id;

select * from table(dbms_xplan.display());

示例11-14中的结果输出表显示了优化器在示例中执行SQL语句时选择的执行计划：

执行计划中的步骤

输出表中的每一行都对应于执行计划中的单个步骤。请注意，带有星号的步骤ID在“谓词信息”部分中列出。

执行计划的每一步都会返回一组行。下一步要么使用这些行，要么在最后一步中将行返回给发出SQL语句的用户或应用程序。行集是一个步骤返回的一组行。

步骤ID的编号反映了响应EXPLAIN PLAN语句显示它们的顺序。执行计划的每一步都从数据库中检索行，或接受来自一个或多个行源的行作为输入。

■示例11-14中的以下步骤从数据库中的对象物理检索数据：

■步骤3读取雇员表的所有行。

■步骤5查找JOB_ID_PK索引中的每个job_id，并查找作业表中关联行的rowid。

■步骤4从作业表中检索步骤5返回的rowid行。

■步骤7查找DEPT_ID_PK索引中的每个department_id，并在departments表中查找关联行的rowid。

■步骤6从部门表中检索步骤7返回的具有rowid的行。

■示例11-14中的以下步骤对上一行源返回的行进行操作：

■步骤2对jobs和employees表中的job_id执行嵌套循环操作，接受步骤3和4中的行源，将Step 3源中的每一行连接到步骤4中的相应行，并将结果行返回到步骤2。

■步骤1执行嵌套循环操作，接受步骤2和步骤6中的行源，将步骤2源中的每一行连接到步骤6中对应的行，并将结果行返回到步骤1。

五：控制优化器行为

表11-3列出了可用于控制查询优化器行为的初始化参数。您可以使用这些参数来启用各种优化器功能来提高SQL执行的性能；

Initialization Parameter 初始化参数 Description 描述

1.CURSOR_SHARING：将SQL语句中的文字值转换为绑定变量。 转换这些值可改善游标共享并可影响SQL语句的执行计划。 优化器根据绑定变量的存在而不是实际的文字值生成执行计划。

2.DB_FILE_MULTIBLOCK_READ_COUNT ：指定在全表扫描或索引快速全扫描期间在单个I / O中读取的块数。 优化器使用DB_FILE_MULTIBLOCK_READ_COUNT的值来为全表扫描和索引快速全扫描进行开销。 较大的值会导致整个表扫描的成本更低，并且可能会导致优化程序通过索引扫描选择全表扫描。 如果未明确设置此参数（或设置为0），则默认值对应于可以高效执行且取决于平台的最大I / O大小。

3.OPTIMIZER_INDEX_CACHING ：控制索引探测与嵌套循环的成本计算。 值范围0到100表示缓冲区缓存中索引块的百分比，它修改了优化程序对嵌套循环和IN列表迭代器的索引缓存的假设。 100的值意味着可能在缓冲区缓存中找到100％的索引块，并且优化器相应地调整索引探测或嵌套循环的成本。 使用此参数时请小心谨慎，因为执行计划可以更改为有利于索引缓存。

4.OPTIMIZER_INDEX_COST_ADJ ：调整索引探查的成本。 值的范围是1到10000.默认值是100，这意味着索引将作为基于正常成本模型的访问路径进行评估。 值为10意味着索引访问路径的成本是索引访问路径的正常成本的十分之一。

5.OPTIMIZER_MODE ：设置实例启动时优化器的模式。 可能的值是ALL_ROWS，FIRST_ROWS_n和FIRST_ROWS。 有关这些参数值的说明，请参阅第11-37页上的“设置OPTIMIZER_MODE初始化参数”。

6.PGA_AGGREGATE_TARGET ：控制为排序和散列连接分配的内存量。 分配给排序或散列连接的大量内存减少了这些操作的优化器成本。

7.STAR_TRANSFORMATION_ENABLED ： 使优化器能够对星型查询进行星型转换（如果为true）。 星号转换组合了各种事实表列上的位图索引。

启用查询优化器功能

OPTIMIZER_FEATURES_ENABLE初始化参数启用一系列优化器相关功能，具体取决于版本。 它接受与版本号相对应的有效字符串值列表中的一个，例如10.2.0.1或11.2.0.1。

您可以使用此参数在数据库升级后保留优化器的旧行为。 例如，如果将Oracle Database 11g从发行版本1（11.1.0.7）升级到发行版本2（11.2.0.2），则OPTIMIZER_FEATURES_ENABLE参数的默认值将从11.1.0.7更改为11.2.0.2。 此升级会导致优化器启用基于11.2.0.2的优化功能。

为了向后兼容，您可能不希望查询计划因新版本中的新优化程序功能而发生更改。 在这种情况下，您可以将OPTIMIZER_FEATURES_ENABLE参数设置为较早的版本。

注意：

Oracle不建议明确地将OPTIMIZER_FEATURES_ENABLE参数设置为早期版本。 为避免可能因执行计划更改而导致可能的SQL性能回归，请考虑使用SQL计划管理。 请参阅第15章“使用SQL计划管理”。

设置OPTIMIZER_FEATURES_ENABLE：

1.查询当前的优化器功能设置。

例如，运行以下SQL * Plus命令：

SQL> SHOW PARAMETER optimizer_features_enable

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

optimizer_features_enable string 11.2.0.2

2.在实例或会话级别设置优化程序功能设置。

例如，运行以下SQL语句将优化器版本设置为10.2.0.5：

SQL> ALTER SYSTEM SET optimizer_features_enable = '10 .2.0.5';

前面的语句禁用了版本10.2.0.5之后的版本中添加的所有新优化器功能。 如果您升级到新版本并且想要启用该版本可用的功能，则不需要显式设置OPTIMIZER_FEATURES_ENABLE初始化参数。

选择优化程序目标

您可以通过设置优化程序目标并收集查询优化程序的代表性统计信息来影响优化程序的选择。您可以设置以下优化器目标：

■最佳吞吐量（默认）

数据库使用处理由语句访问的所有行所需的最少量资源。

对于批量执行的应用程序（例如Oracle Reports应用程序），请优化以获得最佳吞吐量。通常，批处理应用程序的吞吐量更重要，因为启动应用程序的用户只关心应用程序完成所需的时间。响应时间并不重要，因为用户在应用程序运行时不检查单个语句的结果。

■最佳的响应时间

数据库使用处理由SQL语句访问的第一行所需的最少量资源。

对于诸如Oracle Forms应用程序或SQL * Plus查询等交互式应用程序，请优化以获得最佳响应时间。通常，响应时间在交互式应用程序中很重要，因为交互式用户正在等待查看语句访问的第一行或前几行。

选择SQL语句的优化方法和目标时的优化器行为受以下因素影响：

■设置OPTIMIZER_MODE初始化参数

■使用提示更改优化程序目标

■数据字典中的优化程序统计信息

设置OPTIMIZER_MODE初始化参数

OPTIMIZER_MODE初始化参数为选择实例的优化方法建立默认行为。表11-4列出了可能的值和说明。

1. ALL_ROWS ：优化程序对会话中的所有SQL语句使用基于成本的方法，无论统计信息是否存在，并以最佳吞吐量（完成整个语句的最少资源使用）为目标进行优化。 这是默认值。

2. FIRST_ROWS_n ： 无论统计信息是否存在，优化器都使用基于成本的方法，并以最佳响应时间为目标进行优化，以返回前n行，其中n等于1,10,100或1000。

3. FIRST_ROWS ： 优化器使用成本和启发式的组合来找到快速交付前几行的最佳计划。

请注意，使用启发式方法有时会导致优化程序生成计划，其成本明显大于计划成本，而不应用启发式算法。 FIRST_ROWS可用于向后兼容性和计划稳定性; 改用FIRST_ROWS_n。

您可以通过更改初始化文件中的参数值或ALTER SESSION SET OPTIMIZER_MODE语句来更改会话中所有SQL语句的查询优化器的目标。 例如：

■初始化参数文件中的以下语句将实例的所有会话的查询优化器的目标设置为最佳响应时间：

OPTIMIZER_MODE = FIRST_ROWS_1

■以下SQL语句将当前会话的查询优化器的目标更改为最佳响应时间：

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_1;

如果优化程序对SQL语句使用基于成本的方法，并且该语句访问的某些表没有统计信息，那么优化程序将使用内部信息（例如分配给这些表的数据块数）来估计其他统计信息 这些表格。

使用提示更改优化程序目标

要为单个SQL语句指定优化程序的目标，请使用表11-5中的提示。 单个SQL语句中的任何这些提示都可以覆盖该SQL语句的OPTIMIZER_MODE初始化参数。

FIRST_ROWS(n)：此提示指示Oracle数据库以最佳响应时间为目标优化单个SQL语句，以返回前n行，其中n等于任何正整数。 无论统计信息是否存在，该提示都使用基于成本的方法来处理SQL语句。

ALL_ROWS：这个提示明确地选择了基于成本的方法来优化SQL语句，以实现最佳吞吐量。

数据字典中的优化程序统计信息

查询优化器使用的统计信息存储在数据字典中。您可以使用DBMS_STATS包来收集有关这些模式对象中的物理存储特征和数据分布的确切或估计统计信息。

为了保持查询优化器的有效性，您必须具有代表数据的统计信息。对于包含重复次数差异较大的值的表格列（称为倾斜数据），您应该收集直方图。

生成的统计信息为查询优化器提供有关数据唯一性和分布的信息。使用这些信息，查询优化器可以高度准确地计算计划成本，并根据最低成本选择最佳执行计划。

默认情况下，在编译SQL语句期间，优化程序会通过考虑可用的统计信息是否足以生成最佳执行计划来自动决定是否使用动态统计信息（请参阅第13-24页上的“优化程序使用动态统计信息时” 。如果可用的统计信息不足，则优化程序将使用动态统计信息来扩充现有的统计信息。

从Oracle数据库11g第2版（11.2.0.4）开始，OPTIMIZER_DYNAMIC_SAMPLING初始化参数具有11的设置，使优化器在认为必要时收集动态统计信息。例如，优化程序可以收集表扫描，索引访问，联接和GROUP BY操作的动态统计信息，从而提高优化程序决策的质量。

评估表达和条件:

优化器首先尽可能完整地评估包含常量的表达式和条件。

声明转换:

对于涉及（例如）相关子查询或视图的复杂语句，优化程序可能会将原始语句转换为等效的联接语句。

优化程序目标的选择:

优化器确定优化的目标。 请参阅第11-36页的“选择优化程序目标”。

访问路径的选择:

对于语句访问的每个表，优化器会选择一个或多个可用访问路径来获取表数据。 请参阅第11-12页的“优化器访问路径概述”。

加入订单的选择:

对于连接两个以上表的连接语句，优化器会选择首先连接哪对表，然后选择哪个表连接到结果，依此类推。 请参阅第11-22页的“查询优化器如何选择联接的执行计划”。

有时，您可能会获得关于特定应用程序数据的更多信息，而不是优化程序可用的信息。 在这种情况下，您可以在SQL语句中使用提示来指导优化程序如何执行语句。

2：Components of the Query Optimizer

查询优化器的组件

The query optimizer operations include:

■Query Transformation

■Estimation

■Plan Generation

查询优化器操作包括：

■查询转换

■估计

■计划生成

查询转换

语句的每个查询部分称为查询块。 查询转换器的输入是一个解析查询，它由一组查询块表示。

在以下示例中，SQL语句由两个查询块组成。 括号内的子查询是内部查询块。 外部查询块是SQL语句的其余部分，用于检索ID由子查询提供的部门中的员工名称。

SELECT first_name, last_name

FROM employees

WHERE department_id

IN (SELECT department_id FROM departments WHERE location_id = 1800);

查询表单确定查询块如何相互关联。 变换器确定将原始SQL语句重写为可以更高效地处理的语义等效SQL语句是否有利。

查询转换器使用多种查询转换技术，包括以下内容：

■View Merging 视图合并

■Predicate Pushing 谓语推送

Subquery Unnesting 子查询解嵌套

■Query Rewrite with Materialized Views 使用物化视图重写查询

这些转换的任何组合都可以应用于给定的查询。

视图合并

分析器将查询中引用的每个视图展开为一个单独的查询块。 该块基本上表示视图定义，并因此表示视图的结果。 优化器的一个选择是分别分析视图查询块并生成视图子计划。 优化器然后通过使用视图子计划来生成整个查询计划来处理查询的其余部分。 这种技术通常会导致次优的查询计划，因为视图是单独优化的。

在视图合并中，变换器将表示视图的查询块合并到包含的查询块中。 例如，假设您创建一个视图，如下所示：

CREATE VIEW employees_50_vw AS

SELECT employee_id, last_name, job_id, salary, commission_pct, department_id

FROM employees

WHERE department_id = 50;

You then query the view as follows:

SELECT employee_id

FROM employees_50_vw

WHERE employee_id > 150;

优化器可以使用视图合并将employees_50_vw的查询转换为以下等效查询：

SELECT employee_id

FROM employees

WHERE department_id = 50

AND employee_id > 150;

视图合并优化适用于仅包含选择，投影和连接的视图。 也就是说，可合并视图不包含集合运算符，聚合函数，DISTINCT，GROUP BY，CONNECT BY等等。

要使优化程序对用户发出的任何查询使用视图合并，您必须向用户授予MERGE ANY VIEW特权。 在特定视图上为用户授予MERGE VIEW特权，以使优化器能够使用视图合并来查看这些视图。 只有在特定条件下才需要这些权限，例如，由于安全检查失败而未合并视图。

谓词推动

在谓词推送中，优化器将来自包含查询块的相关谓词“推入”视图查询块。 对于未合并的视图，此技术改进了未合并视图的子计划，因为数据库可以使用推入谓词来访问索引或用作过滤器。

例如，假设您创建了一个引用两个员工表的视图。 该视图是使用UNION集合运算符的复合查询定义的，如下所示：

CREATE VIEW all_employees_vw AS

( SELECT employee_id, last_name, job_id, commission_pct, department_id

FROM employees )

UNION

( SELECT employee_id, last_name, job_id, commission_pct, department_id

FROM contract_workers );

You then query the view as follows:

SELECT last_name

FROM all_employees_vw

WHERE department_id = 50;

由于视图是复合查询，因此优化器无法将视图的查询合并到访问查询块中。 相反，优化器可以通过将谓词WHERE子句条件department_id = 50推入视图的复合查询来转换访问语句。 等效的转换查询如下所示：

SELECT last_name

FROM ( SELECT employee_id, last_name, job_id, commission_pct, department_id

FROM employees

WHERE department_id=50

UNION

SELECT employee_id, last_name, job_id, commission_pct, department_id

FROM contract_workers

WHERE department_id=50 );

子查询解嵌套

在子查询unnesting中，优化器将嵌套查询转换为等效的联接语句，然后优化联接。 这种转换使优化器能够利用连接优化器技术。 优化器只有在确保生成的连接语句与原始语句完全返回相同的行并且子查询不包含聚合函数（如AVG）时才能执行此转换。

例如，假设您以用户sh连接并执行以下查询：

SELECT *

FROM sales

WHERE cust_id IN ( SELECT cust_id FROM customers );

因为customers.cust_id列是主键，所以优化器可以将复杂查询转换为以下确保返回相同数据的联接语句：

SELECT sales.*

FROM sales, customers

WHERE sales.cust_id = customers.cust_id;

如果优化程序无法将复杂语句转换为连接语句，那么它将为父语句和子查询选择执行计划，就像它们是单独的语句一样。 优化器然后执行子查询并使用返回的行来执行父查询。 为了提高整个查询计划的执行速度，优化程序有效地订调用子计划。

使用物化视图进行查询重写

物化视图就像一个查询，其结果是数据库实现并存储在一个表中。 当数据库发现用户查询与与物化视图关联的查询兼容时，则数据库可以根据物化视图重写查询。 这种技术改进了查询执行，因为大部分查询结果都是预先计算好的。

查询转换器查找任何与用户查询兼容的物化视图，并选择一个或多个物化视图来重写用户查询。 使用物化视图重写查询是基于成本的。 也就是说，如果未使用实例化视图生成的计划比使用实例化视图生成的计划成本更低，则优化器不会重写查询。

考虑以下物化视图cal_month_sales_mv，它汇总了每月销售的美元数量：

CREATE MATERIALIZED VIEW cal_month_sales_mv

ENABLE QUERY REWRITE

AS

SELECT t.calendar_month_desc, SUM(s.amount_sold) AS dollars

FROM sales s, times t

WHERE s.time_id = t.time_id

GROUP BY t.calendar_month_desc;

假设典型月份的销售数量约为100万。 该视图具有针对每个月销售的美元数量的预计算总计。 考虑下面的查询，该查询要求每月销售金额的总和：

SELECT t.calendar_month_desc, SUM(s.amount_sold)

FROM sales s, times t

WHERE s.time_id = t.time_id

GROUP BY t.calendar_month_desc;

如果没有查询重写，数据库必须直接访问销售额并计算出售金额的总和。 这种方法涉及从销售中读取数百万行，这总会增加查询响应时间。 连接还会进一步减慢查询响应，因为数据库必须计算数百万行上的连接。 通过查询重写，优化器透明地重写查询，如下所示：

SELECT calendar_month, dollars

FROM cal_month_sales_mv;

估计

估算器确定给定执行计划的总成本。估算人员产生三种不同类型的措施来实现这一目标：

■选择性

此度量表示行集合中的一小部分行。选择性与查询谓词相关联，如last_name ='Smith'或谓词组合。

■基数

该度量表示行集合中的行数。

■成本

该度量表示所使用的工作单位或资源。查询优化器使用磁盘I / O，CPU使用率和内存使用量作为工作单位。

如果统计数据可用，则估算人员使用它们来计算这些度量。统计数据提高了措施的准确程度。

选择性

选择性表示行集合中的一小部分行。行集可以是基表，视图或连接或GROUP BY运算符的结果。选择性与查询谓词相关联，例如last_name ='Smith'，或谓词组合，例如last_name ='Smith'AND job_type ='Clerk'。

谓词过滤行集中特定数量的行。因此，谓词的选择性指示有多少行通过谓词测试。选择性范围从0.0到1.0。选择性为0.0意味着从行集合中不选择行，而选择性为1.0意味着所有行都被选中。当值接近1.0时，谓词变得更具选择性，而当值接近1.0时，谓词变得更少选择性（或更不具选择性）。

优化器根据是否有统计信息来估计选择性：

■统计数据不可用

根据OPTIMIZER_DYNAMIC_SAMPLING初始化参数的值，优化器可以使用动态统计或内部默认值。数据库根据谓词类型使用不同的内部默认值。例如，等式谓词（last_name ='Smith'）的内部缺省值比范围谓词（last_name>'Smith'）的内部缺省值低，因为等同谓词预计会返回一小部分行。请参阅第13-22页的“控制动态统计”。

■可用统计数据

当统计数据可用时，估算人员使用它们估算选择性。假设有150个不同的员工姓氏。对于相等谓词last_name ='Smith'，选择性是last_name的不同值的数量n的倒数，在本例中为.006，因为查询选择包含150个不同值中的1个的行。

如果直方图可用于last_name列，则估计器使用直方图而不是不同值的数量。柱状图捕获列中不同值的分布，因此它可以产生更好的选择性估计值，特别是对于包含倾斜数据的列。请参阅第13-28页的“查看直方图”。

基数

基数表示行集合中的行数。 在此上下文中，行集可以是基表，视图或联接或GROUP BY运算符的结果。

成本

成本代表作业中使用的工作单位或资源。优化器使用磁盘I / O，CPU使用率和内存使用量作为工作单位。该操作可以扫描表，通过使用索引访问表中的行，将两个表连接在一起或排序行集。成本是数据库执行查询并产生结果时预期会发生的工作单元数量。

访问路径决定了从基表获取数据所需的工作单元数量。访问路径可以是表扫描，快速全索引扫描或索引扫描。

■表扫描或快速全索引扫描

在表扫描或快速全索引扫描期间，数据库在单个I / O中从磁盘读取多个块。因此，扫描的成本取决于要扫描的块数和多块读取计数值。

■索引扫描

索引扫描的成本取决于B树中的级别，要扫描的索引叶块数量以及使用索引键中的rowid获取的行数。使用rowid获取行的成本取决于索引聚类因子。请参阅第11-16页的“评估块的I / O，而不是行”。

联合成本表示所连接的两个行集的单独访问成本加上联合操作的成本的组合。

计划生成

计划生成器通过尝试不同的访问路径，连接方法和连接顺序来探索查询块的各种计划。许多计划是可能的，因为数据库可以使用不同的访问路径，连接方法和连接顺序的各种组合来产生相同的结果。发电机的目的是选择成本最低的方案。

连接顺序 join order

连接顺序是不同的连接项（例如表）被访问并连接在一起的顺序。假定数据库连接了table1，table2和table3。连接顺序可能如下：

1.The database accesses table1.

2.The database accesses table2 and joins its rows to table1.

3.The database accesses table3 and joins its data to the result of the join between table1 and table2.

查询子计划

优化器通过单独的查询块表示每个嵌套子查询或未合并视图，并生成一个子计划。数据库分别从下往上分别优化查询块。因此，数据库首先优化最内层的查询块并为其生成一个子计划，然后最后生成代表整个查询的外层查询块。

查询块的可能计划数与FROM子句中的连接项数成正比。这个数字随着加入项目的数量呈指数增长。例如，五个表格连接的可能计划将显着高于两个表格连接的可能计划。

截止计划选择

计划生成器使用内部截断来减少在查找最低成本计划时尝试的计划数量。截止时间取决于当前最佳计划的成本。如果当前最好的成本很高，那么计划生成器会探索替代计划来找到一个更低成本的计划。如果当前的最佳成本很小，那么发电机会迅速结束搜索，因为进一步的成本改善不会很大。

如果计划生成器以初始连接顺序开始，生成成本接近最优的计划，则截断工作良好。找到一个好的初始连接顺序是一个难题。

3：Bind Variable Peeking

绑定变量窥视

在绑定变量窥视（也称为绑定窥视）中，当数据库对语句执行硬解析时，优化器将查看绑定变量中的值。

当查询使用文字时，优化器可以使用文字值来查找最佳方案。 但是，当查询使用绑定变量时，优化器必须选择最佳方案，而不在SQL文本中存在文字。 这项任务可能非常困难。 通过查看绑定值，优化器可以确定WHERE子句条件的选择性，就像文字已被使用一样，从而改进计划。

假设数据库中存在以下100,000行的emp表。 该表具有以下定义：

数据在deptno列中显着偏斜。 值99在99.9％的行中找到。 每个其他的deptno值（0到9）都在1％的行中找到。 您已收集表格的统计数据，从而在deptno列上生成直方图。 您可以使用绑定值9定义绑定变量并查询emp，如下所示：

VARIABLE deptno NUMBER

EXEC :deptno := 9

SELECT /*ACS_1*/ count(*), max(empno)

FROM emp

WHERE deptno = :deptno;

The query returns 10 rows:

COUNT(*) MAX(EMPNO)

---------- ----------

10 99

为了生成查询的执行计划，在硬解析期间数据库的值为9。 优化器生成选择性估计值，就好像用户执行了以下查询一样：

select /*ACS_1*/ count(*), max(empno)

from emp

where deptno = 9;

在选择计划时，优化器仅在硬解析期间查看绑定值。 这个计划对于所有可能的价值可能不是最佳的。

由于绑定变量巡视依赖于第一次巡视到的变量值，然后按照这个字面值生成了最优执行计划，一旦更换了变量值，可能还会按照第一次的执行计划执行，从而不一定最佳，所以有了下面的自适应游标共享的概念。

Adaptive Cursor Sharing

自适应游标共享

自适应光标共享功能使包含绑定变量的单个语句可以使用多个执行计划。 游标共享是“自适应的”，因为游标适应其行为，因此数据库并不总是对每次执行使用相同的计划或绑定变量值。

对于适当的查询，数据库监控随着时间的推移访问不同绑定值的数据，确保针对特定绑定值的光标的最佳选择。 例如，优化程序可能为绑定值9选择一个计划，为绑定值10选择另一个计划。游标共享是“自适应”的，因为游标会调整其行为，以便每次执行或绑定变量值时都不会使用相同的计划。

数据库默认启用了自适应光标共享，无法禁用。 请注意，自适应光标共享不适用于包含14个以上绑定变量的SQL语句。

注意：

自适应光标共享独立于CURSOR_SHARING初始化参数（请参阅第7-36页的“共享现有应用程序的光标”）。 自适应光标共享同样适用于包含用户定义的和系统生成的绑定变量的语句。

绑定敏感游标

绑定敏感游标是一个游标，其最佳计划可能取决于绑定变量的值。 数据库监视使用不同绑定值的绑定敏感游标的行为，以确定不同的计划是否有益。

优化器用来决定游标是否是绑定敏感的标准包括以下内容：

优化器偷看绑定值以生成选择性估计值。

■包含绑定值的列上存在直方图。

例11-2绑定敏感游标

在例11-1中，您使用deptno的绑定值9来查询emp表。 现在运行DBMS_XPLAN.DISPLAY_CURSOR函数来显示查询计划：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

The plan indicates that the optimizer chose an index range scan, which is expected because of the selectivity (only 1%) of the value 9. You can query V$SQL to view statistics about the cursor:

COL BIND_SENSI FORMAT a10

COL BIND_AWARE FORMAT a10

COL BIND_SHARE FORMAT a10

SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",

IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"

FROM V$SQL

WHERE SQL_TEXT LIKE 'select /*ACS_1%';

如以下输出所示，此语句存在一个子游标，并且已执行一次。 少量的缓冲区获取与子游标相关联。 由于deptno数据偏斜，数据库创建了一个直方图。 这个直方图导致数据库将光标标记为绑定敏感（IS_BIND_SENSITIVE为Y）。

对于每次执行具有新绑定值的查询，数据库都将记录新值的执行统计信息，并将它们与先前值的执行统计信息进行比较。 如果执行统计数据变化很大，那么数据库将标记游标绑定感知。

Bind-Aware Cursors

一个绑定感知游标是一个绑定敏感游标，可以为不同的绑定值使用不同的计划。 在游标进行绑定感知之后，优化程序将根据绑定值及其选择性估计值为将来的执行选择计划。

当执行带有绑定敏感游标的语句时，数据库将决定是否将游标绑定感知标记。 这个决定取决于游标是否为不同的绑定值产生显着不同的数据访问模式。 如果数据库标记了游标绑定感知，则下一次游标执行数据库时会执行以下操作：

■基于新的绑定值生成新计划。

■将为该语句生成的原始游标标记为不可共享（V $ SQL.IS_SHAREABLE为N）。 该游标不再可用，并且将成为共享SQL区域中第一批老化的游标。

例11-3绑定感知游标

在示例11-1中，您使用绑定值9查询了emp。现在使用绑定值10查询emp。查询返回包含值10的99,900行：

由于此语句的游标是绑定敏感的，因此优化程序假定游标可以共享。 因此，对于值9，优化器对值10使用相同的索引范围扫描。

V $ SQL输出显示第二次执行相同的绑定敏感游标（查询使用10）并且需要比第一次执行更多的缓冲区获取：

SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",

IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"

FROM V$SQL

WHERE SQL_TEXT LIKE 'select /*ACS_1%';

统计以前的执行情况并将光标标记为绑定感知。 在这种情况下，优化器决定新的计划是有保证的，所以它会对语句进行硬解析并生成新的计划。 新计划使用全表扫描而不是索引范围扫描：

V $ SQL的查询显示数据库创建了一个表示包含全表扫描的计划的额外子游标（子代号1）。 这个新的游标显示较少数量的缓冲区获取并且被标记为绑定感知：

SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",

IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"

FROM V$SQL

WHERE SQL_TEXT LIKE 'select /*ACS_1%';

执行查询两次后，值为10，再次使用更具选择性的值9执行该操作。由于自适应光标共享，优化程序会“调整”光标并选择索引范围扫描，而不是对此值执行全表扫描。

V $ SQL的查询表明数据库为查询的执行创建了一个新的子游标（子数字2）：

由于数据库现在使用自适应光标共享，因此数据库不再使用原始光标（子级0），该光标不具有绑定意识。 共享的SQL区域会使已停用的游标变老。

游标合并

如果优化器为绑定感知游标创建一个计划，并且该计划与现有游标相同，则优化器可以执行游标合并。 在这种情况下，数据库合并游标以节省共享SQL区域中的空间。 数据库增加了光标的选择性范围，以包含新绑定的选择性。

假设您执行的查询的绑定值不在现有游标的选择性范围内。 数据库执行硬解析并生成新计划和新游标。 如果此新计划与现有游标使用的计划相同，则数据库合并这两个游标并删除其中一个旧游标。

查看绑定相关的性能数据

您可以使用V $视图进行适应性光标共享，以查看选择性范围，光标信息（例如游标是否为绑定感知或绑定敏感）以及执行统计信息：

■V $ SQL显示游标是绑定敏感的还是绑定感知的

■V $ SQL_CS_HISTOGRAM显示跨三个执行历史记录直方图的执行计数分布

■如果选择性用于检查光标共享，则V $ SQL_CS_SELECTIVITY将显示为包含绑定变量的每个谓词存储的选择性范围

■V $ SQL_CS_STATISTICS总结优化器用于确定是否标记游标绑定感知的信息。

二：Overview of Optimizer Access Paths 优化器访问路径概述

访问路径是从数据库中检索数据的方式。通常，索引访问路径对于检索表格行的一小部分子集的语句很有用，而在访问大部分表格时全部扫描效率更高。联机事务处理（OLTP）应用程序由具有高选择性的短期运行SQL语句组成，通常以使用索引访问路径为特征。但是，决策支持系统倾向于使用分区表并对相关分区执行完整扫描。

本节介绍数据库可用于查找和检索任何表中的任何行的数据访问路径。

■全表扫描

■Rowid扫描

■索引扫描

■群集访问

■散列访问

■样本表扫描

■查询优化器如何选择访问路径

全表扫描

这种类型的扫描会读取表中的所有行，并过滤掉那些不符合选择条件的行。在全表扫描期间，扫描表中高位标记下的所有块。高水位标记表示已使用空间的数量或已格式化为接收数据的空间。检查每一行以确定它是否满足语句的WHERE子句。

当Oracle数据库执行全表扫描时，将按顺序读取这些块。由于这些块是相邻的，因此数据库可以使I / O调用大于单个块来加速该过程。读取调用的大小范围从一个块到由初始化参数DB_FILE_MULTIBLOCK_READ_COUNT指示的块数。使用多块读取，数据库可以非常有效地执行全表扫描。数据库只读取一次每个块。

示例11-14，第11-33页的“EXPLAIN PLAN输出”包含employees表上的全表扫描示例。

为什么全表扫描更快速地访问大量数据

访问表格中大部分块时，全表扫描比索引范围扫描便宜。全表扫描可以使用更大的I / O调用，并且减少大量I / O调用比调用更小的调用便宜。

优化程序使用全表扫描时

优化器在以下任何情况下都使用全表扫描：

缺乏索引

如果查询不能使用现有索引，则它使用全表扫描。例如，如果在查询中索引列上有一个函数，那么优化器不能使用索引，而是使用全表扫描。

如果您需要将索引用于区分大小写的搜索，则不要在搜索列中允许混合大小写数据，也不要在搜索列上创建基于函数的索引（如UPPER（last_name））。请参阅第14-7页的“使用基于功能的索引进行性能”。

大数据量

如果优化器认为查询需要表中的大部分块，那么即使索引可用，它也会使用全表扫描。

小表

如果一个表的数据库可以在单个I / O调用中读取的高水位标记下包含少于DB_FILE_MULTIBLOCK_READ_COUNT块，那么全表扫描可能比索引范围扫描便宜，无论正在访问的表的分数是多少还是索引存在。

平行度高

表格的高度并行性使优化器偏向范围扫描的全表扫描。检查ALL_TABLES中的DEGREE列以确定并行度。

全表扫描提示

使用提示FULL（表别名）来指示优化器使用全表扫描。有关FULL提示的更多信息，请参阅第19-3页的“访问路径的提示”。

您可以使用CACHE和NOCACHE提示来指示检索到的块放置在缓冲区缓存中的位置。 CACHE提示指示优化器在数据库执行全表扫描时，将检索到的块放在缓冲区高速缓存中最近使用的LRU列表末尾。

小表根据表11-2中的标准自动缓存。

对于使用CACHE属性创建或更改的表，禁用对小表的自动缓存。

并行查询执行

当需要全表扫描时，数据库可以通过使用多个并行执行服务器来缩短响应时间。 在某些情况下，当数据库具有大量内存时，数据库可以在SGA中缓存并行查询数据，而不是使用直接读取到PGA中。 通常，由于潜在的资源使用情况，并发查询会发生在低并发性数据仓库中。

Rowid扫描

行的rowid指定包含行和该块中行的位置的数据文件和数据块。通过指定rowid来查找行是检索单个行的最快方法，因为数据库中该行的确切位置是指定的。

要通过rowid访问表，Oracle数据库首先会从语句的WHERE子句或通过索引扫描一个或多个表索引来获取所选行的rowid。 Oracle数据库然后根据其rowid查找表中的每个选定行。

在示例11-14“解释计划输出”（第11-33页）中，计划包括对作业和部门表的索引扫描。数据库使用检索的rowid返回行。

优化器使用Rowid时

这通常是从索引中检索rowid之后的第二步。索引中不存在的任何列可能需要表访问权限。

通过rowid访问并不需要遵循每个索引扫描。如果索引包含该语句所需的所有列，则可能不会发生rowid表访问。

注意：

Rowid是数据库存储数据的内部表示。 Rowid可以在版本之间更改。 不建议访问基于位置的数据，因为由于行迁移和链接，导出和导入以及其他一些操作，行可以四处移动。 外键应该基于主键。 有关rowid的更多信息，请参阅Oracle数据库高级应用程序开发人员指南。

索引扫描

在此方法中，通过使用由语句指定的索引列值遍历索引来检索行。索引扫描根据索引中一个或多个列的值从索引中检索数据。要执行索引扫描，Oracle数据库将在索引中搜索由语句访问的索引列值。如果语句仅访问索引的列，则Oracle数据库直接从索引读取索引列值，而不是从表中读取索引列值。

索引不仅包含索引值，还包含表中具有该值的行的rowid。因此，如果语句除了索引列之外还访问其他列，那么Oracle数据库可以通过使用rowid访问的表或集群扫描来查找表中的行。

索引扫描可以是以下类型之一：

■评估块的I / O，而不是行

■索引唯一扫描

■索引范围扫描

■索引范围扫描降序

■索引跳过扫描

■完整扫描

■快速全索引扫描

■索引连接

■位图索引

评估块的I / O，而不是行

Oracle数据库按块执行I / O。因此，优化器使用全表扫描的决定受访问块百分比的影响，而不是行。这被称为索引聚类因子。如果块包含单个行，则访问的行和访问的块是相同的。

但是，大多数表格在每个块中都有多行。因此，所需数量的行可以聚集在几个块中或分布在更多数量的块上。

虽然聚类因子是索引的一个属性，但聚类因子实际上与表中数据块中类似索引列值的分布有关。较低的聚类因子表示单个行集中在表中较少的块内。相反，高聚类因子表示单个行在表中的块间更随机地分散。因此，高聚簇因子意味着使用范围扫描以rowid获取行花费更高，因为需要访问表中更多块以返回数据。例11-4显示了聚类因子如何影响成本。

例11-4聚类因子对成本的影响

假设以下情况：

■有9行的表格。

■表格的col1上有一个非唯一索引。

■c1列当前存储值A，B和C.

■表格只有三个数据块。

情况1：索引聚类因子在行中较低，因为它们排列在下图中。

这是因为c1具有相同索引列值的行位于表中相同的物理块内。 使用范围扫描返回所有具有值A的行的成本很低，因为只能读取表中的一个块。

情况2：如果表格中的相同行重新排列，以便索引值散布在表格块（而不是并置）上，则索引聚类因子更高。

这是因为必须读取表中的所有三个块才能检索col1中值为A的所有行。

索引唯一扫描

此扫描最多返回一个rowid。 如果语句包含保证仅访问单个行的UNIQUE或PRIMARY KEY约束，Oracle数据库将执行唯一扫描。

在第11-33页的“EXPLAIN PLAN输出”中，数据库分别使用job_id_pk和dept_id_pk索引对作业和部门表执行索引扫描。

优化程序使用索引唯一扫描时

当用户指定唯一（B-tree）索引的所有列或作为主键约束条件的结果创建的索引时，数据库使用此访问路径。

索引唯一扫描提示

一般来说，您不需要使用提示来执行独特的扫描。可能会出现表跨越数据库链接并从本地表访问的情况，或者表的表足够小以使优化器更喜欢全表扫描。

提示INDEX（别名index_name）指定要使用的索引，但不指定访问路径（范围扫描或唯一扫描）。有关INDEX提示的更多信息，请参阅第19-3页的“访问路径的提示”。

索引范围扫描

索引范围扫描是访问选择性数据的常用操作。它可以是有界的（两边都有界限），也可以是无界的（一边或两边）。数据以索引列的升序返回。具有相同值的多行按rowid升序排列。

如果您需要按顺序对数据进行排序，则使用ORDER BY子句，而不要依赖索引。如果索引可以满足ORDER BY子句，那么优化器将使用此选项并避免排序。

在示例11-5中，订单已从旧系统导入，并且您正在使用旧系统中使用的参考查询订单。假定这个参考是order_date。

例11-5索引范围扫描

SELECT order_status, order_id

FROM orders

WHERE order_date = :b1;

这应该是一个高度选择性的查询，并且您应该使用列上的索引查看查询以检索所需的行。返回的数据按order_date的rowid升序排列。由于索引列order_date对于此处选定的行是相同的，因此数据按rowid排序。

优化程序使用索引范围扫描时

优化程序在找到一个或多个在条件中指定的索引的前导列时使用范围扫描，如下所示：

■col1 =：b1

■col1

■col1>：b1

■索引中前导列的前述条件的组合

■col1就像'ASD％'通配符搜索不应该处于领先位置，否则像'％ASD'这样的条件col1不会导致范围扫描

范围扫描可以使用唯一索引或非唯一索引。 当索引列构成ORDER BY / GROUP BY子句时，范围扫描避免排序。

索引范围扫描提示

如果优化器选择其他索引或使用全表扫描，则可能需要提示。 提示INDEX（table_alias index_name）指示优化器使用特定的索引。 有关INDEX提示的更多信息，请参阅第19-3页的“访问路径的提示”。

Index Range Scans Descending 索引范围扫描降序

索引范围扫描降序与索引范围扫描相同，只是数据以降序返回。默认情况下，索引按升序存储。通常，数据库在按降序排序数据时首先使用此扫描来返回最新数据，或者在查找小于指定值的值时使用此扫描。

优化程序使用索引范围扫描下降时

当索引可以通过降序子句满足顺序时，优化器使用索引范围扫描降序。

索引范围扫描降序提示

对此访问路径使用提示INDEX_DESC（table_alias index_name）。有关INDEX_DESC提示的更多信息，请参阅第19-3页的“访问路径的提示”。

索引跳过扫描 Index Skip Scans

索引跳过扫描可以改善非前缀列的索引扫描。通常，扫描索引块比扫描表数据块快。

跳过扫描可以将复合索引逻辑分割为更小的子索引。在跳过扫描中，查询中未指定组合索引的初始列。换句话说，它被跳过。

数据库根据初始列中不同值的数量确定逻辑子索引的数量。当组合索引的前导列中有很少不同的值并且索引的非导向键中有许多不同的值时，跳过扫描是有利的。

当查询谓词中没有指定组合索引的前导列时，数据库可以选择索引跳过扫描。例如，假定您在sh.customers表中为客户运行以下查询：

SELECT * FROM sh.customers WHERE cust_email ='Abbey@company.com';

customers表中有一个cust_gender列，其值为M或F.假设在列（cust_gender，cust_email）上存在一个组合索引，其创建方式如下：

CREATE INDEX customers_gender_email ON sh.customers（cust_gender，cust_email）;

Example 11–6 Composite Index Entries

F,Wolf@company.com,rowid

F,Wolsey@company.com,rowid

F,Wood@company.com,rowid

F,Woodman@company.com,rowid

F,Yang@company.com,rowid

F,Zimmerman@company.com,rowid

M,Abbassi@company.com,rowid

M,Abbey@company.com,rowid

即使未在WHERE子句中指定cust_gender，数据库也可以使用此索引的跳过扫描。

在跳过扫描中，逻辑子索引的数量由前导列中不同值的数量决定。 在例11-6中，前导列有两个可能的值。 数据库在逻辑上用索引F将索引拆分成一个子索引，用索引M将索引拆分成第二个子索引。

在搜索电子邮件为Abbey@company.com的客户的记录时，数据库将使用值F first搜索子索引，然后使用值M搜索子索引。从概念上讲，数据库按如下方式处理查询：

SELECT * FROM sh.customers WHERE cust_gender = 'F'

AND cust_email = 'Abbey@company.com'

UNION ALL

SELECT * FROM sh.customers WHERE cust_gender = 'M'

AND cust_email = 'Abbey@company.com';

full scans 全面扫描

完整索引扫描消除了排序操作，因为数据是通过索引键排序的。它单独读取块。在以下任何情况下，Oracle数据库可能会使用完整扫描：

■查询中存在符合以下要求的ORDER BY子句：

- ORDER BY子句中的所有列必须位于索引中。

- ORDER BY子句中列的顺序必须与前导索引列的顺序相匹配。

ORDER BY子句可以包含索引中的所有列或索引中列的子集。

■查询需要排序合并连接。数据库可以执行完整索引扫描，而不是执行全表扫描，然后在查询满足以下要求时进行排序：

- 查询中引用的所有列必须位于索引中。

- 查询中引用的列的顺序必须与前导索引列的顺序相匹配。

查询可以包含索引中的所有列或索引中列的子集。

■查询中存在GROUP BY子句，并且GROUP BY子句中的列存在于索引中。列不需要在索引和GROUP BY子句中具有相同的顺序。 GROUP BY子句可以包含索引中的所有列或索引中列的子集。

Fast Full Index Scans

快速全索引扫描

当索引包含查询所需的所有列时，快速全索引扫描是全表扫描的替代方法，并且索引键中至少有一列具有NOT NULL约束。快速全扫描访问索引本身中的数据，而不访问表格。数据库无法使用此扫描消除排序操作，因为数据不是由索引键排序的。与完整索引扫描不同，数据库使用多块读取读取整个索引，并且可以并行扫描。

您可以使用初始化参数OPTIMIZER_FEATURES_ENABLE或INDEX_FFS提示指定快速完整索引扫描。快速全扫描比正常的全索引扫描更快，因为它可以使用多块I / O并且可以像表扫描一样并行运行。

快速全索引扫描提示

快速全扫描具有特殊的索引提示INDEX_FFS，它具有与常规INDEX提示相同的格式和参数。有关INDEX_FFS提示的更多信息，请参阅第19-3页的“访问路径的提示”。

索引连接

索引连接是几个索引的散列连接，它们一起包含查询中引用的所有表列。如果数据库使用索引连接，则不需要表访问，因为数据库可以从索引检索所有相关的列值。数据库不能使用索引连接来消除排序操作。

索引加入提示

您可以使用INDEX_JOIN提示指定索引连接。有关INDEX_JOIN提示的更多信息，请参阅第19-3页的“访问路径的提示”。

位图索引

位图连接使用位图的键值和映射函数将每个位的位置转换为rowid。位图可以高效地合并对应于WHERE子句中几个条件的索引，并使用布尔运算来解析AND和OR条件。

注意：

位图索引和位图连接索引仅在Oracle企业版中可用。

Cluster Access 集群访问

数据库使用群集扫描从存储在索引群集中的表中检索具有相同群集键值的所有行。在索引集群中，数据库将具有相同集群键值的所有行存储在同一个数据块中。要执行群集扫描，Oracle数据库首先通过扫描群集索引来获取所选行之一的rowid。 Oracle数据库然后根据此rowid查找行。

Hash Access 哈希访问

数据库使用散列扫描根据散列值在散列群集中查找行。在散列簇中，具有相同散列值的所有行都存储在同一个数据块中。要执行散列扫描，Oracle数据库首先通过将散列函数应用于由语句指定的群集键值来获取散列值。 Oracle数据库然后扫描包含具有该散列值的行的数据块。

Sample Table Scans 示例表扫描

示例表扫描从简单表或复杂SELECT语句中检索随机数据样本，如涉及连接和视图的语句。当语句的FROM子句包含SAMPLE子句或SAMPLE BLOCK子句时，数据库将使用此访问路径。要在使用SAMPLE子句按行进行采样时执行示例表扫描，数据库将读取表中指定百分比的行。要在使用SAMPLE BLOCK子句按块进行采样时执行示例表扫描，数据库将读取指定百分比的表块。

示例11-7使用示例表扫描访问雇员表的1％，按块进行抽样。

例11-7样本表扫描

SELECT *

FROM employees SAMPLE BLOCK (1);

查询优化器如何选择访问路径

查询优化器根据以下因素选择访问路径：

■语句的可用访问路径

■执行语句的估计成本，使用每个访问路径或路径组合

要选择访问路径，优化程序首先通过检查语句的WHERE子句及其FROM子句中的条件来确定哪些访问路径可用。然后优化器使用可用的访问路径生成一组可能的执行计划，并使用该语句可访问的索引，列和表的统计信息来估计每个计划的成本。最后，优化器选择具有最低估计成本的执行计划。

在选择访问路径时，查询优化器受以下因素影响：

■优化器提示

您可以指示优化器使用提示使用特定的访问路径，除非语句的FROM子句包含SAMPLE或SAMPLE BLOCK。

旧统计

例如，如果一个表自创建之后还没有被分析过，并且它在高水位标记下的DB_FILE_MULTIBLOCK_READ_COUNT块数少于一个，那么优化器认为该表很小并且使用全表扫描。查看ALL_TABLES表中的LAST_ANALYZED和BLOCKS列以检查统计信息。

三：连接概述

连接是从多个表中检索数据的语句。一个连接的特点是在FROM子句中有多个表。 WHERE子句中存在连接条件定义了表之间的关系。在一个连接中，一个行集称为inner，另一个称为outer。

本节讨论：

■查询优化器如何执行联接语句

■查询优化器如何为连接选择执行计划

■嵌套循环连接

■哈希连接

■排序合并联接

■笛卡尔联接

■外连接

查询优化器如何执行联接语句

为了选择加入语句的执行计划，优化器必须做出这些相关的决定：

■访问路径

至于简单的语句，优化器必须选择一个访问路径来从连接语句中的每个表检索数据。

■加入方法

要加入每对行源，Oracle数据库必须执行连接操作。连接方法包括嵌套循环，排序合并，笛卡尔和哈希连接。

■Join Order

要执行连接两个以上表的语句，Oracle数据库将连接两个表，然后将生成的行源连接到下一个表。这个过程一直持续到所有表都被加入到结果中。

查询优化器如何为连接选择执行计划

查询优化器在选择执行计划时会考虑以下内容：

■优化程序首先确定连接两个或更多表是否肯定会导致包含至多一行的行源。优化器根据表上的UNIQUE和PRIMARY KEY约束来识别这种情况。如果存在这种情况，那么优化器首先将这些表放入连接顺序中。优化器然后优化剩下的一组表的连接。

■对于具有外部连接条件的连接语句，具有外部连接运算符的表必须位于连接顺序中条件中的另一个表之后。优化器不考虑违反此规则的连接命令。同样，当子查询已转换为反连接或半连接时，子查询中的表必须位于它们连接或关联的外部查询块中的那些表之后。但是，在某些情况下，哈希反连接和半连接能够覆盖此排序条件。

通过查询优化器，优化器根据可能的连接顺序，连接方法和可用访问路径生成一组执行计划。优化器然后估计每个计划的成本并选择成本最低的那个。优化器通过以下方式估算成本：

■嵌套循环操作的成本基于将外部表的每个选定行及其内部表的每个匹配行读入内存的成本。优化器使用数据字典中的统计信息估算这些成本。

■排序合并连接的成本很大程度上取决于将所有资源读入内存并对其进行排序的成本。

■散列连接的成本很大程度上取决于在连接的其中一个输入侧上构建散列表的成本，并使用来自另一连接的行来探测它。

优化程序在确定每个操作的成本时也会考虑其他因素。例如：

■较小的排序区域大小可能会增加排序合并连接的成本，因为排序会在更小的排序区域中占用更多的CPU时间和I / O。请参阅第7-39页的“PGA内存管理”，了解如何调整SQL工作区的大小。

■较大的多块读取计数可能会降低排序合并连接相对于嵌套循环连接的成本。 如果数据库可以在单个I / O中从磁盘读取大量连续块，那么嵌套循环连接的内部表上的索引不太可能提高整个表扫描的性能。 多块读取计数由初始化参数DB_FILE_MULTIBLOCK_READ_COUNT指定。

您可以使用ORDERED提示覆盖优化器对连接订单的选择。 如果ORDERED提示指定了违反外连接规则的连接顺序，那么优化器会忽略该提示并选择顺序。 此外，您可以使用提示覆盖优化器对连接方法的选择。

嵌套循环连接

满足以下条件时，嵌套循环联接很有用：

■数据库连接数据的小部分。

■连接条件是访问第二个表的有效方法。

确保内表从外表驱动（依赖于）是很重要的。如果内部表的访问路径与外部表无关，那么对于外部循环的每次迭代都会检索相同的行，从而大大降低性能。在这种情况下，连接两个独立行源的散列连接表现更好。

嵌套循环连接涉及以下步骤：

1.优化器确定驱动表并将其指定为外部表。

2.另一个表被指定为内部表。

3.对于外部表中的每一行，Oracle数据库都会访问内部表中的所有行。外循环用于外表中的每一行，内循环用于内表中的每一行。外循环出现在执行计划的内循环之前，如下所示：

NESTED LOOPS

outer_loop

inner_loop

嵌套循环连接的原始和新实现

Oracle数据库11g为嵌套循环连接引入了一个新的实现。 因此，包含嵌套循环的执行计划可能会与先前版本的Oracle数据库中的执行计划不同。 在Oracle数据库11g中，新实现和嵌套循环联接的原始实现都是可能的。 因此，在分析执行计划时，理解NESTED LOOPS连接行源的数量可能会有所不同。

嵌套循环联接的原始实现

考虑以下查询：

SELECT e.first_name, e.last_name, e.salary, d.department_name

FROM hr.employees e, hr.departments d

WHERE d.department_name IN ('Marketing', 'Sales')

AND e.department_id = d.department_id;

在Oracle数据库11g之前，此查询的执行计划可能与以下执行计划类似：

在此示例中，联接的外侧包含对hr.departments表的扫描，该表返回匹配条件department_name IN（'Marketing'，'Sales'）的行。 内部循环检索与这些部门关联的hr.employees表中的雇员。

嵌套循环连接的新实现

Oracle数据库11g为嵌套循环连接引入了一个新的实现，以减少物理I / O的总延迟。当需要物理I / O时。 Oracle数据库11g可以批量处理多个物理I / O请求，并使用向量I / O处理它们，而不是一次处理一个请求。

作为嵌套循环联接的新实现的一部分，两个NESTED LOOPS联接行源可能出现在执行计划中，其中只有一个会在先前版本中出现。在这种情况下，Oracle数据库分配一个NESTED LOOPS连接行源以连接外部表中的值与内部索引连接。分配第二行来连接第一个连接的结果，其中包括存储在索引中的rowid，连接内侧的表。

考虑第11-24页的“嵌套循环联接的原始实现”中的查询。在Oracle数据库11g中，对于嵌套循环联接的新实现，此查询的执行计划可能与以下执行计划类似：

在这种情况下，hr.departments表中的行构成第一个连接的外侧。第一个连接的内部是索引emp_department_ix。第一个连接的结果构成第二个连接的外侧，其中hr.employees表为内侧。

在某些情况下，第二个连接行源未分配，并且执行计划看起来与之前版本中的相同。以下列表描述了这些情况：

■连接内部所需的所有列都存在于索引中，并且不需要表访问权限。在这种情况下，Oracle数据库只分配一个连接行源。

■返回的行的顺序可能与以前版本中的顺序不同。因此，当Oracle数据库尝试保留行的特定顺序时（例如为了消除对ORDER BY排序的需要），Oracle数据库可能会将原始实现用于嵌套循环联接。

■OPTIMIZER_FEATURES_ENABLE初始化参数在Oracle数据库11g之前设置为发行版。在这种情况下，Oracle数据库使用嵌套循环联接的原始实现。

优化程序使用嵌套循环联接时

优化器在连接少量行时使用嵌套循环连接，两个表之间具有良好的驱动条件。你从外循环驱动到内循环，所以执行计划中表的顺序很重要。

外环是驱动行源。它生成一组行来驱动连接条件。行源可以是使用索引扫描或全表扫描来访问的表格。此外，行可以从任何其他操作中生成。例如，嵌套循环连接的输出可以作为另一个嵌套循环连接的行源。

内循环针对从外循环返回的每一行进行迭代，理想情况下通过索引扫描。如果内部循环的访问路径不依赖于外部循环，那么你可以得到一个笛卡尔积;对于外部循环的每次迭代，内部循环产生相同的一组行。因此，当两个独立的行源联合在一起时，应该使用其他联接方法。

嵌套循环加入提示

如果优化器选择使用其他联接方法，那么可以使用USE_NL（table1 table2）提示，其中table1和table2是要联接的表的别名。

对于某些SQL示例，数据足够小，优化器可以选择全表扫描并使用散列连接。第11-26页的示例11-8“哈希联接”中显示的SQL示例就是这种情况。但是，您可以添加USE_NL来指示优化程序将联接方法更改为嵌套循环。有关USE_NL提示的更多信息，请参阅第19-4页的“加入操作的提示”。

嵌套循环

嵌套循环的外层循环本身可以是嵌套循环。您可以嵌套两个或更多个外部循环以根据需要连接多个表。每个循环都是一种数据访问方法，如下所示：

Hash Joins

数据库使用散列连接来连接大型数据集。 优化器使用两个表或数据源中较小的一个在内存中的连接键上构建一个散列表。 然后它扫描较大的表，探测哈希表以查找连接的行。

当较小的表适合可用内存时，此方法是最好的。 然后成本被限制为对两个表的数据的单次读取通过。

优化程序使用哈希联接时

优化程序使用散列连接来连接两个表，如果它们使用equijoin进行连接，并且满足以下任一条件，则：

■必须连接大量数据。

■大部分小表必须连接。

在例11-8中，数据库使用表顺序来构建哈希表。 数据库稍后将扫描较大的order_items。

例11-8哈希连接

SELECT o.customer_id, l.unit_price * l.quantity

FROM orders o ,order_items l WHERE l.order_id = o.order_id;

散列连接提示

将USE_HASH提示应用于指示优化器在将两个表连接在一起时使用散列连接。 请参阅第7-39页的“PGA内存管理”，了解如何调整SQL工作区的大小。 请参阅第19-4页的“加入操作提示”以了解USE_HASH提示。

select /*+ USE_HASH */ o.customer_id, l.unit_price * l.quantity

FROM orders o ,order_items l WHERE l.order_id = o.order_id;

Sort Merge Joins

排序合并连接可以连接来自两个独立源的行。散列连接通常比排序合并连接更好。但是，如果存在以下两个条件，则排序合并连接可以比散列连接执行得更好：

■行源已排序。

■排序操作不必完成。

但是，如果排序合并连接涉及选择较慢的访问方法（索引扫描而不是全表扫描），那么使用排序合并的好处可能会丢失。

当两个表之间的连接条件是不等式条件（如，或> =）时，排序合并连接非常有用。对于大型数据集，排序合并连接的执行效果要好于嵌套循环连接。除非存在相等条件，否则不能使用散列连接。

在合并连接中，没有驾驶台的概念。该联合由两个步骤组成：

1.排序连接操作：两个输入都按连接键排序。

2.合并连接操作：将已排序的列表合并在一起。

如果输入按连接列排序，则不对该行源执行排序连接操作。但是，排序合并连接总是为连接的右侧创建一个可定位的排序缓冲区，以便在重复连接键值从连接左侧出来的情况下，它可以回溯到最后一个匹配。

优化程序使用排序合并联接时

如果满足以下任一条件，则优化器可以通过散列连接选择排序合并连接以加入大量数据：

■两个表之间的连接条件不是等值连接。

■由于其他操作需要的排序，优化器发现使用排序合并比散列连接更便宜。

排序合并加入提示

要指示优化器使用排序合并连接，请应用USE_MERGE提示。您可能还需要提示强制访问路径。

有些情况下，使用USE_MERGE提示覆盖优化器是有意义的。例如，优化器可以选择对表进行全面扫描，并避免查询中的排序操作。但是，由于通过索引和单个块读取来访问大型表，而不是通过全表扫描来更快地访问，所以成本增加。

有关USE_MERGE提示的更多信息，请参阅第19-4页的“加入操作的提示”。

笛卡尔联接

当一个或多个表没有与语句中的任何其他表的联接条件时，数据库使用笛卡尔联接。优化器将来自一个数据源的每一行与来自另一个数据源的每一行进行连接，从而创建两个集合的笛卡尔乘积。

优化程序使用笛卡尔联接时

优化程序在要求连接两个没有连接条件的表时使用笛卡尔联接。在某些情况下，优化器可能会将两个表之间的通用筛选条件视为可能的连接条件。在其他情况下，优化程序可能会决定生成两个非常小的表的笛卡尔积，这两个表都连接到同一个大表。

笛卡尔加入提示

应用ORDERED提示，指示优化器使用笛卡尔连接。通过在指定连接表之前指定表，优化器将执行笛卡尔连接。

外连接

外部联接扩展了简单联接的结果。外连接返回满足连接条件的所有行，并且还从一个表中返回部分或全部行，其中没有行满足连接条件。

嵌套循环外连接

数据库使用此操作来遍历两个表之间的外部联接。即使内部（可选）表中没有相应的行，外部联接也会返回外部（保留的）表中的行。

在常规外部联接中，优化器根据成本选择表（驱动和驱动）的顺序。但是，在嵌套循环外部联接中，联接条件确定表的顺序。数据库使用外部表（保留行）驱动到内部表。

优化器在以下情况下使用嵌套循环联接来处理外部联接：

■可以从外表驱动到内表。

■数据量足够低以使嵌套循环方法有效。

对于嵌套循环外连接的示例，可以将USE_NL提示添加到示例11-9，以指示优化器使用嵌套循环。例如：

SELECT /*+ USE_NL(c o) */ cust_last_name, SUM(NVL2(o.customer_id,0,1)) "Count"

FROM customers c, orders o

WHERE c.credit_limit > 1000

AND c.customer_id = o.customer_id(+)

GROUP BY cust_last_name;

哈希连接外连接

在下列情况下，优化器使用散列连接来处理外连接：

■数据量足够大以使散列联接方法有效。

■不可能从外表驱动到内表。

表格的顺序由成本决定。 外部表（包括保留行）可用于构建散列表，或者可用于探测一个散列表。

例11-9显示了一个使用散列连接外连接的典型查询。 本示例查询信用额度大于1000的所有客户。需要外部联接以避免缺少订单的客户。

例11-9哈希连接外连接

SELECT cust_last_name, SUM(NVL2(o.customer_id,0,1)) "Count"

FROM customers c, orders o

WHERE c.credit_limit > 1000

AND c.customer_id = o.customer_id(+)

GROUP BY cust_last_name;

该查询查找满足各种条件的客户。 当外部联接在内部表中找不到任何对应的行时，外部联接会为内部表列和外部（保留的）表行返回NULL。 此操作查找所有没有任何订单行的客户行。

在这种情况下，外部连接条件如下所示：

customers.customer_id = orders.customer_id（+）

这种情况的组成部分如下所示：

■外表是客户。

■内部表格是订单。

■加入会保留客户行，包括那些没有订单中相应行的行。

您可以使用NOT EXISTS子查询来返回行。 但是，因为您正在查询表中的所有行，散列连接的性能会更好（除非NOT EXISTS子查询未嵌套）。

在例11-10中，外连接是一个多视图。 优化器不能像普通连接一样进入视图或推入谓词，因此它会构建视图的整个行集。

例11-10外部连接到一个多视图

SELECT c.cust_last_name, sum(revenue)

FROM customers c, v_orders o

WHERE c.credit_limit > 2000

AND o.customer_id(+) = c.customer_id

GROUP BY c.cust_last_name;

排序合并外连接

当外部连接无法从外部（保留）表驱动到内部（可选）表时，它不能使用散列连接或嵌套循环连接。然后它使用排序合并外连接来执行连接操作。

优化器对外连接使用排序合并：

■如果嵌套循环连接效率低下。由于数据量的原因，嵌套循环连接可能效率低下。

■优化器发现，由于其他操作所需的排序，对散列连接使用排序合并更便宜。

全外连接

完整的外连接就像左外连接和右外连接的组合。除了内部联接之外，两个表中的行还没有被返回

内部连接被保留并用空值扩展。换句话说，完整的外连接允许您将表连接在一起，但仍显示在连接的表中没有对应行的行。

示例11-11中的查询检索每个部门中的所有部门和所有员工，但还包括：

■没有部门的任何员工

■任何部门没有员工

例11-11全外连接

SELECT d.department_id, e.employee_id

FROM employees e

FULL OUTER JOIN departments d

ON e.department_id = d.department_id

ORDER BY d.department_id;

从Oracle数据库11g开始，Oracle数据库会自动使用基于哈希联接的本机执行方法来尽可能执行完整的外部联接。 当数据库使用新方法执行完整外连接时，查询的执行计划包含HASH JOIN FULL OUTER。 例11-12显示了例11-11中查询的执行计划。

例11-12全外连接的执行计划

注意HASH JOIN FULL OUTER包含在计划中。 因此，查询使用哈希完全外连接执行方法。 通常，当两个表之间的完整外连接条件是等连接时，哈希完全外连接执行方法是可能的，Oracle数据库将自动使用它。

要指示优化器考虑使用哈希完全外连接执行方法，请应用NATIVE_FULL_OUTER_JOIN提示。 要指示优化器不考虑使用哈希完整外连接执行方法，请应用NO_NATIVE_FULL_OUTER_JOIN提示。 NO_NATIVE_FULL_OUTER_JOIN提示指示优化器在连接每个指定的表时排除本机执行方法。 相反，完整的外连接是作为左外连接和反连接的联合来执行的。

四：阅读和理解执行计划

要执行SQL语句，Oracle数据库可能需要执行许多步骤。每个步骤都从数据库中物理地检索数据行，或者以某种方式为发布该语句的用户准备它们。 Oracle数据库用于执行语句的步骤组合是一个执行计划。执行计划包括语句访问的每个表的访问路径以及使用适当的连接方法对表（连接顺序）的排序。

EXPLAIN PLAN概述

您可以使用EXPLAIN PLAN语句检查优化程序为SQL语句选择的执行计划。当语句发布时，优化器会选择一个执行计划，然后将描述该计划的数据插入到数据库表中。只需发出EXPLAIN PLAN语句，然后查询输出表。

这些是使用EXPLAIN PLAN语句的基础知识：

■使用SQL脚本CATPLAN.SQL在模式中创建一个名为PLAN_TABLE的示例输出表。请参阅第12-4页的“PLAN_TABLE输出表”。

■在SQL语句之前包含EXPLAIN PLAN FOR子句。请参阅第12-4页上的“运行EXPLAIN PLAN”。

■发出EXPLAIN PLAN语句后，使用Oracle数据库提供的其中一个脚本或程序包来显示最新的计划表输出。请参阅第12-5页的“显示PLAN_TABLE输出”。

■EXPLAIN PLAN输出中的执行顺序以最右侧的行开始。下一步是该行的父母。如果两行同等缩进，则首行通常首先执行。

注意：

■本章中的EXPLAIN PLAN输出表用utlxpls.sql脚本显示。

■本章中EXPLAIN PLAN输出中的步骤可能与您的系统不同。 优化器可以根据数据库配置选择不同的执行计划。

示例11-13使用EXPLAIN PLAN检查为ID小于103的员工选择employee_id，job_title，salary和department_name的SQL语句。

例11-13使用EXPLAIN PLAN

EXPLAIN PLAN FOR

SELECT e.employee_id, j.job_title, e.salary, d.department_name

FROM employees e, jobs j, departments d

WHERE e.employee_id < 103

AND e.job_id = j.job_id

AND e.department_id = d.department_id;

select * from table(dbms_xplan.display());

示例11-14中的结果输出表显示了优化器在示例中执行SQL语句时选择的执行计划：

执行计划中的步骤

输出表中的每一行都对应于执行计划中的单个步骤。请注意，带有星号的步骤ID在“谓词信息”部分中列出。

执行计划的每一步都会返回一组行。下一步要么使用这些行，要么在最后一步中将行返回给发出SQL语句的用户或应用程序。行集是一个步骤返回的一组行。

步骤ID的编号反映了响应EXPLAIN PLAN语句显示它们的顺序。执行计划的每一步都从数据库中检索行，或接受来自一个或多个行源的行作为输入。

■示例11-14中的以下步骤从数据库中的对象物理检索数据：

■步骤3读取雇员表的所有行。

■步骤5查找JOB_ID_PK索引中的每个job_id，并查找作业表中关联行的rowid。

■步骤4从作业表中检索步骤5返回的rowid行。

■步骤7查找DEPT_ID_PK索引中的每个department_id，并在departments表中查找关联行的rowid。

■步骤6从部门表中检索步骤7返回的具有rowid的行。

■示例11-14中的以下步骤对上一行源返回的行进行操作：

■步骤2对jobs和employees表中的job_id执行嵌套循环操作，接受步骤3和4中的行源，将Step 3源中的每一行连接到步骤4中的相应行，并将结果行返回到步骤2。

■步骤1执行嵌套循环操作，接受步骤2和步骤6中的行源，将步骤2源中的每一行连接到步骤6中对应的行，并将结果行返回到步骤1。

五：控制优化器行为

表11-3列出了可用于控制查询优化器行为的初始化参数。您可以使用这些参数来启用各种优化器功能来提高SQL执行的性能；

Initialization Parameter 初始化参数 Description 描述

1.CURSOR_SHARING：将SQL语句中的文字值转换为绑定变量。 转换这些值可改善游标共享并可影响SQL语句的执行计划。 优化器根据绑定变量的存在而不是实际的文字值生成执行计划。

2.DB_FILE_MULTIBLOCK_READ_COUNT ：指定在全表扫描或索引快速全扫描期间在单个I / O中读取的块数。 优化器使用DB_FILE_MULTIBLOCK_READ_COUNT的值来为全表扫描和索引快速全扫描进行开销。 较大的值会导致整个表扫描的成本更低，并且可能会导致优化程序通过索引扫描选择全表扫描。 如果未明确设置此参数（或设置为0），则默认值对应于可以高效执行且取决于平台的最大I / O大小。

3.OPTIMIZER_INDEX_CACHING ：控制索引探测与嵌套循环的成本计算。 值范围0到100表示缓冲区缓存中索引块的百分比，它修改了优化程序对嵌套循环和IN列表迭代器的索引缓存的假设。 100的值意味着可能在缓冲区缓存中找到100％的索引块，并且优化器相应地调整索引探测或嵌套循环的成本。 使用此参数时请小心谨慎，因为执行计划可以更改为有利于索引缓存。

4.OPTIMIZER_INDEX_COST_ADJ ：调整索引探查的成本。 值的范围是1到10000.默认值是100，这意味着索引将作为基于正常成本模型的访问路径进行评估。 值为10意味着索引访问路径的成本是索引访问路径的正常成本的十分之一。

5.OPTIMIZER_MODE ：设置实例启动时优化器的模式。 可能的值是ALL_ROWS，FIRST_ROWS_n和FIRST_ROWS。 有关这些参数值的说明，请参阅第11-37页上的“设置OPTIMIZER_MODE初始化参数”。

6.PGA_AGGREGATE_TARGET ：控制为排序和散列连接分配的内存量。 分配给排序或散列连接的大量内存减少了这些操作的优化器成本。

7.STAR_TRANSFORMATION_ENABLED ： 使优化器能够对星型查询进行星型转换（如果为true）。 星号转换组合了各种事实表列上的位图索引。

启用查询优化器功能

OPTIMIZER_FEATURES_ENABLE初始化参数启用一系列优化器相关功能，具体取决于版本。 它接受与版本号相对应的有效字符串值列表中的一个，例如10.2.0.1或11.2.0.1。

您可以使用此参数在数据库升级后保留优化器的旧行为。 例如，如果将Oracle Database 11g从发行版本1（11.1.0.7）升级到发行版本2（11.2.0.2），则OPTIMIZER_FEATURES_ENABLE参数的默认值将从11.1.0.7更改为11.2.0.2。 此升级会导致优化器启用基于11.2.0.2的优化功能。

为了向后兼容，您可能不希望查询计划因新版本中的新优化程序功能而发生更改。 在这种情况下，您可以将OPTIMIZER_FEATURES_ENABLE参数设置为较早的版本。

注意：

Oracle不建议明确地将OPTIMIZER_FEATURES_ENABLE参数设置为早期版本。 为避免可能因执行计划更改而导致可能的SQL性能回归，请考虑使用SQL计划管理。 请参阅第15章“使用SQL计划管理”。

设置OPTIMIZER_FEATURES_ENABLE：

1.查询当前的优化器功能设置。

例如，运行以下SQL * Plus命令：

SQL> SHOW PARAMETER optimizer_features_enable

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

optimizer_features_enable string 11.2.0.2

2.在实例或会话级别设置优化程序功能设置。

例如，运行以下SQL语句将优化器版本设置为10.2.0.5：

SQL> ALTER SYSTEM SET optimizer_features_enable = '10 .2.0.5';

前面的语句禁用了版本10.2.0.5之后的版本中添加的所有新优化器功能。 如果您升级到新版本并且想要启用该版本可用的功能，则不需要显式设置OPTIMIZER_FEATURES_ENABLE初始化参数。

选择优化程序目标

您可以通过设置优化程序目标并收集查询优化程序的代表性统计信息来影响优化程序的选择。您可以设置以下优化器目标：

■最佳吞吐量（默认）

数据库使用处理由语句访问的所有行所需的最少量资源。

对于批量执行的应用程序（例如Oracle Reports应用程序），请优化以获得最佳吞吐量。通常，批处理应用程序的吞吐量更重要，因为启动应用程序的用户只关心应用程序完成所需的时间。响应时间并不重要，因为用户在应用程序运行时不检查单个语句的结果。

■最佳的响应时间

数据库使用处理由SQL语句访问的第一行所需的最少量资源。

对于诸如Oracle Forms应用程序或SQL * Plus查询等交互式应用程序，请优化以获得最佳响应时间。通常，响应时间在交互式应用程序中很重要，因为交互式用户正在等待查看语句访问的第一行或前几行。

选择SQL语句的优化方法和目标时的优化器行为受以下因素影响：

■设置OPTIMIZER_MODE初始化参数

■使用提示更改优化程序目标

■数据字典中的优化程序统计信息

设置OPTIMIZER_MODE初始化参数

OPTIMIZER_MODE初始化参数为选择实例的优化方法建立默认行为。表11-4列出了可能的值和说明。

1. ALL_ROWS ：优化程序对会话中的所有SQL语句使用基于成本的方法，无论统计信息是否存在，并以最佳吞吐量（完成整个语句的最少资源使用）为目标进行优化。 这是默认值。

2. FIRST_ROWS_n ： 无论统计信息是否存在，优化器都使用基于成本的方法，并以最佳响应时间为目标进行优化，以返回前n行，其中n等于1,10,100或1000。

3. FIRST_ROWS ： 优化器使用成本和启发式的组合来找到快速交付前几行的最佳计划。

请注意，使用启发式方法有时会导致优化程序生成计划，其成本明显大于计划成本，而不应用启发式算法。 FIRST_ROWS可用于向后兼容性和计划稳定性; 改用FIRST_ROWS_n。

您可以通过更改初始化文件中的参数值或ALTER SESSION SET OPTIMIZER_MODE语句来更改会话中所有SQL语句的查询优化器的目标。 例如：

■初始化参数文件中的以下语句将实例的所有会话的查询优化器的目标设置为最佳响应时间：

OPTIMIZER_MODE = FIRST_ROWS_1

■以下SQL语句将当前会话的查询优化器的目标更改为最佳响应时间：

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_1;

如果优化程序对SQL语句使用基于成本的方法，并且该语句访问的某些表没有统计信息，那么优化程序将使用内部信息（例如分配给这些表的数据块数）来估计其他统计信息 这些表格。

使用提示更改优化程序目标

要为单个SQL语句指定优化程序的目标，请使用表11-5中的提示。 单个SQL语句中的任何这些提示都可以覆盖该SQL语句的OPTIMIZER_MODE初始化参数。

FIRST_ROWS(n)：此提示指示Oracle数据库以最佳响应时间为目标优化单个SQL语句，以返回前n行，其中n等于任何正整数。 无论统计信息是否存在，该提示都使用基于成本的方法来处理SQL语句。

ALL_ROWS：这个提示明确地选择了基于成本的方法来优化SQL语句，以实现最佳吞吐量。

数据字典中的优化程序统计信息

查询优化器使用的统计信息存储在数据字典中。您可以使用DBMS_STATS包来收集有关这些模式对象中的物理存储特征和数据分布的确切或估计统计信息。

为了保持查询优化器的有效性，您必须具有代表数据的统计信息。对于包含重复次数差异较大的值的表格列（称为倾斜数据），您应该收集直方图。

生成的统计信息为查询优化器提供有关数据唯一性和分布的信息。使用这些信息，查询优化器可以高度准确地计算计划成本，并根据最低成本选择最佳执行计划。

默认情况下，在编译SQL语句期间，优化程序会通过考虑可用的统计信息是否足以生成最佳执行计划来自动决定是否使用动态统计信息（请参阅第13-24页上的“优化程序使用动态统计信息时” 。如果可用的统计信息不足，则优化程序将使用动态统计信息来扩充现有的统计信息。

从Oracle数据库11g第2版（11.2.0.4）开始，OPTIMIZER_DYNAMIC_SAMPLING初始化参数具有11的设置，使优化器在认为必要时收集动态统计信息。例如，优化程序可以收集表扫描，索引访问，联接和GROUP BY操作的动态统计信息，从而提高优化程序决策的质量。