======================================================================================================= |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| ------------------------------------------------------------------------------------------------------- |0 |SCALAR GROUP BY | |1 |2555 | |1 |└─PX COORDINATOR | |64 |2554 | |2 | └─EXCHANGE OUT DISTR |:EX10001 |64 |2544 | |3 | └─MERGE GROUP BY | |64 |2544 | |4 | └─NESTED-LOOP JOIN | |1605 |2544 | |5 | ├─SHARED HASH JOIN | |1604 |1724 | |6 | │ ├─JOIN FILTER CREATE |:RF0000 |1604 |1619 | |7 | │ │ └─EXCHANGE IN DISTR | |1604 |1619 | |8 | │ │ └─EXCHANGE OUT DISTR (BC2HOST)|:EX10000 |1604 |1607 | |9 | │ │ └─PX BLOCK ITERATOR | |1604 |1578 | |10| │ │ └─TABLE FULL SCAN |BO |1604 |1578 | |11| │ └─JOIN FILTER USE |:RF0000 |1602 |98 | |12| │ └─PX BLOCK ITERATOR | |1602 |98 | |13| │ └─TABLE RANGE SCAN |bf(family_info_familyState_IDX)|1602 |98 | |14| └─DISTRIBUTED TABLE RANGE SCAN |br(family_member_familyId_IDX) |1 |32 | ======================================================================================================= Outputs & filters: ------------------------------------- 0 - output([T_FUN_COUNT_SUM(T_FUN_COUNT(*)(0x7f19ac42fe80))(0x7f1ff0e327c0)]), filter(nil), rowset=256 group(nil), agg_func([T_FUN_COUNT_SUM(T_FUN_COUNT(*)(0x7f19ac42fe80))(0x7f1ff0e327c0)]) 1 - output([T_FUN_COUNT(*)(0x7f19ac42fe80)]), filter(nil), rowset=256 2 - output([T_FUN_COUNT(*)(0x7f19ac42fe80)]), filter(nil), rowset=256 dop=64 3 - output([T_FUN_COUNT(*)(0x7f19ac42fe80)]), filter(nil), rowset=256 group(nil), agg_func([T_FUN_COUNT(*)(0x7f19ac42fe80)]) 4 - output(nil), filter(nil), rowset=256 conds(nil), nl_params_([bf.id(0x7f19ac4237f0)(:2)]), use_batch=false 5 - output([bf.id(0x7f19ac4237f0)]), filter(nil), rowset=256 equal_conds([bf.id(0x7f19ac4237f0) = BO.familyId(0x7f19ac423ae0)(0x7f19ac4230a0)]), other_conds(nil) 6 - output([BO.familyId(0x7f19ac423ae0)]), filter(nil), rowset=256 RF_TYPE(in, range, bloom), RF_EXPR[BO.familyId(0x7f19ac423ae0)] 7 - output([BO.familyId(0x7f19ac423ae0)]), filter(nil), rowset=256 8 - output([BO.familyId(0x7f19ac423ae0)]), filter(nil), rowset=256 dop=64 9 - output([BO.familyId(0x7f19ac423ae0)]), filter(nil), rowset=256 10 - output([BO.familyId(0x7f19ac423ae0)]), filter([cast(BO.status(0x7f19ac42e260), DECIMAL(-1, -1))(0x7f19ac42e7d0) = cast(1, DECIMAL(1, 0))(0x7f19ac42f320)(0x7f19ac42db10)], [BO.policy_id(0x7f19ac42d000) = 200(0x7f19ac42c8b0)]), rowset=256 access([BO.familyId(0x7f19ac423ae0)], [BO.policy_id(0x7f19ac42d000)], [BO.status(0x7f19ac42e260)]), partitions(p0) is_index_back=false, is_global_index=false, filter_before_indexback[false,false], range_key([BO.ss_id(0x7f19ac431280)]), range(MIN ; MAX)always true 11 - output([bf.id(0x7f19ac4237f0)]), filter(nil), rowset=256 12 - output([bf.id(0x7f19ac4237f0)]), filter(nil), rowset=256 13 - output([bf.id(0x7f19ac4237f0)]), filter([RF_IN_FILTER(bf.id(0x7f19ac4237f0))(0x7f1ff0e553e0)], [RF_RANGE_FILTER(bf.id(0x7f19ac4237f0))(0x7f1ff0e55c60)], [RF_BLOOM_FILTER(bf.id(0x7f19ac4237f0))(0x7f1ff0e564e0)]), rowset=256 access([bf.id(0x7f19ac4237f0)]), partitions(p0) is_index_back=false, is_global_index=false, filter_before_indexback[false,false,false], range_key([bf.familyState(0x7f19ac424d40)], [bf.id(0x7f19ac4237f0)]), range(1,MIN ; 1,MAX), range_cond([bf.familyState(0x7f19ac424d40) = 1(0x7f19ac4245f0)]) 14 - output(nil), filter([cast(br.personnelStatus(0x7f19ac42a700), DECIMAL(-1, -1))(0x7f19ac42ac70) = cast(1, DECIMAL(1, 0))(0x7f19ac42b7c0)(0x7f19ac429fb0)], [br.relationShip(0x7f19ac4294a0) = '0'(0x7f19ac428d50)]), rowset=256 access([br.id(0x7f19ac431560)], [br.relationShip(0x7f19ac4294a0)], [br.personnelStatus(0x7f19ac42a700)]), partitions(p0) is_index_back=true, is_global_index=false, filter_before_indexback[false,false], range_key([br.familyId(0x7f19ac428230)], [br.id(0x7f19ac431560)]), range(MIN ; MAX), range_cond([br.familyId(0x7f19ac428230) = :2(0x7f19ac5122c0)]) Used Hint: ------------------------------------- /*+ PARALLEL(64) */ Qb name trace: ------------------------------------- stmt_id:0, stmt_type:T_EXPLAIN stmt_id:1, SEL$1 > SEL$EA493D33 > SEL$60EBDDF2 Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA GBY_PUSHDOWN(@"SEL$60EBDDF2") LEADING(@"SEL$60EBDDF2" (("jlsmz"."BO"@"SEL$1" "jlsmz"."bf"@"SEL$1") "jlsmz"."br"@"SEL$1")) USE_NL(@"SEL$60EBDDF2" "jlsmz"."br"@"SEL$1") PQ_DISTRIBUTE(@"SEL$60EBDDF2" "jlsmz"."br"@"SEL$1" NONE ALL) USE_HASH(@"SEL$60EBDDF2" "jlsmz"."bf"@"SEL$1") PQ_DISTRIBUTE(@"SEL$60EBDDF2" "jlsmz"."bf"@"SEL$1" BC2HOST NONE) PX_JOIN_FILTER(@"SEL$60EBDDF2" "jlsmz"."bf"@"SEL$1" "jlsmz"."BO"@"SEL$1") PARALLEL(@"SEL$60EBDDF2" "BO"@"SEL$1" 64) FULL(@"SEL$60EBDDF2" "BO"@"SEL$1") PARALLEL(@"SEL$60EBDDF2" "bf"@"SEL$1" 64) INDEX(@"SEL$60EBDDF2" "bf"@"SEL$1" "family_info_familyState_IDX") INDEX(@"SEL$60EBDDF2" "br"@"SEL$1" "family_member_familyId_IDX") USE_DAS(@"SEL$60EBDDF2" "br"@"SEL$1") SIMPLIFY_GROUP_BY(@"SEL$1") OUTER_TO_INNER(@"SEL$EA493D33") PARALLEL(64) OPTIMIZER_FEATURES_ENABLE('4.2.1.0') END_OUTLINE_DATA */ Optimization Info: ------------------------------------- BO: table_rows:2190697 physical_range_rows:2190697 logical_range_rows:2190697 index_back_rows:0 output_rows:206 table_dop:64 dop_method:Global DOP avaiable_index_name:[policy_id, bz_objstatus] stats version:1710770405097700 dynamic sampling level:0 bf: table_rows:1386059 physical_range_rows:563714 logical_range_rows:563714 index_back_rows:0 output_rows:563714 table_dop:64 dop_method:Global DOP avaiable_index_name:[family_info_apply_type_IDX, family_info_familyState_IDX, family_info_districts_id_IDX, memberId, family_info_id_IDX, family_info_familyNo_IDX, family_info] pruned_index_name:[family_info_apply_type_IDX, family_info_districts_id_IDX, memberId, family_info_id_IDX, family_info_familyNo_IDX, family_info] stats version:1710626413361880 dynamic sampling level:0 br: table_rows:2063711 physical_range_rows:1 logical_range_rows:1 index_back_rows:1 output_rows:0 table_dop:1 dop_method:DAS DOP avaiable_index_name:[family_member_familyId_IDX, family_member_memberIdcard_IDX, family_member_memberName_IDX, family_member_districts_id_IDX, family_member_familyNo_IDX, memberIdcard, personnelStatusMix, family_member_relationShip, family_member_personnelStatus, family_member] pruned_index_name:[family_member_memberIdcard_IDX, family_member_memberName_IDX, family_member_districts_id_IDX, family_member_familyNo_IDX, memberIdcard, family_member_personnelStatus] unstable_index_name:[family_member] stats version:1710770429759031 dynamic sampling level:0 Plan Type: DISTRIBUTED Note: Degree of Parallelism is 64 because of hint