SQL優化案例-使用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故障診斷、性能調優、數據庫備份恢復、復制、高可用方案及遷移經驗。