多个视图导致无法视图合并的研究

原创张程云和恩墨交付战队 2022-09-03

594

第一章适用范围

本案例中的问题现象发生并测试于当前主流的ORACLE 11G环境。不排除随着后续版本的升级可能会有一定细微的差异表现。但问题的原因及解决方案，在所有版本中都是通用的。

数据库版本:ORACLE 11G

第二章问题引入分析

在分析客户环境的一条SQL时，发现了无法做视图合并的现象。造成视图的访问比较低效。故此对案例做了进一步模拟及测试。以确定问题原因。

问题SQL：

SELECT *
  FROM (SELECT TEMP_ID,
               SUM(T.T1) T1,
               SUM(T.T2) T2,
               SUM(T.T3) T3,
               SUM(T.T4) T4,
               SUM(T.T5) T5,
               SUM(T.T6) T6,
               FILE_ID
          FROM T_BILL_TEMP T
          JOIN T_FILE FUD
            ON FUD.ID = T.FILE_ID
         WHERE FUD.FILE_MAIN_ID = :B1
         GROUP BY TEMP_ID, DETAIL_ID) T
  LEFT JOIN T_ACCID_TEMP C
    ON T.TEMP_ID = C.ID
  LEFT JOIN T_ACCID AC
    ON AC.SINGLE_CODE = C.SINGLE_CODE
  LEFT JOIN T_POLICY I
    ON I.POLICY_CODE = AC.POLICY_CODE
  LEFT JOIN T_PRODUCT_SORT S
    ON S.ID = C.PRODUCT_ID
  LEFT JOIN (SELECT AL.ACC_ID, AL.ACC_NAME
               FROM T_ACCID_P1 AL
              WHERE AL.ACC_NAME IS NOT NULL
              GROUP BY AL.ACC_ID, AL.ACC_NAME) L
    ON L.ACC_ID = C.ACC_NAME
  LEFT JOIN T_BILL P
    ON P.BILL_CODE = C.BILL_CODE
   AND P.CLAIM_ID = AC.ID
   AND P.CODE = T.TEMP_ID
 WHERE C.STATUS = '01'
   AND T.FILE_ID IN (SELECT ID FROM T_FILE WHERE FILE_MAIN_ID = :B1);

SQL文本中包含两个视图，和几张表的关联。对应的执行计划如下：

其中主要的耗时步骤见标红部分，均是由于缺少关联列索引，导致的全表扫描问题。建立索引即可。做了上述调整后，T_BILL_TEMP表已改为通过索引访问。奇怪的是T_ACCID_P1表，仍然无法通过索引访问，保持了现有的全表访问并聚合后，与其他表做HASH关联。

这里分析原因，我们希望对目标表T_ACCID_P1做视图合并，这样可以将外部连接条件与视图做关联。或者做谓词推入。都可以有效应用外部的过滤条件（外表仅有一行，即使考虑到T_ACCID_P1表的关联列上会有较多重复，预估的执行效率也会高于当前）

而实际优化过程中，不论如何添加HINT：push_pred、merge都无法改变当前执行计划。因此这里怀疑是触发了某种情况，导致优化器无法完成视图合并动作。

针对视图合并，通常包括：

简单视图合并（Simple View Merging）;
外连接视图合并（Ourter Join View Merging）;
复杂视图合并（Complex View Merging）

而本例中的视图，明细属于复杂视图合并，是需要考虑COST的，只有当合并后的成本小于原有成本时，才会进行合并。

基于上述问题现象，设计了如下测试脚本：以便于后续实验定位问题：

CREATE TABLE TEST_T1 AS SELECT * FROM DBA_OBJECTS;
CREATE TABLE TEST_T2 AS SELECT * FROM DBA_OBJECTS;
CREATE TABLE TEST_T3 AS SELECT * FROM DBA_OBJECTS;

create index idx_TEST_T1_type on TEST_T1(object_type);
create index idx_t2_objectid on TEST_T2(object_id);
create index idx_t3_objectid on TEST_T3(object_id);

