Oracle 12CR2查询转换之视图合并

Posted on by YongJing(敬勇)

这里的测试数据库版本为12.2.0.1,在视图合并中，优化器代表视图的查询块到包含视国的查询块中。视图合并通过让优化器考虑额外的连接顺序，访问方法与其它转换来提高性能。例如，在一个视图被合并后并且在一个查询块中有多个表，内置在视图中的一个表可以允许优化器使用连接消除来删除视图外部的一个表。

对于特定的简单视图执行视图合并总是会生成更好的执行计划，优化器自动合并视图不会考虑成本。另外的优化器使用成本来进行决定。由于许多原因，包括成本或有效的限制优化器可能选择不执行视图合并。

如果optimizer_secure_view_merging设置为true(缺省值)，那么Oracle数据库将执行检查来确保视图合并与谓词推送不会违反视图创建者的安全意图。为了对特定视图禁用这些额外的安全检查，可以给创建视图的用户授予merge view权限。为了对特定的用户的所有视图禁用额外的安全检查，可以给用户授予merge any view权限。

视图合并之查询块
优化器通过单独的查询块来代表每个嵌套子查询或未合并视图。数据库自下而上优化每一个单独的查询块。因此，数据库首先优化最内部的查询块，生成执行计划的一部分，然后为外部的查询块生成执行计划。解析器展开查询中的每个视图成为了一个单独的查询块。查询块的本质代表了视图定义和视国结果。优化器的一个选项是用来分别分析视图查询块，生成一个视图子执行计划，然后通过使用视图子计划来处理查询中的剩余部分来生成整个查询的执行计划。然而，这种技术因为导致了视图被分别优化而可能产生次优的执行计划。视图合并有些可能会提高性能。

简单视图合并
在简单视图合并中，优化器合并select-project-join视图。例如，查询employees表的一个查询包含一个子查询连接departments与locations表。

因为在视图合并后有额外的连接顺序与访问路径可用所以简单视图合并通常会生成更优化的执行计划。对于简单视图合并不生效，因为:
.视图包含了不允许出现在select-project-join视图中的结构，比如:
-group by
-distinct
-Outer join
-MODEL
-connect by
-Set operators
-Aggregation
–

.视图出现在semijoin或antijoin的右边
.在select列表中包含子查询
.外部查询块包含PL/SQL函数
.视图参与外连接并且不满足视图被合并的几个条件中的任何一个

下面的查询连接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表来为视图生成行记录，然后用这个结果与employees表连接。因为查询包含视图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表开始的连接顺序基于索引的嵌套循环不合适因为对于视图中的列不存在索引。不使用视图合并，优化器生成的执行计划如下:

-----------------------------------------------------------------
| Id | Operation                   | Name        | Cost (%CPU)|
-----------------------------------------------------------------
|  0 | SELECT STATEMENT            |             |      7 (15)|
|* 1 |  HASH JOIN                  |             |      7 (15)|
|  2 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |      2  (0)|
|* 3 |   INDEX RANGE SCAN          | EMP_NAME_IX |      1  (0)|
|  4 |  VIEW                       |             |      5 (20)|
|* 5 |   HASH JOIN                 |             |      5 (20)|
|  6 |    TABLE ACCESS FULL        | LOCATIONS   |      2  (0)|
|  7 |    TABLE ACCESS FULL        | DEPARTMENTS |      2  (0)|
-----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPT_LOCS_V"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
3 - access("E"."LAST_NAME"='Smith')
5 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

视图合并将视图中的表合并到外部查询块中，并删除内部查询块。在视图合并之后，查询语句如下:

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

因为所有三个表都出现在一个查询块，优化器可以从以下6种连接顺序中选择一种:
.employees, departments, locations
.employees, locations, departments
.departments, employees, locations
.departments, locations, employees
.locations, employees, departments
.locations, departments, employees

连接employees与departments表现在可以使用索引，在视图合并之后，优化器将选择更有效的执行计划，使用嵌套循环连接:

-------------------------------------------------------------------
| Id | Operation                      | Name        | Cost (%CPU)|
-------------------------------------------------------------------
|  0 | SELECT STATEMENT               |             |       4 (0)|
|  1 |  NESTED LOOPS                  |             |            |
|  2 |   NESTED LOOPS                 |             |       4 (0)|
|  3 |    NESTED LOOPS                |             |       3 (0)|
|  4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |       2 (0)|
|* 5 |      INDEX RANGE SCAN          | EMP_NAME_IX |       1 (0)|
|  6 |     TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |       1 (0)|
|* 7 |      INDEX UNIQUE SCAN         | DEPT_ID_PK  |       0 (0)|
|* 8 |    INDEX UNIQUE SCAN           | LOC_ID_PK   |       0 (0)|
|  9 |   TABLE ACCESS BY INDEX ROWID  | LOCATIONS   |       1 (0)|
-------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E"."LAST_NAME"='Smith')
7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
8 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

