oracle的查询转换之复杂视图合并

最新推荐文章于 2024-01-24 15:48:10 发布
最新推荐文章于 2024-01-24 15:48:10 发布
阅读量291 收藏
点赞数
文章标签： 数据库

本文参考链接：https://blogs.oracle.com/optimizer/entry/optimizer_transformations_view_merging_part_2

Complex view merging, for distinct and group by views.
==》复杂视图合并是指视图定义中含有 distinct或group by的视图合并。

Delaying a group-by until after joins can result in a reduction in the data set on which the group-by operation is to be performed, if joins are filtering; on the other hand, early group-by can reduce the amount of data to be processed by subsequent joins or the joins could explode the amount of data to undergo group-by. The same is true for distinct operations. Because it is not always better to merge such a view, we choose whether to use this transformation in a cost-based manner. The two options - with and without view merging - are each costed by the optimizer, and we choose to merge the view only if it is cheaper to do so.
 ==》如果表连接能够有效的过滤数据，那么group by可以在视图合并的表连接后再做。
    如果group by能够有效过滤数据而视图合并的表连接不行，那么就要先做group by的操作。
    该原理同样适用于distinct的操作。
    因为复杂视图合并并不能总是获得最优化的执行计划。
    所以只有当复杂视图合并后的sql成本小于原sql的成本时，oracle才会执行复杂视图合并。
 示例1：展示了含有group by视图语句
  1. create view cust_prod_totals_v as
  2. select sum(s.quantity_sold) total, s.cust_id, s.prod_id
  3. from sales s
  4. group by s.cust_id, s.prod_id;

  6. select c.cust_id, c.cust_first_name, c.cust_last_name, c.cust_email
  7. from customers c, products p, cust_prod_totals_v
  8. where c.country_id = 52790
  9. and c.cust_id = cust_prod_totals_v.cust_id
  10. and cust_prod_totals_v.total > 100
  11. and cust_prod_totals_v.prod_id = p.prod_id
  12. and p.prod_name = '256MB Memory Card';

  14. ----------------------------------------------------------
  15. Plan hash value: 263021282

  17. ---------------------------------------------------------------------------------------------------------------------
  18. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
  19. ---------------------------------------------------------------------------------------------------------------------
  20. | 0 | SELECT STATEMENT | | 1 | 101 | 517 (7)| 00:00:07 | | |
  21. | 1 | NESTED LOOPS | | | | | | | |
  22. | 2 | NESTED LOOPS | | 1 | 101 | 517 (7)| 00:00:07 | | |
  23. | 3 | NESTED LOOPS | | 1 | 56 | 516 (7)| 00:00:07 | | |
  24. | 4 | VIEW | CUST_PROD_TOTALS_V | 1 | 26 | 515 (7)| 00:00:07 | | |
  25. |* 5 | FILTER | | | | | | | |
  26. | 6 | HASH GROUP BY | | 1 | 12 | 515 (7)| 00:00:07 | | |
  27. | 7 | PARTITION RANGE ALL | | 918K| 10M| 489 (2)| 00:00:06 | 1 | 28 |
  28. | 8 | TABLE ACCESS FULL | SALES | 918K| 10M| 489 (2)| 00:00:06 | 1 | 28 |
  29. |* 9 | TABLE ACCESS BY INDEX ROWID| PRODUCTS | 1 | 30 | 1 (0)| 00:00:01 | | |
  30. |* 10 | INDEX UNIQUE SCAN | PRODUCTS_PK | 1 | | 0 (0)| 00:00:01 | | |
  31. |* 11 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 0 (0)| 00:00:01 | | |
  32. |* 12 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 45 | 1 (0)| 00:00:01 | | |
  33. ---------------------------------------------------------------------------------------------------------------------
  34. Predicate Information (identified by operation id):
  35. ---------------------------------------------------
  36.    5 - filter(SUM("S"."QUANTITY_SOLD")>100)
  37.    9 - filter("P"."PROD_NAME"='256MB Memory Card')
  38.    省略部分...

