Oracle® Database SQL Tuning Guide

Part II Query Optimizer Fundamentals

--- 3 SQL Processing

--- 4 Query Optimizer Concepts

--- 5 Query Transformations

查询转换

5 Query Transformations

本章描述了用于转换查询的最重要的优化器技术。

优化器根据成本来决定是否使用可用的转换。由于各种原因，包括 hints 提示或缺少约束，优化器可能无法使用转换。例如，对于包含不支持约束的外部分区的混合分区表，不能使用诸如子查询展开之类的转换。

5.1 or 扩 展(OR Expansion)

在 or 扩展中，优化器将包含顶级分隔符的查询块转换为包含两个或多个分支的UNION ALL 查询的形式。

优化器通过将操作分解为其他组件，然后将每个组件与UNION ALL 查询的一个分支关联来实现这个目标。优化器可以根据各种原因进行选择 OR 扩展。例如，它可以启用更有效的访问路径或避免笛卡尔积的可选连接方法。与往常一样，只有在转换语句的成本低于原始语句的成本时，优化器才执行扩展。

在以前的版本中，优化器使用连接操作符来执行OR 扩展。从 Oracle 数据库 12c 版本 2(12.2) 开始，优化器将使用 UNION-ALL 操作符。该框架提供了以下增强功能 :

•支持各种转换之间的交互

•避免共享查询结构

•允许探索各种搜索策略

•提供成本注释的重用

•支持标准的 SQL 语法

示例5-1 查询转换 ：UNION ALL 条件

要准备此示例，请以管理员身份登录数据库，执行以下语句以向

hr.departments.department_name 列添加唯一约束，然后向 hr.employees 表添加 100000 行：

ALTER TABLE hr.departments ADD CONSTRAINT department_name_uk UNIQUE ( department_name );

DELETE FROM hr.employees WHERE employee_id > 999 ;

DECLARE

v_counter NUMBER ( 7 ) := 1000 ;

BEGIN

FOR i IN 1 .. 100000 LOOP

INSERT INTO hr.employees

VALUES

( v_counter ,

null ,

'Doe' ,

'Doe' || v_counter || '@example.com' ,

null ,

'07-JUN-02' ,

'AC_ACCOUNT' ,

null ,

50 );

v_counter := v_counter + 1 ;

END LOOP ;

END ;

/

COMMIT ;

EXEC DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'hr' , tabname => 'employees' );

然后以用户hr 的身份连接，并执行以下查询，该查询将连接“ employees ”和“ departments ”表：

SELECT *

FROM employees e , departments d

WHERE ( e.email = 'SSTILES' OR d.department_name = 'Treasury' )

AND e.department_id = d.department_id ;

在没有 OR 扩展的情况下，优化器将e.email='SSTILES' 或 d.department='Treasury' 视为一个单元。因此，优化器无法在 e.email 或 d.department_name 列上使用索引，因此会对员工和部门执行全表扫描。

使用 OR 扩展，优化器将 OR 对应的谓词分解为两个独立的谓词，如下例所示：

SELECT *

FROM employees e , departments d

WHERE e.email = 'SSTILES'

AND e.department_id = d.department_id

UNION ALL

SELECT *

FROM employees e , departments d

WHERE d.department_name = 'Treasury'

AND e.department_id = d.department_id ;

此转换使e.email 和 d.department_name 列可以用作索引键。性能提高是因为数据库使用两个唯一索引而不是两个完整表扫描来筛选数据，如下执行计划所示：

5.2 视图合并 (View Merging)

在视图合并中，优化器将表示视图的查询块合并到包含视图的查询块中。

视图合并可以通过允许优化器考虑附加的连接顺序、访问方法和其他转换来改进计划。例如，在合并了一个视图并将多个表驻留在一个查询块中之后，视图中的一个表可能允许优化器使用join elimination 来删除视图外的一个表。

对于某些简单视图，合并总是导致更好的计划，优化器会自动合并视图，而不考虑成本。否则，优化器将使用成本来确定。优化器可能出于许多原因（包括成本或有效性限制）选择不合并视图。

如果optimizer_secure_view_merge 为 true( 默认值 ) ，那么 Oracle 数据库将执行检查，以确保视图合并和谓词推送不会违反视图创建者的安全意图。要禁用针对特定视图的这些附加安全检查，可以将 MERGE VIEW 权授予此视图的用户。要禁用针对特定用户的所有视图的附加安全检查，可以将 MERGE ANY VIEW 特权授予该用户。

注意:

您可以使用提示来覆盖由于成本或启发而被拒绝的视图合并，但不能覆盖有效性。

5.2.1 视图合并中的查询块

优化器通过一个单独的查询块来表示每个嵌套的子查询或未合并视图。

数据库分别自底向上优化查询块。因此，数据库首先对最内部的查询块进行优化，为它生成计划的一部分，然后为外部的查询块生成计划，表示整个查询。

解析器将查询中引用的每个视图扩展为一个单独的查询块。块本质上表示视图定义，从而表示视图的结果。优化器的一个选项是单独分析视图查询块，生成视图子计划，然后通过使用视图子计划生成总体执行计划来处理查询的其余部分。但是，这种技术可能导致次优的执行计划，因为视图是单独优化的。

视图合并有时可以提高性能。如“例 5-2 ”所示，视图合并将视图中的表合并到外部查询块中，删除内部查询块。因此，不需要对视图进行单独的优化。

5.2.2 简单视图合并

在简单的视图合并中，优化器合并select-project-join 视图。

例如，对employees 表的查询包含一个子查询，它连接 department 和 locations 表。

简单视图合并经常会导致更优的计划，因为合并之后会有更多的连接顺序和访问路径可用。一个视图可能对简单视图合并无效，因为:

•视图包含的结构不包括在 select-project-join 视图中，包括 :

– GROUP BY

– DISTINCT

– Outer join

– MODEL

