一条半连接SQL的优化过程

青旅半醒 2023-01-20 11:51 3阅读 0赞

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

直接通过模拟实验来说明，在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"

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

![38fd15078a74487df68b35dbeb24a24f.png][]

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

![0f4ff6e53f3746382d939e3fcf5d0f8d.png][]

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

![7eaeb038133871e5b71b5d579ba5ff43.png][]

因为用的内连接，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

执行计划是，

![fda69e48e3d7524b12fc3bc1714c8a29.png][]

方案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

执行计划是，

![183c029b0d87edcf5290d81118e26752.png][]

方案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

执行计划是，

![098f3d4e1090b6df52dafa9ea6a27713.png][]

按照老师归纳的，

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显示的执行计划，可以看到，已经转成了哈希半连接，因此不用改写，就可以很快得到结果集了，

![8eb86f5cad40cd06bd43d66400cb1447.png][]

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

**近期更新的文章：**

《[如何找到隐式转换的SQL？][SQL]》

《[为什么日期不建议使用VARCHAR2或者NUMBER？][VARCHAR2_NUMBER]》

《[查看时间戳类型的绑定变量][Link 1]》

《[写代码如何事半功倍？][Link 2]》

《[支持超过4000字节的varchar2类型][4000_varchar2]》

**文章分类和索引：**

《[公众号700篇文章分类和索引][700]》

[38fd15078a74487df68b35dbeb24a24f.png]: /images/20221021/e55437a3a8914102ad0145cc644f4020.png
[0f4ff6e53f3746382d939e3fcf5d0f8d.png]: /images/20221021/6e7e247b23de4964b721c1839f70235e.png
[7eaeb038133871e5b71b5d579ba5ff43.png]: /images/20221021/de0542408b1549abb7fc8593f9e0fd1b.png
[fda69e48e3d7524b12fc3bc1714c8a29.png]: /images/20221021/0f10996883954727b4275d52e0f1abcf.png
[183c029b0d87edcf5290d81118e26752.png]: /images/20221021/a12215569bf4487586e1fe744935d74e.png
[098f3d4e1090b6df52dafa9ea6a27713.png]: /images/20221021/74f28960b0a745ddba636c3b1385edb3.png
[8eb86f5cad40cd06bd43d66400cb1447.png]: /images/20221021/74fbcc5f22e046cdb6af120d5c5fa522.png
[SQL]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a414ddf52d02b74d3e476d6c4daf1aa9ba6a5be958a4f19d9b880a0029b43895eebceb2f&idx=1&mid=2247492071&scene=21&sn=05655f3f6ec05fd54b94097552708259#wechat_redirect
[VARCHAR2_NUMBER]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a411ddf52d07968c523b8869089f79e531474503306a0561ad1be8ed36625ef8ad8d3b4a&idx=1&mid=2247492066&scene=21&sn=06e18747abd40914a505c0c62da889c0#wechat_redirect
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a421ddf52d377a89c56aaadf746d892ae18c21d70b31c9f150b2b0c81483be5c841c7f71&idx=1&mid=2247492050&scene=21&sn=2bcfb42048e4d1ddf4f7eafa5523b7f2#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a432ddf52d2475aa2c81573af45a075b055cc1d4171c4036b73bc8c9a0648f2ea2952904&idx=1&mid=2247492033&scene=21&sn=d471b5da9c7c18e065fe38b299737db6#wechat_redirect
[4000_varchar2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a449ddf52d5feea2ac1b64a23f9214b1f939e84e5360dc857deeb7dadff780ad488a1c9c&idx=1&mid=2247492026&scene=21&sn=f31d0e69ea77eb0b33ff32da933a3fe6#wechat_redirect
[700]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5dfddf52cc949a5b8b316ee905927ff399713b34aec7d806c656e8ada09bfb562c039ca&idx=1&mid=2247491628&scene=21&sn=83bd320bd28da84501fada2e8d515e77#wechat_redirect