==》这里步骤4出现了关键字view并且name列为‘CUST_PROD_TOTALS_V’，说明这里未做复杂合并。

示例2：在示例1的代码上使用了hint,强制走视图合并。
  1. select /*+ merge(cust_prod_totals_v) */ c.cust_id, c.cust_first_name, c.cust_last_name, c.cust_email
  2. from customers c, products p, cust_prod_totals_v
  3. where c.country_id = 52790
  4. and c.cust_id = cust_prod_totals_v.cust_id
  5. and cust_prod_totals_v.total > 100
  6. and cust_prod_totals_v.prod_id = p.prod_id
  7. and p.prod_name = '256MB Memory Card';

  9. Execution Plan
  10. ----------------------------------------------------------
  11. Plan hash value: 489274641

  13. ------------------------------------------------------------------------------------------------------------------------
  14. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
  15. ------------------------------------------------------------------------------------------------------------------------
  16. | 0 | SELECT STATEMENT | | 639 | 70929 | 829 (1)| 00:00:10 | | |
  17. |* 1 | FILTER | | | | | | | |
  18. | 2 | HASH GROUP BY | | 639 | 70929 | 829 (1)| 00:00:10 | | |
  19. |* 3 | HASH JOIN | | 12761 | 1383K| 828 (1)| 00:00:10 | | |
  20. | 4 | NESTED LOOPS | | | | | | | |
  21. | 5 | NESTED LOOPS | | 12762 | 672K| 422 (0)| 00:00:06 | | |
  22. |* 6 | TABLE ACCESS FULL | PRODUCTS | 1 | 42 | 3 (0)| 00:00:01 | | |
  23. | 7 | PARTITION RANGE ALL | | | | | | 1 | 28 |
  24. | 8 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
  25. |* 9 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX | | | | | 1 | 28 |
  26. | 10 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 12762 | 149K| 422 (0)| 00:00:06 | 1 | 1 |
  27. |* 11 | TABLE ACCESS FULL | CUSTOMERS | 18693 | 1040K| 405 (1)| 00:00:05 | | |
  28. ------------------------------------------------------------------------------------------------------------------------
  29. Predicate Information (identified by operation id):
  30. ---------------------------------------------------
  31.    1 - filter(SUM("S"."QUANTITY_SOLD")>100)
  32.    3 - access("C"."CUST_ID"="S"."CUST_ID")
  33.    省略部分...
==》 这里HASH JOIN出现在了 HASH GROUP BY 操作之前，说明做了视图合并。
    但这里的成本值为829，大于未做视图合并时的517。这就是示例1未做视图合并的原因。  
 示例3：示例2的等价改写
  1. select c.cust_id, cust_first_name, cust_last_name, cust_email
  2. from customers c, products p, sales s
  3. where c.country_id = 52790
  4. and c.cust_id = s.cust_id
  5. and s.prod_id = p.prod_id
  6. and p.prod_name = '256MB Memory Card'
  7. group by s.cust_id, s.prod_id, p.rowid, c.rowid,
  8. c.cust_email, c.cust_last_name, c.cust_first_name, c.cust_id
  9. having sum(s.quantity_sold) > 100;
  10.   
  11.   Execution Plan
  12. ----------------------------------------------------------
  13. Plan hash value: 489274641

  15. ------------------------------------------------------------------------------------------------------------------------
  16. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
  17. ------------------------------------------------------------------------------------------------------------------------
  18. | 0 | SELECT STATEMENT | | 1 | 111 | 829 (1)| 00:00:10 | | |
  19. |* 1 | FILTER | | | | | | | |
  20. | 2 | HASH GROUP BY | | 1 | 111 | 829 (1)| 00:00:10 | | |
  21. |* 3 | HASH JOIN | | 12761 | 1383K| 828 (1)| 00:00:10 | | |
  22. | 4 | NESTED LOOPS | | | | | | | |
  23. | 5 | NESTED LOOPS | | 12762 | 672K| 422 (0)| 00:00:06 | | |
  24. |* 6 | TABLE ACCESS FULL | PRODUCTS | 1 | 42 | 3 (0)| 00:00:01 | | |
  25. | 7 | PARTITION RANGE ALL | | | | | | 1 | 28 |
  26. | 8 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
  27. |* 9 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX | | | | | 1 | 28 |
  28. | 10 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 12762 | 149K| 422 (0)| 00:00:06 | 1 | 1 |
  29. |* 11 | TABLE ACCESS FULL | CUSTOMERS | 18693 | 1040K| 405 (1)| 00:00:05 | | |
  30. ------------------------------------------------------------------------------------------------------------------------
  31. Predicate Information (identified by operation id):
  32. ---------------------------------------------------
  33.    1 - filter(SUM("S"."QUANTITY_SOLD")>100)
  34.    3 - access("C"."CUST_ID"="S"."CUST_ID")
  35.    省略部分...