复杂视图合并
在视图合并中，优化器合并包含group by与distinct操作的视图，像简单视图合燕一样，复杂视图合并能让优化器考虑额外的连接顺序和访问路径。

优化器可能会延迟对group by或distinct操作进行评估直到优化器评估完连接之后。延迟这些操作可能提高或损害性能这依赖于数据的特征。如果连接使用过滤，那么延迟这些操作在连接之后可以减少这些操作将要处理的数据集。尽
早评估可以减少后续连接所要处理的数据量或者连接可能增加这些操作所要处理的数据量。优化器使用成本来评估视图合并并且只有当合并操作之后成本更低才会执行。

除了成本之外，由于以下原因成本可能不会执行复杂视图合并操作:
.外部查询表没有rowid或唯一约束列
.视图出现在connect by查询块中
.视图包含grouping sets,rollup或pivot子句
.视图或外部查询块包含model子句

包含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;

下面的查询将找出来那些自United States并且买了至少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操作不会在原始查询中减少太多的行记录。如果之前的特征发生了改变，合并视图后的成本将不会减少。最终的执行计划不包含视图，如下:

--------------------------------------------------------
| Id | Operation             | Name      | Cost (%CPU)|
--------------------------------------------------------
|  0 | SELECT STATEMENT      |           |  2101 (18)|
|* 1 |  FILTER               |           |           |
|  2 |   HASH GROUP BY       |           |  2101 (18)|
|* 3 |    HASH JOIN          |           |  2099 (18)|
|* 4 |     HASH JOIN         |           |  1801 (19)|
|* 5 |      TABLE ACCESS FULL| PRODUCTS  |     96 (5)|
|  6 |      TABLE ACCESS FULL| SALES     |  1620 (15)|
|* 7 |     TABLE ACCESS FULL | CUSTOMERS |   296 (11)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUM("QUANTITY_SOLD")>100)
3 - access("C"."CUST_ID"="CUST_ID")
4 - access("PROD_ID"="P"."PROD_ID")
5 - filter("P"."PROD_NAME"='T3 Faux Fur-Trimmed Sweater')
7 - filter("C"."COUNTRY_ID"='US')

使用distinct的复杂视图连接
下面的查询对cust_prod_v视图使用了distinct操作:

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;

上面查询的执行计划如下:

-------------------------------------------
| Id | Operation             | Name      |
-------------------------------------------
|  0 | SELECT STATEMENT      |           |
|  1 |  VIEW                 | VM_NWVW_1 |
|  2 |   HASH UNIQUE         |           |
|* 3 |    HASH JOIN          |           |
|* 4 |     HASH JOIN         |           |
|* 5 |      TABLE ACCESS FULL| PRODUCTS  |
|  6 |      TABLE ACCESS FULL| SALES     |
|* 7 |     TABLE ACCESS FULL | CUSTOMERS |
-------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("C"."CUST_ID"="S"."CUST_ID")
4 - access("S"."PROD_ID"="P"."PROD_ID")
5 - filter("P"."PROD_NAME"='T3 Faux Fur-Trimmed Sweater')
7 - filter("C"."COUNTRY_ID"='US')

上面的执行计划即使在视图合并后还是包含了一个名叫vm_nwvw_1的视图，也叫projection view。在查询中的distinct视图已经合并后出现了projection视图，或者group by视图被合并到外部查询块并且包含group by,having或聚合操作。在后一种情况下，projection视图包含了group by,having和原始外部查询块中的聚合操作。

在上面的projection视图中，当优化器合并视图时，它将distinct操作移动到外部查询块中，并且增加了几个额外列来维护与原始查询的等价性。在这之后，查询可以只从外部查询块中的select列表中选择所需要的列。优化器保留了视图合并的所有好处:一个查询块中的所有表，优化器可能会在最终的连接顺序中变换它们的顺序，并且distinct操作可能会延迟到所有连接完成之后。

发表评论

电子邮件地址不会被公开。

Proudly powered by WordPress | Indrajeet by Sus Hill.