子查询优化的经典案例

子查询优化的经典案例分类：oracle性能优化2012-09-0602:19324人阅读评论(0)收藏举报上周五要下班的时候，盖尔发来一个SQL[html]viewplaincopyprint?1.selecttpc.policy_id,2.tcm.policy_code,3.tpf.organ_id,4.to_char(tpf.insert_time,'YYYY-MM-DD')Asinsert_time,5.tpc.change_id,6.d.policy_code,7.e.company_name,8.f.real_name,9.tpf.fee_type,10.sum(tpf.pay_balance)aspay_balance,11.c.actual_type,12.tpc.notice_code,13.d.policy_type,14.g.mode_nameaspay_mode15.fromt_policy_changetpc,16.t_contract_mastertcm,17.t_policy_feetpf,18.t_fee_typec,19.t_contract_masterd,20.t_company_customere,21.t_customerf,22.t_pay_modeg23.wheretpc.change_id=tpf.change_id24.andtpf.policy_id=d.policy_id25.andtcm.policy_id=tpc.policy_id26.andtpf.receiv_status=127.andtpf.fee_status=128.andtpf.payment_idisnull29.andtpf.fee_type=c.type_id30.andtpf.pay_mode=g.mode_id31.andd.company_id=e.company_id(+)32.andd.applicant_id=f.customer_id(+)33.andtpf.organ_idin34.(select35.organ_id36.fromt_company_organ37.startwithorgan_id='101'38.connectbypriororgan_id=parent_id)39.groupbytpc.policy_id,40.tpc.change_id,41.tpf.fee_type,42.to_char(tpf.insert_time,'YYYY-MM-DD'),43.c.actual_type,44.d.policy_code,45.g.mode_name,46.e.company_name,47.f.real_name,48.tpc.notice_code,49.d.policy_type,50.tpf.organ_id,51.tcm.policy_code52.orderbychange_id,fee_type53.54.SQLselect*fromtable(dbms_xplan.display);55.56.PLAN_TABLE_OUTPUT57.----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------58.|Id|Operation|Name|Rows|Bytes|TempSpc|Cost(%CPU)|59.----------------------------------------------------------------------------------------------------------------60.|0|SELECTSTATEMENT||45962|11M||45650(0)|61.|1|SORTGROUPBY||45962|11M|23M|45650(0)|62.|*2|HASHJOIN||45962|11M||43908(0)|63.|3|INDEXFULLSCAN|T_FEE_TYPE_IDX_003|106|636||1(0)|64.|4|NESTEDLOOPSOUTER||45962|11M||43906(0)|65.|*5|HASHJOIN||45962|7271K|6824K|43905(0)|66.|6|NESTEDLOOPS||45961|6283K||42312(0)|67.|*7|HASHJOINSEMI||45961|5655K|50M|33120(1)|68.|*8|HASHJOINOUTER||400K|45M|44M|32315(1)|69.|*9|HASHJOIN||400K|39M|27M|26943(0)|70.|*10|HASHJOIN||400K|23M||16111(0)|71.|11|TABLEACCESSFULL|T_PAY_MODE|25|525||2(0)|72.|*12|TABLEACCESSFULL|T_POLICY_FEE|400K|15M||16107(0)|73.|13|TABLEACCESSFULL|T_CONTRACT_MASTER|1136K|46M||9437(0)|74.|14|VIEW|index_join_007|2028K|30M|||75.|*15|HASHJOIN||400K|45M|44M|32315(1)|76.|16|INDEXFASTFULLSCAN|PK_T_CUSTOMER|2028K|30M||548(0)|77.|17|INDEXFASTFULLSCAN|IDX_CUSTOMER__BIR_REAL_GEN|2028K|30M||548(0)|78.|18|VIEW|VW_NSO_1|7|42|||79.|*19|CONNECTBYWITHFILTERING||||||80.|20|NESTEDLOOPS||||||81.|*21|INDEXUNIQUESCAN|PK_T_COMPANY_ORGAN|1|6|||82.|22|TABLEACCESSBYUSERROWID|T_COMPANY_ORGAN|||||83.|23|NESTEDLOOPS||||||84.|24|BUFFERSORT||7|70|||85.|25|CONNECTBYPUMP||||||86.|*26|INDEXRANGESCAN|T_COMPANY_ORGAN_IDX_002|7|70||1(0)|87.|27|TABLEACCESSBYINDEXROWID|T_POLICY_CHANGE|1|14||2(50)|88.|*28|INDEXUNIQUESCAN|PK_T_POLICY_CHANGE|1|||1(0)|89.|29|INDEXFASTFULLSCAN|IDX1_ACCEPT_DATE|1136K|23M||899(0)|90.|30|TABLEACCESSBYINDEXROWID|T_COMPANY_CUSTOMER|1|90||2(50)|91.|*31|INDEXUNIQUESCAN|PK_T_COMPANY_CUSTOMER|1||||92.----------------------------------------------------------------------------------------------------------------93.94.PredicateInformation(identifiedbyoperationid):95.---------------------------------------------------96.97.2-access(TPF.FEE_TYPE=C.TYPE_ID)98.5-access(TCM.POLICY_ID=TPC.POLICY_ID)99.7-access(TPF.ORGAN_ID=VW_NSO_1.$nso_col_1)100.8-access(D.APPLICANT_ID=F.CUSTOMER_ID(+))101.9-access(TPF.POLICY_ID=D.POLICY_ID)102.10-access(TPF.PAY_MODE=G.MODE_ID)103.12-filter(TPF.CHANGE_IDISNOTNULLANDTO_NUMBER(TPF.RECEIV_STATUS)=1ANDTPF.FEE_STATUS=1AND104.TPF.PAYMENT_IDISNULL)105.15-access(indexjoin_alias_012.ROWID=indexjoin_alias_011.ROWID)106.19-filter(T_COMPANY_ORGAN.ORGAN_ID='101')107.21-access(T_COMPANY_ORGAN.ORGAN_ID='101')108.26-access(T_COMPANY_ORGAN.PARENT_ID=NULL)109.28-access(TPC.CHANGE_ID=TPF.CHANGE_ID)110.31-access(D.COMPANY_ID=E.COMPANY_ID(+))111.112.55rowsselected113.114.Statistics115.----------------------------------------------------------116.21recursivecalls117.0dbblockgets118.125082consistentgets119.21149physicalreads120.0redosize121.2448bytessentviaSQL*Nettoclient122.656bytesreceivedviaSQL*Netfromclient123.2SQL*Netroundtripsto/fromclient124.4sorts(memory)125.0sorts(disk)126.11rowsprocessed这个SQL要21秒才能跑完，逻辑读12W左右，问我能不能优化。优化这个SQL我只花了1分钟左右的时间，因为太简单了你们看这个SQL是典型的JOIN，对付这种SQL肯定要让表走索引，但是从执行计划上看有个1千万行的表T_CONTRACT_MASTER走的是全表扫描，T_POLICY_FEE这个400W行的表也是走全表扫描，那么它不慢才怪呢，然后SQL的过滤条件有个in子查询(selectorgan_idfromt_company_organstartwithorgan_id='101'connectbypriororgan_id=parent_id)从执行计划上看，CBO对这儿子查询进行了unnest，因为通常情况下CBO认为子查询被unnest之后性能好于filter于是我让盖尔查询子查询返回多少行selectorgan_idfromt_company_organstartwithorgan_id='101'connectbypriororgan_id