enable_index_filter
前提
客戶反應現場有sql很慢，故拿來最佳化下。
慢sql
select proh.id , proh.receiveTime , b.main_send_dept , b.copy_send_dept , proh.rootproinstid , proh.configId , proh.proDirId , proh.creator creatorId , proh.createtime createTime , pubh.config_code , proh.name proDefName , config.name configName , pubh.draft_deptname draftDeptName, pubh.word_no wordno , pubh.PUB_FORM_DATA_ID , pubh.title , pubh.drafter_name creatorName , pubh.LAST_MODIFY_TIME modifytime , WHW.PROINSTID , WHW.resourceId resourceId , WHW.NAME , WHW.ACTDEFID , '1' isFinishedNUM , WHW.CREATETIME currentUserTime , WHW.USER_NAME , WHW.DEPT_NAME , WHW.actinstid , WHW.state , 'true' isFinished , wordno.wordno_year from ( SELECT DISTINCT WR.*, ww.createTime as receiveTime FROM WFH_WORKITEM WW LEFT JOIN WFH_PROCESS WR ON WR.ID=WW.proinstid left join ( select ww2.proinstid, max(ww2.createtime) as createTime from wfh_workitem ww2 LEFT JOIN WFH_PROCESS WR ON WR.ID=ww2.proinstid where substr(ww2.full_dept_id, 1, 65) = 'de4457db545b461bb359d3179adf50b4.775c477e6c0945d99b7083036a686949' and WR.prodefid like 'fw\_%' ESCAPE '\' group by ww2.proInstid ) maxReceiveTime on ww.proinstid = maxReceiveTime.proinstid WHERE substr(WW.full_dept_id, 1, 65) = 'de4457db545b461bb359d3179adf50b4.775c477e6c0945d99b7083036a686949' and WR.prodefid like 'fw\_%' ESCAPE '\' and ww.createTime = maxReceiveTime.createtime ) proh LEFT JOIN WFH_WORKITEM as WHW on proh.ID=WHW.proinstid right join OA_PUB_FORM_DATA_HISTORY pubh on pubh.pro_inst_id=WHW.proinstid left join ( select id, main_send_dept, copy_send_dept from oa_form_fwzh union all select id, main_send_dept, copy_send_dept from oa_form_fwzhdw union all select id, main_send_dept, copy_send_dept from oa_form_fwfh union all select id, main_send_dept, copy_send_dept from oa_form_fwfhsm union all select id, main_send_dept, copy_send_dept from oa_form_fwglb union all select id, main_send_dept, copy_send_dept from oa_form_fwbs union all select id, main_send_dept, copy_send_dept from oa_form_fwbssm union all select id, main_send_dept, copy_send_dept from oa_form_fwfhdw union all select id, main_send_dept, copy_send_dept from oa_form_fwjjz ) b on pubh.PUB_FORM_DATA_ID=b.id left join OA_WORDNO_CONTAINER wordno on pubh.pro_inst_id=wordno.pro_inst_id and pubh.word_no = wordno.WORDNO_FULL left join wfm_config config on proh.proDefId = config.code where WHW.createtime = ( select max(C.createtime) from WFH_WORKITEM as C where WHW.proinstid=C.proinstid group by C.proinstid ) limit 10;
檢視此sql的執行計劃
1 #NSET2: [73992, 10, 1932] 2 #PIPE2: [73992, 10, 1932] 3 #PIPE2: [70141, 10, 1932] 4 #PRJT2: [1676, 10, 1932]; exp_num(30), is_atom(FALSE) 5 #TOPN2: [1676, 10, 1932]; top_num(10) 6 #SLCT2: [1676, 29754, 1932]; WHW.CREATETIME = exp48 7 #HASH RIGHT JOIN2: [1636, 1190174, 1932]; key_num(1), ret_null(0), KEY(config.code=proh.PRODEFID) 8 #CSCN2: [1, 66, 96]; INDEX33556893(wfm_config as config) 9 #HASH RIGHT JOIN2: [1274, 1190174, 1932]; key_num(2), ret_null(0), KEY(wordno.PRO_INST_ID=pubh.PRO_INST_ID AND wordno.WORDNO_FULL=pubh.WORD_NO) 10 #SSCN: [66, 495334, 144]; i_OAWORDNOCONTAINER_lh(OA_WORDNO_CONTAINER as wordno) 11 #HASH RIGHT JOIN2: [765, 1190174, 1932]; key_num(1), ret_null(0), KEY(b.id=pubh.PUB_FORM_DATA_ID) 12 #PRJT2: [75, 58389, 144]; exp_num(3), is_atom(FALSE) 13 #UNION ALL: [75, 58389, 144] 14 #PRJT2: [65, 58348, 144]; exp_num(3), is_atom(FALSE) 15 #UNION ALL: [65, 58348, 144] 16 #PRJT2: [54, 53511, 144]; exp_num(3), is_atom(FALSE) 17 #UNION ALL: [54, 53511, 144] 18 #PRJT2: [45, 53492, 144]; exp_num(3), is_atom(FALSE) 19 #UNION ALL: [45, 53492, 144] 20 #PRJT2: [35, 53386, 144]; exp_num(3), is_atom(FALSE) 21 #UNION ALL: [35, 53386, 144] 22 #PRJT2: [26, 53381, 144]; exp_num(3), is_atom(FALSE) 23 #UNION ALL: [26, 53381, 144] 24 #PRJT2: [16, 53344, 144]; exp_num(3), is_atom(FALSE) 25 #UNION ALL: [16, 53344, 144] 26 #PRJT2: [4, 12921, 144]; exp_num(3), is_atom(FALSE) 27 #UNION ALL: [4, 12921, 144] 28 #PRJT2: [1, 12210, 144]; exp_num(3), is_atom(FALSE) 29 #CSCN2: [1, 12210, 144]; INDEX33557161(oa_form_fwzh) 30 #PRJT2: [1, 711, 144]; exp_num(3), is_atom(FALSE) 31 #CSCN2: [1, 711, 144]; INDEX33557159(oa_form_fwzhdw) 32 #PRJT2: [5, 40423, 144]; exp_num(3), is_atom(FALSE) 33 #CSCN2: [5, 40423, 144]; INDEX33557171(oa_form_fwfh) 34 #PRJT2: [1, 37, 144]; exp_num(3), is_atom(FALSE) 35 #CSCN2: [1, 37, 144]; INDEX33557167(oa_form_fwfhsm) 36 #PRJT2: [1, 5, 144]; exp_num(3), is_atom(FALSE) 37 #CSCN2: [1, 5, 144]; INDEX33557165(oa_form_fwglb) 38 #PRJT2: [1, 106, 144]; exp_num(3), is_atom(FALSE) 39 #CSCN2: [1, 106, 144]; INDEX33557177(oa_form_fwbs) 40 #PRJT2: [1, 19, 144]; exp_num(3), is_atom(FALSE) 41 #CSCN2: [1, 19, 144]; INDEX33557175(oa_form_fwbssm) 42 #PRJT2: [1, 4837, 144]; exp_num(3), is_atom(FALSE) 43 #CSCN2: [1, 4837, 144]; INDEX33557169(oa_form_fwfhdw) 44 #PRJT2: [1, 41, 144]; exp_num(3), is_atom(FALSE) 45 #CSCN2: [1, 41, 144]; INDEX33557163(oa_form_fwjjz) 46 #HASH RIGHT JOIN2: [338, 1190174, 1788]; key_num(1), ret_null(0), KEY(WHW.PROINSTID=pubh.PRO_INST_ID) 47 #INDEX JOIN LEFT JOIN2: [1, 20, 1440] ret_null(0) 48 #HEAP TABLE SCAN: [1, 1, 1440]; table_no(0), 49 #BLKUP2: [1, 20, 0]; IDX_WFH_WORKITEM_PROINSTID(WHW) 50 #SSEK2: [1, 20, 0]; scan_type(ASC), IDX_WFH_WORKITEM_PROINSTID(wfh_workitem as WHW), scan_range[proh.ID,proh.ID] 51 #CSCN2: [209, 1190174, 348]; INDEX33557101(oa_pub_form_data_history as pubh) 52 #SPL2: [68465, 20, 1848]; key_num(2), spool_num(0), is_atom(FALSE), has_variable(0) 53 #PRJT2: [68465, 20, 1848]; exp_num(2), is_atom(FALSE) 54 #PRJT2: [68465, 20, 1848]; exp_num(2), is_atom(FALSE) 55 #HAGR2: [68465, 20, 1848]; grp_num(2), sfun_num(1); slave_empty(0) keys(C.PROINSTID, WHW.PROINSTID) 56 #NEST LOOP INDEX JOIN2: [68396, 400, 1848] 57 #DISTINCT: [68391, 20, 1788] 58 #HASH RIGHT JOIN2: [338, 1190174, 1788]; key_num(1), ret_null(0), KEY(WHW.PROINSTID=pubh.PRO_INST_ID) 59 #INDEX JOIN LEFT JOIN2: [1, 20, 1440] ret_null(0) 60 #HEAP TABLE SCAN: [1, 1, 1440]; table_no(0), 61 #SSEK2: [1, 20, 0]; scan_type(ASC), IDX_WFH_WORKITEM_PROINSTID(wfh_workitem as WHW), scan_range[proh.ID,proh.ID] 62 #CSCN2: [209, 1190174, 348]; INDEX33557101(oa_pub_form_data_history as pubh) 63 #BLKUP2: [1, 20, 0]; IDX_WFH_WORKITEM_PROINSTID(C) 64 #SSEK2: [1, 20, 0]; scan_type(ASC), IDX_WFH_WORKITEM_PROINSTID(wfh_workitem as C), scan_range[WHW.PROINSTID,WHW.PROINSTID] 65 #HEAP TABLE: [3850, 1, 1440]; table_no(0) full(FALSE), mpp_full(0) autoid(FALSE) 66 #PRJT2: [3850, 1, 1440]; exp_num(9), is_atom(FALSE) 67 #DISTINCT: [3850, 1, 1440] 68 #SLCT2: [3845, 20, 1440]; (WW.FULL_DEPT_ID >= 'de4457db545b461bb359d3179adf50b4.775c477e6c0945d99b7083036a686949' AND WW.FULL_DEPT_ID < 'de4457db545b461bb359d3179adf50b4.775c477e6c0945d99b7083036a68694:' AND WW.PROINSTID = maxReceiveTime.proinstid AND WR.ID = WW.PROINSTID) 69 #NEST LOOP INDEX JOIN2: [3845, 20, 1440] 70 #SLCT2: [3842, 20, 1332]; WR.PRODEFID LIKE 'fw\_%' ESCAPE '\' 71 #NEST LOOP INDEX JOIN2: [3842, 20, 1332] 72 #PRJT2: [3841, 20, 204]; exp_num(2), is_atom(FALSE) 73 #HAGR2: [3841, 20, 204]; grp_num(1), sfun_num(1); slave_empty(0) keys(ww2.PROINSTID) 74 #SLCT2: [3728, 1226359, 204]; (ww2.FULL_DEPT_ID >= 'de4457db545b461bb359d3179adf50b4.775c477e6c0945d99b7083036a686949' AND ww2.FULL_DEPT_ID < 'de4457db545b461bb359d3179adf50b4.775c477e6c0945d99b7083036a68694:') 75 #NEST LOOP INDEX JOIN2: [3728, 1226359, 204] 76 #SLCT2: [164, 61317, 96]; WR.PRODEFID LIKE 'fw\_%' ESCAPE '\' 77 #SSCN: [164, 1226359, 96]; i_WFHPROCESS_lh(wfh_process as WR) 78 #BLKUP2: [528, 20, 0]; IDX_WFH_WORKITEM_PROINSTID(ww2) 79 #SSEK2: [528, 20, 0]; scan_type(ASC), IDX_WFH_WORKITEM_PROINSTID(wfh_workitem as ww2), scan_range[WR.ID,WR.ID] 80 #BLKUP2: [1, 1, 0]; IDX_WFH_PROCESS_ID_VER(WR) 81 #SSEK2: [1, 1, 0]; scan_type(ASC), IDX_WFH_PROCESS_ID_VER(wfh_process as WR), scan_range[(maxReceiveTime.proinstid,min),(maxReceiveTime.proinstid,max)) 82 #BLKUP2: [1, 1, 0]; IDX_WFH_WORKITEM_CREATETIME(WW) 83 #SSEK2: [1, 1, 0]; scan_type(ASC), IDX_WFH_WORKITEM_CREATETIME(wfh_workitem as WW), scan_range[maxReceiveTime.createTime,maxReceiveTime.createTime]
檢視ET
行號 OP TIME(US) PERCENT RANK SEQ N_ENTER ---------- ---------- -------------------- ------- -------------------- ----------- ----------- 1 PRJT2 0 0% 72 40 4 2 PRJT2 1 0% 67 36 4 3 PRJT2 1 0% 67 44 4 4 PRJT2 1 0% 67 30 4 5 PIPE2 1 0% 67 3 7 6 PIPE2 1 0% 67 2 7 7 PRJT2 2 0% 63 42 12 8 PRJT2 2 0% 63 38 4 9 SPL2 2 0% 63 52 1 10 PRJT2 2 0% 63 34 4 11 UNION_ALL2 4 0% 62 27 31 行號 OP TIME(US) PERCENT RANK SEQ N_ENTER ---------- ---------- -------------------- ------- -------------------- ----------- ----------- 12 PRJT2 5 0% 61 26 30 13 DLCK 6 0% 59 0 2 14 PRJT2 6 0% 59 66 28 15 PRJT2 8 0% 58 28 28 16 UNION_ALL2 9 0% 57 15 131 17 UNION_ALL2 10 0% 53 17 121 18 UNION_ALL2 10 0% 53 23 115 19 UNION_ALL2 10 0% 53 19 119 20 UNION_ALL2 10 0% 53 21 117 21 PRJT2 11 0% 52 32 84 22 PRJT2 13 0% 50 20 116 行號 OP TIME(US) PERCENT RANK SEQ N_ENTER ---------- ---------- -------------------- ------- -------------------- ----------- ----------- 23 PRJT2 13 0% 50 12 132 24 UNION_ALL2 14 0% 49 13 133 25 UNION_ALL2 15 0% 46 25 113 26 PRJT2 15 0% 46 4 6 27 PRJT2 15 0% 46 24 112 28 PRJT2 16 0% 42 18 118 29 PRJT2 16 0% 42 14 130 30 PRJT2 16 0% 42 22 114 31 PRJT2 16 0% 42 72 28 32 CSCN2 18 0% 41 37 2 33 PRJT2 19 0% 40 16 120 行號 OP TIME(US) PERCENT RANK SEQ N_ENTER ---------- ----- -------------------- ------- -------------------- ----------- ----------- 34 HSCN 24 0% 39 48 15 35 CSCN2 28 0% 38 41 2 36 CSCN2 58 0% 37 8 2 37 CSCN2 66 0% 36 45 2 38 CSCN2 78 0% 35 35 2 39 CSCN2 83 0% 34 39 2 40 NSET2 116 0% 33 1 4 41 CSCN2 411 0% 32 31 2 42 HRO2 1710 0.02% 31 7 22 43 CSCN2 2173 0.02% 30 43 6 44 HTAB 2266 0.02% 29 65 16 行號 OP TIME(US) PERCENT RANK SEQ N_ENTER ---------- ----- -------------------- ------- -------------------- ----------- ----------- 45 TOPN2 2873 0.03% 28 5 5 46 CSCN2 6593 0.07% 27 29 14 47 IJI2 6737 0.07% 26 71 36597 48 CSCN2 7733 0.08% 25 51 9 49 IJI2 13586 0.14% 24 69 48778 50 CSCN2 19552 0.20% 23 33 42 51 SLCT2 21453 0.22% 22 70 24390 52 HRO2 25954 0.26% 21 11 86 53 IJI2 29039 0.29% 20 75 143991 54 SLCT2 39512 0.40% 19 68 24390 55 HAGR2 91665 0.93% 18 73 12209 行號 OP TIME(US) PERCENT RANK SEQ N_ENTER ---------- ------ -------------------- ------- -------------------- ----------- ----------- 56 DIST 96037 0.97% 17 67 12209 57 SSCN 101904 1.03% 16 10 497 58 IJLO2 103107 1.04% 15 47 36597 59 BLKUP2 105687 1.07% 14 80 48776 60 SLCT2 121906 1.24% 13 76 1278 61 SSEK2 132649 1.34% 12 81 24388 62 SSEK2 135139 1.37% 11 83 24388 63 SLCT2 175588 1.78% 10 74 60176 64 SSCN 207143 2.10% 9 77 1228 65 SSEK2 221019 2.24% 8 50 24388 66 HRO2 252654 2.56% 7 46 12214 行號 OP TIME(US) PERCENT RANK SEQ N_ENTER ---------- ------ -------------------- ------- -------------------- ----------- ----------- 67 BLKUP2 263793 2.67% 6 82 48776 68 HRO2 269638 2.73% 5 9 517 69 SSEK2 779557 7.90% 4 79 95960 70 BLKUP2 1138279 11.53% 3 49 48776 71 SLCT2 2360006 23.91% 2 6 12 72 BLKUP2 3134386 31.76% 1 78 191920
實驗1
根據分析，慢在此部分上
select ww2.proinstid, max(ww2.createtime) as createTime from wfh_workitem ww2 LEFT JOIN WFH_PROCESS WR ON WR.ID=ww2.proinstid where substr(ww2.full_dept_id, 1, 65) = 'de4457db545b461bb359d3179adf50b4.775c477e6c0945d99b7083036a686949' and WR.prodefid like 'fw\_%' ESCAPE '\' group by ww2.proInstid
再根據ET判斷時間浪費在BLKUP2回表上，第一個反應就是加聯合索引，達到消除聯合索引的效果。
CREATE INDEX "i_wfh_workitem_lh" ON "OA_BOB"."wfh_workitem"(substr(full_dept_id, 1, 65),full_dept_id,proinstid,createtime) ;
可加入後速度並沒有加快很多，後發現是函式條件篩選率造成2個表產生hash關聯，而加入enable_hash_join(0)的hint後執行計劃又重新走IDX_WFH_WORKITEM_PROINSTID，強制走新加的索引，反而會慢。
既然走IDX_WFH_WORKITEM_PROINSTID，那麼可以再一步分析慢在哪裡，可以根據上面執行計劃看到走了IDX_WFH_WORKITEM_PROINSTID索引後BLKUP2回表，而後才進行SLCT2對FULL_DEPT_ID欄位進行篩選，問題就再這裡，那我們可以用hint讓篩選走到前面完成。
enable_index_filter
ENABLE_INDEX_FILTER說明：
預設0，動態會話級
是否進行索引過濾最佳化，可取值為 0、1、2。
0 不進行最佳化；
1 使用索引過濾最佳化，如果過濾條件涉及的列包含在索引中，那麼索引進行 SSEK2 後就可以使用此過濾條件，可以減少中間結果集。
2 在取值為 1 的基礎上，將 IN 查詢列表轉換為 HASH RIGHT SEMI JOIN。
試驗2
我們用一個簡單的語句來闡述此hint的作用，當我們有2個寬表，也就是欄位較多的表進行關聯，而select需要取很多欄位。
--例項語句 select * from tt2 t2 , tt1 t1 where t2.d1 = t1.c1 and d3 < 100 and d1 < 50; --執行計劃 1 #NSET2: [11, 4, 108] 2 #PRJT2: [11, 4, 108]; exp_num(5), is_atom(FALSE) 3 #HASH2 INNER JOIN: [11, 4, 108]; KEY_NUM(1); KEY(T2.D1=T1.C1) KEY_NULL_EQU(0) 4 #SLCT2: [10, 4, 56]; T2.D1 < 50 5 #BLKUP2: [10, 9900, 56]; IDX_ON_D3_TT2(T2) -- 總記錄100000 6 #SSEK2: [10, 9900, 56]; scan_type(ASC), IDX_ON_D3_TT2(TT2 as T2), scan_range(null2,100) 7 #SLCT2: [0, 49, 52]; T1.C1 < 50 8 #CSCN2: [0, 1000, 52]; INDEX33555613(TT1 as T1)
1.先SSEK2走IDX_ON_D3_TT2索引
2.BLKUP2回表
3.SLCT2再過濾d1 < 50
--複合索引（d3,d1） create index idx_on_d3d1_tt2 on tt2(d3,d1); --加hint select /*+ ENABLE_INDEX_FILTER(1)*/ * from tt2 t2 join tt1 t1 on t2.d1 = t1.c1 where d3 < 100 and d1 < 50; --執行計劃 1 #NSET2: [2, 4, 108] 2 #PRJT2: [2, 4, 108]; exp_num(5), is_atom(FALSE) 3 #HASH2 INNER JOIN: [2, 4, 108]; KEY_NUM(1); KEY(T2.D1=T1.C1) KEY_NULL_EQU(0) 4 #BLKUP2: [1, 4, 56]; IDX_ON_D3D1_TT2(T2) 5 #SLCT2: [1, 4, 56]; T2.D1 < 50 -- 怎麼估算成4行 6 #SSEK2: [1, 4, 56]; scan_type(ASC), IDX_ON_D3D1_TT2(TT2 as T2), scan_range[(null2,min),(100,min)) 7 #SLCT2: [0, 49, 52]; T1.C1 < 50 8 #CSCN2: [0, 1000, 52]; INDEX33555613(TT1 as T1)
成功利用複合索引，先索引快速掃描（d3<100），再在索引掃描後的基礎上繼續過濾（d1<50），接著回表查詢，最後才與t1做表連線查詢。
最終修改
慢的sql部分加入此hint
select /*+enable_hash_join(0) enable_index_filter(1)*/ --加的部分 ww2.proinstid, max(ww2.createtime) as createTime from wfh_workitem ww2 LEFT JOIN WFH_PROCESS WR ON WR.ID=ww2.proinstid where substr(ww2.full_dept_id, 1, 65) = 'de4457db545b461bb359d3179adf50b4.775c477e6c0945d99b7083036a686949' and WR.prodefid like 'fw\_%' ESCAPE '\' group by ww2.proInstid
加入hint後，速度s級查出。
DM 武漢達夢資料庫股份有限公司
24小時免費服務熱線：400 991 6599
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/70002866/viewspace-2996020/，如需轉載，請註明出處，否則將追究法律責任。