暂无图片
暂无图片
2
暂无图片
暂无图片
暂无图片
首页 / Group By视图的查询转换场景测试

Group By视图的查询转换场景测试

原创 张程 云和恩墨交付战队 2022-10-02
1058

第一章 适用范围

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

第二章 问题概述

在分析了客户环境多条类似SQL的查询表现后，发现含有group by的复杂视图，在做查询转换（视图合并或谓词推入）时，存在很多非预期的差异（部分写法只能做谓词推入，部分写法只能做视图合并），为了明确具体视图类型及其在做查询转换时的特定性能表现。研究测试了本篇文章的实验内容。以供后续总结归纳。

经过总结，具体测试场景包含如下几部分：
1.视图写法：
只含group by不含聚合列；在此基础上增加having选项；
包含group by和count聚合列；在此基础上增加having选项；
包含group by和sum/max等聚合列；在此基础上增加having选项。
2.视图与外表的关联写法：
左连接、内连接。

为了测试上述组合后一共12种匹配场景，设计了如下建表脚本：

CREATE TABLE TEST_T1 AS SELECT * FROM DBA_OBJECTS;
CREATE TABLE TEST_T2 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);

第三章 不同关联写法的实验对比

基于上一章节提及的不同写法产生的查询转换性能差异，通过不同的视图及关联方式匹配类型，设计了如下12类实验方式。总结测试差异表现。

3.1 视图没有聚合列

包含group by但不包含聚合列的视图。分别通过左连接/内连接测试查询表现。并增加HAVING选项继续测试。

3.1.1 左连接+视图

select *
  from test_t1
  left join (select object_id from test_t2 group by object_id) t2
    on test_t1.object_id = t2.object_id
 where test_t1.object_type = 'EDITION';

执行计划如下：

image.png

优化器对视图做了视图合并。与外部表直接关联。
测试是否可以做谓词推入，在SQL中禁用视图合并：

select *
  from test_t1
  LEFT join (select /*+ no_merge push_pred */
              object_id
               from test_t2
              group by object_id) t2
    on test_t1.object_id = t2.object_id
 where test_t1.object_type = 'EDITION';

执行计划如下：

image.png

在禁用了视图合并后，也无法通过谓词推入技术提升查询效率。直接全表访问整个视图，查询效率较低。

分析10053文件，可以看到：

image.png

优化器也尝试做谓词推入（JPPD），但本SQL的视图是一个非标准的group by视图。谓词推入被跳过。

3.1.2 左连接+视图+having选项

在3.1.1基础上增加having选项。

select *
  from test_t1
  left join (select object_id from test_t2 group by object_id
			having object_id<>0) t2
    on test_t1.object_id = t2.object_id
 where test_t1.object_type = 'EDITION';

执行计划如下：

image.png

原有的视图合并手段也失效了。继续分析10053文件：

CVM:     CVM bypassed: Reference to aggregate with HAVING clause in outer joined view or its outer query.

复杂视图合并被跳过：原因是在外连接时引用了带有HAVING子句的视图。
原本的视图已经无法做谓词推入，这里增加having选项并不能改变视图本身的结构，仍然无法做谓词推入，实验结论不再贴出。

3.1.3 内连接+视图

内连接相比外连接，具有更强的限制条件，因此推测内连接也可以顺利完成视图合并。

select *
  from test_t1
  INNER join (select object_id from test_t2 group by object_id) t2
    on test_t1.object_id = t2.object_id
 where test_t1.object_type = 'EDITION';

执行计划如下：

image.png

优化器对视图做了视图合并。与外部表直接关联。
测试是否可以做谓词推入，在SQL中禁用视图合并：

select *
  from test_t1
  INNER join (select /*+ no_merge push_pred */
              object_id
               from test_t2
              group by object_id) t2
    on test_t1.object_id = t2.object_id
 where test_t1.object_type = 'EDITION';

执行计划如下：

image.png

在禁用了视图合并后，也无法通过谓词推入技术提升查询效率。直接全表访问整改视图，查询效率较低。

分析10053文件，可以看到：

JPPD:     JPPD bypassed: View has non-standard group by.

优化器也尝试做谓词推入（JPPD），但本SQL的视图是一个非标准的group by视图。无法完成谓词推入。

3.1.4 内连接+视图+having选项

在3.1.3基础上增加having选项。

select *
  from test_t1
  inner join (select object_id from test_t2 group by object_id
			having object_id<>0) t2
    on test_t1.object_id = t2.object_id
 where test_t1.object_type = 'EDITION';

执行计划如下：

image.png

