|
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))
說(shuō)明：1、zjjk_t_yssj_o_his 、zjjk_t_yssj_u_his 的d_sjrq 上都有一個(gè)索引了
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
優(yōu)化建議：
1、什么是DISTINCT ? 就是分組排序后取唯一值 ，底層行為 分組排序
2、什么是 UNION 、 UNION ALL ？ UNION ： 對(duì)多個(gè)結(jié)果集取DISTINCT ，生成一個(gè)不含重復(fù)記錄的結(jié)果集，返回給前端，UNION ALL ：不對(duì)結(jié)果集進(jìn)行去重復(fù)操作 底層行為：分組排序
3、什么是 COUNT(*) ？ 累加
4、需要有什么樣的索引？ S_sjrq + v_yjzldm ： 理由： 假如全省的數(shù)據(jù)量在表中全部數(shù)為1000萬(wàn)，查詢(xún)?cè)聰?shù)據(jù)量為200萬(wàn)，1000萬(wàn)中特快占50%， 則 通過(guò) beween 時(shí)間(d_sjrq)+ 種類(lèi)( v_yjzldm )，可過(guò)濾出約100萬(wàn)，這是最好的檢索方式了。
5、兩表都要進(jìn)行一次 NOT EXISTS 運(yùn)算，如何做最優(yōu)？ NOT EXISTS 是不好做的運(yùn)算，但是我們可以合并兩次的NOT EXISTS 運(yùn)算。讓這費(fèi)資源的活只干一次。
綜合以上，我們可以如下優(yōu)化這個(gè)SQL：
1、內(nèi)部的UNION 也是去重復(fù)，外部的DISTINCT 也是去重復(fù)，可左右去掉一個(gè)，建議內(nèi)部的改為 UNION ALL ， 這里稍請(qǐng)注意一下，如果V_YJHM 有NULL的情況，可能會(huì)引起COUNT值不對(duì)實(shí)際數(shù)的情況。
2、建一個(gè) D_SJRQ+V_YJZLDM 的復(fù)合索引
3、將兩個(gè)子查詢(xún)先 UNION ALL 聯(lián)結(jié) ， 另兩個(gè)用來(lái)做 NOT EXISTS 的表也 UNION ALL 聯(lián)結(jié)
4、在3的基礎(chǔ)上再做 NOT EXISTS
5、將NOT EXISTS 替換為NOT IN ，同時(shí)加提示 HASH_AJ 做半連接HASH運(yùn)算
6、最后為外層的COUNT(DISTINCT … 獲得結(jié)果數(shù)
SQL書(shū)寫(xiě)如下：
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))
經(jīng)過(guò)上述改造，原來(lái)這個(gè)SQL的執(zhí)行時(shí)間如果為2分鐘的話，現(xiàn)在應(yīng)該20秒足夠！
|