Oracle 内联视图优化，视图合并的抉择

最新推荐文章于 2024-04-24 21:39:01 发布
最新推荐文章于 2024-04-24 21:39:01 发布
阅读量7.3k 收藏 6
点赞数
分类专栏： OCP试题 oracle系统性能优化及管理 oracle SQL语句优化调优 oracle开发SQL语句

关于oracle中什么是内联视图(in-line view)？


内联视图就是from后面再套一个select的子查询，例如：

select ename, job, dname, loc from (select ename, job, deptno

from emp where sal > (select avg(sal) from emp)) emp1,dept

where emp1.deptno = dept.deptno;

写sql的时候，我们还会经常的遇到真正的视图(create view …as select)

select …. from view where ……..；

有时候更变态的是，视图里面还套视图，一直给你套n个视图

在oracle ebs/sap/erp系统中经常遇见，要优化这样的sql，必须改代码

其实，视图套视图…..套一堆的视图，这种设计就是垃圾

如果非要这么设计，必须严格测试性能

 

本文摘自落落优化博客。

==========================================

每日一句SQL：内联视图

内联视图：

select * from (
select sal as salary,comm as commission from emp ) x
where salary <5000

 

     将取别名的查询作为内联视图，便可以在外部查询中引用其中的别名列。为什么要这么做呢？where 字句是在select 之前进行处理的，这样，在处理求解“问题”查询的where子句之前，Salary和Commission并不存在，要到where子句处理完之后，别名才生效。然后，From子句是在where之前处理的。 所以，将原查询放在FROM子句中，那么，在最外层的WHERE子句之前，以及最外层的WHERE子句“看到”别名之前，那么已经生成了查询结果。这个技巧特别有用。
     注意：这个解决方案中的内联视图取名为X。并非所有的数据库都需要内联视图显式给内联视图取别名，但一些数据库是这样子的。所有的DB都接受这种方式。

===========================================

OCP考题解析_007: 内联视图优化all或any操作符

分类： Oracle OCP考题解析

       个人的理解, 内联视图通常是指： 一个SQL查询的结果作为另一个查询的数据源, 一般在 From字句后面
       
       any表示数据集中的任何一个、相当于or
       x > any (select sal from emp where job='ANA')
       等价于：
       exists (select sal from emp where job='ANA' and x > sal)
       
       使用ALL和ANY的子查询总是可以用内嵌视图来代替，而且这个视图的性能要好的多，因为它利用了被连接表上的索引。
       
       比如：any操作符
       

       需求：返回所有birthday > 出生于1985年之后的任何客户 的职员名称

[sql] view plain copy print ?
  1. select ename  
  2. from emp  
  3. where birthdate > any  
  4.         (select birthdate from customer where birthdate > '31-DEC-1985')  


       上面的SQL语句可以优化为

[sql] view plain copy print ?
  1. select ename  
  2. from emp,(select min(birthdate) min_bday from customer where birthdate > '31-DEC-1985') in_line_view  
  3. where emp.birthdate > in_line_view.min_bday;  


       OCP考题：

[sql] view plain copy print ?
  1. Q: 8 Click the Exhibit button and examine the data from the ORDERS and  
  2. CUSTOMERS tables.  
  3. Evaluate this SQL statement:  
  4.   
  5. SELECT cust_id, ord_total  
  6. FROM orders  
  7. WHERE ord_total > ANY(SELECT ord_total  
  8. FROM orders  
  9. WHERE cust_id IN (SELECT cust_id  
  10. FROM customers  
  11. WHERE city LIKE  
  12. 'New York'));  
  13.   
  14. What is the result when the above query is executed?    


=====================================================


Oracle 内联视图优化，视图合并的抉择


内联视图in-line view，就是sql中from后面有select子查询，或者sql中包含有用create view创建的视图，CBO可能会将内联视图或者视图展开，进行相应的等价改写，这种就叫视图合并。直接看一个sql的执行计划