使用了视图合并。可以看到由于是内连接动作，优化器可以在视图合并后，在最后的部分实现having动作。因此视图合并技术还是有效。

原本的视图已经无法做谓词推入，这里增加having选项并不能改变视图本身的结构，仍然无法做谓词推入，实验结论不再贴出。

3.2 视图包含COUNT聚合列

包含group by同时包含count聚合列的视图。分别通过左连接/内连接测试查询表现。并增加HAVING选项继续测试。

3.2.1 左连接+视图

select *
  from test_t1
  left join (select object_id, count(data_object_id)
               from test_t2
              group by object_id) t2
    on test_t1.object_id = t2.object_id
 where test_t1.object_type = 'EDITION';

执行计划如下：

image.png

优化器默认使用了谓词推入技术。将外部关联条件推入到视图内部。
测试是否可以做视图合并，在SQL中禁用谓词推入：

select *
  from test_t1
  left join (select /*+ no_push_pred */
              object_id, count(data_object_id)
               from test_t2
              group by object_id) t2
    on test_t1.object_id = t2.object_id
 where test_t1.object_type = 'EDITION';

执行计划如下：

image.png

在禁用了谓词推入后，也无法完成视图合并动作。直接全表访问整个视图，查询效率较低。

分析10053文件，可以看到：

image.png

优化器在尝试做视图合并（CVM）时，发现外连接包含的表具有count列，认为该视图如果做视图合并不够安全，无法保证合并后与原查询完全等价。放弃了视图合并手段。

如果去掉对非法列的查询，即：不查count列（将外部查询select *改为select object_id）。相当于视图中没有count查询，则是可以做视图合并的。

select t2.object_id
  from test_t1
  left join (select /*+ no_push_pred */
              object_id, count(data_object_id)
               from test_t2
              group by object_id) t2
    on test_t1.object_id = t2.object_id
 where test_t1.object_type = 'EDITION';

image.png
顺利完成视图合并。也证明了无法完成视图合并的原因是查询的count列。

3.2.2 左连接+视图+having选项

在3.2.1基础上增加having选项。

select *
  from test_t1
 left join (select object_id, count(data_object_id)
               from test_t2
              group by object_id
			  having count(*)>1) t2
    on test_t1.object_id = t2.object_id
 where test_t1.object_type = 'EDITION';

执行计划如下：

image.png

原有的谓词推入手段也失效了。继续分析10053文件：

image.png

在跳过视图合并手段后，谓词推入也被跳过：认为包含having的视图是个非标准group by视图。
原本的视图已经无法做视图合并，这里增加having选项并不能改变视图本身的结构，仍然无法做视图合并，实验结论不再贴出。

3.2.3 内连接+视图

内连接相比外连接，具有更强的限制条件，因此推测内连接也可以顺利完成谓词推入。

select *
  from test_t1
 inner join (select object_id, count(data_object_id)
               from test_t2
              group by object_id) t2
    on test_t1.object_id = t2.object_id
 where test_t1.object_type = 'EDITION';

执行计划如下：

image.png

优化器默认使用了谓词推入技术。将外部条件推入到视图内部。且通过COUNT(*)>0判断是否有记录。

测试是否可以做视图合并，在SQL中禁用谓词推入：

select *
  from test_t1
 inner join (select /*+ no_push_pred */object_id, count(data_object_id)
               from test_t2
              group by object_id) t2
    on test_t1.object_id = t2.object_id
 where test_t1.object_type = 'EDITION';

执行计划如下：

image.png

在禁用了谓词推入后，也成功使用了视图合并技术。

3.2.4 内连接+视图+having选项

在3.2.3基础上增加having选项。

select *
  from test_t1
 inner join (select object_id, count(data_object_id)
               from test_t2
              group by object_id
			  having count(*)>1) t2
    on test_t1.object_id = t2.object_id
 where test_t1.object_type = 'EDITION';

执行计划如下：
image.png

默认使用了视图合并。可以看到由于是内连接动作，优化器可以在视图合并后，在最后的部分实现having动作。因此视图合并技术还是有效。

测试是否可以再做谓词推入：

select *
  from test_t1
 inner join (select /*+ no_merge */object_id, count(data_object_id)
               from test_t2
              group by object_id
			  having count(*)>1) t2
    on test_t1.object_id = t2.object_id
 where test_t1.object_type = 'EDITION';

image.png

无法使用谓词推入技术，也尝试考虑了谓词推入，但认为视图是个非标准视图，不再做谓词推入。

image.png

3.3 视图包含SUM/MAX聚合列

包含group by同时包含SUM/MAX等聚合列的视图。分别通过左连接/内连接测试查询表现。并增加HAVING选项继续测试。

