某保险理赔核心OB SQL优化案例
某保险理赔核心系统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_---------- 0Elapsed: 00:00:01.73
OB慢SQL等价改写：
obclient [UTIC_BUSIAPP]> SELECT -> COUNT(*) AS COL_0_0_ -> FROM -> GGGGGGG WBUSINESSC0_, -> VVVVVVV VVVVVVV1_, -> GGDFFFPRPLBPMCOM2_, -> 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) 返回结果。
页:
[1]