1. user_tables和dba_objects都是静态数据字典，我们来看下sql的执行计划

可以看到id=12这一步有一个view关键字，这一步正好是提取dba_objects视图的数据，然后此表和id=34这一步进行hash join，但hash join的表并不是sql当中的user_tables，而且在整个执行计划当中都找不到这个视图的信息，此视图被展开了。但这种情况我们是不需要展开的

2. 既然不需要展开，我们直接使用hint禁止视图合并

现在可以在执行计划中看到id=3和id=25这两步都是视图，通过hash join连接。

疑问：为什么这里不需要视图合并？

答曰：如果不视图合并，那整个视图就会当成一整块，在sql执行的时候，这个视图就是一个结果集，然后再去和另一个结果集关联。如果合并了的话，那这个视图就会被拆散，视图里面的关联就会分开run，并不是每次视图合并都是高效的。

在执行计划中，如果看到view关键字，说明视图没有展开，也就是视图没有合并，如果本来sql中有内联视图或者视图，但执行计划中没有看到view关键字，那这个sql就进行了视图合并。

此外还需要注意的是，如果sql中的内联视图有聚合等操作，比如rownum，start with，connect by，union，union all，rollup，cube等，这种内联视图就不能展开，因为内联视图被固化了，碰到这种情况就需要注意，如果内联视图中结果集很大，那sql估计就要改写了，因为这个内联视图会最先执行。

  • 本文固定链接: http://www.savedba.com/?p=816
  • 转载请注明: 版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!
============================================

3.1.1.1　内联视图合并

2013-02-25 16:45 黄玮 机械工业出版社 我要评论(0) 字号： T | T
一键收藏，随时查看，分享好友！

《Oracle高性能SQL引擎剖析：SQL优化与调优机制详解》第3章查询转换，在本章中，我们将会了解到以下内容：Oracle的逻辑优化技术中，存在哪些启发式查询转换技术，以及它们的具体含义和示例；Oracle的逻辑优化技术中，存在哪些基于代价的查询转换技术，以及它们的具体含义和示例。本节为大家介绍内联视图合并。

AD：2014WOT全球软件技术峰会北京站 课程视频发布

3.1.1.1　内联视图合并

我们以下面两个执行计划为例，简要说明视图合并技术对执行计划优化的影响，见代码清单3-1。

