【SQL】Oracle查询转换之视图合并_ITPUB博客

相关文章推荐

小胡子的羽毛球 · VScode报错：Module ‘cv2‘ ...· 1 年前 ·

俊逸的杯子 · 为一个列指定多个搜索条件 - Visual ...· 2 年前 ·

逆袭的芒果 · 用Python开发电影客户端 - 知乎· 2 年前 ·

酷酷的酱牛肉 · GMock测试析构函数调用 - 问答 - ...· 2 年前 ·

爱吹牛的香烟 · Count based on ...· 2 年前 ·

在视图合并中，优化器将表示视图的查询块合并到包含该视图的查询块中。
对于某些简单的视图，合并总是导致更好的计划，优化器会自动合并视图，而不考虑成本。否则，优化器将使用成本进行确定。优化器可能出于许多原因选择不合并视图，包括成本或有效性限制
如果OPTIMIZER_SECURE_VIEW_MERGING是true（默认），则 Oracle 数据库执行检查以确保视图合并和谓词推送不会违反视图创建者的安全意图。要为特定视图禁用这些额外的安全检查，您可以向MERGE VIEW用户授予此视图的权限。要对特定用户的所有视图禁用附加安全检查，您可以MERGE ANY VIEW向该用户授予权限。
#### 视图合并中的查询块
优化器通过单独的查询块表示每个嵌套的子查询或未合并的视图。
数据库自下而上分别优化查询块。因此，数据库首先优化最内层的查询块，为其生成部分计划，然后再为外层的查询块生成计划，代表整个查询。
解析器将查询中引用的每个视图扩展为单独的查询块。块本质上代表视图定义，因此代表视图的结果。优化器的一种选择是单独分析视图查询块，生成视图子计划，然后使用视图子计划处理查询的其余部分以生成整体执行计划。然而，这种技术可能会导致一个次优的执行计划，因为视图是单独优化的。
#### 简单的视图合并
视图对于简单的视图合并可能无效，因为：
- 该视图包含未包含在select-project-join视图重点 构造，包括（group by/distinct/外连接/model/connect by/设置运算符/聚合）
- 视图显示在一个右侧半连接或返连接
- 外部查询块包含PL/SQL函数
- 视图参与外部联结，并且不满足确定是否可以合并视图的几个附加有效性要求之一
``` sql
-以下查询将hr.employees表与dept_locs_v视图连接起来，该视图返回每个部门的街道地址。dept_locs_v是departments和locations表的连接。
SQL> set lines 200
SQL> set pages 999
SQL> explain plan for 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
    2    3    4    5    6    7        WHERE  d.location_id = l.location_id ) dept_locs_v
WHERE  dept_locs_v.department_id = e.department_id
AND    e.last_name = 'Smith';  8    9  
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1591417065
--------------------------------------------------------------------------------------------------
| Id  | Operation		      | Name		 | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	      | 		 |   972 | 46656 |    18   (6)| 00:00:01 |
|*  1 |  HASH JOIN		      | 		 |   972 | 46656 |    18   (6)| 00:00:01 |
|   2 |   MERGE JOIN		      | 		 |    27 |  1026 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| LOCATIONS	 |    23 |   713 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN	      | LOC_ID_PK	 |    23 |	 |     1   (0)| 00:00:01 |
|*  5 |    SORT JOIN		      | 		 |    27 |   189 |     3  (34)| 00:00:01 |
|   6 |     VIEW		      | index$_join$_003 |    27 |   189 |     2   (0)| 00:00:01 |
|*  7 |      HASH JOIN		      | 		 |	 |	 |	      | 	 |
|   8 |       INDEX FAST FULL SCAN    | DEPT_ID_PK	 |    27 |   189 |     1   (0)| 00:00:01 |
|   9 |       INDEX FAST FULL SCAN    | DEPT_LOCATION_IX |    27 |   189 |     1   (0)| 00:00:01 |
|  10 |   TABLE ACCESS BY INDEX ROWID | EMPLOYEES	 |   972 |  9720 |    13   (0)| 00:00:01 |
|* 11 |    INDEX RANGE SCAN	      | EMP_NAME_IX	 |   972 |	 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
   5 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
       filter("D"."LOCATION_ID"="L"."LOCATION_ID")
   7 - access(ROWID=ROWID)
  11 - access("E"."LAST_NAME"='Smith')
27 rows selected.
数据库可以通过加入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
#### 复杂的视图合并
在视图合并中，优化器合并视图包含GROUP BY和DISTINCT视图。与简单的视图合并一样，复杂的合并使优化器能够考虑额外的连接顺序和访问路径。
除成本之外，优化器可能无法执行复杂的视图合并，原因如下：
- 外部查询表没有 rowid 或唯一列。
- 该视图出现在CONNECT BY查询块中。
- 该视图包含GROUPING SETS，ROLLUP或PIVOT条款。
- 视图或外部查询块包含MODEL子句。
以下视图使用GROUP BY子句：
``` sql
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 件毛皮边饰毛衣的所有客户：
``` sql
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视图符合复杂视图合并的条件。合并后查询如下：
```sql
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操作成本较低。join比较贵是因为sales表没有缩小，但也不是贵很多，因为GROUP BY在原查询中操作并没有把行集的大小缩小很多。如果上述任何特征发生变化，合并视图可能不再是更低的成本。最终方案如下：
``` sql
--------------------------------------------------------
| 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运算符：
``` sql
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';
在确定视图合并产生一个低成本的计划后，优化器将查询重写为这个等效的查询：
``` sql
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')
如果不视图合并，那整个视图就会当成一整块，在sql执行的时候，这个视图就是一个结果集，然后再去和另一个结果集关联。如果合并了的话，那这个视图就会被拆散，视图里面的关联就会分开run，并不是每次视图合并都是高效的。
在执行计划中，如果看到view关键字（一般情况下），说明视图没有展开，也就是视图没有合并，如果本来sql中有内联视图或者视图，但执行计划中没有看到view关键字，那这个sql就进行了视图合并。
此外还需要注意的是，如果sql中的内联视图有聚合等操作，比如rownum，start with，connect by，union，union all，rollup，cube等，这种内联视图就不能展开，因为内联视图被固化了，碰到这种情况就需要注意，如果内联视图中结果集很大，那sql估计就要改写了，因为这个内联视图会最先执行

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29487349/viewspace-2791999/，如需转载，请注明出处，否则将追究法律责任。主要从事Oracle数据库 DBA相关工作，其他操作系统、中间件等也有涉及，热衷分享。岁月老将至，如有幸在江湖中留点踪迹，也是欣慰。【文盲筱烨】好读书爱运动的IT技术爱好者微博：文盲筱烨微信公众号：筱烨视点

注册时间： 2014-02-15

1010514

广播电视节目制作经营许可证(京) 字第1234号中国互联网协会会员