一条半连接SQL的优化过程

最近一直在看落落老师的书,受益匪浅,举其中提到的一个案例。

直接通过模拟实验来说明,在Oracle 11.2.0.4下,创建两张表,

SQL> create table a as select *  from dba_objects;
Table created.


SQL> create table b as select * from dba_objects;
Table created.

执行如下SQL,等了很久,只能强行ctrl+c,

SQL> select count(distinct owner), count(distinct object_name) from a where owner in (select owner from b);
^Cselect count(distinct owner), count(distinct object_name) from a where owner in (select owner from b)
                                                               *
ERROR at line 1:
ORA-01013: user requested cancel of current operation

如果不带distinct,结果集很快返回,

SQL> select count(a.owner), count(a.owner) from a where a.owner in (select owner from b);
COUNT(A.OWNER) COUNT(A.OWNER)
-------------- --------------
         96883          96883

为什么SQL存在性能问题?我们通过10053,可以看到经过Oracle转换的SQL如下所示,

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(DISTINCT "A"."OWNER") "COUNT(DISTINCTOWNER)",COUNT(DISTINCT "A"."OBJECT_NAME") "COUNT(DISTINCTOBJECT_NAME)" FROM "BISAL"."B" "B","BISAL"."A" "A" WHERE "A"."OWNER"="B"."OWNER"

而他的执行计划,是哈希内连接,

我们看下a和b连接列的数据分布情况,a表owner列统计,

b表owner列统计(其实和a相同),

因为用的内连接,owner='SYS'这条数据关联得到的结果集,应该是37797*37797,这个效率,可想而知了。

针对这个场景,有三种解决方案。

方案1,

SQL> with t as (select /*+ materialize */ owner, object_name from a where owner in (select owner from b))
  2  select count(distinct owner), count(distinct object_name) from t;
COUNT(DISTINCTOWNER) COUNT(DISTINCTOBJECT_NAME)
-------------------- --------------------------
                  36                      58701

执行计划是,

方案2,

SQL> with t as (select owner, object_name from a where owner in (select owner from b) and rownum > 0)
  2  select count(distinct owner), count(distinct object_name) from t;
COUNT(DISTINCTOWNER) COUNT(DISTINCTOBJECT_NAME)
-------------------- --------------------------
                  36                      58701

执行计划是,

方案3,

SQL> select count(distinct owner), count(distinct object_name) from a where owner in (select owner from b group by owner);
COUNT(DISTINCTOWNER) COUNT(DISTINCTOBJECT_NAME)
-------------------- --------------------------
                  36                      58701

执行计划是,

按照老师归纳的,

1. 如果半连接中主表属于1的关系,子表(子查询中的表)属于n的关系,改写内连接时,需要加上group by去重,此时半连接性能高于内连接。

2. 如果半连接中主表属于n的关系,子表(子查询中的表)属于1的关系,改写内连接时,不需要去重,此时半连接和内连接性能相同。

3. 如果半连接中主表属于n的关系,子表(子查询中的表)属于n的关系,可以先对子查询进行去重,将子表转换为1的关系,然后再关联,不能先关联再进行去重。

半连接的表关联得到的数据量不应该翻番,而内连接得表关联得到得数据量可能翻番。原始SQL被改写为哈希内连接,没做去重,而且确实存在owner='SYS'这种关联数据量很大的情况,因为数据量大,执行时间就会相应增加。方案1和方案2,是将不带count(distinct ...)这种能很快返回结果集的放到with子句,作为单独的query block,再对临时表执行count(distinct ...)。而方案3,则是先对子查询进行了去重的操作,实际上数据量已经很小了,所以执行效率是可控的。

在这个场景中,原始SQL被改写为内连接的逻辑,在12c以上版本中得到了纠正,如下是在19c的环境,执行原始SQL,10053显示的执行计划,可以看到,已经转成了哈希半连接,因此不用改写,就可以很快得到结果集了,

性能问题的优化是一件系统工程,理论基础是一个方面,更重要的是实际经验的积累,以及问题的总结,这可能需要我们找到自己的节奏,对我来说,道行尚浅,还需磨练,要多向各位大佬学习请教了。

近期更新的文章:

如何找到隐式转换的SQL?

为什么日期不建议使用VARCHAR2或者NUMBER?

查看时间戳类型的绑定变量

写代码如何事半功倍?

支持超过4000字节的varchar2类型

文章分类和索引:

公众号700篇文章分类和索引

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值