3.3.1 左连接+视图

select *
  from test_t1
  left join (select object_id, sum(data_object_id), max(object_name)
               from test_t2
              group by object_id) t2
    on test_t1.object_id = t2.object_id
 where test_t1.object_type = 'EDITION';

执行计划如下：

image.png

优化器默认使用了谓词推入技术。将外部关联条件推入到视图内部。
测试是否可以做视图合并，在SQL中禁用谓词推入：

select *
  from test_t1
  left join (select /*+ no_push_pred */
              object_id, count(data_object_id)
               from test_t2
              group by object_id) t2
    on test_t1.object_id = t2.object_id
 where test_t1.object_type = 'EDITION';

执行计划如下：
image.png

在禁用了谓词推入后，也可以通过视图合并手段，与外部表做关联。提升查询效率。

3.3.2 左连接+视图+having选项

在3.3.1基础上增加having选项。

select *
  from test_t1
 left join (select object_id, sum(data_object_id), max(object_name)
               from test_t2
              group by object_id
			  having sum(data_object_id)>1) t2
    on test_t1.object_id = t2.object_id
 where test_t1.object_type = 'EDITION';

执行计划如下：
image.png

原有的谓词推入和视图合并手段都失效了。继续分析10053文件：

CVM:     CVM bypassed: Reference to aggregate with HAVING clause in outer joined view or its outer query.
JPPD:     JPPD bypassed: View has non-standard group by.

视图合并被跳过：原因是外连接包含having字句。
谓词推入被跳过：认为视图是个非标准视图。

3.3.3 内连接+视图

内连接相比外连接，具有更强的限制条件，因此推测内连接也可以顺利完成视图合并和谓词推入。

select *
  from test_t1
  inner join (select object_id, sum(data_object_id), max(object_name)
               from test_t2
              group by object_id) t2
    on test_t1.object_id = t2.object_id
 where test_t1.object_type = 'EDITION';

执行计划如下：

image.png
优化器默认使用了谓词推入技术。将外部条件推入到视图内部。且通过COUNT(*)>0判断是否有记录。

测试是否可以做视图合并，在SQL中禁用谓词推入：

select *
  from test_t1
 inner join (select /*+ no_push_pred */object_id, 
sum(data_object_id), max(object_name)
               from test_t2
              group by object_id) t2
    on test_t1.object_id = t2.object_id
 where test_t1.object_type = 'EDITION';

执行计划如下：

image.png

在禁用了谓词推入后，也成功使用了视图合并技术。

3.3.4 内连接+视图+having选项

在3.2.3基础上增加having选项。

select *
  from test_t1
 inner join (select object_id, sum(data_object_id), max(object_name)
               from test_t2
              group by object_id
			 sum(data_object_id)>1) t2
    on test_t1.object_id = t2.object_id
 where test_t1.object_type = 'EDITION';

执行计划如下：
image.png
默认使用了视图合并。可以看到由于是内连接动作，优化器可以在视图合并后，在最后的部分实现having动作。因此视图合并技术还是有效。

测试是否可以再做谓词推入：

select *
  from test_t1
 inner join (select /*+ no_merge */
object_id, sum(data_object_id), max(object_name)
               from test_t2
              group by object_id
			  having sum(data_object_id)>1) t2
    on test_t1.object_id = t2.object_id
 where test_t1.object_type = 'EDITION';

image.png

无法使用谓词推入技术，也尝试考虑了谓词推入，但认为视图是个非标准视图，不在做谓词推入。

image.png

第四章 实验结论总结

经过上述章节的实验分析，ORACLE优化器会基于不同的聚合及关联写法，采用相对合适的技术。只有数据库认为安全的且满足一定条件时，才会做相应的查询转换。

根据上述10053中的提示信息，总结出以下规律：

  1. 当视图中含有having子句，或者视图不包含聚合列时，都被认为是一个非标准视图，导致谓词推入（JPPD）被跳过；
  2. 当外连接视图，且视图中包含having子句时，会导致视图合并（CVM）被跳过；
  3. 当外连接视图，且视图包含count聚合列时，被认为是不安全的，视图合并（CVM）被跳过。

并根据上述实验，总结了下表可以做查询转换的场景：
以下用PUSH标识谓词推入，用MERGE标识视图合并。用VIEW标识无法做视图合并和谓词推入的场景。

image.png

上表为不同场景下可以完成的查询转换技术。因此在实际的编写SQL过程中，就要注意不同场景下可以完成的查询转换手段。对某些场景下的性能问题给与关注。

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

评论