– CONNECT BY

– Set operators

– Aggregation

•视图出现在半连接或反连接的右侧。

•视图在 select 列表中包含子查询。

•外部查询块包含 PL/SQL 函数。

•视图参与外部连接，并且不满足决定视图是否可以合并的几个附加有效性要求之一。

示例5-2 简单视图合并

下面的查询将hr.employees 表与 dept_locs_v 视图连接起来，后者返回每个部门的街道地址。 dept_locs_v 是“ departments 和 locations ”表的联接。

SELECT e.first_name ,

e.last_name ,

dept_locs_v.street_address ,

dept_locs_v.postal_code

FROM employees e ,

( SELECT d.department_id ,

d.department_name ,

l.street_address ,

l.postal_code

FROM departments d , locations l

WHERE d.location_id = l.location_id ) dept_locs_v

WHERE dept_locs_v.department_id = e.department_id

AND e.last_name = 'Smith' ;

数据库可以执行前面的查询，方法是连接departments 和 locations 以生成视图的行，然后将此结果连接到 employee 。由于查询包含视图 dept_locs_v ，并且此视图包含两个表，优化器必须使用以下联接顺序之一：

• employees, dept_locs_v (departments, locations)

• employees, dept_locs_v (locations, departments)

• dept_locs_v (departments, locations), employees

• dept_locs_v (locations, departments), employees

联接方法也受到约束。基于索引的嵌套循环联接不适用于以employees 开头的联接顺序，因为此视图的列上不存在索引。在不合并视图的情况下，优化器生成以下执行计划：

视图合并将视图中的表合并到外部查询块中，删除内部查询块。视图合并后，查询如下：

SELECT e.first_name , e.last_name , l.street_address , l.postal_code

FROM employees e , departments d , locations l

WHERE d.location_id = l.location_id

AND d.department_id = e.department_id

AND e.last_name = 'Smith' ;

由于这三个表都出现在一个查询块中，优化器可以从以下六个联接顺序中进行选择：

• employees, departments, locations

• employees, locations, departments

• departments, employees, locations

• departments, locations, employees

• locations, employees, departments

• locations, departments, employees

到employees 和 departments 的连接现在可以基于索引。视图合并后，优化器选择以下更有效的计划，该计划使用嵌套循环：

5.2.3 复杂视图合并

在视图合并中，优化器合并包含GROUP BY 和 DISTINCT 视图的视图。与简单的视图合并一样，复杂的合并使优化器能够考虑附加的连接顺序和访问路径。

优化器可以延迟GROUP BY 或 DISTINCT 操作的计算，直到计算完连接之后。根据数据特征延迟这些操作可以提高或降低性能。如果连接使用筛选器，那么将操作延迟到连接之后可以减少要执行操作的数据集。尽早评估操作可以减少后续连接要处理的数据量，或者连接可以增加操作要处理的数据量。优化器使用成本来评估视图合并，并且仅在视图是较低成本选项时才合并视图。

除了成本，优化器可能无法执行复杂的视图合并的原因如下:

•外部查询表没有 rowid 或唯一列。

•视图出现在 CONNECT BY 查询块中。

•视图包含分组集、 ROLLUP 或 PIVOT 子句。

•视图或外部查询块包含 MODEL 子句。

例5-3 复杂视图与 GROUP BY 连接

下面的视图使用了一个GROUP BY 子句 :

CREATE VIEW cust_prod_totals_v AS

SELECT SUM ( s.quantity_sold ) total , s.cust_id , s.prod_id FROM sales s

GROUP BY s.cust_id , s.prod_id ;

下面的查询找到了所有购买了至少100 件毛边毛衣的美国客户 :

SELECT c.cust_id , c.cust_first_name , c.cust_last_name , c.cust_email

FROM customers c , products p , cust_prod_totals_v

WHERE c.country_id = 52790

AND c.cust_id = cust_prod_totals_v.cust_id

AND cust_prod_totals_v.total > 100

AND cust_prod_totals_v.prod_id = p.prod_id

AND p.prod_name = 'T3 Faux Fur-Trimmed Sweater' ;

cust_prod_totals_v 视图适合复杂的视图合并。合并后的查询如下 :

SELECT c.cust_id , cust_first_name , cust_last_name , cust_email

FROM customers c , products p , sales s

WHERE c.country_id = 52790

AND c.cust_id = s.cust_id

AND s.prod_id = p.prod_id

AND p.prod_name = 'T3 Faux Fur-Trimmed Sweater'

GROUP BY s.cust_id ,

s.prod_id ,

p.rowid ,

c.rowid ,

c.cust_email ,

c.cust_last_name ,

c.cust_first_name ,

c.cust_id

HAVING SUM ( s.quantity_sold ) > 100 ;

转换后的查询比未转换的查询成本更低，因此优化器选择合并视图。在未转换的查询中，GROUP BY 操作符应用于视图中的整个 sales 表。在转换后的查询中，连接到 products 和 customers 将过滤掉 sales 表中的大部分行，因此 GROUP BY 操作的成本更低。连接的开销更大，因为 sales 表还没有被缩减，但开销也不会特别大，因为GROUP BY 操作并没有很大程度地减少原始查询中的行集大小。如果前面的任何特征发生了变化，那么合并视图的成本可能就不再低了。不包括视图的最终计划如下 :

示例5-4 具有 DISTINCT 的复杂视图连接

以下对cust_prod_v 视图的查询使用不同的运算符：

SELECT c.cust_id , c.cust_first_name , c.cust_last_name , c.cust_email

FROM customers c ,

products p ,

( SELECT DISTINCT s.cust_id , s.prod_id FROM sales s ) cust_prod_v

WHERE c.country_id = 52790

AND c.cust_id = cust_prod_v.cust_id

AND cust_prod_v.prod_id = p.prod_id

AND p.prod_name = 'T3 Faux Fur-Trimmed Sweater' ;

