④内部公开请勿外传--1原始语句的耗时：8.12秒SELECT*FROM(SELECTt0.FSENDERIDFSENDERID_ID,t0.FTITLEFTITLE,t0.FCREATETIMEFCREATETIME,t0_L.FPRERESULTNAMEFPRERESULTNAME,t0.FPREDISPOSITIONFPREDISPOSITION,t0.FBILLNUMBERFBILLNUMBER,t0.FPROCDEFIDFPROCDEFID_ID,t0.FREADSTATUSFREADSTATUS,t0.FASSIGNIDFASSIGNID,ROW_NUMBER()OVER(ORDERBYt0.FCREATETIMEDESC,t0.FREADSTATUSASC)FIDENTITYIDFROMV_WF_ASSIGNT0LEFTOUTERJOINV_WF_ASSIGN_LT0_LON(t0.FASSIGNID=t0_L.FASSIGNIDANDt0_L.FLocaleId=2052)WHERE(t0.FRECEIVERID='110643'ANDt0.FSTATUS=0))TLIST5WHERE((fidentityid>=1)AND(fidentityid<=60))6/FSENDERID_ID------------FTITLE----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------FCREATETIME-------------------FPRERESULTNAME----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------FPREDISPOSITION----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------FBILLNUMBER----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------FPROCDEFID_IDFRFASSIGNIDFIDENTITYID-------------------------------------------------------------------------------------------------------------------------------------------------------------106528112020-04-3015:40:07FYBX202004300000795d42517825c00e05eaa80d7e4d0a91，，，7rowsselected.Elapsed:00:00:08.12--2分析语句的耗时，发现时间都消耗在步骤34(3.81秒)和步骤31（7.33秒（含步骤34的3.81秒））的HASHJOIN上。SQL>select*fromtable(dbms_xplan.display_cursor(NULL,NULL,'allstatslast'));PLAN_TABLE_OUTPUT1/7④内部公开请勿外传----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID1zxvzdqfs9h00,childnumber0-------------------------------------SELECT*FROM(SELECTt0.FSENDERIDFSENDERID_ID,t0.FTITLEFTITLE,t0.FCREATETIMEFCREATETIME,t0_L.FPRERESULTNAMEFPRERESULTNAME,t0.FPREDISPOSITIONFPREDISPOSITION,t0.FBILLNUMBERFBILLNUMBER,t0.FPROCDEFIDFPROCDEFID_ID,t0.FREADSTATUSFREADSTATUS,t0.FASSIGNIDFASSIGNID,ROW_NUMBER()OVER(ORDERBYt0.FCREATETIMEDESC,t0.FREADSTATUSASC)FIDENTITYIDFROMV_WF_ASSIGNT0LEFTOUTERJOINV_WF_ASSIGN_LT0_LON(t0.FASSIGNID=t0_L.FASSIGNIDANDt0_L.FLocaleId=2052)WHERE(t0.FRECEIVERID='110643'ANDt0.FSTATUS=0))TLISTWHERE((fidentityid>=1)AND(fidentityid<=60))Planhashvalue:2174371054-------------------------------------------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|OMem|1Mem|Used-Mem|-------------------------------------------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||7|00:00:08.04|167K|||||*1|VIEW||1|860|7|00:00:08.04|167K|||||*2|WINDOWSORTPUSHEDRANK||1|860|7|00:00:08.03|167K|2048|2048|2048(0)||*3|HASHJOINOUTER||1|860|7|00:00:08.03|167K|765K|765K|763K(0)||4|NESTEDLOOPSOUTER||1|604|7|00:00:00.02|7696|||||*5|FILTER||1||7|00:00:00.02|7682|||||6|NESTEDLOOPSOUTER||1|604|7|00:00:00.02|7682|||||7|VIEW||1|604|7|00:00:00.02|7662|||||8|NESTEDLOOPSOUTER||1|604|7|00:00:00.02|7662|||||9|NESTEDLOOPS||1|601|7|00:00:00.02|7646|||||10|NESTEDLOOPS||1|597|7|00:00:00.02|7623|||||11|NESTEDLOOPS||1|597|7|00:00:00.01|7600|||||12|NESTEDLOOPS||1|597|7|00:00:00.01|7591|||||13|NESTEDLOOPS||1|1455|44|00:00:00.01|7457|||||14|TABLEACCESSBYINDEXROWID|T_WF_RECEIVER|1|1455|2060|00:00:00.01|1475|||||*15|INDEXRANGESCAN|IDX_WF_REC_REC|1|1455|2060|00:00:00.01|10|||||*16|TABLEACCESSBYINDEXROWID|T_WF_ASSIGN|2060|1|44|00:00:00.01|5982|||||*17|INDEXUNIQUESCAN|PK_WF_ASSIGN|2060|1|2060|00:00:00.01|3922|||||*18|TABLEACCESSBYINDEXROWID|T_WF_PROCINST|44|1|7|00:00:00.01|134|||||*19|INDEXUNIQUESCAN|PK_WF_PROCINST|44|1|44|00:00:00.01|90|||||*20|INDEXUNIQUESCAN|PK_WF_TEMPLATE|7|1|7|00:00:00.01|9|||||*21|TABLEACCESSBYINDEXROWID|T_WF_ACTINST|7|1|7|00:00:00.01|23|||||*22|INDEXUNIQUESCAN|PK_WF_ACTINST|7|1|7|00:00:00.01|16|||||23|TABLEACCESSBYINDEXROWID|T_WF_APPROVALASSIGN|7|1|7|00:00:00.01|23|||||*24|INDEXRANGESCAN|IDX_WF_APP_ASSIGN|7|1|7|00:00:00.01|16|||||*25|INDEXRANGESCAN|IDX_WF_PIBI_INST|7|1|7|00:00:00.01|16|||||26|TABLEACCESSBYINDEXROWID|T_WF_APPROVALITEM|7|1|4|00:00:00.01|20||||2/7④内部公开请勿外传|*27|INDEXUNIQUESCAN|IDX_WF_APPITEM_ASSIGN|7|1|4|00:00:00.01|16|||||28|TABLEACCESSBYINDEXROWID|T_WF_APPROVALASSIGN|7|1|0|00:00:00.01|14|||||*29|INDEXUNIQUESCAN|PK_WF_APPROVALASSIGN|7|1|0|00:00:00.01|14|||||30|VIEW|V_WF_ASSIGN_L|1|4221K|2988K|00:00:07.55|160K|||||*31|HASHJOINRIGHTOUTER||1|4221K|2988K|00:00:07.33|160K|213M|11M|241M(0)|--时间都消耗在HASHJOIN右关联上|*32|TABLEACCESSFULL|T_WF_APPROVALASSIGN_L|1|2943K|2976K|00:00:00.42|70285|||||33|VIEW||1|2983K|2988K|00:00:04.04|89944|||||*34|HASHJOIN||1|2983K|2988K|00:00:03.81|89944|172M|13M|174M(0)||*35|INDEXFASTFULLSCAN|IDX_WF_ASSIGNL|1|2983K|2988K|00:00:00.43|33354|||||36|TABLEACCESSFULL|T_WF_ASSIGN|1|2985K|2988K|00:00:00.56|56590||||-------------------------------------------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-filter(("FIDENTITYID">=1AND"FIDENTITYID"<=60))2-filter(ROW_NUMBER()OVER(ORDERBYINTERNAL_FUNCTION("from$_subquery$_023"."QCSJ_C000000001700000")DESC,CASEWHEN"T_WF_APPROVALITEM"."FREADSTATUS"ISNULLTHEN'0'ELSE"T_WF_APPROVALITEM"."FREADSTATUS"END)<=60)3-access("from$_subquery$_023"."QCSJ_C000000001500002"="T0_L"."FASSIGNID")5-filter((TO_NUMBER(CASEWHEN"T_WF_APPROVALITEM"."FSTATUS"ISNULLTHEN'0'ELSE"T_WF_APPROVALITEM"."FSTATUS"END)=0AND("T_WF_APPROVALITEM"."FAPPROVALITEMID"ISNULLOR"T_WF_APPROVALITEM"."FSTATUS"='0')))15-access("T_WF_RECEIVER"."FRECEIVERID"=110643)16-filter("T_WF_ASSIGN"."FSTATUS"='0')17-access("T_WF_ASSIGN"."FASSIGNID"="T_WF_RECEIVER"."FASSIGNID")18-filter("T_WF_PROCINST"."FSTATUS"='2')19-access("T_WF_ASSIGN"."FPROCINSTID"="T_WF_PROCINST"."FPROCINSTID")20-access("T_WF_TEMPLATE"."FTMPID"="T_WF_PROCINST"."FTMPID")21-filter("T_WF_ACTINST"."FSTATUS"='2')22-access("T_WF_ASSIGN"."FACTINSTID"="T_WF_ACTINST"."FACTINSTID")24-access("T_WF_ASSIGN"."FASSIGNID"="T_WF_APPROVALASSIGN"."FASSIGNID")25-access("T_WF_ASSIGN"."FPROCINSTID"="T_WF_PIBIMAP"."FPROCINSTID")27-access("T_WF_APPROVALASSIGN"."FAPPROVALASSIGNID"="T_WF_APPROVALITEM"."FAPPROVALASSIGNID"AND"T_WF_APPROVALITEM"."FRECEIVERID"=110643)29-access("from$_subquery$_023"."FPREAPPROVALASSIGNID"="PREASSIGNRESULT"."FAPPROVALASSIGNID")31-access("PREASSIGNRESULT_L"."FLOCALEID"="T_WF_ASSIGN_L"."FLOCALEID"AND"T_WF_ASSIGN"."FPREAPPROVALASSIGNID"="PREASSIGNRESULT_L"."FAPPROVALASSIGNID")32-filter("PREASSIGNRESULT_L"."FLOCALEID"=2052)34-access("T_WF_ASSIGN"."FASSIGNID"="T_WF_ASSIGN_L"."FASSIGNID")35-filter("T_WF_ASSIGN_L"."FLOCALEID"=2052)83rowsselected.3/7④内部公开请勿外传Elapsed:00:00:00.21--3分析语句，发现语句只需要提取前60行数据，于是考虑把消耗时间的外关联操作，迁移到外面操作，也就是，先取满足排序条件的前60行记录，再和表V_WF_ASSIGN_L作外关联，因为此时，优化器认识到驱动表只有60行记录，可能会选择走效率更高的嵌套循环，SELECTt0.FSENDERID_ID,t0.FTITLE,t0.FCREATETIME,t0_L.FPRERESULTNAMEFPRERESULTNAME,t0.FPREDISPOSITION,t0.FBILLNUMBER,t0.FPROCDEFID_ID,t0.FREADSTATUS,t0.FASSIGNID,t0.FIDENTITYIDFROM(SELECTt0.FSENDERIDFSENDERID_ID,t0.FTITLEFTITLE,t0.FCREATETIMEFCREATETIME,t0.FPREDISPOSITIONFPREDISPOSITION,t0.FBILLNUMBERFBILLNUMBER,t0.FPROCDEFIDFPROCDEFID_ID,t0.FREADSTATUSFREADSTATUS,t0.FASSIGNIDFASSIGNID,ROW_NUMBER()OVER(ORDERBYt0.FCREATETIMEDESC,t0.FREADSTATUSASC)FIDENTITYIDFROMV_WF_ASSIGNT0WHERE(t0.FRECEIVERID='110643'ANDt0.FSTATUS=0))T0LEFTOUTERJOINV_WF_ASSIGN_LT0_LON(t0.FASSIGNID=t0_L.FASSIGNIDANDt0_L.FLocaleId=2052)8WHERE((fidentityid>=1)AND(fidentityid<=60));FSENDERID_ID------------FTITLE----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------FCREATETIME-------------------FPRERESULTNAME----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------FPREDISPOSITION----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------FBILLNUMBER----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------FPROCDEFID_IDFRFASSIGNIDFIDENTITYID-------------------------------------------------------------------------------------------------------------------------------------------------------------106528112020-04-3015:40:07FYBX202004300000795d42517825c00e05eaa80d7e4d0a91，，，7rowsselected.Elapsed:00:00:00.044/7④内部公开请勿外传--4SQL>select*fromtable(dbms_xplan.display_cursor(NULL,NULL,'allstatslast'));PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_IDdfvwcmv9xgrn2,childnumber0-------------------------------------SELECTt0.FSENDERID_ID,t0.FTITLE,t0.FCREATETIME,t0_L.FPRERESULTNAMEFPRERESULTNAME,t0.FPREDISPOSITION,t0.FBILLNUMBER,t0.FPROCDEFID_ID,t0.FREADSTATUS,t0.FASSIGNID,t0.FIDENTITYIDFROM(SELECTt0.FSENDERIDFSENDERID_ID,t0.FTITLEFTITLE,t0.FCREATETIMEFCREATETIME,t0.FPREDISPOSITIONFPREDISPOSITION,t0.FBILLNUMBERFBILLNUMBER,t0.FPROCDEFIDFPROCDEFID_ID,t0.FREADSTATUSFREADSTATUS,t0.FASSIGNIDFASSIGNID,ROW_NUMBER()OVER(ORDERBYt0.FCREATETIMEDESC,t0.FREADSTATUSASC)FIDENTITYIDFROMV_WF_ASSIGNT0WHERE(t0.FRECEIVERID='110643'ANDt0.FSTATUS=0))T0LEFTOUTERJOINV_WF_ASSIGN_LT0_LON(t0.FASSIGNID=t0_L.FASSIGNIDANDt0_L.FLocaleId=2052)WHERE((fidentityid>=1)AND(fidentityid<=60))Planhashvalue:2672837093-------------------------------------------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|OMem|1Mem|Used-Mem|-------------------------------------------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||7|00:00:00.01|7749|||||1|NESTEDLOOPSOUTER||1|4221K|7|00:00:00.01|7749||||--2走圈套循环|*2|VIEW||1|604|7|00:00:00.01|7696|||||*3|WINDOWSORTPUSHEDRANK||1|604|7|00:00:00.01|7696|2048|2048|2048(0)|--1先获取60条记录|4|NESTEDLOOPSOUTER||1|604|7|00:00:00.01|7696|||||*5|FILTER||1||7|00:00:00.01|7682|||||6|NESTEDLOOPSOUTER||1|604|7|00:00:00.01|7682|||||7|VIEW||1|604|7|00:00:00.01|7662|||||8|NESTEDLOOPSOUTER||1|604|7|00:00:00.01|7662|||||9|NESTEDLOOPS||1|601|7|00:00:00.01|7646|||||10|NESTEDLOOPS||1|597|7|00:00:00.01|7623|||||11|NESTEDLOOPS||1|597|7|00:00:00.01|7600|||||12|NESTEDLOOPS||1|597|7|00:00:00.01|7591|||||13|NESTEDLOOPS||1|1455|44|00:00:00.01|7457|||||14|TABLEACCESSBYINDEXROWID|T_WF_RECEIVER|1|1455|2060|00:00:00.01|1475|||||*15|INDEXRANGESCAN|IDX_WF_REC_REC|1|1455|2060|00:00:00.01|10|||||*16|TABLEACCESSBYINDEXROWID|T_WF_ASSIGN|2060|1|44|00:00:00.01|5982|||||*17|INDEXUNIQUESCAN|PK_WF_ASSIGN|2060|1|2060|00:00:00.01|3922|||||*18|TABLEACCESSBYINDEXROWID|T_WF_PROCINST|44|1|7|00:00:00.01|134||||5/7④内部公开请勿外传|*19|INDEXUNIQUESCAN|PK_WF_PROCINST|44|1|44|00:00:00.01|90|||||*20|INDEXUNIQUESCAN|PK_WF_TEMPLATE|7|1|7|00:00:00.01|9|||||*21|TABLEACCESSBYINDEXROWID|T_WF_ACTINST|7|1|7|00:00:00.01|23|||||*22|INDEXUNIQUESCAN|PK_WF_ACTINST|7|1|7|00:00:00.01|16|||||23|TABLEACCESSBYINDEXROWID|T_WF_APPROVALASSIGN|7|1|7|00:00:00.01|23|||||*24|INDEXRANGESCAN|IDX_WF_APP_ASSIGN|7|1|7|00:00:00.01|16|||||*25|INDEXRANGESCAN|IDX_WF_PIBI_INST|7|1|7|00:00:00.01|16|||||26|TABLEACCESSBYINDEXROWID|T_WF_APPROVALITEM|7|1|4|00:00:00.01|20|||||*27|INDEXUNIQUESCAN|IDX_WF_APPITEM_ASSIGN|7|1|4|00:00:00.01|16|||||28|TABLEACCESSBYINDEXROWID|T_WF_APPROVALASSIGN|7|1|0|00:00:00.01|14|||||*29|INDEXUNIQUESCAN|PK_WF_APPROVALASSIGN|7|1|0|00:00:00.01|14|||||30|VIEWPUSHEDPREDICATE|V_WF_ASSIGN_L|7|1|7|00:00:00.01|53|||||31|NESTEDLOOPSOUTER||7|1|7|00:00:00.01|53|||||32|VIEW||7|1|7|00:00:00.01|39|||||33|NESTEDLOOPS||7|1|7|00:00:00.01|39|||||*34|INDEXUNIQUESCAN|IDX_WF_ASSIGNL|7|1|7|00:00:00.01|16||||--3走索引|35|TABLEACCESSBYINDEXROWID|T_WF_ASSIGN|7|1|7|00:00:00.01|23|||||*36|INDEXUNIQUESCAN|PK_WF_ASSIGN|7|1|7|00:00:00.01|16||||--4走索引|37|TABLEACCESSBYINDEXROWID|T_WF_APPROVALASSIGN_L|7|1|0|00:00:00.01|14|||||*38|INDEXUNIQUESCAN|IDX_WF_APPL|7|1|0|00:00:00.01|14||||--5走索引-------------------------------------------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------2-filter(("T0"."FIDENTITYID">=1AND"T0"."FIDENTITYID"<=60))3-filter(ROW_NUMBER()OVER(ORDERBYINTERNAL_FUNCTION("from$_subquery$_023"."QCSJ_C000000001700000")DESC,CASEWHEN"T_WF_APPROVALITEM"."FREADSTATUS"ISNULLTHEN'0'ELSE"T_WF_APPROVALITEM"."FREADSTATUS"END)<=60)5-filter((TO_NUMBER(CASEWHEN"T_WF_APPROVALITEM"."FSTATUS"ISNULLTHEN'0'ELSE"T_WF_APPROVALITEM"."FSTATUS"END)=0AND("T_WF_APPROVALITEM"."FAPPROVALITEMID"ISNULLOR"T_WF_APPROVALITEM"."FSTATUS"='0')))15-access("T_WF_RECEIVER"."FRECEIVERID"=110643)16-filter("T_WF_ASSIGN"."FSTATUS"='0')17-access("T_WF_ASSIGN"."FASSIGNID"="T_WF_RECEIVER"."FASSIGNID")18-filter("T_WF_PROCINST"."FSTATUS"='2')19-access("T_WF_ASSIGN"."FPROCINSTID"="T_WF_PROCINST"."FPROCINSTID")20-access("T_WF_TEMPLATE"."FTMPID"="T_WF_PROCINST"."FTMPID")21-filter("T_WF_ACTINST"."FSTATUS"='2')22-access("T_WF_ASSIGN"."FACTINSTID"="T_WF_ACTINST"."FACTINSTID")24-access("T_WF_ASSIGN"."FASSIGNID"="T_WF_APPROVALASSIGN"."FASSIGNID")25-access("T_WF_ASSIGN"."FPROCINSTID"="T_WF_PIBIMAP"."FPROCINSTID")27-access("T_WF_APPROVALASSIGN"."FAPPROVALASSIGNID"="T_WF_APPROVALITEM"."FAPPROVALASSIGNID"AND"T_WF_APPROVALITEM"."FRECEIVERID"=110643)29-access("from$_subquery$_023"."FPREAPPROVALASSIGNID"="PREASSIGNRESULT"."FAPPROVALASSIGNID")34-access("T_WF_ASSIGN_L"."FASSIGNID"="T0"."FASSIGNID"AND"T_WF_ASSIGN_L"."FLOCALEID"=2052)6/7④内部公开请勿外传36-access("T_WF_ASSIGN"."FASSIGNID"="T0"."FASSIGNID")38-access("T_WF_ASSIGN"."FPREAPPROVALASSIGNID"="PREASSIGNRESULT_L"."FAPPROVALASSIGNID"AND"PREASSIGNRESULT_L"."FLOCALEID"=2052)85rowsselected.Elapsed:00:00:00.04SQL>7/7
