SQL优化之子查询展开

SQL优化之子查询展开

你好，我是本际云服务器推荐网的小编小本本，在SQL优化中，我们常常会遇到子查询展开导致SQL执行计划变差的情况。以下是一个典型案例。

一段SQL语句中涉及三张表a、b、c的关联查询，其中b和c表在外面，a在in里面。原始SQL如下：

SELECT DISTINCT 
    CASE WHEN REGEXP_LIKE(B.REGISTERORGID, '.XM') THEN SUBSTR(B.REGISTERORGID, 1, INSTR(B.REGISTERORGID, '.XM', 1) - 1) ELSE B.REGISTERORGID 
    END, 
    B.SERVNUMBER, 
    C.RECOPID, 
    C.RECDATE, 
    B.SUBSID 
FROM TBCS.SUBSCRIBERPARTITION(SUBSCRIBER_724) B, 
     TBCS.RECEPTIONPARTITION(RECEPTION_724_202202) C 
WHERE B.NETTYPE = GSM 
AND   B.ACTIVE = 1 
AND   B.STATUS = US10 
AND   B.CREATEDATE > TO_DATE(20220201, YYYYMMDD) 
AND   B.REGISTERORGID LIKE HB.JM.03.% 
AND   C.SERVNUMBER = B.SERVNUMBER 
AND   C.RECDATE > TO_DATE(20220201, YYYYMMDD) 
AND   C.RECDEFID = Install 
AND   B.SUBSID NOT IN (
                   SELECT A.SUBSID 
                   FROM   TBCS.SUBS_PRODUCTPARTITION(SUBS_PRODUCT_724) A 
                   WHERE  A.PRODID LIKE G238354% 
                   AND    A.APPLYDATE > TO_DATE(20220201, YYYYMMDD) 
                   UNION ALL 
                   SELECT A.SUBSID 
                   FROM   TBCS.SUBS_PRODUCTPARTITION(SUBS_PRODUCT_724) A 
                   WHERE  A.PRODID = MP9990103000300 
                   AND    A.APPLYDATE > TO_DATE(20220201, YYYYMMDD) 
                   UNION ALL 
                   SELECT /*+index(aIDX_SUBS_PRODUCT_PRODID)*/ A.SUBSID 
                   FROM   TBCS.SUBS_PRODUCTPARTITION(SUBS_PRODUCT_724) A 
                          ,TBCS.PRODUCTD D 
                   WHERE  A.PRODID IN (
                                       SELECT D.PRODID 
                                       FROM   TBCS.PRODUCTD 
                                       WHERE  D.PRODNAME LIKE XX%XXX% 
                                       OR     D.PRODNAME LIKE XX%XXX% 
                                       OR     D.PRODNAME LIKE XX%XXX%% 
                                       OR     D.PRODNAME LIKE XX%XXX%% 
                                       OR     D.PRODNAME LIKE %XX% 
                                       OR     D.PRODNAME LIKE %XX%
                                     ) 
                   AND    A.APPLYDATE > TO_DATE(20220201, YYYYMMDD)
                  )
ORDER BY CASE WHEN REGEXP_LIKE(B.REGISTERORGID, '.XM') THEN SUBSTR(B.REGISTERORGID, 1, INSTR(B.REGISTERORGID, '.XM', 1) - 1) ELSE B.REGISTERORGID END, C.RECDATE;

在测试SQL的执行过程中，我们发现在执行过程中产生gccrrequest等待事件，执行很长时间结果也无法出来。进一步拆分SQL查询，我们发现notin里面的SQL具有非常快的运行速度（不到1秒就完成），但是联合B表一起执行的时候就非常慢，也产生了gccrrequest等待。多带带把B、C表关联查询并不执行notin，得到结果只有5000多行，如下：

结合以上情况，我们可以尝试让B、C表优先进行关联查询，关联完成后再去notin里面与A的结果进行过滤，避免子查询展开。在子查询里面加入no_unnest的hint后一分钟即出现结果。修改前的执行计划如下：

修改后的执行计划如下：

分析总结

子查询展开（Subquery Unnesting）是优化器处理带子查询的目标SQL的一种常见优化手段。它是指优化器将目标SQL中的子查询转换为它自身和外部查询之间等价的表连接。这种等价连接转换可将子查询展开，将该子查询中的表和视图从子查询中拿出来，然后和外部查询中的表和视图做表连接，也可将该子查询转换为一个内嵌视图（Inline View）然后再和外部查询中的表和视图做表连接。

子查询展开通常会提高SQL执行效率，因为如果原SQL不做展开，该子查询通常会在其执行计划的最后一步才被执行，并且会走filter类型的执行计划，这也就意味着对于外部查询所在结果集的每一条记录，该子查询就会被执行多少次。

在本次SQL优化中，外部B、C表通过关联查询得到的结果集比较小，且子查询中的SQL全部走了索引，因此我们可以尝试不展开子查询，用B、C关联后的结果集去关联子查询。对于不同情况下的SQL优化，我们应该根据实际情况选择是否展开子查询来提高SQL执行效率。

更多有关SQL优化的内容，请关注“IT那活儿”公众号。