代码清单3-1　内联视图合并

   
   
  1. HELLODBA.COM>exec sql_explain('select /*+no_merge(o)*/* from t_tables t, v_objects_sys o where  
  2.              t.owner=o.owner and t.table_name = object_name and t.tablespace_name = :A and t.table_name  
  3.              like :B and o.status=:C', 'TYPICAL');  
  4. Plan hash value: 3284354748  
  5. ----------------------------------------------------------------------------------------------------  
  6. | Id  | Operation                         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |  
  7. ----------------------------------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT                  |                |     3 |   840 |    87   (3)| 00:00:01 |  
  9. |*  1 |  HASH JOIN                        |                |     3 |   840 |    87   (3)| 00:00:01 |  
  10. |   2 |   TABLE ACCESS BY INDEX ROWID     | T_TABLES       |     9 |  1836 |    13   (8)| 00:00:01 |  
  11. |   3 |    BITMAP CONVERSION TO ROWIDS    |                |       |       |            |          |  
  12. |   4 |     BITMAP AND                    |                |       |       |            |          |  
  13. |   5 |      BITMAP CONVERSION FROM ROWIDS|                |       |       |            |          |  
  14. |*  6 |       INDEX RANGE SCAN            | T_TABLES_IDX3  |   184 |       |     1   (0)| 00:00:01 |  
  15. |   7 |      BITMAP CONVERSION FROM ROWIDS|                |       |       |            |          |  
  16. |   8 |       SORT ORDER BY               |                |       |       |            |          |  
  17. |*  9 |        INDEX RANGE SCAN           | T_TABLES_PK    |   184 |       |     2   (0)| 00:00:01 |  
  18. |  10 |   VIEW                            | V_OBJECTS_SYS  |   571 | 43396 |    73   (0)| 00:00:01 |  
  19. |  11 |    TABLE ACCESS BY INDEX ROWID    | T_OBJECTS      |   571 | 47393 |    73   (0)| 00:00:01 |  
  20. |* 12 |     INDEX RANGE SCAN              | T_OBJECTS_IDX1 |   103 |       |     3   (0)| 00:00:01 |  
  21. ----------------------------------------------------------------------------------------------------  
  22.  
  23. Predicate Information (identified by operation id):  
  24. ---------------------------------------------------  
  25.    1 - access("T"."OWNER"="O"."OWNER" AND "T"."TABLE_NAME"="OBJECT_NAME")  
  26.    6 - access("T"."TABLESPACE_NAME"=:A)  
  27.    9 - access("T"."TABLE_NAME" LIKE :B)  
  28.        filter("T"."TABLE_NAME" LIKE :B AND "T"."TABLE_NAME" LIKE :B)  
  29.   12 - access("STATUS"=:C AND "OWNER"='SYS' AND "OBJECT_NAME" LIKE :B)  
  30.        filter("OBJECT_NAME" LIKE :B)  
  31.  
  32. PL/SQL procedure successfully completed.  
  33.  
  34. HELLODBA.COM>exec sql_explain('select * from t_tables t, v_objects_sys o where t.owner=o.owner and  
  35.              t.table_name = object_name and t.tablespace_name = :A and t.table_name like :B and o.status=:C',  
  36.              'TYPICAL OUTLINE');  
  37. Plan hash value: 2603737735  
  38. -----------------------------------------------------------------------------------------------------  
  39. | Id  | Operation                          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |  
  40. -----------------------------------------------------------------------------------------------------  
  41. |   0 | SELECT STATEMENT                   |                |     3 |   843 |    16   (7)| 00:00:01 |  
  42. |   1 |  TABLE ACCESS BY INDEX ROWID       | T_OBJECTS      |     1 |    77 |     3   (0)| 00:00:01 |  
  43. |   2 |   NESTED LOOPS                     |                |     3 |   843 |    16   (7)| 00:00:01 |  
  44. |   3 |    TABLE ACCESS BY INDEX ROWID     | T_TABLES       |     3 |   612 |     7  (15)| 00:00:01 |  
  45. |   4 |     BITMAP CONVERSION TO ROWIDS    |                |       |       |            |          |  
  46. |   5 |      BITMAP AND                    |                |       |       |            |          |  
  47. |   6 |       BITMAP CONVERSION FROM ROWIDS|                |       |       |            |          |  
  48. |*  7 |        INDEX RANGE SCAN            | T_TABLES_IDX3  |   184 |       |     1   (0)| 00:00:01 |  
  49. |   8 |       BITMAP CONVERSION FROM ROWIDS|                |       |       |            |          |  
  50. |   9 |        SORT ORDER BY               |                |       |       |            |          |  
  51. |* 10 |         INDEX RANGE SCAN           | T_TABLES_PK    |   184 |       |     2   (0)| 00:00:01 |  
  52. |* 11 |    INDEX RANGE SCAN                | T_OBJECTS_IDX1 |     1 |       |     2   (0)| 00:00:01 |  
  53. -----------------------------------------------------------------------------------------------------  
  54.  
  55. Outline Data  
  56. -------------  
  57.   /*+  
  58.       BEGIN_OUTLINE_DATA  
  59.       USE_NL(@"SEL$F5BB74E1" "T_OBJECTS"@"SEL$2")  
  60.       LEADING(@"SEL$F5BB74E1" "T"@"SEL$1" "T_OBJECTS"@"SEL$2")  
  61.       INDEX(@"SEL$F5BB74E1" "T_OBJECTS"@"SEL$2" ("T_OBJECTS"."STATUS" "T_OBJECTS"."OWNER"  
  62.               "T_OBJECTS"."OBJECT_NAME"))  
  63.       BITMAP_TREE(@"SEL$F5BB74E1" "T"@"SEL$1" AND(("T_TABLES"."TABLESPACE_NAME")  
  64.               ("T_TABLES"."TABLE_NAME" "T_TABLES"."OWNER")))  
  65.       OUTLINE(@"SEL$2")  
  66.       OUTLINE(@"SEL$1")  
  67.       MERGE(@"SEL$2")  
  68.       OUTLINE_LEAF(@"SEL$F5BB74E1")  
  69.       OPTIMIZER_FEATURES_ENABLE('10.2.0.4')  
  70.       IGNORE_OPTIM_EMBEDDED_HINTS  
  71.       END_OUTLINE_DATA  
  72.   */  
  73.  
  74. Predicate Information (identified by operation id):  
  75. ---------------------------------------------------  
  76.    7 - access("T"."TABLESPACE_NAME"=:A)  
  77.   10 - access("T"."TABLE_NAME" LIKE :B AND "T"."OWNER"='SYS')  
  78.        filter("T"."OWNER"='SYS' AND "T"."TABLE_NAME" LIKE :B AND "T"."TABLE_NAME" LIKE :B)  
  79.   11 - access("STATUS"=:C AND "OWNER"='SYS' AND "T"."TABLE_NAME"="OBJECT_NAME")  
  80.        filter("OBJECT_NAME" LIKE :B)  
  81.  
  82. PL/SQL procedure successfully completed. 

示例分析：上面第一个执行计划是未使用视图合并（通过提示强制）所生成的执行计划。它为视图V_OBJECTS生成了一个子计划（即操作10～12）。该计划从视图查询语句及主查询中获得的谓词条件为（"STATUS"=:C AND "OWNER"='SYS' AND "OBJECT_NAME" LIKE :B），并且优化器估算出它会返回571条数据记录。在这种情况下，优化器选择其与另外一个表T_TABLES做哈希关联，估算代价为49。

第二个执行计划使用了视图合并技术，在选择执行计划之前，用视图的依赖表T_OBJECTS替换了视图V_OBJECTS，并且将视图查询中WHERE条件"OWNER"='SYS'与主查询WHERE条件"STATUS"=:C和关联条件"T"."TABLE_NAME"="OBJECT_NAME"合并，最终决定由这些条件访问索引T_OBJECTS_IDX1后与表T_TABLES做嵌套关联，估算代价仅为9。

==============================
Oracle更新的两种方式（merge/update内联视图） 

分类： Oracle

原文地址：Oracle更新的两种方式（merge/update内联视图） 作者：旧梦依稀


Oracle更新的两种方式：一是用merge，二是用update 内联视图

对于Oracle的两表联合更新的场景（有A、B两表，以A.id=B.id关联，根据B表中的记录更新A表中的相应字段），一般有update内联视图和merge两种方式，下面举例介绍：

创建用例表：

CREATE TABLE test1(IDNUMBER(10),NAME VARCHAR2(20));

INSERT INTO test1VALUES(1,'lucy');

INSERT INTO test1VALUES(2,'lily');

 

CREATE TABLE test2(IDNUMBER(10),NAME VARCHAR2(20));

INSERT INTO test2VALUES(1,'lucy');

INSERT INTO test2VALUES(2,'hanmeimei');

 

merge方式：

MERGE INTO test1USING test2

ON (test1.id =test2.id)

WHEN MATCHED THENUPDATE

  SET test1.name =NVL2(test1.name,test2.name,test1.name);

 

merge方法是最简洁，效率最高的方式，在大数据量更新时优先使用这种方式。

 

update内联视图方式：

使用这种方式必须在test2.id上有主键（这里很好理解，必须保证每一个test1.id对应在test2里只有一条记录，如果test2中有多条对应的记录，怎么更新test1？），一般而言这种方式代价比merge方式稍高。

ALTERTABLE test2 ADD CONSTRAINT pk_test2 PRIMARY KEY(ID);

UPDATE (SELECT a.idaid,a.name aname,b.id bid,b.name bname FROM TEST1 a,test2 b WHERE a.id=b.id) t

SETaname = NVL2(aname,bname,aname);

 

使用并行，加快大量数据更新：

MERGE /*+parallel(test1,4)*/ INTO test1 USING test2

ON (test1.id =test2.id)

WHEN MATCHED THENUPDATE

  SET test1.name =NVL2(test1.name,test2.name,test1.name);


参见：
oracle 内联视图  谷歌


haiross
关注
  • 0
    点赞
  • 踩
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
视图优化oracle,干货｜Oracle复杂视图优化案例分析
weixin_36260304的博客
04-02 1141
在使用Oracle数据库过程中，视图是作为数据库对象存在的，因而，在创建了这样的视图后，就可以通过工具或者数据字典来查看视图的相关信息。视图来源于表，所有对视图数据的修改最终都会被反映到视图的基表中，这些修改必须服从基表的完整性约束，并同样会触发定义在基表上的触发器。Oracle的视图分为简单视图和复杂视图两种，简单视图就是从单一的表中获取数据，其中不包含函数和数据组等，复杂视图是指在视图定义中包...
Oracle 视图的创建、使用、删除
weixin_42321155的博客
09-08 7142
视图就是一张或多张表上的预定义查询。视图作用：减少子查询的复杂性；提高运行效率；可以仅提供视图数据，提高数据的安全性。视图以定义的方式存储在数据库中，不占用表空间。查询视图的时候就是执行视图里封装的逻辑，逻辑里用到的表称为基表。提示：以下是本篇文章正文内容，下面案例可供参考。
ORACLE优化总结
12-30
oralce 优化总结，提升语句访问速度
Oracle内联视图及其等价改写优化方法
最新发布
专注于数据库技术分享，包含但不限于Oracle，MySQL，PostgreSQL，ElasticSearch及国产数据库等
04-24 344
Oracle内联视图（Inline View）是一种在SQL查询中直接嵌入子查询的构造，它允许您在FROM子句中定义一个临时的、只在当前查询上下文中存在的逻辑视图。这个“视图”实际上是子查询的结果集，它在查询执行期间被计算，但不会存储为持久的数据库对象。内联视图有助于简化复杂查询、抽象数据处理逻辑以及实现查询优化。
视图性能优化——索引视图
yousss的博客
05-10 9450
原文：索引视图索引视图索引视图有助于提高 T-SQL 的性能Itzik Ben-GanT-SQL 是一种允许您以逻辑方式设计请求的语言。这里所说的“逻辑”的意思是，当编写查询时，您指定的是希望获得什么 结果，而不是希望以何种方式 获得结果。对如何处理查询进行设计是查询优化器的工作。您所遇到的需要 T-SQL 解决方案的每个问题通常都有许多不同的解决方案，它们最终会返回同样的结果。在理想情况下，假设...
视图之间传值的方法
08-24
4. **Block**：Block是一种内联函数，可以捕获和存储执行环境中的变量。视图A可以定义一个Block，作为参数传递给视图B。视图B在适当的时候调用这个Block，实现值的传递。Block可以嵌套，支持返回值，适用于需要回调...
内联视图 标量子查询 with子查询分解.txt
05-17
oracle
详解ASP.NET Core 2.0 视图引擎(译)
10-19
在本文中，我们将深入探讨如何在ASP.NET Core 2.0中使用Razor引擎创建视图，以及视图引擎的工作原理。 首先，要创建一个使用Razor视图的ASP.NET Core 2.0项目，你需要按照以下步骤操作： 1. 新建一个ASP.NET Core ...
ORACLE_SQL.rar_oracle
09-23
学习如何使用内联视图、物化视图、连接顺序、连接类型（如哈希JOIN、嵌套循环JOIN、并行JOIN）以及使用适当的JOIN条件，可以显著改善JOIN查询的效率。 4. **子查询**：子查询有时会导致性能下降。通过重写为JOIN、...
微信小程序 wxapp视图容器 view详解
09-01
2. `style`：内联样式，可以定义组件的CSS属性，如颜色、尺寸、位置等。 例如： ```html 标题 内容 ``` 这里的`.section`、`.section__title`和`.content`都是自定义的CSS类，用于控制组件的样式。 二、`...
oracle优化常用经典参考
conglueh193642的博客
04-08 652
2-调优步骤： 1设计调优 2应用调优程序员代码 3内存 4连接 5操作系统 2-已有产品的调优 1定...
oracle几个优化有用的视图
我思，故我在！--My data life
01-25 2453
目的：不要死记硬背script，深入理解view的含义，才能自如运用它们。 包括：V$SYSTEM_EVENT/ V$SESSION_EVENT/ V$SESSION_WAIT / V$WAITSTAT http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/sqlviews.htm#17389 V$SYST
oracle视图关联性能优化,Oracle 性能优化之高消耗的SQL
weixin_39520880的博客
04-03 292
高资源消耗的 SQL与用户执行 SQL 有关的动态视图有 v$sql、v$sqlarea、v$sqltext、v$sql_plan、 v$sqlstats 等。v$sql 中包含了所有用户执行的所有 SQL 信息，不同用户、不同会 话执行相同 SQL 的语义、执行计划可能会不同，这些 SQL 字面值相同(具有相同 的 sql_id)，通过不同的 child_number 来区分。v$sqlarea...
oracle视图混合子查询,《基于ORACLE的SQL优化读书》笔记 子查询展开/视图合并
weixin_42342010的博客
04-06 190
查询转换是硬解析的第一步，在9i 中，它在CBO之前执行。10G以后，属于CBO的一部分。CBO会对查询转换做成本估算。子查询展开：将子查询拆开：不能做子查询展开的子查询通常在目标SQL的执行计划的最后一步才会被执行，并且走FILTER类的执行方式。改写不基于成本！！where条件为exists,in,=any的子查询，ORACLE会转换成相应的半连接。Notexists, not in,<...
视图合并（View Merging）
Laughing
07-29 8151
在使用视图或嵌套视图的查询语句中，oracle 为了取得最优的执行计划会将这些视图进行合并，将视图中的表与外部查询的表进行连接。 --示例： SQL> select e.first_name, e.last_name, dept_locs_v.street_address, dept_locs_v.postal_code 2 from employees e, 3 (s
oracle 内联视图 案例
07-20
当使用内联视图时，你可以将一个查询嵌套在主查询的 FROM 子句中，以创建一个虚拟的、临时的表。这个内联视图的结果会作为一个表来处理，并与主查询的其他部分进行联接和过滤。下面是一个简单的 Oracle 内联视图的案例： 假设有两个表：employees（员工表）和 departments（部门表）。我们想要查询每个部门的员工数量，并且只返回员工数量大于等于 5 的部门。这个查询可以使用内联视图来实现。 ```sql SELECT d.department_name, v.employee_count FROM departments d JOIN ( SELECT department_id, COUNT(*) as employee_count FROM employees GROUP BY department_id ) v ON d.department_id = v.department_id WHERE v.employee_count >= 5; ``` 在这个例子中，内联视图 `(SELECT department_id, COUNT(*) as employee_count FROM employees GROUP BY department_id)` 是嵌套在主查询的 FROM 子句中。它使用了子查询来计算每个部门的员工数量，并将结果命名为 `employee_count`。主查询使用这个内联视图与部门表进行联接，并根据 `employee_count` 进行过滤。 这个例子展示了如何使用内联视图来动态地计算一个临时表，并将其结果与其他表进行联接和过滤。你可以根据自己的需求扩展和修改这个例子。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值