"begin EXPLAIN extended insert/*+ append nologing*/ into URP3_TUSP.DM_GAAP_PARENT_COURSE_TMP(count_proj_code,sub_code, sub_name,data_source) SELECT /*+ PARALLEL(t,4)*/ count_proj_code,sub_code, sub_name,data_source FROM URP3_TUSP.DM_D_GAAP_COURSE_BAS t where exists(select 1 from URP3_TUSP.DM_CTRC_BASD_REPORT_FUND f where f.STATS_BATCH_ID = '20241231_1_1' and f.report_flag_east5 = '1' and t.count_proj_code = f.count_proj_code) and t.STATS_BATCH_ID = '20241231_1_1'; commit; end;" Query Plan ======================================================================================== |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| ---------------------------------------------------------------------------------------- |0 |INSERT | |1 |42 | |1 |└─OPTIMIZER STATS GATHER | |1 |29 | |2 | └─SUBPLAN SCAN |ANONYMOUS_VIEW1 |1 |29 | |3 | └─NESTED-LOOP SEMI JOIN | |1 |29 | |4 | ├─PX COORDINATOR | |1 |4 | |5 | │ └─EXCHANGE OUT DISTR |:EX10000 |1 |3 | |6 | │ └─PX BLOCK ITERATOR | |1 |3 | |7 | │ └─TABLE RANGE SCAN|T(PK_DM_D_GAAP_COURSE_BAS) |1 |3 | |8 | └─SUBPLAN SCAN |VIEW1 |1 |25 | |9 | └─TABLE RANGE SCAN |F(I1_DM_CTRC_BASD_REPORT_FUND)|1 |25 | ======================================================================================== Outputs & filters: ------------------------------------- 0 - output(nil), filter(nil) columns([{DM_GAAP_PARENT_COURSE_TMP: ({DM_GAAP_PARENT_COURSE_TMP: (DM_GAAP_PARENT_COURSE_TMP.__pk_increment(0xfff90406e6b0), DM_GAAP_PARENT_COURSE_TMP.COUNT_PROJ_CODE(0xfff904026530), DM_GAAP_PARENT_COURSE_TMP.SUB_CODE(0xfff904026b00), DM_GAAP_PARENT_COURSE_TMP.SUB_NAME(0xfff9040270d0), DM_GAAP_PARENT_COURSE_TMP.DATA_SOURCE(0xfff9040276a0))})}]), partitions(p0), column_values([T_HIDDEN_PK(0xfff90406e990)], [column_conv(VARCHAR2,utf8mb4_bin,length:255,NULL,ANONYMOUS_VIEW1.COUNT_PROJ_CODE(0xfff904059eb0))(0xfff90406ec60)], [column_conv(VARCHAR2,utf8mb4_bin,length:64,NULL,ANONYMOUS_VIEW1.SUB_CODE(0xfff90405f0b0))(0xfff904075fc0)], [column_conv(VARCHAR2,utf8mb4_bin,length:255, NULL,ANONYMOUS_VIEW1.SUB_NAME(0xfff9040642b0))(0xfff90407d320)], [column_conv(VARCHAR2,utf8mb4_bin,length:32,NULL,ANONYMOUS_VIEW1.DATA_SOURCE(0xfff9040694b0))(0xfff904084690)]) 1 - output([column_conv(VARCHAR2,utf8mb4_bin,length:255,NULL,ANONYMOUS_VIEW1.COUNT_PROJ_CODE(0xfff904059eb0))(0xfff90406ec60)], [column_conv(VARCHAR2, utf8mb4_bin,length:64,NULL,ANONYMOUS_VIEW1.SUB_CODE(0xfff90405f0b0))(0xfff904075fc0)], [column_conv(VARCHAR2,utf8mb4_bin,length:255,NULL,ANONYMOUS_VIEW1.SUB_NAME(0xfff9040642b0))(0xfff90407d320)], [column_conv(VARCHAR2,utf8mb4_bin,length:32,NULL,ANONYMOUS_VIEW1.DATA_SOURCE(0xfff9040694b0))(0xfff904084690)]), filter(nil), rowset=16 2 - output([ANONYMOUS_VIEW1.COUNT_PROJ_CODE(0xfff904059eb0)], [ANONYMOUS_VIEW1.SUB_CODE(0xfff90405f0b0)], [ANONYMOUS_VIEW1.SUB_NAME(0xfff9040642b0)], [ANONYMOUS_VIEW1.DATA_SOURCE(0xfff9040694b0)]), filter(nil), rowset=16 access([ANONYMOUS_VIEW1.COUNT_PROJ_CODE(0xfff904059eb0)], [ANONYMOUS_VIEW1.SUB_CODE(0xfff90405f0b0)], [ANONYMOUS_VIEW1.SUB_NAME(0xfff9040642b0)], [ANONYMOUS_VIEW1.DATA_SOURCE(0xfff9040694b0)]) 3 - output([T.COUNT_PROJ_CODE(0xfff904054ff0)], [T.SUB_CODE(0xfff904058a40)], [T.SUB_NAME(0xfff904059040)], [T.DATA_SOURCE(0xfff904059640)]), filter(nil), rowset=16 conds(nil), nl_params_([T.COUNT_PROJ_CODE(0xfff904054ff0)(:0)]), use_batch=false 4 - output([T.COUNT_PROJ_CODE(0xfff904054ff0)], [T.SUB_CODE(0xfff904058a40)], [T.SUB_NAME(0xfff904059040)], [T.DATA_SOURCE(0xfff904059640)]), filter(nil), rowset=16 5 - output([T.COUNT_PROJ_CODE(0xfff904054ff0)], [T.SUB_CODE(0xfff904058a40)], [T.SUB_NAME(0xfff904059040)], [T.DATA_SOURCE(0xfff904059640)]), filter(nil), rowset=16 dop=4 6 - output([T.COUNT_PROJ_CODE(0xfff904054ff0)], [T.SUB_CODE(0xfff904058a40)], [T.SUB_NAME(0xfff904059040)], [T.DATA_SOURCE(0xfff904059640)]), filter(nil), rowset=16 7 - output([T.COUNT_PROJ_CODE(0xfff904054ff0)], [T.SUB_CODE(0xfff904058a40)], [T.SUB_NAME(0xfff904059040)], [T.DATA_SOURCE(0xfff904059640)]), filter(nil), rowset=16 access([T.__pk_increment(0xfff9040a4bc0)], [T.COUNT_PROJ_CODE(0xfff904054ff0)], [T.SUB_CODE(0xfff904058a40)], [T.SUB_NAME(0xfff904059040)], [T.DATA_SOURCE(0xfff904059640)]), partitions(p0) is_index_back=true, is_global_index=false, range_key([T.STATS_BATCH_ID(0xfff904057300)], [T.COUNT_PROJ_CODE(0xfff904054ff0)], [T.SUB_CODE(0xfff904058a40)], [T.DATA_SOURCE(0xfff904059640)], [T.shadow_pk_0(0xfff97567e180)]), range(20241231_1_1,MIN,MIN,MIN,MIN ; 20241231_1_1,MAX,MAX,MAX,MAX), range_cond([T.STATS_BATCH_ID(0xfff904057300) = cast('20241231_1_1', VARCHAR2(1048576 ))(0xfff9040578b0)(0xfff904056bb0)]) 8 - output(nil), filter(nil), rowset=16 access(nil) 9 - output([F.COUNT_PROJ_CODE(0xfff904055570)]), filter([F.REPORT_FLAG_EAST5(0xfff904052f70) = cast('1', VARCHAR2(1048576 ))(0xfff904053520)(0xfff904052820)]), rowset=16 access([F.__pk_increment(0xfff9040a3fc0)], [F.REPORT_FLAG_EAST5(0xfff904052f70)], [F.COUNT_PROJ_CODE(0xfff904055570)]), partitions(p0) is_index_back=true, is_global_index=false, filter_before_indexback[false], range_key([F.STATS_BATCH_ID(0xfff904050f70)], [F.COUNT_PROJ_CODE(0xfff904055570)], [F.__pk_increment(0xfff9040a3fc0)]), range(20241231_1_1,MIN,MIN ; 20241231_1_1,MAX,MAX), range_cond([F.STATS_BATCH_ID(0xfff904050f70) = cast('20241231_1_1', VARCHAR2(1048576 ))(0xfff904051520)(0xfff904050820)], [:0 = F.COUNT_PROJ_CODE(0xfff904055570)(0xfff9756e29b0)]) Used Hint: ------------------------------------- /*+ PARALLEL(\t\" 4)" USE_PLAN_CACHE( NONE ) APPEND APPEND */ Qb name trace: ------------------------------------- stmt_id:0, stmt_type:T_EXPLAIN stmt_id:1, INS$1 stmt_id:2, SEL$1 > SEL$6FCAE2AA stmt_id:3, SEL$2 Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@\SEL$6FCAE2AA\" (\"URP3_TUSP\".\"T\"@\"SEL$1\" \"VIEW1\"@\"SEL$1\"))" USE_NL(@\SEL$6FCAE2AA\" \"VIEW1\"@\"SEL$1\")" PQ_DISTRIBUTE(@\SEL$6FCAE2AA\" \"VIEW1\"@\"SEL$1\" LOCAL LOCAL)" PARALLEL(@\SEL$6FCAE2AA\" \"T\"@\"SEL$1\" 4)" INDEX(@\SEL$6FCAE2AA\" \"T\"@\"SEL$1\" \"PK_DM_D_GAAP_COURSE_BAS\")" INDEX(@\SEL$2\" \"F\"@\"SEL$2\" \"I1_DM_CTRC_BASD_REPORT_FUND\")" UNNEST(@\SEL$2\")" USE_PLAN_CACHE( NONE ) PARALLEL(4) OPTIMIZER_FEATURES_ENABLE('4.2.1.8') APPEND APPEND END_OUTLINE_DATA */ Optimization Info: ------------------------------------- T: table_rows:2177700 physical_range_rows:1 logical_range_rows:1 index_back_rows:1 output_rows:1 table_dop:4 dop_method:Table Parallel Hint avaiable_index_name:[PK_DM_D_GAAP_COURSE_BAS, DM_D_GAAP_COURSE_BAS] unstable_index_name:[DM_D_GAAP_COURSE_BAS] stats version:1751638158056319 dynamic sampling level:0 F: table_rows:1576489 physical_range_rows:1 logical_range_rows:1 index_back_rows:1 output_rows:0 table_dop:1 dop_method:Table DOP avaiable_index_name:[I1_DM_CTRC_BASD_REPORT_FUND, DM_CTRC_BASD_REPORT_FUND] unstable_index_name:[DM_CTRC_BASD_REPORT_FUND] stats version:1751638285101722 dynamic sampling level:0 Plan Type: DISTRIBUTED Note: Degree of Parallelisim is 4 because of table property