第三章问题原因排查

基于上一章节的分析，分别设计了如下几种查询场景。以期望逐步定位问题原因及解决办法。

3.1 表与一个视图关联

首先测试表与单一视图做关联，是否可以完成视图合并：

select count(*)
  from TEST_T1
  left join (select 
              object_id, object_name
               from TEST_T2
              group by object_id, object_name) t2
    on TEST_T1.object_id = t2.object_id
 where TEST_T1.object_type = 'EDITION';

目标视图与案例中的结构保持一致。

执行计划如下：

外表与视图中的表做了合并，不再出现VIEW字样。

尝试添加HINT：no_merge。分析效果：

select count(*)
  from TEST_T1
  left join (select /*+ no_merge */
              object_id, object_name
               from TEST_T2
              group by object_id, object_name) t2
    on TEST_T1.object_id = t2.object_id
 where TEST_T1.object_type = 'EDITION';

视图单独查询。也进一步说明上面的查询转换确实是视图合并。

尝试做谓词推入：

select count(*)
  from TEST_T1
  left join (select /*+ no_merge */
              object_id, object_name,count(*)
               from TEST_T2
              group by object_id, object_name) t2
    on TEST_T1.object_id = t2.object_id
 where TEST_T1.object_type = 'EDITION';

经过测试，如果需要完成谓词推入，需要对视图增加COUNT等聚合函数，否则无法推入。由于本篇重点分析视图合并概念，上述谓词推入的具体原因不在此处展开。

3.2 表与多个视图关联

将表分别与T2、T3两个视图关联，符合案例中的两个视图场景。

select count(*)
  from TEST_T1
  left join (select object_id, object_name
               from TEST_T2
              group by object_id, object_name) t2
    on TEST_T1.object_id = t2.object_id
  left join (select object_id, object_name
               from TEST_T3
              group by object_id, object_name) t3
    on TEST_T1.object_id = t3.object_id
 where TEST_T1.object_type = 'EDITION';

测试执行计划：

增加了视图数量后，原本的T2视图也不再合并。不论如何添加HINT：MERGE。调整过滤条件、连接顺序等。都无法合并。也就是当前SQL中所有的复杂视图，都不能合并了。

这里分析是否是多个复杂视图导致的无法合并呢？

分析10053文件，查看是否能找到痕迹：
CVM - complex view merging

这里看到，复杂视图合并被跳过。原因是查询中超过了一个视图。分析到这里，基本上可以确定，是由于语句中包含多个视图，导致复杂视图合并被跳过。故此无法做视图合并。

3.3 表与视图做外连接合并关联

分析其余视图类型的合并表现：

尝试将第一个视图调整为外连接视图。即：不包含GROUP BY的形式。

select count(*)
  from TEST_T1
  left join (select /*+ merge */
              object_id, object_name
               from TEST_T2) t2
    on TEST_T1.object_id = t2.object_id
 left join (select /*+ merge */
              object_id, object_name
               from TEST_T3
              group by object_id, object_name) t3
    on TEST_T1.object_id = t3.object_id
 where TEST_T1.object_type = 'EDITION';

可以看到，调整后所有视图都顺利完成了合并。

这里分析：是否是因为首先完成外连接视图合并，这时SQL中只剩下一个T3的复杂视图，故此能合并呢？

select count(*)
  from TEST_T1
  left join (select /*+ no_merge */
              object_id, object_name
               from TEST_T2) t2
    on TEST_T1.object_id = t2.object_id
 left join (select /*+ merge */
              object_id, object_name
               from TEST_T3
              group by object_id, object_name) t3
    on TEST_T1.object_id = t3.object_id
 where TEST_T1.object_type = 'EDITION';

尝试让T2不要合并：

这里由于T2视图禁用了合并，采用了谓词推入的形式。但T2视图还在。再看T3，由于仍然存在大于一个视图，导致了T3也无法合并。即使指定了视图合并HINT。

再颠倒连接的顺序，尝试先连接T3，测试能否不受T2视图的影响，首先进行合并：

