某保險理賠核心系統oracle平遷ob國產化專案，目前已經投產完成，穩定執行。
其中遇到條執行3600s 慢sql，這條sql是 hibernate 拼接出來的語句，如果頁面不同選項的話，表順序，謂詞過濾條件內容都會不一樣。
目前只針對這條拼接的慢sql進行最佳化，並無實際多大用處，記錄個改法案例，實際最佳化方案還是建議應用基於業務邏輯最佳化拼接sql的邏輯。
OB原廠技術、架構交流，效能最佳化溝通交流可以聯絡筆者。
慢SQL如下：
SELECT COUNT(*) AS COL_0_0_ FROM GGGGGGG WBUSINESSC0_, VVVVVVV VVVVVVV1_, GGDFFF PRPLBPMCOM2_, SDQQQQQ PRPDCOMPAN3_, YUYUYU WBUSINESSS4_ WHERE WBUSINESSC0_.NODEID=WBUSINESSS4_.ID AND WBUSINESSC0_.TASKID=PRPLBPMCOM2_.BPMMAINID AND WBUSINESSC0_.VALID=1 AND VVVVVVV1_.RISKCODE<>'9999' AND VVVVVVV1_.MAKECOM=PRPDCOMPAN3_.COMCODE AND VVVVVVV1_.MAKECOM=PRPDCOMPAN3_.COMCODE AND WBUSINESSC0_.BUSINESSNO=VVVVVVV1_.PROPOSALNO AND VVVVVVV1_.CLASSCODE=('01') AND ( WBUSINESSC0_.INDATE BETWEEN (TO_DATE('2024-11-20 00:00:00','YYYY-MM-DD HH24:MI:SS')) AND ( TO_DATE('2024-11-24 00:00:00','YYYY-MM-DD HH24:MI:SS'))) AND WBUSINESSC0_.STATE=('1') AND ( EXISTS (SELECT 1 FROM SDQQQQQ B_ WHERE PRPLBPMCOM2_.COMCODE1=B_.COMCODE START WITH B_.COMCODE IN ('00000000') CONNECT BY PRIOR B_.COMCODE= B_.UPPERCOMCODE AND PRIOR B_.COMCODE <> B_.COMCODE) AND 1=1 AND ( WBUSINESSC0_.RISKCODE IN ('0105' , '0128' , '0103' , '0101' , '0198' , '0199' , '0109' , '0107' , '0108')) AND WBUSINESSS4_.NODENAME='UNDERWRITEFOUR' OR EXISTS (SELECT 1 FROM SDQQQQQ B_ WHERE PRPLBPMCOM2_.COMCODE1=B_.COMCODE START WITH B_.COMCODE IN ('00000000') CONNECT BY PRIOR B_.COMCODE= B_.UPPERCOMCODE AND PRIOR B_.COMCODE <> B_.COMCODE) AND 1=1 AND ( WBUSINESSC0_.RISKCODE IN ('0105' , '0128' , '0103' , '0101' , '0198' , '0199' , '0109' , '0107' , '0108')) AND WBUSINESSS4_.NODENAME='UNDERWRITEFIVE' OR EXISTS (SELECT 1 FROM SDQQQQQ B_ WHERE PRPLBPMCOM2_.COMCODE1=B_.COMCODE START WITH B_.COMCODE IN ('00000000') CONNECT BY PRIOR B_.COMCODE= B_.UPPERCOMCODE AND PRIOR B_.COMCODE <> B_.COMCODE) AND 1=1 AND ( WBUSINESSC0_.RISKCODE IN ('0105' , '0128' , '0103' , '0101' , '0198' , '0199' , '0109' , '0107' , '0108')) AND WBUSINESSS4_.NODENAME='UNDERWRITESIX' OR EXISTS (SELECT 1 FROM SDQQQQQ B_ WHERE PRPLBPMCOM2_.COMCODE1=B_.COMCODE START WITH B_.COMCODE IN ('00000000') CONNECT BY PRIOR B_.COMCODE= B_.UPPERCOMCODE AND PRIOR B_.COMCODE <> B_.COMCODE) AND 1=1 AND ( WBUSINESSC0_.RISKCODE IN ('0105' , '0128' , '0103' , '0101' , '0198' , '0199' , '0109' , '0107' , '0108')) AND WBUSINESSS4_.NODENAME='UNDERWRITESEVEN' OR EXISTS (SELECT 1 FROM SDQQQQQ B_ WHERE PRPLBPMCOM2_.COMCODE1=B_.COMCODE START WITH B_.COMCODE IN ('00000000') CONNECT BY PRIOR B_.COMCODE= B_.UPPERCOMCODE AND PRIOR B_.COMCODE <> B_.COMCODE) AND 1=1 AND ( WBUSINESSC0_.RISKCODE IN ('0105' , '0128' , '0103' , '0101' , '0198' , '0199' , '0109' , '0107' , '0108')) AND WBUSINESSS4_.NODENAME='UNDERWRITETWO' OR EXISTS (SELECT 1 FROM SDQQQQQ B_ WHERE PRPLBPMCOM2_.COMCODE1=B_.COMCODE START WITH B_.COMCODE IN ('00000000') CONNECT BY PRIOR B_.COMCODE= B_.UPPERCOMCODE AND PRIOR B_.COMCODE <> B_.COMCODE) AND 1=1 AND ( WBUSINESSC0_.RISKCODE IN ('0105' , '0128' , '0103' , '0101' , '0198' , '0199' , '0109' , '0107' , '0108')) AND WBUSINESSS4_.NODENAME='UNDERWRITETHREE' OR EXISTS (SELECT 1 FROM SDQQQQQ B_ WHERE PRPLBPMCOM2_.COMCODE1=B_.COMCODE START WITH B_.COMCODE IN ('00000000') CONNECT BY PRIOR B_.COMCODE= B_.UPPERCOMCODE AND PRIOR B_.COMCODE <> B_.COMCODE) AND 1=1 AND ( WBUSINESSC0_.RISKCODE IN ('0105' , '0128' , '0103' , '0101' , '0198' , '0199' , '0109' , '0107' , '0108')) AND WBUSINESSS4_.NODENAME='UNDERWRITEONE');
慢SQL執行計劃：
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ============================================================================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | -------------------------------------------------------------------------------------------------------------- | | |0 |SCALAR GROUP BY | |1 |93712 | | | |1 |└─SUBPLAN FILTER | |2 |93712 | | | |2 | ├─NESTED-LOOP JOIN | |2 |93412 | | | |3 | │ ├─NESTED-LOOP JOIN | |2 |93382 | | | |4 | │ │ ├─NESTED-LOOP JOIN | |2 |93353 | | | |5 | │ │ │ ├─HASH JOIN | |2 |93310 | | | |6 | │ │ │ │ ├─TABLE RANGE SCAN |WBUSINESSS4_(IDX_YUYUYU) |1 |17 | | | |7 | │ │ │ │ └─TABLE RANGE SCAN |WBUSINESSC0_(IDX_VVVVVVV_STATE1) |105 |93283 | | | |8 | │ │ │ └─DISTRIBUTED TABLE RANGE SCAN |PRPLBPMCOM2_(IDX_GGDFFF_BPMMAINID) |1 |27 | | | |9 | │ │ └─DISTRIBUTED TABLE GET |VVVVVVV1_ |1 |18 | | | |10| │ └─DISTRIBUTED TABLE GET |PRPDCOMPAN3_ |1 |18 | | | |11| ├─LIMIT | |1 |27 | | | |12| │ └─SUBPLAN SCAN |VIEW1 |1 |27 | | | |13| │ └─NESTED-LOOP CONNECT BY | |18 |27 | | | |14| │ ├─SUBPLAN SCAN |VIEW2 |1 |5 | | | |15| │ │ └─TABLE GET |B_ |1 |5 | | | |16| │ └─SUBPLAN SCAN |VIEW3 |17 |21 | | | |17| │ └─DISTRIBUTED TABLE RANGE SCAN|B_(IDX_COMPANY_UPPERCOMCODE) |17 |21 | | | |18| ├─LIMIT | |1 |27 | | | |19| │ └─SUBPLAN SCAN |VIEW4 |1 |27 | | | |20| │ └─NESTED-LOOP CONNECT BY | |18 |27 | | | |21| │ ├─SUBPLAN SCAN |VIEW5 |1 |5 | | | |22| │ │ └─TABLE GET |B_ |1 |5 | | | |23| │ └─SUBPLAN SCAN |VIEW6 |17 |21 | | | |24| │ └─DISTRIBUTED TABLE RANGE SCAN|B_(IDX_COMPANY_UPPERCOMCODE) |17 |21 | | | |25| ├─LIMIT | |1 |27 | | | |26| │ └─SUBPLAN SCAN |VIEW7 |1 |27 | | | |27| │ └─NESTED-LOOP CONNECT BY | |18 |27 | | | |28| │ ├─SUBPLAN SCAN |VIEW8 |1 |5 | | | |29| │ │ └─TABLE GET |B_ |1 |5 | | | |30| │ └─SUBPLAN SCAN |VIEW9 |17 |21 | | | |31| │ └─DISTRIBUTED TABLE RANGE SCAN|B_(IDX_COMPANY_UPPERCOMCODE) |17 |21 | | | |32| ├─LIMIT | |1 |27 | | | |33| │ └─SUBPLAN SCAN |VIEW10 |1 |27 | | | |34| │ └─NESTED-LOOP CONNECT BY | |18 |27 | | | |35| │ ├─SUBPLAN SCAN |VIEW11 |1 |5 | | | |36| │ │ └─TABLE GET |B_ |1 |5 | | | |37| │ └─SUBPLAN SCAN |VIEW12 |17 |21 | | | |38| │ └─DISTRIBUTED TABLE RANGE SCAN|B_(IDX_COMPANY_UPPERCOMCODE) |17 |21 | | | |39| ├─LIMIT | |1 |27 | | | |40| │ └─SUBPLAN SCAN |VIEW13 |1 |27 | | | |41| │ └─NESTED-LOOP CONNECT BY | |18 |27 | | | |42| │ ├─SUBPLAN SCAN |VIEW14 |1 |5 | | | |43| │ │ └─TABLE GET |B_ |1 |5 | | | |44| │ └─SUBPLAN SCAN |VIEW15 |17 |21 | | | |45| │ └─DISTRIBUTED TABLE RANGE SCAN|B_(IDX_COMPANY_UPPERCOMCODE) |17 |21 | | | |46| ├─LIMIT | |1 |27 | | | |47| │ └─SUBPLAN SCAN |VIEW16 |1 |27 | | | |48| │ └─NESTED-LOOP CONNECT BY | |18 |27 | | | |49| │ ├─SUBPLAN SCAN |VIEW17 |1 |5 | | | |50| │ │ └─TABLE GET |B_ |1 |5 | | | |51| │ └─SUBPLAN SCAN |VIEW18 |17 |21 | | | |52| │ └─DISTRIBUTED TABLE RANGE SCAN|B_(IDX_COMPANY_UPPERCOMCODE) |17 |21 | | | |53| └─LIMIT | |1 |27 | | | |54| └─SUBPLAN SCAN |VIEW19 |1 |27 | | | |55| └─NESTED-LOOP CONNECT BY | |18 |27 | | | |56| ├─SUBPLAN SCAN |VIEW20 |1 |5 | | | |57| │ └─TABLE GET |B_ |1 |5 | | | |58| └─SUBPLAN SCAN |VIEW21 |17 |21 | | | |59| └─DISTRIBUTED TABLE RANGE SCAN|B_(IDX_COMPANY_UPPERCOMCODE) |17 |21 | | | ============================================================================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([T_FUN_COUNT(*)(0x7fa12f987a40)]), filter(nil) | | group(nil), agg_func([T_FUN_COUNT(*)(0x7fa12f987a40)]) | | 1 - output(nil), filter([(T_OP_OR, (T_OP_EXISTS, subquery(1)(0x7fa12f87b1c0))(0x7fa12f87db90) AND WBUSINESSS4_.NODENAME(0x7fa12f969dc0) = cast('UNDERWRITEFOUR', | | VARCHAR2(1048576 ))(0x7fa12f97adc0)(0x7fa12f881d10)(0x7fa12f970830), (T_OP_EXISTS, subquery(2)(0x7fa12f882b10))(0x7fa12f8854e0) AND WBUSINESSS4_.NODENAME(0x7fa12f969dc0) | | = cast('UNDERWRITEFIVE', VARCHAR2(1048576 ))(0x7fa12f97bd80)(0x7fa12f889690)(0x7fa12f970f30), (T_OP_EXISTS, subquery(3)(0x7fa12f88a490))(0x7fa12f88ce60) | | AND WBUSINESSS4_.NODENAME(0x7fa12f969dc0) = cast('UNDERWRITESIX', VARCHAR2(1048576 ))(0x7fa12f97cd40)(0x7fa12f890fe0)(0x7fa12f971630), (T_OP_EXISTS, subquery(4)(0x7fa12f891de0))(0x7fa12f8947b0) | | AND WBUSINESSS4_.NODENAME(0x7fa12f969dc0) = cast('UNDERWRITESEVEN', VARCHAR2(1048576 ))(0x7fa12f97dd00)(0x7fa12f898990)(0x7fa12f971d30), (T_OP_EXISTS, subquery(5)(0x7fa12f899790))(0x7fa12f89c160) | | AND WBUSINESSS4_.NODENAME(0x7fa12f969dc0) = cast('UNDERWRITETWO', VARCHAR2(1048576 ))(0x7fa12f97ecc0)(0x7fa12f8a02e0)(0x7fa12f972430), (T_OP_EXISTS, subquery(6)(0x7fa12f8a10e0))(0x7fa12f8a3ab0) | | AND WBUSINESSS4_.NODENAME(0x7fa12f969dc0) = cast('UNDERWRITETHREE', VARCHAR2(1048576 ))(0x7fa12f97fc80)(0x7fa12f8a7c30)(0x7fa12f972b30), (T_OP_EXISTS, subquery(7)(0x7fa12f8a8a30))(0x7fa12f8ab400) | | AND WBUSINESSS4_.NODENAME(0x7fa12f969dc0) = cast('UNDERWRITEONE', VARCHAR2(1048576 ))(0x7fa12f980c40)(0x7fa12f8af640)(0x7fa12f973230))(0x7fa12f970130)]) | | exec_params_([PRPLBPMCOM2_.COMCODE1(0x7fa12f8c9fc0)(:0)], [PRPLBPMCOM2_.COMCODE1(0x7fa12f8c9fc0)(:1)], [PRPLBPMCOM2_.COMCODE1(0x7fa12f8c9fc0)(:2)], | | [PRPLBPMCOM2_.COMCODE1(0x7fa12f8c9fc0)(:3)], [PRPLBPMCOM2_.COMCODE1(0x7fa12f8c9fc0)(:4)], [PRPLBPMCOM2_.COMCODE1(0x7fa12f8c9fc0)(:5)], [PRPLBPMCOM2_.COMCODE1(0x7fa12f8c9fc0)(:6)]), | | onetime_exprs_(nil), init_plan_idxs_(nil), use_batch=false | | 2 - output([PRPLBPMCOM2_.COMCODE1(0x7fa12f8c9fc0)], [WBUSINESSS4_.NODENAME(0x7fa12f969dc0)]), filter(nil) | | conds(nil), nl_params_([VVVVVVV1_.MAKECOM(0x7fa12f8681f0)(:13)]), use_batch=false | | 3 - output([PRPLBPMCOM2_.COMCODE1(0x7fa12f8c9fc0)], [VVVVVVV1_.MAKECOM(0x7fa12f8681f0)], [WBUSINESSS4_.NODENAME(0x7fa12f969dc0)]), filter(nil) | | conds(nil), nl_params_([WBUSINESSC0_.BUSINESSNO(0x7fa12f86af40)(:7)]), use_batch=false | | 4 - output([PRPLBPMCOM2_.COMCODE1(0x7fa12f8c9fc0)], [WBUSINESSC0_.BUSINESSNO(0x7fa12f86af40)], [WBUSINESSS4_.NODENAME(0x7fa12f969dc0)]), filter(nil) | | conds(nil), nl_params_([WBUSINESSC0_.TASKID(0x7fa12f861a80)(:9)]), use_batch=false | | 5 - output([WBUSINESSC0_.BUSINESSNO(0x7fa12f86af40)], [WBUSINESSC0_.TASKID(0x7fa12f861a80)], [WBUSINESSS4_.NODENAME(0x7fa12f969dc0)]), filter(nil) | | equal_conds([WBUSINESSC0_.NODEID(0x7fa12f860050) = WBUSINESSS4_.ID(0x7fa12f860340)(0x7fa12f85f900)]), other_conds(nil) | | 6 - output([WBUSINESSS4_.ID(0x7fa12f860340)], [WBUSINESSS4_.NODENAME(0x7fa12f969dc0)]), filter(nil) | | access([WBUSINESSS4_.ID(0x7fa12f860340)], [WBUSINESSS4_.NODENAME(0x7fa12f969dc0)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([WBUSINESSS4_.NODENAME(0x7fa12f969dc0)], [WBUSINESSS4_.ID(0x7fa12f860340)]), range(UNDERWRITEFOUR,MIN ; UNDERWRITEFOUR,MAX), (UNDERWRITEFIVE, | | MIN ; UNDERWRITEFIVE,MAX), (UNDERWRITESIX,MIN ; UNDERWRITESIX,MAX), (UNDERWRITESEVEN,MIN ; UNDERWRITESEVEN,MAX), (UNDERWRITETWO,MIN ; UNDERWRITETWO,MAX), | | (UNDERWRITETHREE,MIN ; UNDERWRITETHREE,MAX), (UNDERWRITEONE,MIN ; UNDERWRITEONE,MAX), | | range_cond([(T_OP_OR, WBUSINESSS4_.NODENAME(0x7fa12f969dc0) = cast('UNDERWRITEFOUR', VARCHAR2(1048576 ))(0x7fa12f97adc0)(0x7fa12f881d10), WBUSINESSS4_.NODENAME(0x7fa12f969dc0) | | = cast('UNDERWRITEFIVE', VARCHAR2(1048576 ))(0x7fa12f97bd80)(0x7fa12f889690), WBUSINESSS4_.NODENAME(0x7fa12f969dc0) = cast('UNDERWRITESIX', VARCHAR2(1048576 | | ))(0x7fa12f97cd40)(0x7fa12f890fe0), WBUSINESSS4_.NODENAME(0x7fa12f969dc0) = cast('UNDERWRITESEVEN', VARCHAR2(1048576 ))(0x7fa12f97dd00)(0x7fa12f898990), | | WBUSINESSS4_.NODENAME(0x7fa12f969dc0) = cast('UNDERWRITETWO', VARCHAR2(1048576 ))(0x7fa12f97ecc0)(0x7fa12f8a02e0), WBUSINESSS4_.NODENAME(0x7fa12f969dc0) | | = cast('UNDERWRITETHREE', VARCHAR2(1048576 ))(0x7fa12f97fc80)(0x7fa12f8a7c30), WBUSINESSS4_.NODENAME(0x7fa12f969dc0) = cast('UNDERWRITEONE', VARCHAR2(1048576 | | ))(0x7fa12f980c40)(0x7fa12f8af640))(0x7f6bda65f0a0)]) | | 7 - output([WBUSINESSC0_.NODEID(0x7fa12f860050)], [WBUSINESSC0_.TASKID(0x7fa12f861a80)], [WBUSINESSC0_.BUSINESSNO(0x7fa12f86af40)]), filter([cast(cast(WBUSINESSC0_.VALID(0x7fa12f863230), | | VARCHAR2(2 BYTE))(0x7fa12f863550), NUMBER(-1, -85))(0x7fa12f863f00) = 1(0x7fa12f862ae0)]) | | access([WBUSINESSC0_.ID(0x7f89bd543420)], [WBUSINESSC0_.NODEID(0x7fa12f860050)], [WBUSINESSC0_.TASKID(0x7fa12f861a80)], [WBUSINESSC0_.VALID(0x7fa12f863230)], | | [WBUSINESSC0_.BUSINESSNO(0x7fa12f86af40)]), partitions(p0) | | is_index_back=true, is_global_index=false, filter_before_indexback[false], | | range_key([WBUSINESSC0_.STATE(0x7fa12f876750)], [WBUSINESSC0_.RISKCODE(0x7fa12f969ad0)], [WBUSINESSC0_.INDATE(0x7fa12f85ac40)], [WBUSINESSC0_.ID(0x7f89bd543420)]), | | range(1,0105,2024-11-20 00:00:00,MIN ; 1,0105,2024-11-24 00:00:00,MAX), (1,0128,2024-11-20 00:00:00,MIN ; 1,0128,2024-11-24 00:00:00,MAX), (1,0103,2024-11-20 | | 00:00:00,MIN ; 1,0103,2024-11-24 00:00:00,MAX), (1,0101,2024-11-20 00:00:00,MIN ; 1,0101,2024-11-24 00:00:00,MAX), (1,0198,2024-11-20 00:00:00,MIN ; 1,0198, | | 2024-11-24 00:00:00,MAX), (1,0199,2024-11-20 00:00:00,MIN ; 1,0199,2024-11-24 00:00:00,MAX), (1,0109,2024-11-20 00:00:00,MIN ; 1,0109,2024-11-24 00:00:00, | | MAX), (1,0107,2024-11-20 00:00:00,MIN ; 1,0107,2024-11-24 00:00:00,MAX), (1,0108,2024-11-20 00:00:00,MIN ; 1,0108,2024-11-24 00:00:00,MAX), | | range_cond([WBUSINESSC0_.STATE(0x7fa12f876750) = cast('1', VARCHAR2(1048576 ))(0x7fa12f876d00)(0x7fa12f876000)], [WBUSINESSC0_.INDATE(0x7fa12f85ac40) | | >= TO_DATE(cast('2024-11-20 00:00:00', VARCHAR2(1048576 ))(0x7fa12f871a30), cast('YYYY-MM-DD HH24:MI:SS', VARCHAR2(1048576 ))(0x7fa12f872580))(0x7fa12f86dd30)(0x7fa12f8702b0)], | | [WBUSINESSC0_.INDATE(0x7fa12f85ac40) <= TO_DATE(cast('2024-11-24 00:00:00', VARCHAR2(1048576 ))(0x7fa12f873110), cast('YYYY-MM-DD HH24:MI:SS', VARCHAR2(1048576 | | ))(0x7fa12f872580))(0x7fa12f86ee60)(0x7fa12f8709b0)], [WBUSINESSC0_.RISKCODE(0x7fa12f969ad0) IN (cast('0105', VARCHAR2(4 BYTE))(0x7f89bd5574c0), cast('0128', | | VARCHAR2(4 BYTE))(0x7f89bd558010), cast('0103', VARCHAR2(4 BYTE))(0x7f89bd558b60), cast('0101', VARCHAR2(4 BYTE))(0x7f89bd5596b0), cast('0198', VARCHAR2(4 | | BYTE))(0x7f89bd55a200), cast('0199', VARCHAR2(4 BYTE))(0x7f89bd55ad50), cast('0109', VARCHAR2(4 BYTE))(0x7f89bd55b8a0), cast('0107', VARCHAR2(4 BYTE))(0x7f89bd55c3f0), | | cast('0108', VARCHAR2(4 BYTE))(0x7f89bd55cf40))(0x7f306f19ac80)(0x7fa12f87eeb0)]) | | 8 - output([PRPLBPMCOM2_.COMCODE1(0x7fa12f8c9fc0)]), filter(nil) | | access([PRPLBPMCOM2_.ID(0x7f89bd543700)], [PRPLBPMCOM2_.COMCODE1(0x7fa12f8c9fc0)]), partitions(p0) | | is_index_back=true, is_global_index=false, | | range_key([PRPLBPMCOM2_.BPMMAINID(0x7fa12f861d70)], [PRPLBPMCOM2_.ID(0x7f89bd543700)]), range(MIN ; MAX), | | range_cond([:9 = PRPLBPMCOM2_.BPMMAINID(0x7fa12f861d70)(0x7f7261665830)]) | | 9 - output([VVVVVVV1_.MAKECOM(0x7fa12f8681f0)]), filter([VVVVVVV1_.CLASSCODE(0x7fa12f86c6f0) = cast('01', VARCHAR2(1048576 ))(0x7fa12f86cca0)(0x7fa12f86bfa0)], | | [VVVVVVV1_.RISKCODE(0x7fa12f865f60) != cast('9999', VARCHAR2(1048576 ))(0x7fa12f866510)(0x7fa12f865810)]) | | access([VVVVVVV1_.RISKCODE(0x7fa12f865f60)], [VVVVVVV1_.MAKECOM(0x7fa12f8681f0)], [VVVVVVV1_.CLASSCODE(0x7fa12f86c6f0)]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false,false], | | range_key([VVVVVVV1_.PROPOSALNO(0x7fa12f86b230)]), range(MIN ; MAX), | | range_cond([:7 = VVVVVVV1_.PROPOSALNO(0x7fa12f86b230)(0x7f6bda7968a0)]) | | 10 - output(nil), filter(nil) | | access(nil), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([PRPDCOMPAN3_.COMCODE(0x7fa12f8684e0)]), range(MIN ; MAX), | | range_cond([:13 = PRPDCOMPAN3_.COMCODE(0x7fa12f8684e0)(0x7f72617c0800)]) | | 11 - output([1]), filter(nil) | | limit(1), offset(nil) | | 12 - output(nil), filter([:0 = VIEW1.B_.COMCODE(0x7fa12f9ad170)(0x7fa12f8c9870)]) | | access([VIEW1.B_.COMCODE(0x7fa12f9ad170)]) | | 13 - output([VIEW3.B_.COMCODE(0x7fa12f9d4630)], [VIEW3.B_.UPPERCOMCODE(0x7fa12f9d4910)]), filter(nil) | | conds([VIEW2.B_.COMCODE(0x7fa12f9d3d80) != VIEW3.B_.COMCODE(0x7fa12f9d4630)(0x7fa12f8c83b0)]), nl_params_([VIEW2.B_.COMCODE(0x7fa12f9d3d80)(:15)]), | | use_batch=false | | 14 - output([VIEW2.B_.COMCODE(0x7fa12f9d3d80)], [VIEW2.B_.UPPERCOMCODE(0x7fa12f9d4060)]), filter(nil) | | access([VIEW2.B_.COMCODE(0x7fa12f9d3d80)], [VIEW2.B_.UPPERCOMCODE(0x7fa12f9d4060)]) | | 15 - output([B_.COMCODE(0x7fa12f8c4100)], [B_.UPPERCOMCODE(0x7fa12f8c6a80)]), filter(nil) | | access([B_.COMCODE(0x7fa12f8c4100)], [B_.UPPERCOMCODE(0x7fa12f8c6a80)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([B_.COMCODE(0x7fa12f8c4100)]), range[00000000 ; 00000000], | | range_cond([B_.COMCODE(0x7fa12f8c4100) = cast('00000000', VARCHAR2(1048576 ))(0x7fa12f8c4670)(0x7fa12f8c3400)]) | | 16 - output([VIEW3.B_.COMCODE(0x7fa12f9d4630)], [VIEW3.B_.UPPERCOMCODE(0x7fa12f9d4910)]), filter(nil) | | access([VIEW3.B_.COMCODE(0x7fa12f9d4630)], [VIEW3.B_.UPPERCOMCODE(0x7fa12f9d4910)]) | | 17 - output([B_.COMCODE(0x7fa12f9d1e60)], [B_.UPPERCOMCODE(0x7fa12f9d2140)]), filter(nil) | | access([B_.COMCODE(0x7fa12f9d1e60)], [B_.UPPERCOMCODE(0x7fa12f9d2140)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([B_.UPPERCOMCODE(0x7fa12f9d2140)], [B_.COMCODE(0x7fa12f9d1e60)]), range(MIN,MIN ; MAX,MAX)always true, | | range_cond([:15 = B_.UPPERCOMCODE(0x7fa12f9d2140)(0x7f395d1e59c0)]) | | 18 - output([1]), filter(nil) | | limit(1), offset(nil) | | 19 - output(nil), filter([:1 = VIEW4.B_.COMCODE(0x7fa12f9e8250)(0x7fa12f8e3830)]) | | access([VIEW4.B_.COMCODE(0x7fa12f9e8250)]) | | 20 - output([VIEW6.B_.COMCODE(0x7f89bd418bc0)], [VIEW6.B_.UPPERCOMCODE(0x7f89bd418ea0)]), filter(nil) | | conds([VIEW5.B_.COMCODE(0x7f89bd418310) != VIEW6.B_.COMCODE(0x7f89bd418bc0)(0x7fa12f8e2370)]), nl_params_([VIEW5.B_.COMCODE(0x7f89bd418310)(:16)]), | | use_batch=false | | 21 - output([VIEW5.B_.COMCODE(0x7f89bd418310)], [VIEW5.B_.UPPERCOMCODE(0x7f89bd4185f0)]), filter(nil) | | access([VIEW5.B_.COMCODE(0x7f89bd418310)], [VIEW5.B_.UPPERCOMCODE(0x7f89bd4185f0)]) | | 22 - output([B_.COMCODE(0x7fa12f8de0c0)], [B_.UPPERCOMCODE(0x7fa12f8e0a40)]), filter(nil) | | access([B_.COMCODE(0x7fa12f8de0c0)], [B_.UPPERCOMCODE(0x7fa12f8e0a40)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([B_.COMCODE(0x7fa12f8de0c0)]), range[00000000 ; 00000000], | | range_cond([B_.COMCODE(0x7fa12f8de0c0) = cast('00000000', VARCHAR2(1048576 ))(0x7fa12f8de630)(0x7fa12f8dd3c0)]) | | 23 - output([VIEW6.B_.COMCODE(0x7f89bd418bc0)], [VIEW6.B_.UPPERCOMCODE(0x7f89bd418ea0)]), filter(nil) | | access([VIEW6.B_.COMCODE(0x7f89bd418bc0)], [VIEW6.B_.UPPERCOMCODE(0x7f89bd418ea0)]) | | 24 - output([B_.COMCODE(0x7f89bd4163f0)], [B_.UPPERCOMCODE(0x7f89bd4166d0)]), filter(nil) | | access([B_.COMCODE(0x7f89bd4163f0)], [B_.UPPERCOMCODE(0x7f89bd4166d0)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([B_.UPPERCOMCODE(0x7f89bd4166d0)], [B_.COMCODE(0x7f89bd4163f0)]), range(MIN,MIN ; MAX,MAX)always true, | | range_cond([:16 = B_.UPPERCOMCODE(0x7f89bd4166d0)(0x7f9b14d76e40)]) | | 25 - output([1]), filter(nil) | | limit(1), offset(nil) | | 26 - output(nil), filter([:2 = VIEW7.B_.COMCODE(0x7f89bd42c7e0)(0x7fa12f8fd510)]) | | access([VIEW7.B_.COMCODE(0x7f89bd42c7e0)]) | | 27 - output([VIEW9.B_.COMCODE(0x7f89bd453ca0)], [VIEW9.B_.UPPERCOMCODE(0x7f89bd453f80)]), filter(nil) | | conds([VIEW8.B_.COMCODE(0x7f89bd4533f0) != VIEW9.B_.COMCODE(0x7f89bd453ca0)(0x7fa12f8fc050)]), nl_params_([VIEW8.B_.COMCODE(0x7f89bd4533f0)(:17)]), | | use_batch=false | | 28 - output([VIEW8.B_.COMCODE(0x7f89bd4533f0)], [VIEW8.B_.UPPERCOMCODE(0x7f89bd4536d0)]), filter(nil) | | access([VIEW8.B_.COMCODE(0x7f89bd4533f0)], [VIEW8.B_.UPPERCOMCODE(0x7f89bd4536d0)]) | | 29 - output([B_.COMCODE(0x7fa12f8f7da0)], [B_.UPPERCOMCODE(0x7fa12f8fa720)]), filter(nil) | | access([B_.COMCODE(0x7fa12f8f7da0)], [B_.UPPERCOMCODE(0x7fa12f8fa720)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([B_.COMCODE(0x7fa12f8f7da0)]), range[00000000 ; 00000000], | | range_cond([B_.COMCODE(0x7fa12f8f7da0) = cast('00000000', VARCHAR2(1048576 ))(0x7fa12f8f8310)(0x7fa12f8f70a0)]) | | 30 - output([VIEW9.B_.COMCODE(0x7f89bd453ca0)], [VIEW9.B_.UPPERCOMCODE(0x7f89bd453f80)]), filter(nil) | | access([VIEW9.B_.COMCODE(0x7f89bd453ca0)], [VIEW9.B_.UPPERCOMCODE(0x7f89bd453f80)]) | | 31 - output([B_.COMCODE(0x7f89bd4514d0)], [B_.UPPERCOMCODE(0x7f89bd4517b0)]), filter(nil) | | access([B_.COMCODE(0x7f89bd4514d0)], [B_.UPPERCOMCODE(0x7f89bd4517b0)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([B_.UPPERCOMCODE(0x7f89bd4517b0)], [B_.COMCODE(0x7f89bd4514d0)]), range(MIN,MIN ; MAX,MAX)always true, | | range_cond([:17 = B_.UPPERCOMCODE(0x7f89bd4517b0)(0x7f9d04d144e0)]) | | 32 - output([1]), filter(nil) | | limit(1), offset(nil) | | 33 - output(nil), filter([:3 = VIEW10.B_.COMCODE(0x7f89bd4678c0)(0x7fa12f9171f0)]) | | access([VIEW10.B_.COMCODE(0x7f89bd4678c0)]) | | 34 - output([VIEW12.B_.COMCODE(0x7f89bd48ed80)], [VIEW12.B_.UPPERCOMCODE(0x7f89bd48f060)]), filter(nil) | | conds([VIEW11.B_.COMCODE(0x7f89bd48e4d0) != VIEW12.B_.COMCODE(0x7f89bd48ed80)(0x7fa12f915d30)]), nl_params_([VIEW11.B_.COMCODE(0x7f89bd48e4d0)(:18)]), | | use_batch=false | | 35 - output([VIEW11.B_.COMCODE(0x7f89bd48e4d0)], [VIEW11.B_.UPPERCOMCODE(0x7f89bd48e7b0)]), filter(nil) | | access([VIEW11.B_.COMCODE(0x7f89bd48e4d0)], [VIEW11.B_.UPPERCOMCODE(0x7f89bd48e7b0)]) | | 36 - output([B_.COMCODE(0x7fa12f911a80)], [B_.UPPERCOMCODE(0x7fa12f914400)]), filter(nil) | | access([B_.COMCODE(0x7fa12f911a80)], [B_.UPPERCOMCODE(0x7fa12f914400)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([B_.COMCODE(0x7fa12f911a80)]), range[00000000 ; 00000000], | | range_cond([B_.COMCODE(0x7fa12f911a80) = cast('00000000', VARCHAR2(1048576 ))(0x7fa12f911ff0)(0x7fa12f910d80)]) | | 37 - output([VIEW12.B_.COMCODE(0x7f89bd48ed80)], [VIEW12.B_.UPPERCOMCODE(0x7f89bd48f060)]), filter(nil) | | access([VIEW12.B_.COMCODE(0x7f89bd48ed80)], [VIEW12.B_.UPPERCOMCODE(0x7f89bd48f060)]) | | 38 - output([B_.COMCODE(0x7f89bd48c5b0)], [B_.UPPERCOMCODE(0x7f89bd48c890)]), filter(nil) | | access([B_.COMCODE(0x7f89bd48c5b0)], [B_.UPPERCOMCODE(0x7f89bd48c890)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([B_.UPPERCOMCODE(0x7f89bd48c890)], [B_.COMCODE(0x7f89bd48c5b0)]), range(MIN,MIN ; MAX,MAX)always true, | | range_cond([:18 = B_.UPPERCOMCODE(0x7f89bd48c890)(0x7f4206e9e0a0)]) | | 39 - output([1]), filter(nil) | | limit(1), offset(nil) | | 40 - output(nil), filter([:4 = VIEW13.B_.COMCODE(0x7f89bd4a29a0)(0x7fa12f930ed0)]) | | access([VIEW13.B_.COMCODE(0x7f89bd4a29a0)]) | | 41 - output([VIEW15.B_.COMCODE(0x7f89bd4c9e60)], [VIEW15.B_.UPPERCOMCODE(0x7f89bd4ca140)]), filter(nil) | | conds([VIEW14.B_.COMCODE(0x7f89bd4c95b0) != VIEW15.B_.COMCODE(0x7f89bd4c9e60)(0x7fa12f92fa10)]), nl_params_([VIEW14.B_.COMCODE(0x7f89bd4c95b0)(:19)]), | | use_batch=false | | 42 - output([VIEW14.B_.COMCODE(0x7f89bd4c95b0)], [VIEW14.B_.UPPERCOMCODE(0x7f89bd4c9890)]), filter(nil) | | access([VIEW14.B_.COMCODE(0x7f89bd4c95b0)], [VIEW14.B_.UPPERCOMCODE(0x7f89bd4c9890)]) | | 43 - output([B_.COMCODE(0x7fa12f92b760)], [B_.UPPERCOMCODE(0x7fa12f92e0e0)]), filter(nil) | | access([B_.COMCODE(0x7fa12f92b760)], [B_.UPPERCOMCODE(0x7fa12f92e0e0)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([B_.COMCODE(0x7fa12f92b760)]), range[00000000 ; 00000000], | | range_cond([B_.COMCODE(0x7fa12f92b760) = cast('00000000', VARCHAR2(1048576 ))(0x7fa12f92bcd0)(0x7fa12f92aa60)]) | | 44 - output([VIEW15.B_.COMCODE(0x7f89bd4c9e60)], [VIEW15.B_.UPPERCOMCODE(0x7f89bd4ca140)]), filter(nil) | | access([VIEW15.B_.COMCODE(0x7f89bd4c9e60)], [VIEW15.B_.UPPERCOMCODE(0x7f89bd4ca140)]) | | 45 - output([B_.COMCODE(0x7f89bd4c7690)], [B_.UPPERCOMCODE(0x7f89bd4c7970)]), filter(nil) | | access([B_.COMCODE(0x7f89bd4c7690)], [B_.UPPERCOMCODE(0x7f89bd4c7970)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([B_.UPPERCOMCODE(0x7f89bd4c7970)], [B_.COMCODE(0x7f89bd4c7690)]), range(MIN,MIN ; MAX,MAX)always true, | | range_cond([:19 = B_.UPPERCOMCODE(0x7f89bd4c7970)(0x7f522e216340)]) | | 46 - output([1]), filter(nil) | | limit(1), offset(nil) | | 47 - output(nil), filter([:5 = VIEW16.B_.COMCODE(0x7f89bd4df990)(0x7fa12f94cac0)]) | | access([VIEW16.B_.COMCODE(0x7f89bd4df990)]) | | 48 - output([VIEW18.B_.COMCODE(0x7f89bd506e50)], [VIEW18.B_.UPPERCOMCODE(0x7f89bd507130)]), filter(nil) | | conds([VIEW17.B_.COMCODE(0x7f89bd5065a0) != VIEW18.B_.COMCODE(0x7f89bd506e50)(0x7fa12f94b600)]), nl_params_([VIEW17.B_.COMCODE(0x7f89bd5065a0)(:20)]), | | use_batch=false | | 49 - output([VIEW17.B_.COMCODE(0x7f89bd5065a0)], [VIEW17.B_.UPPERCOMCODE(0x7f89bd506880)]), filter(nil) | | access([VIEW17.B_.COMCODE(0x7f89bd5065a0)], [VIEW17.B_.UPPERCOMCODE(0x7f89bd506880)]) | | 50 - output([B_.COMCODE(0x7fa12f947350)], [B_.UPPERCOMCODE(0x7fa12f949cd0)]), filter(nil) | | access([B_.COMCODE(0x7fa12f947350)], [B_.UPPERCOMCODE(0x7fa12f949cd0)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([B_.COMCODE(0x7fa12f947350)]), range[00000000 ; 00000000], | | range_cond([B_.COMCODE(0x7fa12f947350) = cast('00000000', VARCHAR2(1048576 ))(0x7fa12f9478c0)(0x7fa12f946650)]) | | 51 - output([VIEW18.B_.COMCODE(0x7f89bd506e50)], [VIEW18.B_.UPPERCOMCODE(0x7f89bd507130)]), filter(nil) | | access([VIEW18.B_.COMCODE(0x7f89bd506e50)], [VIEW18.B_.UPPERCOMCODE(0x7f89bd507130)]) | | 52 - output([B_.COMCODE(0x7f89bd504680)], [B_.UPPERCOMCODE(0x7f89bd504960)]), filter(nil) | | access([B_.COMCODE(0x7f89bd504680)], [B_.UPPERCOMCODE(0x7f89bd504960)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([B_.UPPERCOMCODE(0x7f89bd504960)], [B_.COMCODE(0x7f89bd504680)]), range(MIN,MIN ; MAX,MAX)always true, | | range_cond([:20 = B_.UPPERCOMCODE(0x7f89bd504960)(0x7f522e38b950)]) | | 53 - output([1]), filter(nil) | | limit(1), offset(nil) | | 54 - output(nil), filter([:6 = VIEW19.B_.COMCODE(0x7f89bd51aa70)(0x7fa12f9686b0)]) | | access([VIEW19.B_.COMCODE(0x7f89bd51aa70)]) | | 55 - output([VIEW21.B_.COMCODE(0x7f89bd541f30)], [VIEW21.B_.UPPERCOMCODE(0x7f89bd542210)]), filter(nil) | | conds([VIEW20.B_.COMCODE(0x7f89bd541680) != VIEW21.B_.COMCODE(0x7f89bd541f30)(0x7fa12f9671f0)]), nl_params_([VIEW20.B_.COMCODE(0x7f89bd541680)(:21)]), | | use_batch=false | | 56 - output([VIEW20.B_.COMCODE(0x7f89bd541680)], [VIEW20.B_.UPPERCOMCODE(0x7f89bd541960)]), filter(nil) | | access([VIEW20.B_.COMCODE(0x7f89bd541680)], [VIEW20.B_.UPPERCOMCODE(0x7f89bd541960)]) | | 57 - output([B_.COMCODE(0x7fa12f962f40)], [B_.UPPERCOMCODE(0x7fa12f9658c0)]), filter(nil) | | access([B_.COMCODE(0x7fa12f962f40)], [B_.UPPERCOMCODE(0x7fa12f9658c0)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([B_.COMCODE(0x7fa12f962f40)]), range[00000000 ; 00000000], | | range_cond([B_.COMCODE(0x7fa12f962f40) = cast('00000000', VARCHAR2(1048576 ))(0x7fa12f9634b0)(0x7fa12f962240)]) | | 58 - output([VIEW21.B_.COMCODE(0x7f89bd541f30)], [VIEW21.B_.UPPERCOMCODE(0x7f89bd542210)]), filter(nil) | | access([VIEW21.B_.COMCODE(0x7f89bd541f30)], [VIEW21.B_.UPPERCOMCODE(0x7f89bd542210)]) | | 59 - output([B_.COMCODE(0x7f89bd53f760)], [B_.UPPERCOMCODE(0x7f89bd53fa40)]), filter(nil) | | access([B_.COMCODE(0x7f89bd53f760)], [B_.UPPERCOMCODE(0x7f89bd53fa40)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([B_.UPPERCOMCODE(0x7f89bd53fa40)], [B_.COMCODE(0x7f89bd53f760)]), range(MIN,MIN ; MAX,MAX)always true, | | range_cond([:21 = B_.UPPERCOMCODE(0x7f89bd53fa40)(0x7f50af3144e0)]) | | Used Hint: | | ------------------------------------- | | /*+ | | | | */ | | Qb name trace: | | ------------------------------------- | | stmt_id:0, stmt_type:T_EXPLAIN | | stmt_id:1, SEL$1 > SEL$F8C4A4E7 > SEL$6A0485E1 > SEL$2E4A359C | | stmt_id:2, SEL$2 | | stmt_id:3, SEL$3 | | stmt_id:4, SEL$4 | | stmt_id:5, SEL$5 | | stmt_id:6, SEL$6 | | stmt_id:7, SEL$7 | | stmt_id:8, SEL$8 | | stmt_id:9, parent:SEL$2 > SEL$9B6BAA9A | | stmt_id:10, parent:SEL$2 > SEL$9B6BAA9B | | stmt_id:11, parent:SEL$9B6BAA9B > SEL$E382C6D8_1 | | stmt_id:12, parent:SEL$3 > SEL$B648BD05 | | stmt_id:13, parent:SEL$3 > SEL$B648BD06 | | stmt_id:14, parent:SEL$B648BD06 > SEL$8174842E_1 | | stmt_id:15, parent:SEL$4 > SEL$BE9AA69A | | stmt_id:16, parent:SEL$4 > SEL$BE9AA69B | | stmt_id:17, parent:SEL$BE9AA69B > SEL$1CD07649_1 | | stmt_id:18, parent:SEL$5 > SEL$CAF5870C | | stmt_id:19, parent:SEL$5 > SEL$CAF5870D | | stmt_id:20, parent:SEL$CAF5870D > SEL$9DB3BA64_1 | | stmt_id:21, parent:SEL$6 > SEL$815A45AE | | stmt_id:22, parent:SEL$6 > SEL$815A45AF | | stmt_id:23, parent:SEL$815A45AF > SEL$40EB5C3B_1 | | stmt_id:24, parent:SEL$7 > SEL$F6CF69DE | | stmt_id:25, parent:SEL$7 > SEL$F6CF69DF | | stmt_id:26, parent:SEL$F6CF69DF > SEL$DC9DA216_1 | | stmt_id:27, parent:SEL$8 > SEL$2B018010 | | stmt_id:28, parent:SEL$8 > SEL$2B018011 | | stmt_id:29, parent:SEL$2B018011 > SEL$3F38A740_1 | | Outline Data: | | ------------------------------------- | | /*+ | | BEGIN_OUTLINE_DATA | | LEADING(@"SEL$2E4A359C" (((("BUSIUSER"."WBUSINESSS4_"@"SEL$1" "BUSIUSER"."WBUSINESSC0_"@"SEL$1") "BUSIUSER"."PRPLBPMCOM2_"@"SEL$1") "BUSIUSER"."VVVVVVV1_"@"SEL$1") "BUSIUSER"."PRPDCOMPAN3_"@"SEL$1")) | | USE_NL(@"SEL$2E4A359C" "BUSIUSER"."PRPDCOMPAN3_"@"SEL$1") | | USE_NL(@"SEL$2E4A359C" "BUSIUSER"."VVVVVVV1_"@"SEL$1") | | USE_NL(@"SEL$2E4A359C" "BUSIUSER"."PRPLBPMCOM2_"@"SEL$1") | | USE_HASH(@"SEL$2E4A359C" "BUSIUSER"."WBUSINESSC0_"@"SEL$1") | | INDEX(@"SEL$2E4A359C" "WBUSINESSS4_"@"SEL$1" "IDX_YUYUYU") | | INDEX(@"SEL$2E4A359C" "WBUSINESSC0_"@"SEL$1" "IDX_VVVVVVV_STATE1") | | INDEX(@"SEL$2E4A359C" "PRPLBPMCOM2_"@"SEL$1" "IDX_GGDFFF_BPMMAINID") | | USE_DAS(@"SEL$2E4A359C" "PRPLBPMCOM2_"@"SEL$1") | | FULL(@"SEL$2E4A359C" "VVVVVVV1_"@"SEL$1") | | USE_DAS(@"SEL$2E4A359C" "VVVVVVV1_"@"SEL$1") | | FULL(@"SEL$2E4A359C" "PRPDCOMPAN3_"@"SEL$1") | | USE_DAS(@"SEL$2E4A359C" "PRPDCOMPAN3_"@"SEL$1") | | LEADING(@"SEL$9B6BAA9A" ("VIEW2"@"SEL$9B6BAA9A" "VIEW3"@"SEL$9B6BAA9A")) | | USE_NL(@"SEL$9B6BAA9A" "VIEW3"@"SEL$9B6BAA9A") | | FULL(@"SEL$9B6BAA9B" "B_"@"SEL$2") | | INDEX(@"SEL$E382C6D8_1" "B_"@"SEL$2" "IDX_COMPANY_UPPERCOMCODE") | | USE_DAS(@"SEL$E382C6D8_1" "B_"@"SEL$2") | | LEADING(@"SEL$B648BD05" ("VIEW5"@"SEL$B648BD05" "VIEW6"@"SEL$B648BD05")) | | USE_NL(@"SEL$B648BD05" "VIEW6"@"SEL$B648BD05") | | FULL(@"SEL$B648BD06" "B_"@"SEL$3") | | INDEX(@"SEL$8174842E_1" "B_"@"SEL$3" "IDX_COMPANY_UPPERCOMCODE") | | USE_DAS(@"SEL$8174842E_1" "B_"@"SEL$3") | | LEADING(@"SEL$BE9AA69A" ("VIEW8"@"SEL$BE9AA69A" "VIEW9"@"SEL$BE9AA69A")) | | USE_NL(@"SEL$BE9AA69A" "VIEW9"@"SEL$BE9AA69A") | | FULL(@"SEL$BE9AA69B" "B_"@"SEL$4") | | INDEX(@"SEL$1CD07649_1" "B_"@"SEL$4" "IDX_COMPANY_UPPERCOMCODE") | | USE_DAS(@"SEL$1CD07649_1" "B_"@"SEL$4") | | LEADING(@"SEL$CAF5870C" ("VIEW11"@"SEL$CAF5870C" "VIEW12"@"SEL$CAF5870C")) | | USE_NL(@"SEL$CAF5870C" "VIEW12"@"SEL$CAF5870C") | | FULL(@"SEL$CAF5870D" "B_"@"SEL$5") | | INDEX(@"SEL$9DB3BA64_1" "B_"@"SEL$5" "IDX_COMPANY_UPPERCOMCODE") | | USE_DAS(@"SEL$9DB3BA64_1" "B_"@"SEL$5") | | LEADING(@"SEL$815A45AE" ("VIEW14"@"SEL$815A45AE" "VIEW15"@"SEL$815A45AE")) | | USE_NL(@"SEL$815A45AE" "VIEW15"@"SEL$815A45AE") | | FULL(@"SEL$815A45AF" "B_"@"SEL$6") | | INDEX(@"SEL$40EB5C3B_1" "B_"@"SEL$6" "IDX_COMPANY_UPPERCOMCODE") | | USE_DAS(@"SEL$40EB5C3B_1" "B_"@"SEL$6") | | LEADING(@"SEL$F6CF69DE" ("VIEW17"@"SEL$F6CF69DE" "VIEW18"@"SEL$F6CF69DE")) | | USE_NL(@"SEL$F6CF69DE" "VIEW18"@"SEL$F6CF69DE") | | FULL(@"SEL$F6CF69DF" "B_"@"SEL$7") | | INDEX(@"SEL$DC9DA216_1" "B_"@"SEL$7" "IDX_COMPANY_UPPERCOMCODE") | | USE_DAS(@"SEL$DC9DA216_1" "B_"@"SEL$7") | | LEADING(@"SEL$2B018010" ("VIEW20"@"SEL$2B018010" "VIEW21"@"SEL$2B018010")) | | USE_NL(@"SEL$2B018010" "VIEW21"@"SEL$2B018010") | | FULL(@"SEL$2B018011" "B_"@"SEL$8") | | INDEX(@"SEL$3F38A740_1" "B_"@"SEL$8" "IDX_COMPANY_UPPERCOMCODE") | | USE_DAS(@"SEL$3F38A740_1" "B_"@"SEL$8") | | SIMPLIFY_EXPR(@"SEL$1") | | SIMPLIFY_SUBQUERY(@"SEL$F8C4A4E7") | | PRED_DEDUCE(@"SEL$6A0485E1") | | OPTIMIZER_FEATURES_ENABLE('4.2.1.7') | | END_OUTLINE_DATA | | */ | | Optimization Info: | | ------------------------------------- | | WBUSINESSS4_: | | table_rows:129 | | physical_range_rows:1 | | logical_range_rows:1 | | index_back_rows:0 | | output_rows:1 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[IDX_YUYUYU, YUYUYU] | | unstable_index_name:[YUYUYU] | | stats version:1731691135620165 | | dynamic sampling level:0 | | WBUSINESSC0_: | | table_rows:56458998 | | physical_range_rows:20966 | | logical_range_rows:20966 | | index_back_rows:20966 | | output_rows:104 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[IDX_VVVVVVV_STATE1, IDX_GGGGGGG_1, IDX_GGGGGGG_MAINNO, IND_ID_MAINNO_RCODE_INDATE, MAINNO_RCODE_INDATE, IDX_LBPMMAIN_USERCODE, IDX_LBPMMAINTASKID, IDX_GGGGGGG_USERCODE, IDX_LBPMMAINPROCESSID, IDX_LBPMMAINBUSINESSNO, IDX_LBPMMAINBUSINESSID, IDX_GGGGGGG_PREPTASKID, IDX_GGGGGGG_INDATE, IDX_GGGGGGG_NODE, IDX_LBPMMAIN_INDATENODEID, IDX_GGGGGGG_UPDATEDDATE, IDX_LBPMMAIN_TOCHARINDATE, GGGGGGG] | | pruned_index_name:[IDX_GGGGGGG_1, IDX_GGGGGGG_MAINNO, IND_ID_MAINNO_RCODE_INDATE, MAINNO_RCODE_INDATE, IDX_LBPMMAIN_USERCODE, IDX_GGGGGGG_USERCODE, IDX_LBPMMAINPROCESSID, IDX_LBPMMAINBUSINESSID, IDX_GGGGGGG_PREPTASKID, IDX_GGGGGGG_INDATE, IDX_LBPMMAIN_INDATENODEID, IDX_GGGGGGG_UPDATEDDATE, IDX_LBPMMAIN_TOCHARINDATE] | | stats version:1732198831019172 | | dynamic sampling level:0 | | PRPLBPMCOM2_: | | table_rows:56130216 | | physical_range_rows:1 | | logical_range_rows:1 | | index_back_rows:1 | | output_rows:1 | | table_dop:1 | | dop_method:DAS DOP | | avaiable_index_name:[IDX_GGDFFF_COMCODE, IDX_GGDFFF_BPMMAINID, GGDFFF] | | pruned_index_name:[IDX_GGDFFF_COMCODE] | | unstable_index_name:[GGDFFF] | | stats version:1731710769077342 | | dynamic sampling level:0 | | VVVVVVV1_: | | table_rows:23309582 | | physical_range_rows:1 | | logical_range_rows:1 | | index_back_rows:0 | | output_rows:0 | | table_dop:1 | | dop_method:DAS DOP | | avaiable_index_name:[IDX_VVVVVVV_ENDDATE, IDX_VVVVVVV_HANDLER1CODE, IDX_VVVVVVV_HANDLERCODE, IDX_VVVVVVV_COMCODE2, IDX_VVVVVVV_CONTRACTNO, IDX_VVVVVVV_STARTDATE, IDX_VVVVVVV_RISKCODE, IDX_VVVVVVV_RISKCODE1, IDX_VVVVVVV_INPUTDATE, IDX_VVVVVVV_INSUREDCODE, IDX_VVVVVVV_OPERATORCODE, IDX_VVVVVVV_APPLICODE, IDX_VVVVVVV_COMCODE, IDX_VVVVVVV_COMCODE1, IDX_VVVVVVV_APPLINAME, IDX_VVVVVVV_INSUREDNAME, IDX_VVVVVVV_UNDERWRITEFLAG, IDX_VVVVVVV_UNDERWRITEENDDATE, IDX_VVVVVVV_MAKECOM, IDX_VVVVVVV_UPDATEDDATE, IDX_VVVVVVV_01, IDX_VVVVVVV_02, IDX_VVVVVVV_UNDERWRITEDATE, IDX_VVVVVVV_OPERATORCODE2, VVVVVVV] | | pruned_index_name:[IDX_VVVVVVV_ENDDATE, IDX_VVVVVVV_HANDLER1CODE, IDX_VVVVVVV_HANDLERCODE, IDX_VVVVVVV_COMCODE2, IDX_VVVVVVV_CONTRACTNO, IDX_VVVVVVV_STARTDATE, IDX_VVVVVVV_RISKCODE, IDX_VVVVVVV_RISKCODE1, IDX_VVVVVVV_INPUTDATE, IDX_VVVVVVV_INSUREDCODE, IDX_VVVVVVV_OPERATORCODE, IDX_VVVVVVV_APPLICODE, IDX_VVVVVVV_COMCODE, IDX_VVVVVVV_COMCODE1, IDX_VVVVVVV_APPLINAME, IDX_VVVVVVV_INSUREDNAME, IDX_VVVVVVV_UNDERWRITEFLAG, IDX_VVVVVVV_UNDERWRITEENDDATE, IDX_VVVVVVV_MAKECOM, IDX_VVVVVVV_UPDATEDDATE, IDX_VVVVVVV_01, IDX_VVVVVVV_02, IDX_VVVVVVV_UNDERWRITEDATE, IDX_VVVVVVV_OPERATORCODE2] | | stats version:1731719565265749 | | dynamic sampling level:0 | | PRPDCOMPAN3_: | | table_rows:3598 | | physical_range_rows:1 | | logical_range_rows:1 | | index_back_rows:0 | | output_rows:1 | | table_dop:1 | | dop_method:DAS DOP | | avaiable_index_name:[IDX_COMPANY_UPPERCOMCODE, IDX_SDQQQQQSGZ01, SDQQQQQ] | | pruned_index_name:[IDX_COMPANY_UPPERCOMCODE, IDX_SDQQQQQSGZ01] | | stats version:1732319051780078 | | dynamic sampling level:0 | | B_: | | table_rows:3598 | | physical_range_rows:1 | | logical_range_rows:1 | | index_back_rows:0 | | output_rows:1 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[IDX_COMPANY_UPPERCOMCODE, IDX_SDQQQQQSGZ01, SDQQQQQ] | | pruned_index_name:[IDX_COMPANY_UPPERCOMCODE, IDX_SDQQQQQSGZ01] | | stats version:1732319051780078 | | dynamic sampling level:0 | | B_: | | table_rows:3598 | | physical_range_rows:16 | | logical_range_rows:16 | | index_back_rows:0 | | output_rows:16 | | table_dop:1 | | dop_method:DAS DOP | | avaiable_index_name:[IDX_COMPANY_UPPERCOMCODE, IDX_SDQQQQQSGZ01, SDQQQQQ] | | pruned_index_name:[SDQQQQQ] | | stats version:1732319051780078 | | dynamic sampling level:0 | | B_: | | table_rows:3598 | | physical_range_rows:1 | | logical_range_rows:1 | | index_back_rows:0 | | output_rows:1 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[IDX_COMPANY_UPPERCOMCODE, IDX_SDQQQQQSGZ01, SDQQQQQ] | | pruned_index_name:[IDX_COMPANY_UPPERCOMCODE, IDX_SDQQQQQSGZ01] | | stats version:1732319051780078 | | dynamic sampling level:0 | | B_: | | table_rows:3598 | | physical_range_rows:16 | | logical_range_rows:16 | | index_back_rows:0 | | output_rows:16 | | table_dop:1 | | dop_method:DAS DOP | | avaiable_index_name:[IDX_COMPANY_UPPERCOMCODE, IDX_SDQQQQQSGZ01, SDQQQQQ] | | pruned_index_name:[SDQQQQQ] | | stats version:1732319051780078 | | dynamic sampling level:0 | | B_: | | table_rows:3598 | | physical_range_rows:1 | | logical_range_rows:1 | | index_back_rows:0 | | output_rows:1 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[IDX_COMPANY_UPPERCOMCODE, IDX_SDQQQQQSGZ01, SDQQQQQ] | | pruned_index_name:[IDX_COMPANY_UPPERCOMCODE, IDX_SDQQQQQSGZ01] | | stats version:1732319051780078 | | dynamic sampling level:0 | | B_: | | table_rows:3598 | | physical_range_rows:16 | | logical_range_rows:16 | | index_back_rows:0 | | output_rows:16 | | table_dop:1 | | dop_method:DAS DOP | | avaiable_index_name:[IDX_COMPANY_UPPERCOMCODE, IDX_SDQQQQQSGZ01, SDQQQQQ] | | pruned_index_name:[SDQQQQQ] | | stats version:1732319051780078 | | dynamic sampling level:0 | | B_: | | table_rows:3598 | | physical_range_rows:1 | | logical_range_rows:1 | | index_back_rows:0 | | output_rows:1 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[IDX_COMPANY_UPPERCOMCODE, IDX_SDQQQQQSGZ01, SDQQQQQ] | | pruned_index_name:[IDX_COMPANY_UPPERCOMCODE, IDX_SDQQQQQSGZ01] | | stats version:1732319051780078 | | dynamic sampling level:0 | | B_: | | table_rows:3598 | | physical_range_rows:16 | | logical_range_rows:16 | | index_back_rows:0 | | output_rows:16 | | table_dop:1 | | dop_method:DAS DOP | | avaiable_index_name:[IDX_COMPANY_UPPERCOMCODE, IDX_SDQQQQQSGZ01, SDQQQQQ] | | pruned_index_name:[SDQQQQQ] | | stats version:1732319051780078 | | dynamic sampling level:0 | | B_: | | table_rows:3598 | | physical_range_rows:1 | | logical_range_rows:1 | | index_back_rows:0 | | output_rows:1 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[IDX_COMPANY_UPPERCOMCODE, IDX_SDQQQQQSGZ01, SDQQQQQ] | | pruned_index_name:[IDX_COMPANY_UPPERCOMCODE, IDX_SDQQQQQSGZ01] | | stats version:1732319051780078 | | dynamic sampling level:0 | | B_: | | table_rows:3598 | | physical_range_rows:16 | | logical_range_rows:16 | | index_back_rows:0 | | output_rows:16 | | table_dop:1 | | dop_method:DAS DOP | | avaiable_index_name:[IDX_COMPANY_UPPERCOMCODE, IDX_SDQQQQQSGZ01, SDQQQQQ] | | pruned_index_name:[SDQQQQQ] | | stats version:1732319051780078 | | dynamic sampling level:0 | | B_: | | table_rows:3598 | | physical_range_rows:1 | | logical_range_rows:1 | | index_back_rows:0 | | output_rows:1 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[IDX_COMPANY_UPPERCOMCODE, IDX_SDQQQQQSGZ01, SDQQQQQ] | | pruned_index_name:[IDX_COMPANY_UPPERCOMCODE, IDX_SDQQQQQSGZ01] | | stats version:1732319051780078 | | dynamic sampling level:0 | | B_: | | table_rows:3598 | | physical_range_rows:16 | | logical_range_rows:16 | | index_back_rows:0 | | output_rows:16 | | table_dop:1 | | dop_method:DAS DOP | | avaiable_index_name:[IDX_COMPANY_UPPERCOMCODE, IDX_SDQQQQQSGZ01, SDQQQQQ] | | pruned_index_name:[SDQQQQQ] | | stats version:1732319051780078 | | dynamic sampling level:0 | | B_: | | table_rows:3598 | | physical_range_rows:1 | | logical_range_rows:1 | | index_back_rows:0 | | output_rows:1 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[IDX_COMPANY_UPPERCOMCODE, IDX_SDQQQQQSGZ01, SDQQQQQ] | | pruned_index_name:[IDX_COMPANY_UPPERCOMCODE, IDX_SDQQQQQSGZ01] | | stats version:1732319051780078 | | dynamic sampling level:0 | | B_: | | table_rows:3598 | | physical_range_rows:16 | | logical_range_rows:16 | | index_back_rows:0 | | output_rows:16 | | table_dop:1 | | dop_method:DAS DOP | | avaiable_index_name:[IDX_COMPANY_UPPERCOMCODE, IDX_SDQQQQQSGZ01, SDQQQQQ] | | pruned_index_name:[SDQQQQQ] | | stats version:1732319051780078 | | dynamic sampling level:0 | | Plan Type: | | LOCAL | | Note: | | Degree of Parallelisim is 1 because of table property | | Expr Constraints: | | 1 = 1 result is TRUE | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 611 rows in set (0.762 sec)
慢SQL涉及表的資料量：
+----------+ | COUNT(1) | +----------+ | 56486711 | GGGGGGG | 23395268 | VVVVVVV | 56300838 | GGDFFF | 3598 | SDQQQQQ | 129 | YUYUYU +----------+
這個SQL跑不出來，OCP預計跑3600秒，ORACLE 很快，1s左右出結果。
ORACLE 返回時間和資料量：
-- ORACLE 執行時間 COL_0_0_ ---------- 0 Elapsed: 00:00:01.73
OB慢SQL等價改寫：
obclient [UTIC_BUSIAPP]> SELECT -> COUNT(*) AS COL_0_0_ -> FROM -> GGGGGGG WBUSINESSC0_, -> VVVVVVV VVVVVVV1_, -> GGDFFF PRPLBPMCOM2_, -> SDQQQQQ PRPDCOMPAN3_, -> YUYUYU WBUSINESSS4_ -> WHERE -> WBUSINESSC0_.NODEID=WBUSINESSS4_.ID -> AND WBUSINESSC0_.TASKID=PRPLBPMCOM2_.BPMMAINID -> AND WBUSINESSC0_.VALID=1 -> AND VVVVVVV1_.RISKCODE<>'9999' -> AND VVVVVVV1_.MAKECOM=PRPDCOMPAN3_.COMCODE -> AND VVVVVVV1_.MAKECOM=PRPDCOMPAN3_.COMCODE -> AND WBUSINESSC0_.BUSINESSNO=VVVVVVV1_.PROPOSALNO -> AND VVVVVVV1_.CLASSCODE=('01') -> AND -> ( -> WBUSINESSC0_.INDATE BETWEEN (TO_DATE('2024-11-20 00:00:00','YYYY-MM-DD HH24:MI:SS')) AND -> ( -> TO_DATE('2024-11-24 00:00:00','YYYY-MM-DD HH24:MI:SS'))) -> AND WBUSINESSC0_.STATE=('1') -> AND -> ( -> EXISTS -> ( SELECT -> 1 -> FROM -> SDQQQQQ B_ -> WHERE -> PRPLBPMCOM2_.COMCODE1=B_.COMCODE START WITH B_.COMCODE IN ('00000000') CONNECT BY -> PRIOR B_.COMCODE= B_.UPPERCOMCODE -> AND PRIOR B_.COMCODE <> B_.COMCODE) -> AND 1=1 -> AND -> ( -> WBUSINESSC0_.RISKCODE IN ('0105' , -> '0128' , -> '0103' , -> '0101' , -> '0198' , -> '0199' , -> '0109' , -> '0107' , -> '0108')) -> AND WBUSINESSS4_.NODENAME in ('UNDERWRITEFOUR','UNDERWRITEFIVE','UNDERWRITESIX','UNDERWRITESEVEN','UNDERWRITETWO','UNDERWRITETHREE','UNDERWRITEONE')); +----------+ | COL_0_0_ | +----------+ | 0 | +----------+ 1 row in set (0.002 sec)
改寫後執行計劃：
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ================================================================================================================ | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ---------------------------------------------------------------------------------------------------------------- | | |0 |SCALAR GROUP BY | |1 |93421 | | | |1 |└─NESTED-LOOP JOIN | |1 |93421 | | | |2 | ├─NESTED-LOOP JOIN | |1 |93403 | | | |3 | │ ├─HASH SEMI JOIN | |1 |93385 | | | |4 | │ │ ├─NESTED-LOOP JOIN | |2 |93353 | | | |5 | │ │ │ ├─HASH JOIN | |2 |93310 | | | |6 | │ │ │ │ ├─TABLE RANGE SCAN |WBUSINESSS4_(IDX_YUYUYU) |1 |17 | | | |7 | │ │ │ │ └─TABLE RANGE SCAN |WBUSINESSC0_(IDX_VVVVVVV_STATE1) |105 |93283 | | | |8 | │ │ │ └─DISTRIBUTED TABLE RANGE SCAN |PRPLBPMCOM2_(IDX_GGDFFF_BPMMAINID)|1 |27 | | | |9 | │ │ └─SUBPLAN SCAN |VIEW1 |18 |27 | | | |10| │ │ └─NESTED-LOOP CONNECT BY | |18 |27 | | | |11| │ │ ├─SUBPLAN SCAN |VIEW2 |1 |5 | | | |12| │ │ │ └─TABLE GET |B_ |1 |5 | | | |13| │ │ └─SUBPLAN SCAN |VIEW3 |17 |21 | | | |14| │ │ └─DISTRIBUTED TABLE RANGE SCAN|B_(IDX_COMPANY_UPPERCOMCODE) |17 |21 | | | |15| │ └─DISTRIBUTED TABLE GET |VVVVVVV1_ |1 |18 | | | |16| └─DISTRIBUTED TABLE GET |PRPDCOMPAN3_ |1 |18 | | | ================================================================================================================ | | Outputs & filters: | | ------------------------------------- | | 0 - output([T_FUN_COUNT(*)(0x7f2f76e820e0)]), filter(nil) | | group(nil), agg_func([T_FUN_COUNT(*)(0x7f2f76e820e0)]) | | 1 - output(nil), filter(nil) | | conds(nil), nl_params_([VVVVVVV1_.MAKECOM(0x7f2f76e403a0)(:7)]), use_batch=true | | 2 - output([VVVVVVV1_.MAKECOM(0x7f2f76e403a0)]), filter(nil) | | conds(nil), nl_params_([WBUSINESSC0_.BUSINESSNO(0x7f2f76e430f0)(:1)]), use_batch=true | | 3 - output([WBUSINESSC0_.BUSINESSNO(0x7f2f76e430f0)]), filter(nil) | | equal_conds([PRPLBPMCOM2_.COMCODE1(0x7f2f76e6bd40) = VIEW1.B_.COMCODE(0x7f2f76ea2f90)(0x7f2f76f26cf0)]), other_conds(nil) | | 4 - output([WBUSINESSC0_.BUSINESSNO(0x7f2f76e430f0)], [PRPLBPMCOM2_.COMCODE1(0x7f2f76e6bd40)]), filter(nil) | | conds(nil), nl_params_([WBUSINESSC0_.TASKID(0x7f2f76e39c30)(:3)]), use_batch=false | | 5 - output([WBUSINESSC0_.BUSINESSNO(0x7f2f76e430f0)], [WBUSINESSC0_.TASKID(0x7f2f76e39c30)]), filter(nil) | | equal_conds([WBUSINESSC0_.NODEID(0x7f2f76e38200) = WBUSINESSS4_.ID(0x7f2f76e384f0)(0x7f2f76e37ab0)]), other_conds(nil) | | 6 - output([WBUSINESSS4_.ID(0x7f2f76e384f0)]), filter(nil) | | access([WBUSINESSS4_.ID(0x7f2f76e384f0)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([WBUSINESSS4_.NODENAME(0x7f2f76e79aa0)], [WBUSINESSS4_.ID(0x7f2f76e384f0)]), range(UNDERWRITEFOUR,MIN ; UNDERWRITEFOUR,MAX), (UNDERWRITEFIVE, | | MIN ; UNDERWRITEFIVE,MAX), (UNDERWRITESIX,MIN ; UNDERWRITESIX,MAX), (UNDERWRITESEVEN,MIN ; UNDERWRITESEVEN,MAX), (UNDERWRITETWO,MIN ; UNDERWRITETWO,MAX), | | (UNDERWRITETHREE,MIN ; UNDERWRITETHREE,MAX), (UNDERWRITEONE,MIN ; UNDERWRITEONE,MAX), | | range_cond([WBUSINESSS4_.NODENAME(0x7f2f76e79aa0) IN (cast('UNDERWRITEFOUR', VARCHAR2(14 BYTE))(0x7f2f76eeb9e0), cast('UNDERWRITEFIVE', VARCHAR2(14 | | BYTE))(0x7f2f76eec530), cast('UNDERWRITESIX', VARCHAR2(13 BYTE))(0x7f2f76eed080), cast('UNDERWRITESEVEN', VARCHAR2(15 BYTE))(0x7f2f76eedbd0), cast('UNDERWRITETWO', | | VARCHAR2(13 BYTE))(0x7f2f76eee720), cast('UNDERWRITETHREE', VARCHAR2(15 BYTE))(0x7f2f76eef270), cast('UNDERWRITEONE', VARCHAR2(13 BYTE))(0x7f2f76eefdc0))(0x7f455d9b0540)(0x7f2f76e77530)]) | | 7 - output([WBUSINESSC0_.NODEID(0x7f2f76e38200)], [WBUSINESSC0_.TASKID(0x7f2f76e39c30)], [WBUSINESSC0_.BUSINESSNO(0x7f2f76e430f0)]), filter([cast(cast(WBUSINESSC0_.VALID(0x7f2f76e3b3e0), | | VARCHAR2(2 BYTE))(0x7f2f76e3b700), NUMBER(-1, -85))(0x7f2f76e3c0b0) = 1(0x7f2f76e3ac90)]) | | access([WBUSINESSC0_.ID(0x7f2f76ecb940)], [WBUSINESSC0_.NODEID(0x7f2f76e38200)], [WBUSINESSC0_.TASKID(0x7f2f76e39c30)], [WBUSINESSC0_.VALID(0x7f2f76e3b3e0)], | | [WBUSINESSC0_.BUSINESSNO(0x7f2f76e430f0)]), partitions(p0) | | is_index_back=true, is_global_index=false, filter_before_indexback[false], | | range_key([WBUSINESSC0_.STATE(0x7f2f76e4e900)], [WBUSINESSC0_.RISKCODE(0x7f2f76e70ad0)], [WBUSINESSC0_.INDATE(0x7f2f76e32df0)], [WBUSINESSC0_.ID(0x7f2f76ecb940)]), | | range(1,0105,2024-11-20 00:00:00,MIN ; 1,0105,2024-11-24 00:00:00,MAX), (1,0128,2024-11-20 00:00:00,MIN ; 1,0128,2024-11-24 00:00:00,MAX), (1,0103,2024-11-20 | | 00:00:00,MIN ; 1,0103,2024-11-24 00:00:00,MAX), (1,0101,2024-11-20 00:00:00,MIN ; 1,0101,2024-11-24 00:00:00,MAX), (1,0198,2024-11-20 00:00:00,MIN ; 1,0198, | | 2024-11-24 00:00:00,MAX), (1,0199,2024-11-20 00:00:00,MIN ; 1,0199,2024-11-24 00:00:00,MAX), (1,0109,2024-11-20 00:00:00,MIN ; 1,0109,2024-11-24 00:00:00, | | MAX), (1,0107,2024-11-20 00:00:00,MIN ; 1,0107,2024-11-24 00:00:00,MAX), (1,0108,2024-11-20 00:00:00,MIN ; 1,0108,2024-11-24 00:00:00,MAX), | | range_cond([WBUSINESSC0_.STATE(0x7f2f76e4e900) = cast('1', VARCHAR2(1048576 ))(0x7f2f76e4eeb0)(0x7f2f76e4e1b0)], [WBUSINESSC0_.INDATE(0x7f2f76e32df0) | | >= TO_DATE(cast('2024-11-20 00:00:00', VARCHAR2(1048576 ))(0x7f2f76e49be0), cast('YYYY-MM-DD HH24:MI:SS', VARCHAR2(1048576 ))(0x7f2f76e4a730))(0x7f2f76e45ee0)(0x7f2f76e48460)], | | [WBUSINESSC0_.INDATE(0x7f2f76e32df0) <= TO_DATE(cast('2024-11-24 00:00:00', VARCHAR2(1048576 ))(0x7f2f76e4b2c0), cast('YYYY-MM-DD HH24:MI:SS', VARCHAR2(1048576 | | ))(0x7f2f76e4a730))(0x7f2f76e47010)(0x7f2f76e48b60)], [WBUSINESSC0_.RISKCODE(0x7f2f76e70ad0) IN (cast('0105', VARCHAR2(4 BYTE))(0x7f2f76ededf0), cast('0128', | | VARCHAR2(4 BYTE))(0x7f2f76edf940), cast('0103', VARCHAR2(4 BYTE))(0x7f2f76ee0490), cast('0101', VARCHAR2(4 BYTE))(0x7f2f76ee0fe0), cast('0198', VARCHAR2(4 | | BYTE))(0x7f2f76ee1b30), cast('0199', VARCHAR2(4 BYTE))(0x7f2f76ee2680), cast('0109', VARCHAR2(4 BYTE))(0x7f2f76ee31d0), cast('0107', VARCHAR2(4 BYTE))(0x7f2f76ee3d20), | | cast('0108', VARCHAR2(4 BYTE))(0x7f2f76ee4870))(0x7f455d9a9870)(0x7f2f76e6e0c0)]) | | 8 - output([PRPLBPMCOM2_.COMCODE1(0x7f2f76e6bd40)]), filter(nil) | | access([PRPLBPMCOM2_.ID(0x7f2f76ecbc20)], [PRPLBPMCOM2_.COMCODE1(0x7f2f76e6bd40)]), partitions(p0) | | is_index_back=true, is_global_index=false, | | range_key([PRPLBPMCOM2_.BPMMAINID(0x7f2f76e39f20)], [PRPLBPMCOM2_.ID(0x7f2f76ecbc20)]), range(MIN ; MAX), | | range_cond([:3 = PRPLBPMCOM2_.BPMMAINID(0x7f2f76e39f20)(0x7f6a27d8f940)]) | | 9 - output([VIEW1.B_.COMCODE(0x7f2f76ea2f90)]), filter(nil) | | access([VIEW1.B_.COMCODE(0x7f2f76ea2f90)]) | | 10 - output([VIEW3.B_.COMCODE(0x7f2f76eca450)], [VIEW3.B_.UPPERCOMCODE(0x7f2f76eca730)]), filter(nil) | | conds([VIEW2.B_.COMCODE(0x7f2f76ec9ba0) != VIEW3.B_.COMCODE(0x7f2f76eca450)(0x7f2f76e6a130)]), nl_params_([VIEW2.B_.COMCODE(0x7f2f76ec9ba0)(:0)]), | | use_batch=false | | 11 - output([VIEW2.B_.COMCODE(0x7f2f76ec9ba0)], [VIEW2.B_.UPPERCOMCODE(0x7f2f76ec9e80)]), filter(nil) | | access([VIEW2.B_.COMCODE(0x7f2f76ec9ba0)], [VIEW2.B_.UPPERCOMCODE(0x7f2f76ec9e80)]) | | 12 - output([B_.COMCODE(0x7f2f76e65e80)], [B_.UPPERCOMCODE(0x7f2f76e68800)]), filter(nil) | | access([B_.COMCODE(0x7f2f76e65e80)], [B_.UPPERCOMCODE(0x7f2f76e68800)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([B_.COMCODE(0x7f2f76e65e80)]), range[00000000 ; 00000000], | | range_cond([B_.COMCODE(0x7f2f76e65e80) = cast('00000000', VARCHAR2(1048576 ))(0x7f2f76e663f0)(0x7f2f76e65180)]) | | 13 - output([VIEW3.B_.COMCODE(0x7f2f76eca450)], [VIEW3.B_.UPPERCOMCODE(0x7f2f76eca730)]), filter(nil) | | access([VIEW3.B_.COMCODE(0x7f2f76eca450)], [VIEW3.B_.UPPERCOMCODE(0x7f2f76eca730)]) | | 14 - output([B_.COMCODE(0x7f2f76ec7c80)], [B_.UPPERCOMCODE(0x7f2f76ec7f60)]), filter(nil) | | access([B_.COMCODE(0x7f2f76ec7c80)], [B_.UPPERCOMCODE(0x7f2f76ec7f60)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([B_.UPPERCOMCODE(0x7f2f76ec7f60)], [B_.COMCODE(0x7f2f76ec7c80)]), range(MIN,MIN ; MAX,MAX)always true, | | range_cond([:0 = B_.UPPERCOMCODE(0x7f2f76ec7f60)(0x7f6a27c5ada0)]) | | 15 - output([VVVVVVV1_.MAKECOM(0x7f2f76e403a0)]), filter([VVVVVVV1_.CLASSCODE(0x7f2f76e448a0) = cast('01', VARCHAR2(1048576 ))(0x7f2f76e44e50)(0x7f2f76e44150)], | | [VVVVVVV1_.RISKCODE(0x7f2f76e3e110) != cast('9999', VARCHAR2(1048576 ))(0x7f2f76e3e6c0)(0x7f2f76e3d9c0)]) | | access([GROUP_ID(0x7f3e52aba710)], [VVVVVVV1_.RISKCODE(0x7f2f76e3e110)], [VVVVVVV1_.MAKECOM(0x7f2f76e403a0)], [VVVVVVV1_.CLASSCODE(0x7f2f76e448a0)]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false,false], | | range_key([VVVVVVV1_.PROPOSALNO(0x7f2f76e433e0)]), range(MIN ; MAX), | | range_cond([:1 = VVVVVVV1_.PROPOSALNO(0x7f2f76e433e0)(0x7f6a27cc4fc0)]) | | 16 - output(nil), filter(nil) | | access([GROUP_ID(0x7f3e52abc870)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([PRPDCOMPAN3_.COMCODE(0x7f2f76e40690)]), range(MIN ; MAX), | | range_cond([:7 = PRPDCOMPAN3_.COMCODE(0x7f2f76e40690)(0x7f7caa8f8a30)]) | | Used Hint: | | ------------------------------------- | | /*+ | | | | */ | | Qb name trace: | | ------------------------------------- | | stmt_id:0, stmt_type:T_EXPLAIN | | stmt_id:1, SEL$1 > SEL$F8C4A4E7 > SEL$48AF3A1C > SEL$7E0BD2D3 > SEL$7DC71705 | | stmt_id:2, SEL$2 | | stmt_id:3, parent:SEL$2 > SEL$9B6BAA9A | | stmt_id:4, parent:SEL$2 > SEL$9B6BAA9B | | stmt_id:5, parent:SEL$9B6BAA9B > SEL$E382C6D8_1 | | Outline Data: | | ------------------------------------- | | /*+ | | BEGIN_OUTLINE_DATA | | LEADING(@"SEL$7DC71705" ((((("BUSIUSER"."WBUSINESSS4_"@"SEL$1" "BUSIUSER"."WBUSINESSC0_"@"SEL$1") "BUSIUSER"."PRPLBPMCOM2_"@"SEL$1") "VIEW1"@"SEL$2") "BUSIUSER"."VVVVVVV1_"@"SEL$1") "BUSIUSER"."PRPDCOMPAN3_"@"SEL$1")) | | USE_NL(@"SEL$7DC71705" "BUSIUSER"."PRPDCOMPAN3_"@"SEL$1") | | USE_NL(@"SEL$7DC71705" "BUSIUSER"."VVVVVVV1_"@"SEL$1") | | USE_HASH(@"SEL$7DC71705" "VIEW1"@"SEL$2") | | USE_NL(@"SEL$7DC71705" "BUSIUSER"."PRPLBPMCOM2_"@"SEL$1") | | USE_HASH(@"SEL$7DC71705" "BUSIUSER"."WBUSINESSC0_"@"SEL$1") | | INDEX(@"SEL$7DC71705" "WBUSINESSS4_"@"SEL$1" "IDX_YUYUYU") | | INDEX(@"SEL$7DC71705" "WBUSINESSC0_"@"SEL$1" "IDX_VVVVVVV_STATE1") | | INDEX(@"SEL$7DC71705" "PRPLBPMCOM2_"@"SEL$1" "IDX_GGDFFF_BPMMAINID") | | USE_DAS(@"SEL$7DC71705" "PRPLBPMCOM2_"@"SEL$1") | | LEADING(@"SEL$9B6BAA9A" ("VIEW2"@"SEL$9B6BAA9A" "VIEW3"@"SEL$9B6BAA9A")) | | USE_NL(@"SEL$9B6BAA9A" "VIEW3"@"SEL$9B6BAA9A") | | FULL(@"SEL$9B6BAA9B" "B_"@"SEL$2") | | INDEX(@"SEL$E382C6D8_1" "B_"@"SEL$2" "IDX_COMPANY_UPPERCOMCODE") | | USE_DAS(@"SEL$E382C6D8_1" "B_"@"SEL$2") | | FULL(@"SEL$7DC71705" "VVVVVVV1_"@"SEL$1") | | USE_DAS(@"SEL$7DC71705" "VVVVVVV1_"@"SEL$1") | | FULL(@"SEL$7DC71705" "PRPDCOMPAN3_"@"SEL$1") | | USE_DAS(@"SEL$7DC71705" "PRPDCOMPAN3_"@"SEL$1") | | SIMPLIFY_EXPR(@"SEL$1") | | UNNEST(@"SEL$2") | | PRED_DEDUCE(@"SEL$48AF3A1C") | | MERGE(@"SEL$2" > "SEL$7E0BD2D3") | | OPTIMIZER_FEATURES_ENABLE('4.2.1.7') | | END_OUTLINE_DATA | | */ | | Optimization Info: | | ------------------------------------- | | WBUSINESSS4_: | | table_rows:129 | | physical_range_rows:1 | | logical_range_rows:1 | | index_back_rows:0 | | output_rows:1 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[IDX_YUYUYU, YUYUYU] | | unstable_index_name:[YUYUYU] | | stats version:1731691135620165 | | dynamic sampling level:0 | | WBUSINESSC0_: | | table_rows:56458998 | | physical_range_rows:20966 | | logical_range_rows:20966 | | index_back_rows:20966 | | output_rows:104 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[IDX_VVVVVVV_STATE1, IDX_GGGGGGG_1, IDX_GGGGGGG_MAINNO, IND_ID_MAINNO_RCODE_INDATE, MAINNO_RCODE_INDATE, IDX_LBPMMAIN_USERCODE, IDX_LBPMMAINTASKID, IDX_GGGGGGG_USERCODE, IDX_LBPMMAINPROCESSID, IDX_LBPMMAINBUSINESSNO, IDX_LBPMMAINBUSINESSID, IDX_GGGGGGG_PREPTASKID, IDX_GGGGGGG_INDATE, IDX_GGGGGGG_NODE, IDX_LBPMMAIN_INDATENODEID, IDX_GGGGGGG_UPDATEDDATE, IDX_LBPMMAIN_TOCHARINDATE, GGGGGGG] | | pruned_index_name:[IDX_GGGGGGG_1, IDX_GGGGGGG_MAINNO, IND_ID_MAINNO_RCODE_INDATE, MAINNO_RCODE_INDATE, IDX_LBPMMAIN_USERCODE, IDX_GGGGGGG_USERCODE, IDX_LBPMMAINPROCESSID, IDX_LBPMMAINBUSINESSID, IDX_GGGGGGG_PREPTASKID, IDX_GGGGGGG_INDATE, IDX_LBPMMAIN_INDATENODEID, IDX_GGGGGGG_UPDATEDDATE, IDX_LBPMMAIN_TOCHARINDATE] | | stats version:1732198831019172 | | dynamic sampling level:0 | | PRPLBPMCOM2_: | | table_rows:56130216 | | physical_range_rows:1 | | logical_range_rows:1 | | index_back_rows:1 | | output_rows:1 | | table_dop:1 | | dop_method:DAS DOP | | avaiable_index_name:[IDX_GGDFFF_COMCODE, IDX_GGDFFF_BPMMAINID, GGDFFF] | | pruned_index_name:[IDX_GGDFFF_COMCODE] | | unstable_index_name:[GGDFFF] | | stats version:1731710769077342 | | dynamic sampling level:0 | | B_: | | table_rows:3598 | | physical_range_rows:1 | | logical_range_rows:1 | | index_back_rows:0 | | output_rows:1 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[IDX_COMPANY_UPPERCOMCODE, IDX_SDQQQQQSGZ01, SDQQQQQ] | | pruned_index_name:[IDX_COMPANY_UPPERCOMCODE, IDX_SDQQQQQSGZ01] | | stats version:1732319051780078 | | dynamic sampling level:0 | | B_: | | table_rows:3598 | | physical_range_rows:16 | | logical_range_rows:16 | | index_back_rows:0 | | output_rows:16 | | table_dop:1 | | dop_method:DAS DOP | | avaiable_index_name:[IDX_COMPANY_UPPERCOMCODE, IDX_SDQQQQQSGZ01, SDQQQQQ] | | pruned_index_name:[SDQQQQQ] | | stats version:1732319051780078 | | dynamic sampling level:0 | | VVVVVVV1_: | | table_rows:23309582 | | physical_range_rows:1 | | logical_range_rows:1 | | index_back_rows:0 | | output_rows:0 | | table_dop:1 | | dop_method:DAS DOP | | avaiable_index_name:[IDX_VVVVVVV_ENDDATE, IDX_VVVVVVV_HANDLER1CODE, IDX_VVVVVVV_HANDLERCODE, IDX_VVVVVVV_COMCODE2, IDX_VVVVVVV_CONTRACTNO, IDX_VVVVVVV_STARTDATE, IDX_VVVVVVV_RISKCODE, IDX_VVVVVVV_RISKCODE1, IDX_VVVVVVV_INPUTDATE, IDX_VVVVVVV_INSUREDCODE, IDX_VVVVVVV_OPERATORCODE, IDX_VVVVVVV_APPLICODE, IDX_VVVVVVV_COMCODE, IDX_VVVVVVV_COMCODE1, IDX_VVVVVVV_APPLINAME, IDX_VVVVVVV_INSUREDNAME, IDX_VVVVVVV_UNDERWRITEFLAG, IDX_VVVVVVV_UNDERWRITEENDDATE, IDX_VVVVVVV_MAKECOM, IDX_VVVVVVV_UPDATEDDATE, IDX_VVVVVVV_01, IDX_VVVVVVV_02, IDX_VVVVVVV_UNDERWRITEDATE, IDX_VVVVVVV_OPERATORCODE2, VVVVVVV] | | pruned_index_name:[IDX_VVVVVVV_ENDDATE, IDX_VVVVVVV_HANDLER1CODE, IDX_VVVVVVV_HANDLERCODE, IDX_VVVVVVV_COMCODE2, IDX_VVVVVVV_CONTRACTNO, IDX_VVVVVVV_STARTDATE, IDX_VVVVVVV_RISKCODE, IDX_VVVVVVV_RISKCODE1, IDX_VVVVVVV_INPUTDATE, IDX_VVVVVVV_INSUREDCODE, IDX_VVVVVVV_OPERATORCODE, IDX_VVVVVVV_APPLICODE, IDX_VVVVVVV_COMCODE, IDX_VVVVVVV_COMCODE1, IDX_VVVVVVV_APPLINAME, IDX_VVVVVVV_INSUREDNAME, IDX_VVVVVVV_UNDERWRITEFLAG, IDX_VVVVVVV_UNDERWRITEENDDATE, IDX_VVVVVVV_MAKECOM, IDX_VVVVVVV_UPDATEDDATE, IDX_VVVVVVV_01, IDX_VVVVVVV_02, IDX_VVVVVVV_UNDERWRITEDATE, IDX_VVVVVVV_OPERATORCODE2] | | stats version:1731719565265749 | | dynamic sampling level:0 | | PRPDCOMPAN3_: | | table_rows:3598 | | physical_range_rows:1 | | logical_range_rows:1 | | index_back_rows:0 | | output_rows:1 | | table_dop:1 | | dop_method:DAS DOP | | avaiable_index_name:[IDX_COMPANY_UPPERCOMCODE, IDX_SDQQQQQSGZ01, SDQQQQQ] | | pruned_index_name:[IDX_COMPANY_UPPERCOMCODE, IDX_SDQQQQQSGZ01] | | stats version:1732319051780078 | | dynamic sampling level:0 | | Plan Type: | | LOCAL | | Note: | | Degree of Parallelisim is 1 because of table property | | Expr Constraints: | | 1 = 1 result is TRUE | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 232 rows in set (0.158 sec)
3600秒降至(0.002 sec) 返回結果。