==》这里的执行计划和示例2是一致的。

接下来我们来看下含有distinc函数的复杂视图合并。
示例4:
  1. select c.cust_id, c.cust_first_name, c.cust_last_name, c.cust_email
  2. from customers c, products p,
  3. (select distinct s.cust_id, s.prod_id
  4. from sales s) cust_prod_v
  5. where c.country_id = 52790
  6. and c.cust_id = cust_prod_v.cust_id
  7. and cust_prod_v.prod_id = p.prod_id
  8. and p.prod_name = '256MB Memory Card';
  9. Execution Plan
  10. ----------------------------------------------------------
  11. Plan hash value: 2262313492

  13. --------------------------------------------------------------------------------------------------------------------------------
  14. | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
  15. --------------------------------------------------------------------------------------------------------------------------------
  16. | 0 | SELECT STATEMENT | | 12761 | 797K| | 1141 (1)| 00:00:14 | | |
  17. | 1 | VIEW | VM_NWVW_1 | 12761 | 797K| | 1141 (1)| 00:00:14 | | |
  18. | 2 | HASH UNIQUE | | 12761 | 1345K| 1584K| 1141 (1)| 00:00:14 | | |
  19. |* 3 | HASH JOIN | | 12761 | 1345K| | 828 (1)| 00:00:10 | | |
  20. | 4 | NESTED LOOPS | | | | | | | | |
  21. | 5 | NESTED LOOPS | | 12762 | 635K| | 422 (0)| 00:00:06 | | |
  22. |* 6 | TABLE ACCESS FULL | PRODUCTS | 1 | 42 | | 3 (0)| 00:00:01 | | |
  23. | 7 | PARTITION RANGE ALL | | | | | | | 1 | 28 |
  24. | 8 | BITMAP CONVERSION TO ROWIDS | | | | | | | | |
  25. |* 9 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX | | | | | | 1 | 28 |
  26. | 10 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 12762 | 112K| | 422 (0)| 00:00:06 | 1 | 1 |
  27. |* 11 | TABLE ACCESS FULL | CUSTOMERS | 18693 | 1040K| | 405 (1)| 00:00:05 | | |
  28. --------------------------------------------------------------------------------------------------------------------------------
  29. Predicate Information (identified by operation id):
  30. ---------------------------------------------------
  31.    3 - access("C"."CUST_ID"="S"."CUST_ID")
  32.    6 - filter("P"."PROD_NAME"='256MB Memory Card')
  33.    省略部分...
So why do we still have a view after we've supposedly merged the view? The new view is what we call a "projection view". When we merge the view, we move the distinct to the outer query block. But when we move the distinct, we have to add several additional columns, in order to maintain semantic equivalence with the original query. So we put all of that into a new view, so we can select out just the columns we want in the outer query block's select list. 

