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

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

SELECTSEGMENT_NAME,SUM(BYTES/1024/1024)mFROMDBA_SEGMENTSWHERESEGMENT_NAMENOTIN(selectindex_namefromdba_indexeswhereUNIQUENESS='NONUNIQUE')GROUPBYSEGMENT_NAME;

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

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

SELECTSEGMENT_NAME,SUM(BYTES/1024/1024)mFROMDBA_SEGMENTSWHERESEGMENT_NAMENOTIN(select/*+UNNEST*/index_namefromdba_indexeswhereUNIQUENESS='NONUNIQUE')GROUPBYSEGMENT_NAME;

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

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

SQL如下：

selectAREA_NAME,sum(reve)fromt_orderowhereexists(selectAREA_IDfromt_customercwherenation='Aus'andc.AREA_ID=o.AREA_IDunionselectAREA_IDfromf_customerfwherenation='US'andf.AREA_ID<>o.f_area_id)groupbyAREA_NAME;

selectsum(bytes/1024/1024)Mfromdba_segmentswheresegment_name='F_CUSTOMER';M----------192

表F_CUSTOMER192M

执行计划如下:

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

CREATEINDEXIDX_FFS_NATION_IDONf_customer(AREA_ID,nation,0);


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

如下：

withxas(select/*+materialize*/AREA_IDfromf_customerfwherenation='US')selectAREA_NAME,sum(reve)fromt_orderowhereexists(selectAREA_IDfromt_customercwherenation='Aus'andc.AREA_ID=o.AREA_IDunionselectAREA_IDfromxwherex.AREA_ID<>o.f_area_id)groupbyAREA_NAME;


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


| 作者简介

姚崇·沃趣科技高级数据库技术专家熟悉Oracle、MySQL数据库内部机制，丰富的Oracle、MySQL故障诊断、性能调优、数据库备份恢复、复制、高可用方案及迁移经验。