SQL优化案例-使用with as优化Subquery Unnesting（七）

悠悠 2022-05-16 14:23 148阅读 0赞

使用 no\_unnest hint可以让执行计划产生filter，即不展开，但一般情况下使用unnest hint无法消除filter。

如下SQL，找出库中非唯一索引，那么大家可能会这么写SQL：

SELECT SEGMENT_NAME,SUM(BYTES/1024/1024) m FROM DBA_SEGMENTS WHERE SEGMENT_NAME 
    NOT IN (select index_name from dba_indexes where UNIQUENESS ='NONUNIQUE') GROUP BY SEGMENT_NAME;

耗时一分钟，为什么这么慢呢？在SQL语句where子查询后有not in、not exists、in、exists时，CBO会尝试将子查询展开(unnest)消除filter，但是上面的例子CBO并没有做到，下面我们看下执行计划。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70][]

我们再看下在子查询中加unnest hint的执行计划：

SELECT SEGMENT_NAME,SUM(BYTES/1024/1024) m FROM DBA_SEGMENTS WHERE
    SEGMENT_NAME  NOT IN (select /*+UNNEST */index_name from dba_indexes
    where UNIQUENESS ='NONUNIQUE') GROUP BY SEGMENT_NAME;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 1][]

filter消除，CBO将基于数据字典底层的基表重新组合，使执行计划变成hash joinanti，0.23秒便执行完成了。

下面我们再来看下通过with as materialize优化subquery unnesting的例子。

SQL如下：

select  AREA_NAME,sum(reve)
    from t_order o 
    where exists (select AREA_ID
            from t_customer c
           where nation = 'Aus'
             and c.AREA_ID = o.AREA_ID
          union
          select AREA_ID
            from f_customer f 
          where nation = 'US'
            and f.AREA_ID <> o.f_area_id) group by AREA_NAME;

selectsum(bytes/1024/1024) M from dba_segments where segment_name ='F_CUSTOMER';
    
        M
    ----------
        192

表F\_CUSTOMER192M

![20181031100211350.png][]

执行计划如下:![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 2][]

我们看到并不走索引，要近2个小时执行完成，通过建立组合索引，让其走index\_ffs

CREATE INDEX IDX_FFS_NATION_ID ONf_customer(AREA_ID,nation,0);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 3][]

8分钟执行完成，那么还有没有更好的办法呢？

如下：

with x as ( select /*+ materialize */ AREA_ID from f_customer f 
          where nation = 'US') 
    select  AREA_NAME,sum(reve)
    from t_order o 
    where exists (select AREA_ID
            from t_customer c
           where nation = 'Aus'
             and c.AREA_ID = o.AREA_ID
          union
          select AREA_ID
            from x where x.AREA_ID <> o.f_area_id) group by AREA_NAME;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 4][]

在FILTER中，NOT IN(NOT EXISTS)后的SQL语句多次执行，本来数据量就很大，每次都要执行一遍，结果可想而知。但是使用HINT MATERIALIZE和WITH AS 结合使用，把表中部分列实体化，执行过程中会创建基于视图的临时表。这样就不会每次NOT EXISTS都去执行一遍大数据表的扫描或者大的索引快速扫描，并且当表的数据越大，表越宽，这样的优化效果越明显。

### **|  作者简介** ###

**姚崇·沃趣科技高级数据库技术专家**

熟悉Oracle、MySQL数据库内部机制，丰富的Oracle、MySQL故障诊断、性能调优、数据库备份恢复、复制、高可用方案及迁移经验。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70]: /images/20220503/0bb95e8875114c5b9317cd93c83dc063.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 1]: /images/20220503/1f2a88a57428459f84743d9fec867e3d.png
[20181031100211350.png]: /images/20220503/ac01d1df5165472195db6d4bf8ea8270.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 2]: /images/20220503/8133eaeb0afd48adb91081c6a1a4bb6e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 3]: /images/20220503/70eb25ad0bde4c76974917dc73e09e1b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70 4]: /images/20220503/2d4b1a25fa63483ab9137c3d06098b56.png