上周五要下班的时候，盖尔发来一个SQL
select tpc.policy_id,tcm.policy_code,an_id,to_char(tpf.insert_time, 'YYYY-MM-DD') As insert_time,tpc.change_id,d.policy_code,pany_name,f.real_name,tpf.fee_type,sum(tpf.pay_balance) as pay_balance,c.actual_type,tpc.notice_code,d.policy_type,g.mode_name as pay_modefrom t_policy_change tpc,t_contract_master tcm,t_policy_fee tpf,t_fee_type c,t_contract_master d,t_company_customer e,t_customer f,t_pay_mode gwhere tpc.change_id = tpf.change_idand tpf.policy_id = d.policy_idand tcm.policy_id = tpc.policy_idand tpf.receiv_status = 1 and tpf.fee_status = 1and tpf.payment_id is nulland tpf.fee_type = c.type_idand tpf.pay_mode = g.mode_idand pany_id = pany_id(+)and d.applicant_id = f.customer_id(+)and an_id in(select organ_idfrom t_company_organstart with organ_id = '101'connect by prior organ_id = parent_id)group by tpc.policy_id,tpc.change_id,tpf.fee_type,to_char(tpf.insert_time, 'YYYY-MM-DD'),c.actual_type,d.policy_code,g.mode_name,pany_name,f.real_name,tpc.notice_code,d.policy_type,an_id,tcm.policy_codeorder by change_id, fee_typeSQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|----------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT| | 45962 | 11M| | 45650 (0)|| 1 | SORT GROUP BY | | 45962 | 11M| 23M| 45650 (0)||* 2 | HASH JOIN| | 45962 | 11M| | 43908 (0)|| 3 | INDEX FULL SCAN | T_FEE_TYPE_IDX_003| 106 | 636 | |1 (0)|| 4 | NESTED LOOPS OUTER| | 45962 | 11M| | 43906 (0)||* 5 |HASH JOIN | | 45962 | 7271K| 6824K| 43905 (0)|| 6 |NESTED LOOPS | | 45961 | 6283K| | 42312 (0)||* 7 | HASH JOIN SEMI| | 45961 | 5655K| 50M| 33120 (1)||* 8 | HASH JOIN OUTER | | 400K| 45M| 44M| 32315 (1)||* 9 | HASH JOIN | | 400K| 39M| 27M| 26943 (0)||* 10 |HASH JOIN | | 400K| 23M| | 16111 (0)|| 11 | TABLE ACCESS FULL | T_PAY_MODE | 25 | 525 | |2 (0)||* 12 | TABLE ACCESS FULL | T_POLICY_FEE| 400K| 15M| | 16107 (0)|| 13 |TABLE ACCESS FULL| T_CONTRACT_MASTER | 1136K| 46M| | 9437 (0)|| 14 | VIEW | index_join_007 | 2028K| 30M| | ||* 15 |HASH JOIN | | 400K| 45M| 44M| 32315 (1)|| 16 | INDEX FAST FULL SCAN| PK_T_CUSTOMER| 2028K| 30M| | 548 (0)|| 17 | INDEX FAST FULL SCAN| IDX_CUSTOMER__BIR_REAL_GEN | 2028K| 30M| | 548 (0)|| 18 | VIEW| VW_NSO_1|7 | 42 | | ||* 19 | CONNECT BY WITH FILTERING | | | | | || 20 |NESTED LOOPS| | | | | ||* 21 | INDEX UNIQUE SCAN | PK_T_COMPANY_ORGAN|1 |6 | | || 22 | TABLE ACCESS BY USER ROWID| T_COMPANY_ORGAN | | | | || 23 |NESTED LOOPS| | | | | || 24 | BUFFER SORT| |7 | 70 | | || 25 | CONNECT BY PUMP| | | | | ||* 26 | INDEX RANGE SCAN| T_COMPANY_ORGAN_IDX_002|7 | 70 | |1 (0)|| 27 | TABLE ACCESS BY INDEX ROWID | T_POLICY_CHANGE |1 | 14 | |2 (50)||* 28 | INDEX UNIQUE SCAN | PK_T_POLICY_CHANGE|1 | | |1 (0)|| 29 |INDEX FAST FULL SCAN | IDX1_ACCEPT_DATE | 1136K| 23M| | 899 (0)|| 30 |TABLE ACCESS BY INDEX ROWID| T_COMPANY_CUSTOMER|1 | 90 | |2 (50)||* 31 |INDEX UNIQUE SCAN | PK_T_COMPANY_CUSTOMER |1 | | | |----------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("TPF"."FEE_TYPE"="C"."TYPE_ID")5 - access("TCM"."POLICY_ID"="TPC"."POLICY_ID")7 - access("TPF"."ORGAN_ID"="VW_NSO_1"."$nso_col_1")8 - access("D"."APPLICANT_ID"="F"."CUSTOMER_ID"(+))9 - access("TPF"."POLICY_ID"="D"."POLICY_ID")10 - access("TPF"."PAY_MODE"="G"."MODE_ID")12 - filter("TPF"."CHANGE_ID" IS NOT NULL AND TO_NUMBER("TPF"."RECEIV_STATUS")=1 AND "TPF"."FEE_STATUS"=1 AND"TPF"."PAYMENT_ID" IS NULL)15 - access("indexjoin_alias_012".ROWID="indexjoin_alias_011".ROWID)19 - filter("T_COMPANY_ORGAN"."ORGAN_ID"='101')21 - access("T_COMPANY_ORGAN"."ORGAN_ID"='101')26 - access("T_COMPANY_ORGAN"."PARENT_ID"=NULL)28 - access("TPC"."CHANGE_ID"="TPF"."CHANGE_ID")31 - access("D"."COMPANY_ID"="E"."COMPANY_ID"(+))55 rows selectedStatistics----------------------------------------------------------21 recursive calls0 db block gets125082 consistent gets21149 physical reads0 redo size2448 bytes sent via SQL*Net to client656 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client4 sorts (memory)0 sorts (disk)11 rows processed
这个SQL要21秒才能跑完，逻辑读12W左右，问我能不能优化。优化这个SQL我只花了1分钟左右的时间，因为太简单了
你们看这个SQL是典型的JOIN，对付这种SQL肯定要让表走索引，但是从执行计划上看有个1千万行的表T_CONTRACT_MASTER走的是全表扫描，
T_POLICY_FEE 这个400W行的表也是走全表扫描，那么它不慢才怪呢，然后SQL的过滤条件有个 in 子查询
(select
organ_id
from t_company_organ
start with organ_id = '101'
connect by prior organ_id = parent_id)
从执行计划上看，CBO对这儿子查询进行了unnest，因为通常情况下CBO认为子查询被unnest之后性能好于filter
于是我让盖尔查询 子查询返回多少行
select organ_id
from t_company_organ
start with organ_id = '101'
connect by prior organ_id = parent_id ---盖尔说它返回1行
对于子查询，如果它返回数据很少(这里返回1行)，那么可以让它走filter, 而且filter基本上是在SQL最后去阶段执行，这样t_policy_fee就可以走索引了
所以我给这个子查询加了个HINT，禁止子查询扩展
select tpc.policy_id,tcm.policy_code,an_id,to_char(tpf.insert_time, 'YYYY-MM-DD') As insert_time,tpc.change_id,d.policy_code,pany_name,f.real_name,tpf.fee_type,sum(tpf.pay_balance) as pay_balance,c.actual_type,tpc.notice_code,d.policy_type,g.mode_name as pay_modefrom t_policy_change tpc,t_contract_master tcm,t_policy_fee tpf,t_fee_type c,t_contract_master d,t_company_customer e,t_customer f,t_pay_mode gwhere tpc.change_id = tpf.change_idand tpf.policy_id = d.policy_idand tcm.policy_id = tpc.policy_idand tpf.receiv_status = '1' ---这里原来没引号，是开发那SB搞忘了写'',我让盖尔添加上了，不添加上就没法用索引and tpf.fee_status = 1and tpf.payment_id is nulland tpf.fee_type = c.type_idand tpf.pay_mode = g.mode_idand pany_id = pany_id(+)and d.applicant_id = f.customer_id(+)and an_id in(select /*+ no_unnest */--此处的HINT后加的organ_idfrom t_company_organstart with organ_id = '101'connect by prior organ_id = parent_id)group by tpc.policy_id,tpc.change_id,tpf.fee_type,to_char(tpf.insert_time, 'YYYY-MM-DD'),c.actual_type,d.policy_code,g.mode_name,pany_name,f.real_name,tpc.notice_code,d.policy_type,an_id,tcm.policy_codeorder by change_id, fee_typeSQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------| Id | Operation | Name| Rows | Bytes |TempSpc| Cost (%CPU)|--------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 20026 | 4928K| | 68615 (30)|| 1 | SORT GROUP BY | | 20026 | 4928K| 10M| 28563 (0)||* 2 | FILTER | | | | | || 3 | NESTED LOOPS | | 20026 | 4928K| | 27812 (0)|| 4 |NESTED LOOPS | | 20026 | 4498K| | 23807 (0)|| 5 |NESTED LOOPS OUTER | | 20026 | 4224K| | 19802 (0)|| 6 | NESTED LOOPS OUTER | | 20026 | 3911K| | 15797 (0)|| 7 | NESTED LOOPS | | 20026 | 2151K| | 15796 (0)||* 8 | HASH JOIN| | 20026 | 1310K| | 11791 (0)|| 9 |INDEX FULL SCAN | T_FEE_TYPE_IDX_003 | 106 | 636 | |1 (0)||* 10 |HASH JOIN | | 20026 | 1192K| | 11789 (0)|| 11 | TABLE ACCESS FULL| T_PAY_MODE | 25 | 525 | |2 (0)||* 12 | TABLE ACCESS BY INDEX ROWID| T_POLICY_FEE | 20026 | 782K| | 11786 (0)||* 13 | INDEX RANGE SCAN| IDX_POLICY_FEE__RECEIV_STATUS | 1243K| | | 10188 (0)|| 14 | TABLE ACCESS BY INDEX ROWID | T_CONTRACT_MASTER |1 | 43 | |2 (50)||* 15 |INDEX UNIQUE SCAN | PK_T_CONTRACT_MASTER |1 | | |1 (0)|| 16 | TABLE ACCESS BY INDEX ROWID | T_COMPANY_CUSTOMER |1 | 90 | |2 (50)||* 17 | INDEX UNIQUE SCAN | PK_T_COMPANY_CUSTOMER|1 | | | || 18 | TABLE ACCESS BY INDEX ROWID | T_CUSTOMER |1 | 16 | |2 (50)||* 19 | INDEX UNIQUE SCAN | PK_T_CUSTOMER |1 | | |1 (0)|| 20 |TABLE ACCESS BY INDEX ROWID| T_POLICY_CHANGE|1 | 14 | |2 (50)||* 21 | INDEX UNIQUE SCAN | PK_T_POLICY_CHANGE |1 | | |1 (0)|| 22 |TABLE ACCESS BY INDEX ROWID| T_CONTRACT_MASTER |1 | 22 | |2 (50)||* 23 |INDEX UNIQUE SCAN| PK_T_CONTRACT_MASTER |1 | | |1 (0)||* 24 | FILTER | | | | | ||* 25 |CONNECT BY WITH FILTERING | | | | | || 26 |NESTED LOOPS| | | | | ||* 27 | INDEX UNIQUE SCAN | PK_T_COMPANY_ORGAN |1 |6 | | || 28 | TABLE ACCESS BY USER ROWID| T_COMPANY_ORGAN| | | | || 29 |NESTED LOOPS| | | | | || 30 | BUFFER SORT| |7 | 70 | | || 31 | CONNECT BY PUMP| | | | | ||* 32 | INDEX RANGE SCAN| T_COMPANY_ORGAN_IDX_002 |7 | 70 | |1 (0)|--------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T_COMPANY_ORGAN" "T_COMPANY_ORGAN" WHERE"T_COMPANY_ORGAN"."PARENT_ID"=NULL AND ("T_COMPANY_ORGAN"."ORGAN_ID"=:B1)))8 - access("SYS_ALIAS_1"."FEE_TYPE"="C"."TYPE_ID")10 - access("SYS_ALIAS_1"."PAY_MODE"="G"."MODE_ID")12 - filter("SYS_ALIAS_1"."CHANGE_ID" IS NOT NULL AND "SYS_ALIAS_1"."FEE_STATUS"=1 AND "SYS_ALIAS_1"."PAYMENT_ID"IS NULL)13 - access("SYS_ALIAS_1"."RECEIV_STATUS"='1')15 - access("SYS_ALIAS_1"."POLICY_ID"="D"."POLICY_ID")17 - access("D"."COMPANY_ID"="E"."COMPANY_ID"(+))19 - access("D"."APPLICANT_ID"="F"."CUSTOMER_ID"(+))21 - access("TPC"."CHANGE_ID"="SYS_ALIAS_1"."CHANGE_ID")23 - access("TCM"."POLICY_ID"="TPC"."POLICY_ID")24 - filter("T_COMPANY_ORGAN"."ORGAN_ID"=:B1)25 - filter("T_COMPANY_ORGAN"."ORGAN_ID"='101')27 - access("T_COMPANY_ORGAN"."ORGAN_ID"='101')32 - access("T_COMPANY_ORGAN"."PARENT_ID"=NULL)58 rows selected.Statistics----------------------------------------------------------0 recursive calls0 db block gets2817 consistent gets0 physical reads0 redo size2268 bytes sent via SQL*Net to client656 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client40 sorts (memory)0 sorts (disk)9 rows processed
最终这个SQL能在1秒以内跑完，逻辑读下降到2817 ，到此我就没继续优化了，这个时候停止优化吧，别的了强迫优化症
这个优化案例很简单，我都不好意思贴在博客上，通过这个文章你要学到的就是，如果子查询返回数据很少，那么不妨让它走filter