在确定视图合并会产生较低成本的计划之后，优化器会将查询重写为此等效查询：

SELECT nwvw.cust_id ,

nwvw.cust_first_name ,

nwvw.cust_last_name ,

nwvw.cust_email

FROM ( SELECT DISTINCT ( c.rowid ),

p.rowid ,

s.prod_id ,

s.cust_id ,

c.cust_first_name ,

c.cust_last_name ,

c.cust_email

FROM customers c , products p , sales s

WHERE c.country_id = 52790

AND c.cust_id = s.cust_id

AND s.prod_id = p.prod_id

AND p.prod_name = 'T3 Faux Fur-Trimmed Sweater' ) nwvw ;

上述查询的计划如下：

前面的计划包含一个名为vm_nwvw_1 的视图，称为投影视图，即使在视图合并之后也是如此。投影视图出现在合并了不同视图的查询中，或将逐视图组合并到还包含 GROUP BY 、 HAVING 或聚合的外部查询块中。在后一种情况下，投影视图包含来自原始外部查询块的 GROUP BY 、 HAVING 和 aggregates 。

在前面的投影视图示例中，优化器在合并视图时，会将DISTINCT 运算符移到外部查询块，然后添加多个附加列以保持与原始查询的语义等价。

之后，查询只能在外部查询块的选择列表中选择所需的列。优化保留了视图合并的所有好处：所有表都在一个查询块中，优化器可以根据需要按最终联接顺序排列它们，而 DISTINCT 操作被延迟到所有联接完成之后。

5.3 谓词推 进( Predicate Pushing)

在谓词推进中，优化器将相关谓词从包含查询块“推送”到视图查询块中。

对于未合并的视图，此技术改进了未合并视图的子计您将创建一个引用employees 和 contract_workers 的视图。视图是通过使用 UNION set 操作符的查询定义的，如下所示 : 划。数据库可以使用推入式谓词来访问索引或用作筛选器。

例如，假设您创建一个表hr.contract_workers ，如下所示：

DROP TABLE contract_workers ;

CREATE TABLE contract_workers AS ( SELECT * FROM employees where 1 = 2 );

INSERT INTO contract_workers VALUES ( 306 , 'Bill' , 'Jones' , 'BJONES' , '555.555.2000' , '07-JUN-02' , 'AC_ACCOUNT' , 8300 , 0 , 205 , 110 );

INSERT INTO contract_workers VALUES ( 406 , 'Jill' , 'Ashworth' , 'JASHWORTH' , '555.999.8181' , '09-JUN-05' , 'AC_ACCOUNT' , 8300 , 0 , 205 , 50 );

INSERT INTO contract_workers VALUES ( 506 , 'Marcie' , 'Lunsford' , 'MLUNSFORD' , '555.888.2233' , '22-JUL-01' , 'AC_ACCOUNT' , 8300 , 0 , 205 , 110 );

COMMIT ;

CREATE INDEX contract_workers_index ON contract_workers ( department_id );

您将创建一个引用employees 和 contract_workers 的视图。视图是通过使用 UNION set 操作符的查询定义的，如下所示 :

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 );

然后按如下方式查询视图:

SELECT last_name FROM all_employees_vw WHERE department_id = 50 ;

因为视图是一个UNION set 查询，所以优化器不能将视图的查询合并到正在访问的查询块中。相反，优化器可以通过将其谓词 WHERE 子句条件 department_id=50 推入视图的 UNION set 查询来转换访问语句。等价转换查询如下 :

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 );

转换后的查询现在可以考虑在每个查询块中进行索引访问。

