暂无图片
暂无图片
5
暂无图片
暂无图片
暂无图片

额外的相等连接条件 导致预估行数不准的问题研究

941

第一章 适用范围

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

第二章 问题概述

问题SQL是至少包含对三个表的查询,且表1、表2是通过内连接做的等值关联。表3同时对表1、表2的等值连接列做关联。

造成的现象是预估行数远远低于实际行数。分析此类问题原因,优化器计算表3返回行数时,通过单个条件计算预估行数,在施加了第二个连接条件时,还会在第一个连接列的基础上再次根据连接列计算返回行数。两次的选择性计算导致预估行数不准的问题。

这类问题与两个连接条件存在某些关系的查询场景比较类似,一般是通过建立组合索引、扩展统计信息、动态采样等方案来解决。而本案例中前述表1、表2是两个表。上述方案均不可行。故此对此类问题做了深入的研究测试:

下面通过SQL案例进行说明:

select sum(a)
from (select 1 as a
from (select T3.policy_id,
T2.type_num,
T1.org_id,
T4.real_name,
T4.real_code,
T5.account_code,
T5.id1 ,
T6.id_name
from
T_TEST3 T3,
T_TEST1 T1,
T_TEST2 T2,

T_TEST4 T4,
T_TEST5 T5,
T_TEST6 T6
where 1 = 1
and T3.policy_id = T1.policy_id
and T3.policy_id = T2.policy_id

and T1.policy_id = T5.policy_id
and T1.item_id = T5.item_id
and T1.policy_id = T2.policy_id
and T1.org_id = T2.org_id

and T1.org_id = T4.customer_id
and T5.id1 = T6.id1
and T5.type <> 16
and T1.product_id in (1001,1002,1003,1004,1005,1006,1007,1008))
group by policy_id,
item_id,
type_num,
org_id,
real_name,
real_code,
id1,
id_name);

上述关键部分做了标粗处理。T_TEST3同时与T_TEST1、T_TEST2做关联。且T_TEST1、T_TEST2已通过相同列做了关联。
查看执行计划如下:

image.png

关联了T3(后续以表别名简称)后,返回结果只有1行。分析执行计划的耗时步骤:

image.png

主要来自于第14步骤的多次索引访问。

image.png

而之所以出现上述访问步骤,主要原因来自于T3表的预估行数为1。这里分析预估为1的原因很可能由于T3同时与T1、T2表做了关联。

第三章 问题分析优化

让我们分析查询逻辑:T1与T2的关联条件(T1.policy_id = T2.policy_id)已经限定了返回结果集是包含policy_id相等的记录。T3在与上面的结果集关联时,与任何一个表的policy_id关联后,得到的结果集必然也意味着能满足与另一个表的关联。因此我们分析:

SQL中T3的两个关联条件:

and T3.policy_id = T1.policy_id
and T3.policy_id = T2.policy_id

是完全可以省去任意一个的。

调整后测试执行效率表现:

image.png

调整后,数据库不会受到T3的两个关联条件的影响,最后的返回行数也是接近于T1、T2关联后的初始行数。也正是对上述T3表的预估行数的改变,导致数据库将最耗时的步骤(T5表的多次循环访问)提前。并改变连接方式通过HASH关联。只访问一次T5表,其执行效率也是能得到明显提升的。

为了验证上述设想方案是否符合预期,设计了如下更为简便、易读的查询脚本:

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_t3_id on TEST_t3(object_id);

exec dbms_stats.gather_table_stats(ownname=>'SZT',tabname=>'TEST_t1');
exec dbms_stats.gather_table_stats(ownname=>'SZT',tabname=>'TEST_t2');
exec dbms_stats.gather_table_stats(ownname=>'SZT',tabname=>'TEST_t3');

查询语句如下:

select count(*)
  from TEST_t1, TEST_t2, TEST_t3
 where TEST_t1.object_id = TEST_t2.object_id
   and TEST_t1.object_type = 'SEQUENCE'
   and TEST_t3.object_id = TEST_t1.object_id
   and TEST_t3.object_id = TEST_t2.object_id;

查询真实的执行计划如下:

image.png

T3关联后,直接将T1、T2关联后的预估行数(1970)降低为1。而实际上基于前述的分析,T3是对T1、T2的等值连接列做关联。T1、T2又可以看做是一个完整的集合。对集合的同一个列关联两次,理论上只应该计算一次选择率。而本例中优化器是将T1、T2作为两个表,通过access+filter共同作用,怀疑是这里造成了预估行数为1的问题。

进一步查看10053的优化成本信息:

image.png

正是由于与两个表同时过滤,优化器在计算选择率时,使用了带过滤的选择率计算公式ix_sel_with_filters。也就是ix_sel* ix_sel。最后选择率接近于0。循环嵌套时,又使用接近于0的选择率ix_sel_with_filters。最后预估返回行数仅为1。

而根据执行计划及SQL语义理解,在关联T3表时,T1、T2表已经满足了连接条件相等。且T3又是与上述满足相等条件的连接集合做关联。因此仅与一个表关联即可得到准确的选择率。而优化器却没有这么智能,重复的以两个条件做关联最后计算选择率,得到了不准确的预估返回结果。

基于此分析,进一步确定了我们的设想,可以去掉一个T3的关联条件:

select count(*)
  from TEST_t1, TEST_t2, TEST_t3
 where TEST_t1.object_id = TEST_t2.object_id
   and TEST_t1.object_type = 'SEQUENCE'
   and TEST_t3.object_id = TEST_t1.object_id;

调整后的执行计划测试:

image.png

调整后,省去了一步filter过滤的步骤,预估行数与实际更为接近。

image.png

进一步对比10053信息,可知优化器采用了ix_sel的索引选择率。也能真实的评估返回行数。

第四章 解决方案

经过上述章节的分析,对具有等值关联条件的多个连接表,再与其他表做相同条件的关联时,可以只写其中一个表即可。不必将所有的关联条件写全(这会导致计算选择率不准确)

即使字段名不同,只要满足对相等关联条件做其他表的等值关联,也是可以省去不必要的连接条件的。

如下举例说明:

select count(*)
  from TEST_t1, TEST_t2, TEST_t3
 where TEST_t1.object_id = TEST_t2.data_object_id
   and TEST_t1.object_type = 'SEQUENCE'
   and TEST_t3.OBJECT_ID2 = TEST_t1.object_id
   and TEST_t3.OBJECT_ID2 = TEST_t2.data_object_id;

执行计划如下:

image.png

只要满足T1、T2表的内连接,且包含等值关联条件。
在与T3关联时,就可以省去一部分的T3关联条件:

select count(*)
  from TEST_t1, TEST_t2, TEST_t3
 where TEST_t1.object_id = TEST_t2.data_object_id
   and TEST_t1.object_type = 'SEQUENCE'
   and TEST_t3.OBJECT_ID2 = TEST_t1.object_id;

执行计划如下:

image.png

第五章 问题总结

本案例是通过实验模拟了内连接时相等连接条件,再与其他表关联过程中如果写了额外连接条件所导致的后果。优化器并没有做足够多的判断,T3表所关联的列是否已经满足了相等条件。造成了错误的预估。因此这类问题我们在明确问题原理后,需要在实际的代码编写过程中进行预防,避免预估行数不准造成的SQL性能问题。

最后修改时间:2022-10-10 09:18:06
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论