SQL优化案例-使用with as优化Subquery Unnesting(七)

使用with as优化Subquery Unnesting

作者 姚崇·沃趣科技高级数据库技术专家
出品 沃趣科技

使用 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并没有做到,下面我们看下执行计划。

我们再看下在子查询中加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;

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

执行计划如下:

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

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

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;

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

| 作者简介

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

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

发表评论

电子邮件地址不会被公开。 必填项已用*标注