5.4 子查询 展开(Subquery 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 ;

如果优化器不能将复杂语句转换成连接语句，它将选择父语句和子查询的执行计划，就好像它们是独立的语句一样。然后优化器执行子查询并使用返回的行来执行父查询。为了提高整体执行计划的执行速度，优化器可以有效地对子计划进行排序。

5.5 物化视图重写查询

---Query Rewrite with Materialized Views

物化视图是存储在表中的查询结果。

当优化器发现用户查询与与物化视图关联的查询兼容时，数据库可以根据物化视图重写查询。该技术改进了查询执行，因为数据库已经预先计算了大部分查询结果。

优化器寻找与用户查询兼容的物化视图，然后使用基于成本的算法选择物化视图来重写查询。当生成计划时，优化器不会重写查询，除非物化视图的成本低于使用物化视图生成的计划。

5.5.1 关于查询重写和优化器

一个查询要经过多次检查，以确定它是否是查询重写的候选对象。

如果查询没有通过任何检查，那么查询将应用于详细表，而不是物化视图。在响应时间和处理能力方面，不能重写的代价可能很高。

优化器使用两种不同的方法来确定何时根据物化视图重写查询。第一个方法将查询的SQL 文本与物化视图定义的 SQL 文本匹配。如果第一个方法失败，那么优化器将使用更通用的方法，在该方法中比较查询和物化视图之间的连接、选择、数据列、分组列和聚合函数。

查询重写操作查询和子查询在以下类型的SQL 语句 :

• SELECT

• CREATE TABLE … AS SELECT

• INSERT INTO … SELECT

它还对集合操作符UNION 、 UNION ALL 、 INTERSECT 、 INTERSECT ALL 、 EXCEPT 、 MINUS 和 MINUS ALL 中的子查询以及 INSERT 、 DELETE 和 UPDATE 等 DML 语句中的子查询进行操作。

维度、约束和重写完整性级别影响是否将查询重写为使用物化视图。此外，可以通过rewrite 和 NOREWRITE 提示以及 QUERY_REWRITE_ENABLED 会话参数来启用或禁用查询重写。

DBMS_MVIEW 。 EXPLAIN_REWRITE 过程建议是否可以在一个查询上重写查询，如果可以，则建议使用哪个物化视图。它还解释了为什么不能重写查询。

5.5.2 关于查询重写的初始化参数

查询重写行为由某些数据库初始化参数控制。

参数：QUERY_REWRITE_ENABLED

参数值：TRUE (default), FALSE, 或 FORCE

查询重写行为： 在OPTIMIZER_MODE 设置为 FIRST_ROWS 的情况下，优化器使用成本和启发式的混合来寻找快速交付前几行的最佳计划。当设置为 FIRST_ROWS_n 时，优化器使用基于成本的方法并以最佳响应时间为目标进行优化，以返回前 n 行（其中 n=1 、 10 、 100 、 1000 ）。

参数：QUERY_REWRITE_ENABLED

参数值：TRUE (default), FALSE, 或 FORCE

查询重写行为： 此选项启用优化器的查询重写功能，使优化器能够利用物化视图来提高性能。如果设置为FALSE ，则此选项将禁用优化器的查询重写功能，并指示优化器不要使用物化视图重写查询，即使未写入查询的估计查询成本较低。

如果设置为FORCE ，则此选项将启用优化器的查询重写功能，并指示优化器使用物化视图重写查询，即使未写入查询的估计查询成本较低。

参数：QUERY_REWRITE_INTEGRITY

参数值：STALE_TOLERATED,TRUSTED, or ENFORCED (the default)

查询重写行为： 此参数是可选的。但是，如果设置了该值，则该值必须是在“初始化参数值”列中指定的值之一。

默认情况下，完整性级别设置为“ ENFORCED ”。在此模式下，必须验证所有约束。

因此，如果使用ENABLE NOVALIDATE relient ，某些类型的查询重写可能无法工作。若要在此环境中启用查询重写（在未验证约束的情况下），应将完整性级别设置为较低的粒度级别，如 TRUSTED 或 STALE_TOLERATED 。

5.5.3 关于查询重写的准确性

查询重写提供了由初始化参数QUERY_REWRITE_INTEGRITY 控制的三层重写完整性。

您可以为QUERY_REWRITE_INTEGRITY 参数设置的值如下 :

• ENFORCED

这是默认模式。优化器只使用来自物化视图的新数据，并且只使用基于已启用的、有效的主键、惟一键或外键约束的关系。

• TRUSTED

在可信模式下，优化器相信在维度中声明的关系和依赖约束是正确的。在此模式下，优化器还使用预构建的物化视图或基于视图的物化视图，并使用未强制执行的关系和强制执行的关系。它还信任声明但未启用的、经过验证的主键或惟一键约束以及使用维度指定的数据关系。此模式提供了更强大的查询重写功能，但如果您声明的任何可信关系不正确，则会产生不正确结果的风险。

• STALE_TOLERATED

在STALE_TOLERATED 模式下，优化器使用的物化视图是有效的，但是包含过时的数据，也包含新的数据。这种模式提供了最大的重写能力，但会产生不准确结果的风险。

5.5.4 查询重写示例

这个示例演示了使用物化视图重写查询的强大功能。

考虑下面的实体化视图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 ;

让我们假设，在一个典型的月份里，商店的销售额大约是一百万。所以这个具体化的汇总视图包含了每个月销售金额的预先计算的汇总。

考虑下面的查询，它要求商店每个日历月的销售额总和:

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 ;

在缺少前面的物化视图和查询重写特性的情况下，Oracle 数据库必须直接访问 sales 表并计算已销售的金额总和才能返回结果。这涉及到从 sales 表中读取数百万行，这将不可避免地由于磁盘访问而增加查询响应时间。查询中的连接还将进一步降低查询响应的速度，因为需要对数百万行计算连接。

当实体化视图cal_month_sales_mv 出现时，查询重写将透明地将之前的查询重写为以下查询 :

SELECT calendar_month , dollars FROM cal_month_sales_mv ;

因为在物化视图中只有几十行， cal_month_sales_mv 没有连接， Oracle 数据库立即返回结果。

5.6 星 型转换 (Star Transformation)

星型转换是一种优化器转换，它避免了对星型模式中的事实表进行全表扫描。

5.6.1 关于星型模式

星型模式将数据分为事实和维度。

事实是对事件( 如销售 ) 的度量，通常是数字。维度是标识事实的类别，例如日期、位置和产品。

事实表有一个由模式维度表的主键组成的组合键。维度表充当查找或引用表，使您能够选择约束查询的值。

关系图通常显示一个中心事实表，其中的行将其连接到维度表，呈现星形的外观。下面的图形显示销售作为事实表，产品、时间、客户和渠道作为维度表。

雪花模式是一种星型模式，其中维度表引用其他表。暴风雪模式是雪花模式的组合。

5.6.2 星 型 转换的目的

在事实表和维度表的联接中，星型转换可以避免对事实表的完全扫描。

星形转换通过仅获取连接到约束维度行的相关事实行来提高性能。在某些情况下，查询对维度表的其他列具有限制性筛选器。过滤器的组合可以显著减少数据库从事实表处理的数据集。

5.6.3 星 型 转换工作原理

星型转换添加子查询谓词，称为位图半连接谓词，对应于约束维度。

当事实连接列上存在索引时，优化器将执行转换。通过驱动位图和和或子查询提供的键值操作，数据库只需要从事实表中检索相关行。如果维度表上的谓词过滤掉了重要的数据，那么转换可能比事实表上的完整扫描更有效。

在数据库从事实表检索到相关行之后，数据库可能需要使用原始谓词将这些行连接回维度表。当满足以下条件时，数据库可以消除维度表的连接 :

• 维度表上的所有谓词都是半连接子查询谓词的一部分。

• 从子查询中选择的列是唯一的。

• 维度列不在选择列表、 GROUP BY 子句等中。

5.6.4 星转换控制

STAR_TRANSFORMATION_ENABLED 初始化参数控制星型转换。

该参数接受以下值:

•true

优化器通过自动识别事实和约束维度表来执行星型转换。优化器仅在转换计划的成本低于备选方案的情况下执行星型转换。此外，每当物化提高性能时，优化器将自动尝试临时表转换( 请参阅 “ 临时表转换 : 场景 ”) 。

• false ( 默认 )

优化器不执行星型转换。

•TEMP_DISABLE

这个值与true 相同，只是优化器不会尝试临时表转换。

5.6.5 星 型 转换: 场景

这个场景演示了星型查询的星型转换。

示例5-5 星型查询

以下查询是加州所有城市在1999 年第一季度和第二季度的网络销售总额 :

SELECT c.cust_city ,

t.calendar_quarter_desc ,

SUM ( s.amount_sold ) sales_amount

FROM sales s , times t , customers c , channels ch

WHERE s.time_id = t.time_id

AND s.cust_id = c.cust_id

AND s.channel_id = ch.channel_id

AND c.cust_state_province = 'CA'

AND ch.channel_desc = 'Internet'

AND t.calendar_quarter_desc IN ( '1999-01' , '1999-02' )

GROUP BY c.cust_city , t.calendar_quarter_desc ;

输入如下：

在本例中，sales 是事实表，其他表是维度表。 sales 表包含产品每次销售的一行，因此可以想象它包含数十亿条销售记录。然而，只有少数产品在指定的季度通过互联网销售给加州的客户。

例5-6 星形变换

此示例显示了示例5-5 中查询的星形转换。转换避免了对销售的全表扫描。

SELECT c.cust_city ,

t.calendar_quarter_desc ,

SUM ( s.amount_sold ) sales_amount

FROM sales s , times t , customers c

WHERE s.time_id = t.time_id

AND s.cust_id = c.cust_id

AND c.cust_state_province = 'CA'

AND t.calendar_quarter_desc IN ( '1999-01' , '1999-02' )

AND s.time_id IN

( SELECT time_id

FROM times

WHERE calendar_quarter_desc IN ( '1999-01' , '1999-02' ))

AND s.cust_id IN

( SELECT cust_id FROM customers WHERE cust_state_province = 'CA' )

AND s.channel_id IN

( SELECT channel_id FROM channels WHERE channel_desc = 'Internet' )

GROUP BY c.cust_city , t.calendar_quarter_desc ;

例5-7 星型 转换 部分执行计划

此示例显示示例5-6 中星形转换的执行计划的编辑版本。

第26 行显示 sales 表有一个索引访问路径，而不是一个完整的表扫描。对于 channels （第 14 行）、 times （第 19 行）和 customers （第 24 行）的子查询产生的每个键值，数据库从 sales fact 表（第 15 、 20 、 25 行）的索引中检索位图。

位图中的每个位对应于事实表中的一行。当子查询中的键值与事实表行中的值相同时，将设置该位。例如，在位图101000 中 ... （省略号表示其余行的值为0 ）、事实表的第 1 行和第 3 行具有子查询中的匹配键值。

第12 、 17 和 22 行中的操作迭代子查询中的键并检索相应的位图。在示例 5-6 中， customers 子查询查找其州或省为 CA 的客户的 id 。假设位图 101000 ... 对应于customers 表子查询中的 customer ID 键值 103515 。还假设 customers 子查询使用位图 010000… ，生成键值 103516 ，这意味着 sales 中只有第 2 行具有来自子查询的匹配键值。

数据库合并（使用OR 运算符）每个子查询（第 11 、 16 、 21 行）的位图。在我们的客户示例中，数据库生成一个位图 111000 ... 对于合并两个位图后的客户子查询：

101000... # bitmap corresponding to key 103515

010000... # bitmap corresponding to key 103516

---------

111000... # result of OR operation

在第10 行中，数据库将 AND 运算符应用于合并的位图。假设数据库执行完所有或操作后，通道的位图为 100000 ... 如果数据库对此位图和“ 来自客户的位图 ” 子查询执行 “ AND ” 操作，则结果如下：

100000... # channels bitmap after all OR operations performed

111000... # customers bitmap after all OR operations performed

---------

100000... # bitmap result of AND operation for channels and customers

在第9 行中，数据库生成最终位图的相应 rowid 。数据库使用rowids 从 sales 事实表中检索行（第 26 行）。在我们的示例中，数据库只生成一个 rowid ，它对应于第一行，因此只获取一行，而不是扫描整个 sales 表。

5.6.6 临时表转换 : 场景

在前面的场景中，优化器不会将表通道连接回sales 表，因为它没有在外部引用，而 channel_id 是惟一的。

但是，如果优化器无法消除回联接，则数据库将子查询结果存储在临时表中，以避免重新扫描维度表以生成位图键并回联接。此外，如果查询是并行运行的，那么数据库将物化结果，以便每个并行执行服务器可以从临时表中选择结果，而不是再次执行子查询。

例5-8 星转换使用临时表

在本例中，数据库将客户的子查询结果物化为一个临时表:

SELECT t1.c1 cust_city ,

t.calendar_quarter_desc calendar_quarter_desc ,

SUM ( s.amount_sold ) sales_amount

FROM sales s , sh.times t , sys_temp_0fd9d6621_e7e24 t1

WHERE s.time_id = t.time_id

AND s.cust_id = t1.c0

AND ( t.calendar_quarter_desc = '1999-q1' OR

t.calendar_quarter_desc = '1999- q2' )

AND s.cust_id IN ( SELECT t1.c0 FROM sys_temp_0fd9d6621_e7e24 t1 )

AND s.channel_id IN

( SELECT ch.channel_id

FROM channels ch

WHERE ch.channel_desc = 'internet' )

AND s.time_id IN

( SELECT t.time_id

FROM times t

WHERE t.calendar_quarter_desc = '1999-q1'

OR t.calendar_quarter_desc = '1999-q2' )

GROUP BY t1.c1 , t.calendar_quarter_desc

优化器使用临时表sys_temp_0fd9d6621_e7e24 替换客户，并使用临时表的相应列替换对列 cust_id 和 cust_city 的引用。数据库创建了包含两列的临时表 :(c0 NUMBER, c1 VARCHAR2(30)) 。这些列对应于客户表的 cust_id 和 cust_city 。数据库通过在执行前一个查询的开始执行以下查询来填充临时表 :

SELECT c.cust_id , c.cust_city FROM customers WHERE c.cust_state_province = 'CA'

示例5-9 使用临时表进行星型转换的部分执行计划

下面的示例显示了示例5-8 中查询的执行计划的编辑版本 :

计划的第1 、 2 和 3 行将客户子查询物化到临时表中。在第 6 行，数据库扫描临时表 ( 而不是子查询 ) ，从事实表构建位图。第 27 行扫描临时表，而不是扫描客户。数据库不需要对临时表上的客户应用筛选器，因为筛选器是在具体化临时表时应用的。

5.7 In-Memory ( 向量组 BY)

内存聚合的关键优化是在扫描时进行聚合。

为了优化包含从单个大表到多个小表的聚合和连接的查询块，例如在典型的星型查询中，转换使用键向量和向量组操作。这些操作使用高效的内存内数组进行连接和聚合，特别是当底层表是内存内的列表时，这些操作尤其有效。

5.8 指针持续时间临时表

为了实现查询的中间结果，Oracle 数据库可以在查询编译期间隐式地在内存中创建一个指针持续时间的临时表。

5.8.1 指针持续时间临时表的用途

复杂查询有时会多次处理相同的查询块，这会造成不必要的性能开销。

为了避免这种情况，Oracle 数据库可以为查询结果自动创建临时表，并在游标期间将它们存储在内存中。对于复杂的操作，例如 WITH 子句查询、星型转换和分组集，这种优化增强了重复使用的子查询的中间结果的物化。通过这种方式，指针持续时间的临时表可以提高性能并优化 I/O 。

5.8.2 指针持续时间临时表的工作方式

指针定义临时表的定义驻留在内存中。表定义与游标相关联，并且仅对执行游标的会话可见。

使用指针持续时间临时表时，数据库执行以下步骤:

1. 选择使用指针持续时间临时表的计划

2. 使用唯一名称创建临时表

3. 重写查询以引用临时表

4. 将数据加载到内存中，直到没有内存剩余，在这种情况下，它在磁盘上创建临时段

5. 执行查询，从临时表返回数据

6. 截断表，释放内存和磁盘上的任何临时段

注意:

指针持续时间临时表的元数据只要指针在内存中，就会一直保留在内存中。元数据没有存储在数据字典中，这意味着它在数据字典视图中是不可见的。您不能显式删除元数据。

前面的场景取决于内存的可用性。对于串行查询，临时表使用PGA 内存。

指针持续时间临时表的实现类似于排序。如果没有更多的内存可用，则数据库将数据写入临时段。对于指针持续时间的临时表，区别如下:

• 数据库在查询结束时释放内存和临时段，而不是在行源不再活动时释放。

• 内存中的数据停留在内存中，不像排序中数据可以在内存和临时段之间移动。

当数据库使用光标持续时间临时表时，关键字光标持续时间内存将出现在执行计划中。

5.8.3 持续时间临时表：示例

重复相同子查询的WITH 查询有时可以受益于游标持续时间临时表。

以下查询使用WITH 子句创建三个子查询块：

WITH q1 AS

( SELECT department_id , SUM ( salary ) sum_sal

FROM hr.employees

GROUP BY department_id ),

q2 AS

( SELECT * FROM q1 ),

q3 AS

( SELECT department_id , sum_sal FROM q1 )

SELECT *

FROM q1

UNION ALL

SELECT *

FROM q2

UNION ALL

SELECT * FROM q3 ;

下面的示例计划显示了转换：

SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY_CURSOR ( FORMAT => 'BASIC +ROWS +COST' ));

