暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / Oracle 19C 视图合并

Oracle 19C 视图合并

原创 Asher.HU 2021-02-04
1286


在视图合并，优化器合并所述查询块表示的视图到包含它的查询块。

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

对于某些总能带来更好计划的简单视图，优化器会自动合并视图而无需考虑成本。否则，优化程序将使用成本进行确定。由于许多原因，包括成本或有效性限制，优化器可能选择不合并视图。

如果OPTIMIZER_SECURE_VIEW_MERGING 为true（默认），则Oracle数据库执行检查以确保视图合并和谓词推送不会违反视图创建者的安全意图。要为特定视图禁用这些其他安全检查，您可以MERGE VIEW向该视图的用户授予特权。要为特定用户的所有视图禁用其他安全检查，可以MERGE ANY VIEW向该用户授予特权。

注意：

您可以使用hint 来覆盖因成本或试探法而无效但由于有效性而被拒绝的视图合并。

也可以看看：


5.2.1视图合并中的查询块

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

数据库从下至上分别优化查询块。因此，数据库首先优化最里面的查询块，为其生成计划的一部分，然后为代表整个查询的外部查询块生成计划。

解析器将查询中引用的每个视图扩展为单独的查询块。该块实质上表示视图定义，因此也表示视图的结果。优化程序的一个选项是分别分析视图查询块，生成视图子计划，然后使用视图子计划来处理其余查询以生成总体执行计划。但是，由于视图是单独优化的，因此该技术可能会导致执行计划欠佳。

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

 

5.2.2简单视图合并

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

例如，该employees表的查询包含连接departments和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生成视图的行，然后将该结果联接到来执行前面的查询employees。因为查询包含view 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';

因为所有三个表都出现在一个查询块中，所以优化器可以从以下六个连接顺序中进行选择：

  • 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')Z
 7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
 8 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

也可以看看：

Oracle Optimizer博客，网址https://blogs.oracle.com/optimizer/为：了解外部联接视图合并，这是简单视图合并的一种特殊情况

 

5.2.3复杂视图合并

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

优化器可以将对GROUP BY或DISTINCT操作的评估延迟到评估连接之后。延迟这些操作可能会提高或降低性能，具体取决于数据特征。如果联接使用过滤器，则将操作延迟到联接之后可以减少要对其执行操作的数据集。尽早评估操作可以减少后续连接要处理的数据量，或者连接可以增加操作要处理的数据量。优化器使用成本来评估视图合并，并且仅当视图是成本较低的选项时才合并视图。

除了成本外，由于以下原因，优化器可能无法执行复杂的视图合并：

  • 外部查询表没有 rowid或唯一列。
  • 该视图出现在CONNECT BY查询块中。
  • 该视图包含GROUPING SETS，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操作并没有很大程度地减小原始查询中行集的大小，因此联接的开销也不会太大。如果要更改上述任何特征，则合并视图可能不再是降低成本。最终计划（不包括视图）如下：

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

示例5-4使用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，先前的计划仍包含一个名为的视图，称为投影视图。投影图显示在其中查询DISTINCT视图已经被合并，或一个GROUP BY视图被合并到一个外部查询块还包含GROUP BY，HAVING或聚集体。在后者的情况下，投影视图包含GROUP BY，HAVING和从原始外部查询的区块集合体。

在投影视图的前面的示例中，当优化器合并视图时，它会将DISTINCT运算符移动到外部查询块，然后添加几个附加列以保持与原始查询的语义等效。之后，查询只能SELECT在外部查询块的列表中选择所需的列。该优化保留了视图合并的所有好处：所有表都在一个查询块中，优化器可以根据需要在最终DISTINCT联接顺序中对它们进行置换，并且该操作已延迟到所有联接完成之后。


oracle19csql优化
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论