==》这段英文描述解释了步骤1出现了关键字view且name列为‘VM_NWVW_1’的原因。
 大致意思是说，做视图合并时为了保证语义的完整性需要添加额外的列，将查询列和新增列放在一个查询块中。
 我们最后只需要在外部查询中选出我们所需要的列即可。
 示例5： 示例4的等价改写
  1. select VM_NWVW_1.cust_id, VM_NWVW_1.cust_first_name, VM_NWVW_1.cust_last_name, VM_NWVW_1.cust_email
  2. from
  3. (select distinct s.cust_id as cust_id,
  4.        c.cust_first_name as cust_first_name,
  5.      c.cust_last_name as cust_last_name ,
  6.      c.cust_email as cust_email,
  7.      c.rowid,
  8.      p.rowid
  9. from customers c, products p,sales s
  10. where c.country_id = 52790
  11. and c.cust_id = s.cust_id
  12. and s.prod_id = p.prod_id
  13. and p.prod_name = '256MB Memory Card') VM_NWVW_1;
  14. Execution Plan
  15. ----------------------------------------------------------
  16. Plan hash value: 2322365470

  18. --------------------------------------------------------------------------------------------------------------------------------
  19. | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
  20. --------------------------------------------------------------------------------------------------------------------------------
  21. | 0 | SELECT STATEMENT | | 12761 | 797K| | 1141 (1)| 00:00:14 | | |
  22. | 1 | VIEW | | 12761 | 797K| | 1141 (1)| 00:00:14 | | |
  23. | 2 | HASH UNIQUE | | 12761 | 1345K| 1584K| 1141 (1)| 00:00:14 | | |
  24. |* 3 | HASH JOIN | | 12761 | 1345K| | 828 (1)| 00:00:10 | | |
  25. | 4 | NESTED LOOPS | | | | | | | | |
  26. | 5 | NESTED LOOPS | | 12762 | 635K| | 422 (0)| 00:00:06 | | |
  27. |* 6 | TABLE ACCESS FULL | PRODUCTS | 1 | 42 | | 3 (0)| 00:00:01 | | |
  28. | 7 | PARTITION RANGE ALL | | | | | | | 1 | 28 |
  29. | 8 | BITMAP CONVERSION TO ROWIDS | | | | | | | | |
  30. |* 9 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX | | | | | | 1 | 28 |
  31. | 10 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 12762 | 112K| | 422 (0)| 00:00:06 | 1 | 1 |
  32. |* 11 | TABLE ACCESS FULL | CUSTOMERS | 18693 | 1040K| | 405 (1)| 00:00:05 | | |
  33. --------------------------------------------------------------------------------------------------------------------------------
  34. Predicate Information (identified by operation id):
  35. ---------------------------------------------------
  36.    3 - access("C"."CUST_ID"="S"."CUST_ID")
  37.    6 - filter("P"."PROD_NAME"='256MB Memory Card')
  38.    省略部分...
==》这里可以看到除了步骤1view的name列为空外，其它都示例4的执行计划一致。

最后介绍2个控制视图合并的隐含擦参数
_optimizer_cost_based_transformation 
默认值为LINEAR，及根据转换前后的成本对比来决定是否视图合并。
若将值设置为off，则表示能做视图合并的一定做视图合并而不考虑成本。
_complex_view_merging 
默认值为TRUE，表示默认情况下允许复杂视图合并。
 
end!

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25923810/viewspace-2130036/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25923810/viewspace-2130036/