在前面的计划中，步骤1 中的临时表转换指示数据库使用游标持续时间临时表来执行查询。步骤 2 中的 CURSOR DURATION MEMORY 关键字表示数据库使用内存（如果可用）存储 sys TEMP_0FD9D6606_1AE004 的结果。如果内存不可用，则数据库将临时数据写入磁盘。

5.9 表扩展 (Table Expansion)

在表扩展中，优化器生成一个计划，该计划在分区表的主要读取部分使用索引，但在表的活动部分不使用索引。

5.9.1 表格扩展的目的

基于索引的计划可以提高性能，但索引维护会产生开销。在许多数据库中，DML 只影响一小部分数据。

表扩展对高更新表使用基于索引的计划。您可以只对主要读取的数据创建索引，从而消除活动数据的索引开销。这样，表扩展在避免索引维护的同时提高了性能。

5.9.2 表扩展的工作原理

表分区使表扩展成为可能。

如果分区表上存在本地索引，优化器可以将该索引标记为对特定分区不可用。实际上，有些分区没有索引。

在表扩展中，优化器将查询转换为UNION ALL 语句，其中一些子查询访问索引分区，其他子查询访问未索引分区。优化器可以为一个分区选择最有效的访问方法，而不管它是否存在于查询中访问的所有分区中。

