一条半连接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篇文章分类和索引》