七郎's JavaBlog

select count(distinct v_yjhm)
  from (select v_yjhm
          from zjjk_t_yssj_o_his a
         where n_yjzl > 0
           and d_sjrq between to_date('20070301', 'yyyymmdd') and
               to_date('20070401', 'yyyymmdd')
           and v_yjzldm like '40%'
           and not exists(select 'a' from INST_TRIG_ZJJK_T_YSSJ_O b where a.v_yjtm=b.yjbh)
           --and v_yjtm not in (select yjbh from INST_TRIG_ZJJK_T_YSSJ_O)
        union
        select v_yjhm
          from zjjk_t_yssj_u_his a
         where n_yjzl > 0
           and d_sjrq between to_date('20070301', 'yyyymmdd') and
               to_date('20070401', 'yyyymmdd')
           and v_yjzldm like '40%'
           and not exists(select 'a' from INST_TRIG_ZJJK_T_YSSJ_U b where a.v_yjtm=b.yjbh))
           --and v_yjtm not in (select yjbh from INST_TRIG_ZJJK_T_YSSJ_U))

 

說明：1、zjjk_t_yssj_o_his 、zjjk_t_yssj_u_his  的d_sjrq 上都有一個索引了

         2、zjjk_t_yssj_o_his 、zjjk_t_yssj_u_his   的v_yjtm 都為 not null 字段

        3、INST_TRIG_ZJJK_T_YSSJ_O、INST_TRIG_ZJJK_T_YSSJ_U 的 yjbh 為PK

     

優化建議：

1、什么是DISTINCT ?   就是分組排序后取唯一值   ，底層行為  分組排序

2、什么是 UNION 、 UNION ALL  ？  UNION ： 對多個結果集取DISTINCT ，生成一個不含重復記錄的結果集，返回給前端，UNION ALL ：不對結果集進行去重復操作     底層行為：分組排序

3、什么是 COUNT(*)   ？   累加

4、需要有什么樣的索引？   S_sjrq + v_yjzldm  ： 理由： 假如全省的數據量在表中全部數為1000萬，查詢月數據量為200萬，1000萬中特快占50%， 則 通過 beween 時間(d_sjrq)+ 種類( v_yjzldm )，可過濾出約100萬，這是最好的檢索方式了。

5、兩表都要進行一次 NOT EXISTS 運算，如何做最優？   NOT EXISTS 是不好做的運算，但是我們可以合并兩次的NOT EXISTS 運算。讓這費資源的活只干一次。

 

綜合以上，我們可以如下優化這個SQL：

  1、內部的UNION 也是去重復，外部的DISTINCT 也是去重復，可左右去掉一個，建議內部的改為 UNION ALL ， 這里稍請注意一下，如果V_YJHM 有NULL的情況，可能會引起COUNT值不對實際數的情況。

  2、建一個 D_SJRQ+V_YJZLDM 的復合索引

  3、將兩個子查詢先 UNION ALL 聯結 ， 另兩個用來做 NOT EXISTS 的表也 UNION ALL  聯結

  4、在3的基礎上再做 NOT EXISTS

  5、將NOT EXISTS 替換為NOT IN ，同時加提示 HASH_AJ 做半連接HASH運算

  6、最后為外層的COUNT(DISTINCT … 獲得結果數

 

 

SQL書寫如下：

select count(distinct v_yjhm)
  from (select v_yjtm, v_yjhm
          from zjjk_t_yssj_o_his a
         where n_yjzl > 0
           and d_sjrq between to_date('20070301', 'yyyymmdd') and
               to_date('20070401', 'yyyymmdd')
           and v_yjzldm like '40%'
        union all
        select v_yjtm, v_yjhm
          from zjjk_t_yssj_u_his a
         where n_yjzl > 0
           and d_sjrq between to_date('20070301', 'yyyymmdd') and
               to_date('20070401', 'yyyymmdd')
           and v_yjzldm like '40%'
        ) a
 where a.v_yjtm not IN
       (select /*+ HASH_AJ */
         yjbh
          from (select yjbh
                  from INST_TRIG_ZJJK_T_YSSJ_O
                union all
                select yjbh from INST_TRIG_ZJJK_T_YSSJ_U))

經過上述改造，原來這個SQL的執行時間如果為2分鐘的話，現在應該20秒足夠！