优化器并不总是选择表扩展：

• 表扩展是基于成本的。

虽然数据库只跨UNION all 的所有分支访问扩展表的每个分区一次，但数据库连接到它的任何表都在每个分支中访问。

• 语义问题可能导致扩展无效。

例如，出现在外部联接右侧的表对于表扩展无效。

您可以使用EXPAND_table 提示来控制表扩展。提示覆盖基于成本的决策，但不覆盖语义检查。

5.9.3 表扩展 : 场景

优化器根据查询中出现的谓词跟踪必须从每个表访问哪些分区。分区修剪使优化器能够使用表扩展来生成更优的计划。

假设

该场景假设如下:

• 您希望对 sh.sales 表运行星型查询，该表在 time_id 列上进行了范围分区。

• 您希望禁用特定分区上的索引，以查看表扩展的好处。

使用表扩展 :

1. 以 sh 用户身份登录数据库。

2. 运行以下查询 :

SELECT *

FROM sales

WHERE time_id >= TO_DATE ( '2000-01-01 00:00:00' , 'SYYYY-MM-DD HH24:MI:SS' )

AND prod_id = 38 ;

使用DBMS_XPLAN 查看执行计划

SET LINESIZE 150

SET PAGESIZE 0

SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY_CURSOR ( format => 'BASIC,PARTITION' ));