cty48663
关注
oracle多个视图整合,SQL关联方式-视图合并 （View Merging）
weixin_30670859的博客
04-06 1944
周末时，近日有网友朋友向我提问，“什么是视图合并？多次看到这个名词，但是每次都是扎入到执行计划中去分析了，但是对这个概念和功能完全不清楚，干啥用的？” 对于这个概念，趁赶着这个问题，自己也好好复习一下。一.什么是视图合并(View Merging)？我们通过一个SQL案例来说明，现有如下SQL：SQL1:select emp1.empno, emp1.ename, emp1.commfrom em...
Oracle SQL谓词推进、视图合并
07-07 1542
Oracle SQL谓词推进、视图合并
Oracle查询转换（二）复杂视图合并
cuishan5183的博客
06-14 283
视图的定义及分类参考：http://blog.itpub.net/28536251/viewspace-1871039/ 如果目标SQL中包含了视图，且该视图为一个复杂视图，则优化器会评估视图合并的成本，如果成本低于原S...
Oracle 视图 (二) 复杂视图
xy的博客
10-11 95
theme: smartblue 一、复杂视图 1.1 定义 视图的SQL语句中，有聚合函数或多表查询 1.2 案例一(键保留表) 1.2.1 需求 创建视图，连接给出的两张表 1.2.2 语句 1.2.3 修改 当修改的字段属于键保留表时，可以修改 当要修改的字段不属于键保留表时，不可以修改 键保留表：把主键保留下来的表 countries 存在主键，...
oracle创建复杂的视图
萧寒专栏
02-07 3409
分组视图<br />例1：<br />CREATE OR REPLACE VIEW dept_tot as<br />select a.dname dept,sum(b.sal) total_sal from dept a,emp b<br />where a.deptno=b.deptno group by a.dname<br /> <br />例2：<br />CRATE OR REPLACE ITEMTOT AS <br />SELECT PERSION,SUM(AMOUNT) ITEMTOT<br
Oracle查询转换之视图合并
weixin_33948416的博客
10-06 763
一.简单视图合并：指针对那些不含外连接，以及所带视图定义sql语句中不含distinct，group by等聚合函数的目标sql的视图合并。createorreplaceviewview_1as SELECTt2.prod_id FROMsalest2,customerst3 WHEREt2.cust_id=t3.cust_id AND...
Oracle CBO几种基本的查询转换详解
09-10
在本篇内容中，我们将深入探讨Oracle CBO的几种基本查询转换，这些转换有助于提高查询性能，包括视图合并、子查询解嵌套、谓语前推和物化视图查询重写。 1. 视图合并 视图合并是CBO优化过程中的一个常见操作，它将...
oracle 将多张表中的列合并到一张表中
09-09
在Oracle数据库中，有时我们需要将来自多个表的列合并到一张新表中，以便于数据分析或报表生成。本文将详细讲解如何使用Oracle SQL语句来实现这个目标。 首先，我们回顾一下问题背景：假设我们有两个表，表A和表B，...
hint oracle group by,ORACLE的视图合并HINT
weixin_33914982的博客
04-05 604
第一章ORACLE的视图合并介绍1.1视图合并首先介绍一下ORACLE的视图合并概念：视图合并(View Merging)是优化器处理带视图的目标SQL的一种优化手段，它是指优化器不再将目标SQL中视图的定义语句当做一个独立的处理单元来执行，而是会将其拆开，把其定义SQL语句的基表拿出来与外表合并，合并后的SQL将只剩下外部查询的表和原视图中的基表，不再有视图出现。视图合并的好处是让优化器有更多的...
oracle的complex,Oracle 10g的隐含参数_complex_view_merging引发的性能问题
weixin_39873741的博客
04-05 334
今天在Oracle 10g上碰到一个奇怪的问题，有一条sql在数据库1上很快，在数据库2上很慢，数据库2的数据是从数据库1上导的，数据量差不多。在数据库1上执行0.01s。SQL> SELECT A.*,2 B.INCREASE_ID,3 B.TRANSACTION_ID,4 B.LINK_CARD_ID,5 B.VALIDAT...
Oracle查询转换（一）简单视图合并
cuishan5183的博客
06-12 288
视图的定义及分类参考：http://blog.itpub.net/28536251/viewspace-1871039/ 如果目标SQL中包含了视图，且该视图为一个简单视图，则优化器会将视图拆开，直接使用...
oracle中的视图
liunan0828的专栏
08-06 315
描述：视图是虚拟存在的，并没有实际的对应物件，所以无法对view的资料进行修改。 只能通过view寻找到对应的table，再针对table进行数据的操作， 对table进行数据操作后，结果会反应到调用该table的view中。   例如： CREATE  OR REPLACE  VIEW view_name AS SELECT * from table1 where  union  ...
视图优化oracle,干货｜Oracle复杂视图优化案例分析
weixin_31900373的博客
04-02 467
(2)修改视图定义，将谓词前提写入至基表前提中，并从新编译视图。在应用Oracle数据库过程中，视图是作为数据库对象存在的，因而，在创建了如许的视图后，就可以经由过程对象或者数据字典来查看视图的相干信息。视图来源竽暌冠表，所有对视图数据的修改最终都邑被反竽暌钩到视图的基表中，这些修改必须服大年夜基表的完全性束缚，并同样会触发定义在基表上的触发器。Oracle的视图分为简单视图和复杂视凸?现，简单视...
12.查询转换-视图合并技术
最新发布
weixin_43346403的博客
01-24 477
如果不做视图合并最优:/*+no_merge(view_1)*/视图合并并不总是是最优的执行计划，要视图具体情况来看。如果视图合并最优:/*+merge(view_1)*/2.简单视图合并的例子。
Oracle（二）--> 视图详解（高清多图，还怕懂不了？）
BookFriend の Talking
08-27 1589
一、什么是视图。 1、视图是一种虚表； 2、视图建立在已有表的基础上，视图来一建立的这些表称为基表； 3、向视图提供数据内容的语句为 SELECT 语句，可以将视图理解为存储起来的 SELECT语句； 4、视图向用户提供基表数据的另一种表现形式。 二、为什么使用视图 1、提供了另外一种级别的表的安全性（控制数据访问：不想让每个人都看到） 2、隐藏了数据的复杂性 3、简化了用
Oracle数据库：创建和删除视图view，简单和复杂视图，内建视图，topN分析，oracle分页查询
weixin_46838716的博客
10-30 2416
Oracle数据库：创建和删除视图view，简单和复杂视图，内建视图，topN分析，oracle分页查询
达梦 dm.ini 设置实例文件
weixin_44265105的博客
08-23 2830
#DaMeng Database Server Configuration file #this is comments #file location of dm.ctl CTL_PATH = /TRS/data/DAMENG/dm.ctl #ctl file path CTL_BAK_PATH = /TRS/data/DAMENG/ctl_bak #dm.ctl backup...
达梦新建视图后，无法查询，web控制台“引用列未找到“的处理
nenuo的专栏
09-13 793
解决过程 tomcat启动就卡在了这个位置怎么都无法进行下去了。于是乎在网上百度了一阵。基本方法如下： 第一种：严格遵守java规范，修改对象的属性名称，要求不包含java关键字; 第二种：修改EL表达式，例如"${owner.new}"可以修改为"${owner['new']}"; 第三种：修改tomcat属性，忽略对EL表达式的关键字检查。修改$CATALINA_BASE/conf/catalina.properties文件，添加org.apache.el.parser.SKIP_IDENT..
DM数据库：dm.ini参数（二）查询相关参数
qq_44753286的博客
07-12 4413
2.3 线程相关参数 2.3.1 WORKER_THREADS 缺省值： 4 属性： 静态 说明： 工作线程的数目，有效值范围（1~64） 2.3.2 TASK_THREADS 缺省值： 4 属性： 静态 说明： 任务线程个数，有效值范围（1~1000） 2.3.3 UTHR_FLAG 缺省值： 0 属性： 手动 说明： 用户线程标记，1：启用；0：不启用。启用用户线程时，并行查询失效，并行查询的相关参数不起作用； 2.3.4 FAST_RW_LOCK 缺省值： 1 属性： 手动 说明： 快速读写锁标记，0
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值