select /*+ ordered */count(*)
  from TEST_T1
 left join (select /*+ merge */
              object_id, object_name
               from TEST_T3
              group by object_id, object_name) t3
    on TEST_T1.object_id = t3.object_id
  left join (select /*+ no_merge */
              object_id, object_name
               from TEST_T2) t2
    on TEST_T1.object_id = t2.object_id
 where TEST_T1.object_type = 'EDITION';

结果是不好的，仍然无法先做复杂视图合并。也就是这里优化器能判断出当前所有的视图，一旦满足超过一个视图，则复杂视图合并被跳过。

3.4 尝试能否通过with写法绕过视图数限制

考虑将T2视图定义成with写法并做物化，目的是希望优化器把with部分当做一个单独的表处理，而不是当前的视图。查看是否可以顺利做合并。

with temp as
 (select /*+ materialize */
              object_id, object_name
               from TEST_T2
              group by object_id, object_name)
select count(*)
  from TEST_T1
  left join temp t2
    on TEST_T1.object_id = t2.object_id
 left join (select /*+ merge */
              object_id, object_name
               from TEST_T3
              group by object_id, object_name) t3
    on TEST_T1.object_id = t3.object_id
 where TEST_T1.object_type = 'EDITION';

执行计划如下：

尽管对T2视图做了物化动作，但下面对物化临时表访问时，仍然当做一个视图在处理。故此也无法做合并。

第四章解决方案

通过上一章节分析，基本明确，是由于语句中含有多个视图，导致的复杂视图合并被绕过。这里分析其影响还是很大的，因为实际环境中出现GROUP BY的复杂视图查询还是很常见的。那一但超过一个视图使用，则意味着复杂视图合并被跳过。可能造成一定的性能隐患。

经过分析，考虑有如下解决方案，可尝试避免性能问题：

4.1 通过谓词推入获得外部条件

在无法做视图合并的情况下，优化器是会考虑做谓词推入的。只是谓词推入也要考虑可推入的场景。如3.1章节案例中，需要增加count函数才能顺利推入谓词。

故此。第一个解决方案是需要对语句做一定的调整：

select count(*)
  from TEST_T1
  left join (select /*+ push_pred */
              object_id, object_name,count(*)
               from TEST_T2
              group by object_id, object_name) t2
    on TEST_T1.object_id = t2.object_id
 left join (select /*+ push_pred */
              object_id, object_name,count(*)
               from TEST_T3
              group by object_id, object_name) t3
    on TEST_T1.object_id = t3.object_id
 where TEST_T1.object_type = 'EDITION';

调整后，是可以通过谓词推入技术，避免大表的全表扫描及性能问题的。

4.2 通过临时表降低视图的数量

既然with处理后仍然是做一个视图，那就需要通过临时表的方式。数据库会视为一个表而不会认为是视图：

create global temporary table 
session_temp_tb (object_id number,object_name varchar2(100)) on commit preserve rows; 
CREATE INDEX IDX_TEMP_id on session_temp_tb(object_id);

将意图减少的视图提前写入临时表中：

insert into session_temp_tb
select object_id, object_name
       from TEST_T2
   group by object_id, object_name;
commit;

改写语句通过临时表代替视图：

select count(*)
  from TEST_T1
  left join session_temp_tb t2
    on TEST_T1.object_id = t2.object_id
 left join (select 
              object_id, object_name,count(*)
               from TEST_T3
              group by object_id, object_name) t3
    on TEST_T1.object_id = t3.object_id
 where TEST_T1.object_type = 'EDITION';

调整后去掉了一个视图，则可以顺利实现视图合并：

第五章问题总结

经过上述章节的分析，明确的主要问题原因是多个视图导致的语句中所有的复杂视图都无法合并。这类影响还是比较大的。

因此在书写代码的过程中，应该考虑能否省略不必要的视图。或者在无法省略的前提下，对代码做一定的处理，以规避性能问题隐患。

视图合并墨力计划

最后修改时间：2022-09-04 18:49:36

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者