如下面的Pstart 和 Pstop 列所示，优化器从过滤器中确定表中的 28 个分区中只有 16 个必须被访问 :

在优化器确定要访问的分区之后，它将考虑所有这些分区上可用的任何索引。在前面的计划中，优化器选择使用sales_prod_bix 位图索引。

4. 禁用 sales 表的 SALES_1995 分区上的索引 :

ALTER INDEX sales_prod_bix MODIFY PARTITION sales_1995 UNUSABLE ;

前面的DDL 禁用分区 1 上的索引，该分区包含 1996 年以前的所有销售。

5. 再次执行 sales 查询，然后查询 DBMS_XPLAN 以获得计划。输出显示计划没有改变 :

计划是相同的，因为禁用的索引分区与查询无关。如果查询访问的所有分区都建立了索引，那么数据库可以使用索引回答查询。因为查询只访问分区16 到 28 ，所以禁用分区 1 上的索引不会影响计划。

6. 禁用分区 28 的索引 (SALES_Q4_2003) ，该分区是查询需要访问的分区 :

ALTER INDEX sales_prod_bix MODIFY PARTITION sales_1995 UNUSABLE ;

ALTER INDEX sales_prod_bix MODIFY PARTITION sales_q4_2003 UNUSABLE ;

ALTER INDEX sales_time_bix MODIFY PARTITION sales_q4_2003 UNUSABLE ;

通过禁用查询确实需要访问的分区上的索引，查询就不能再使用这个索引( 没有表展开 ) 。

7. 使用 DBMS_XPLAN 查询计划。

如下图所示，优化器不使用索引:

在前面的示例中，查询访问16 个分区。在其中 15 个分区上，有一个索引可用，但是最后一个分区没有可用的索引。

因为优化器必须选择一个或另一个访问路径，所以优化器不能在任何分区上使用索引。

8. 通过表扩展，优化器按如下方式重写原始查询 :

SELECT *

FROM sales

WHERE time_id >= TO_DATE ( '2000-01-01 00:00:00' , 'SYYYY-MM-DD HH24:MI:SS' )

AND time_id < TO_DATE ( '2003-10-01 00:00:00' , 'SYYYY-MM-DD HH24:MI:SS' )

AND prod_id = 38

UNION ALL

SELECT *

FROM sales

WHERE time_id >= TO_DATE ( '2003-10-01 00:00:00' , 'SYYYY-MM-DD HH24:MI:SS' )

AND time_id < TO_DATE ( '2004-01-01 00:00:00' , 'SYYYY-MM-DD HH24:MI:SS' )

AND prod_id = 38 ;

在前面的查询中，UNION 中的第一个查询块将访问所有已建立索引的分区，而第二个查询块将访问未建立索引的分区。这两个子查询使优化器能够选择在第一个查询块中使用索引，如果它比使用所有被访问分区的表扫描更优的话。

9. 使用 DBMS_XPLAN 查询计划。计划内容如下 :

如前面的计划所示, 查询优化器使用 UNION ALL 两块 ( 步骤 2) 。优化器选择索引访问分区 13 到 27 日在第一个查询块 ( 步骤 6) 。因为没有可用索引分区 28 日优化器选择一个全表扫描在第二个查询块 ( 步骤 8) 。

5.9.4 表展开与星 型转换 : 场景

星型转换支持特定类型的查询，以避免访问大型事实表的大部分。

星型转换需要定义几个索引，在主动更新的表中可能会有开销。通过表扩展，您可以只在不活动的分区上定义索引，这样优化器就可以只在表的索引部分上考虑星型转换。

假设

该场景假设如下:

• 查询 “ 星型转换 : 场景 ” 中使用的相同模式。

•sales 的最后一个分区正在被积极地更新，时间分区表通常都是这样。

• 您希望优化器利用表扩展。

要在星型查询中利用表扩展:

1. 按如下方式禁用最后一个分区上的索引 :

ALTER INDEX sales_channel_bix MODIFY PARTITION sales_q4_2003 UNUSABLE ;

ALTER INDEX sales_cust_bix MODIFY PARTITION sales_q4_2003 UNUSABLE ;

2. 执行以下星号查询 :

SELECT t.calendar_quarter_desc , SUM ( s.amount_sold ) sales_amount

FROM sales s , times t , customers c , channels ch

WHERE s.time_id = t.time_id

AND s.cust_id = c.cust_id

AND s.channel_id = ch.channel_id

AND c.cust_state_province = 'CA'

AND ch.channel_desc = 'Internet'

AND t.calendar_quarter_desc IN ( '1999-01' , '1999-02' )

GROUP BY t.calendar_quarter_desc ;

3. 执行计划如下：

前面的计划使用表扩展。访问除最后一个分区以外的每个分区的UNION ALL 分支使用星型转换。因为分区 28 上的索引被禁用，所以数据库使用全表扫描访问最终分区。

5.10 连接因式分解 (Join Factorization)

在基于成本的转换( 称为连接分解 ) 中，优化器可以分解 UNION ALL 查询分支的公共计算。

5.10.1 连接因子分解的目的

UNION ALL 查询在数据库应用程序中很常见，尤其是在数据集成应用程序中。

通常，UNION ALL 查询中的分支引用相同的基表。没有连接因子分解，优化器独立地评估 UNION ALL 查询的每个分支，这会导致重复的处理，包括数据访问和连接。连接分解转换可以在联合的所有分支之间共享公共计算。避免对大型基表进行额外的扫描可以极大地提高性能。

5.10.2 连接因式分解的工作原理

连接分解可以分解多个表，并且可以从两个以上的UNION ALL 分支中分解。

通过示例可以很好地解释连接因式分解。

示例5-10 union ALL 查询

下面的查询显示了四个表(t1 、 t2 、 t3 和 t4) 和两个 UNION ALL 分支的查询 :

SELECT t1.c1 , t2.c2

FROM t1 , t2 , t3

WHERE t1.c1 = t2.c1

AND t1.c1 > 1

AND t2.c2 = 2

AND t2.c2 = t3.c2

UNION ALL

SELECT t1.c1 , t2.c2

FROM t1 , t2 , t4

WHERE t1.c1 = t2.c1

AND t1.c1 > 1

AND t2.c3 = t4.c3

在前面的查询中，表t1 同时出现在 UNION ALL 分支中，与过滤谓词 t1 一样。 c1 > 1 和连接谓词 t1 . c1 = t2 . c1 。在不进行任何转换的情况下，数据库必须对表 t1 执行两次扫描和过滤，每个分支执行一次。

例5-11 分解查询

例5 - 10

SELECT t1.c1 , VW_JF_1.item_2

FROM t1 ,

( SELECT t2.c1 item_1 , t2.c2 item_2

FROM t2 , t3

WHERE t2.c2 = t3.c2

AND t2.c2 = 2

UNION ALL

SELECT t2.c1 item_1 , t2.c2 item_2 FROM t2 , t4 WHERE t2.c3 = t4.c3 ) VW_JF_1

WHERE t1.c1 = VW_JF_1.item_1

AND t1.c1 > 1

在本例中，由于对表t1 进行了因式分解，因此数据库仅对 t1 执行一次表扫描和过滤。如果 t1 很大，那么这种因式分解避免了两次扫描和过滤 t1 的巨大性能代价。

注意:

如果UNION 中的所有分支都有使用 DISTINCT 的子句函数，则联接因式分解无效。

5.10.3 分解和连接顺序 : 场景

连接因数分解可以为连接顺序创造更多的可能性

例5-12 查询涉及 5 个表

在下面的查询中，view V 与例 5-10 中的查询相同 :

SELECT *

FROM t5 ,

( SELECT t1.c1 , t2.c2

FROM t1 , t2 , t3

WHERE t1.c1 = t2.c1

AND t1.c1 > 1

AND t2.c2 = 2

AND t2.c2 = t3.c2

UNION ALL

SELECT t1.c1 , t2.c2

FROM t1 , t2 , t4

WHERE t1.c1 = t2.c1

AND t1.c1 > 1

AND t2.c3 = t4.c3 ) V

WHERE t5.c1 = V.c1

t1t2t3t5

例5-13 将 t1 从 V 中因式分解

如果join factorization 将 t1 从 view V 中进行因式分解，如下面的查询所示，则数据库可以将 t1 与 t5 进行连接 :

SELECT *

FROM t5 ,

( SELECT t1.c1 , VW_JF_1.item_2

FROM t1 ,

( SELECT t2.c1 item_1 , t2.c2 item_2

FROM t2 , t3

WHERE t2.c2 = t3.c2

AND t2.c2 = 2

UNION ALL

SELECT t2.c1 item_1 , t2.c2 item_2

FROM t2 , t4

WHERE t2.c3 = t4.c3 ) VW_JF_1

WHERE t1.c1 = VW_JF_1.item_1

AND t1.c1 > 1 )

WHERE t5.c1 = V.c1

前面的查询转换打开了新的连接顺序。然而，连接因数分解强制使用特定的连接顺序。例如，在前面的查询中，表t2 和 t3 出现在视图 VW_JF_1 中的 UNION ALL 查询的第一个分支中。数据库必须先连接 t2 和 t3 ，然后才能连接 t1 ，这在 VW_JF_1 视图中没有定义。强制的连接顺序不一定是最佳的连接顺序。由于这个原因，优化器使用基于成本的转换框架执行连接分解。优化器计算包含和不包含连接因子分解的计划的成本，然后选择最便宜的计划。

例5-14 从视图 V 中分解 t1 ，去掉了视图定义

下面的查询与例5-13 中的查询相同，但是去掉了视图定义，这样分解就更容易看到了 :

SELECT *

FROM t5 ,

( SELECT t1.c1 , VW_JF_1.item_2

FROM t1 , VW_JF_1

WHERE t1.c1 = VW_JF_1.item_1

AND t1.c1 > 1 )

WHERE t5.c1 = V.c1

5.10.4 外部连接的因式分解 : 场景

该数据库支持外部连接、反连接和半连接的连接因数分解，但仅针对此类连接中的正确表。

例如，join factorization 可以通过对 t2 进行因子分解来转换下面的 UNION ALL 查询 :

SELECT t1.c2 , t2.c2

FROM t1 , t2

WHERE t1.c1 = t2.c1 (+)

AND t1.c1 = 1

UNION ALL

SELECT t1.c2 , t2.c2

FROM t1 , t2

WHERE t1.c1 = t2.c1 (+)

AND t1.c1 = 2

下面的示例显示了转换。表t2 现在不再出现在联合子查询的所有分支中。

SELECT VW_JF_1.item_2 , t2.c2

FROM t2 ,

( SELECT t1.c1 item_1 , t1.c2 item_2

FROM t1

WHERE t1.c1 = 1

UNION ALL

SELECT t1.c1 item_1 , t1.c2 item_2 FROM t1 WHERE t1.c1 = 2 ) VW_JF_1

WHERE VW_JF_1.item_1 = t2.c1 (+)

欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！

Oracle 查询转换