=================================================================================== |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| ----------------------------------------------------------------------------------- |0 |PX COORDINATOR | |2491 |37095 | |1 |└─EXCHANGE OUT DISTR |:EX10001 |2491 |29084 | |2 | └─HASH RIGHT OUTER JOIN | |2491 |11057 | |3 | ├─TABLE FULL SCAN |c |1240 |111 | |4 | └─NESTED-LOOP OUTER JOIN | |2487 |10345 | |5 | ├─NESTED-LOOP OUTER JOIN | |7 |146 | |6 | │ ├─EXCHANGE IN DISTR | |6 |50 | |7 | │ │ └─EXCHANGE OUT DISTR (BC2HOST)|:EX10000 |6 |49 | |8 | │ │ └─MERGE JOIN | |6 |47 | |9 | │ │ ├─TABLE FULL SCAN |r |91 |11 | |10| │ │ └─SORT | |6 |29 | |11| │ │ └─TABLE RANGE SCAN |ur(userid) |6 |28 | |12| │ └─TABLE GET |s |1 |16 | |13| └─TABLE FULL SCAN |o(cascadeident)|413 |1685 | =================================================================================== Outputs & filters: ------------------------------------- 0 - output([INTERNAL_FUNCTION(o.id(0x7e3db24388d0), o.topid(0x7e3db2438c20), o.card_id(0x7e3db2438f70), SUBSTRING_INDEX(o.organizationname(0x7e3db24397e0), ':C'(0x7e3db2439430), 1(0x7e3db2439580))(0x7e3db2439110), o.parentid(0x7e3db24374e0), c.organizationname(0x7e3db2439d50), o.encoding(0x7e3db243a0a0), o.type(0x7e3db243a3f0), o.organizationtype(0x7e3db243c650), o.principal(0x7e3db243c9a0), o.cascadeident(0x7e3db2434680), o.is_external(0x7e3db243ceb0), o.address(0x7e3db243d200), o.lat(0x7e3db243d550), o.lng(0x7e3db243d8a0), o.address(0x7e3db243d200), o.sort(0x7e3db243ddb0))(0x7e3b45b19540)]), filter(nil), rowset=32 1 - output([INTERNAL_FUNCTION(o.id(0x7e3db24388d0), o.topid(0x7e3db2438c20), o.card_id(0x7e3db2438f70), SUBSTRING_INDEX(o.organizationname(0x7e3db24397e0), ':C'(0x7e3db2439430), 1(0x7e3db2439580))(0x7e3db2439110), o.parentid(0x7e3db24374e0), c.organizationname(0x7e3db2439d50), o.encoding(0x7e3db243a0a0), o.type(0x7e3db243a3f0), o.organizationtype(0x7e3db243c650), o.principal(0x7e3db243c9a0), o.cascadeident(0x7e3db2434680), o.is_external(0x7e3db243ceb0), o.address(0x7e3db243d200), o.lat(0x7e3db243d550), o.lng(0x7e3db243d8a0), o.address(0x7e3db243d200), o.sort(0x7e3db243ddb0))(0x7e3b45b19540)]), filter(nil), rowset=32 is_single, dop=1 2 - output([o.address(0x7e3db243d200)], [o.parentid(0x7e3db24374e0)], [c.organizationname(0x7e3db2439d50)], [o.id(0x7e3db24388d0)], [o.cascadeident(0x7e3db2434680)], [o.topid(0x7e3db2438c20)], [o.card_id(0x7e3db2438f70)], [o.organizationname(0x7e3db24397e0)], [o.encoding(0x7e3db243a0a0)], [o.type(0x7e3db243a3f0)], [o.organizationtype(0x7e3db243c650)], [o.principal(0x7e3db243c9a0)], [o.is_external(0x7e3db243ceb0)], [o.lat(0x7e3db243d550)], [o.lng(0x7e3db243d8a0)], [o.sort(0x7e3db243ddb0)]), filter(nil), rowset=32 equal_conds([c.id(0x7e3db2437350) = o.parentid(0x7e3db24374e0)(0x7e3db24371e0)]), other_conds(nil) 3 - output([c.id(0x7e3db2437350)], [c.organizationname(0x7e3db2439d50)]), filter(nil), rowset=32 access([c.id(0x7e3db2437350)], [c.organizationname(0x7e3db2439d50)]), partitions(p0) is_index_back=false, is_global_index=false, range_key([c.id(0x7e3db2437350)]), range(MIN ; MAX)always true 4 - output([o.address(0x7e3db243d200)], [o.parentid(0x7e3db24374e0)], [o.id(0x7e3db24388d0)], [o.cascadeident(0x7e3db2434680)], [o.topid(0x7e3db2438c20)], [o.card_id(0x7e3db2438f70)], [o.organizationname(0x7e3db24397e0)], [o.encoding(0x7e3db243a0a0)], [o.type(0x7e3db243a3f0)], [o.organizationtype(0x7e3db243c650)], [o.principal(0x7e3db243c9a0)], [o.is_external(0x7e3db243ceb0)], [o.lat(0x7e3db243d550)], [o.lng(0x7e3db243d8a0)], [o.sort(0x7e3db243ddb0)]), filter(nil), rowset=32 conds(nil), nl_params_([s.cascadeident(0x7e3db2434810)(:4(0x7e3b45a1f6e0))]), use_batch=true 5 - output([s.cascadeident(0x7e3db2434810)]), filter(nil), rowset=32 conds(nil), nl_params_([ur.organizationid(0x7e3db2432d90)(:2(0x7e3db25ab2d0))]), use_batch=true 6 - output([ur.organizationid(0x7e3db2432d90)]), filter(nil), rowset=32 7 - output([ur.organizationid(0x7e3db2432d90)]), filter(nil), rowset=32 is_single, dop=1 8 - output([ur.organizationid(0x7e3db2432d90)]), filter(nil), rowset=32 equal_conds([cast(r.id(0x7e3db242f7b0), DECIMAL(20, 0))(0x7e3db242fc20) = cast(ur.roleid(0x7e3db242f940), DECIMAL(-1, -1))(0x7e3db242fff0)(0x7e3db242f640)]), other_conds(nil) merge_directions([ASC]) 9 - output([r.id(0x7e3db242f7b0)]), filter([r.disable(0x7e3db2437c80) = 0(0x7e3db24379c0)(0x7e3db2437b10)]), rowset=32 access([r.id(0x7e3db242f7b0)], [r.disable(0x7e3db2437c80)]), partitions(p0) is_index_back=false, is_global_index=false, filter_before_indexback[false], range_key([r.id(0x7e3db242f7b0)]), range(MIN ; MAX)always true 10 - output([ur.organizationid(0x7e3db2432d90)], [cast(ur.roleid(0x7e3db242f940), DECIMAL(-1, -1))(0x7e3db242fff0)]), filter(nil), rowset=32 sort_keys([cast(ur.roleid(0x7e3db242f940), DECIMAL(-1, -1))(0x7e3db242fff0), ASC]) 11 - output([ur.organizationid(0x7e3db2432d90)], [cast(ur.roleid(0x7e3db242f940), DECIMAL(-1, -1))(0x7e3db242fff0)]), filter(nil), rowset=32 access([ur.id(0x7e3db2460a00)], [ur.roleid(0x7e3db242f940)], [ur.organizationid(0x7e3db2432d90)]), partitions(p0) is_index_back=true, is_global_index=false, range_key([ur.userid(0x7e3db24383f0)], [ur.id(0x7e3db2460a00)]), range(1,MIN ; 1,MAX), range_cond([ur.userid(0x7e3db24383f0) = 1(0x7e3b45b31a60)(0x7e3b45b31820)]) 12 - output([s.cascadeident(0x7e3db2434810)]), filter(nil), rowset=32 access([GROUP_ID(0x7e3b45b21bb0)], [s.cascadeident(0x7e3db2434810)]), partitions(p0) is_index_back=false, is_global_index=false, range_key([s.id(0x7e3db2432c00)]), range(MIN ; MAX), range_cond([cast(s.id(0x7e3db2432c00), DECIMAL(20, 0))(0x7e3b45b31e20) = cast(:2(0x7e3db25ab2d0), DECIMAL(-1, -1))(0x7e3b45b321e0)(0x7e3b45b31bb0)]) 13 - output([o.id(0x7e3db24388d0)], [o.cascadeident(0x7e3db2434680)], [o.parentid(0x7e3db24374e0)], [o.topid(0x7e3db2438c20)], [o.card_id(0x7e3db2438f70)], [o.organizationname(0x7e3db24397e0)], [o.encoding(0x7e3db243a0a0)], [o.type(0x7e3db243a3f0)], [o.organizationtype(0x7e3db243c650)], [o.principal(0x7e3db243c9a0)], [o.is_external(0x7e3db243ceb0)], [o.address(0x7e3db243d200)], [o.lat(0x7e3db243d550)], [o.lng(0x7e3db243d8a0)], [o.sort(0x7e3db243ddb0)]), filter([(T_OP_LIKE, o.cascadeident(0x7e3db2434680), CONCAT(:4(0x7e3b45a1f6e0), '%'(0x7e3db2434290))(0x7e3b45a1f850), '\\')(0x7e3b45a1fa90)]), rowset=32 access([GROUP_ID(0x7e3b45b21cb0)], [o.id(0x7e3db24388d0)], [o.cascadeident(0x7e3db2434680)], [o.parentid(0x7e3db24374e0)], [o.topid(0x7e3db2438c20)], [o.card_id(0x7e3db2438f70)], [o.organizationname(0x7e3db24397e0)], [o.encoding(0x7e3db243a0a0)], [o.type(0x7e3db243a3f0)], [o.organizationtype(0x7e3db243c650)], [o.principal(0x7e3db243c9a0)], [o.is_external(0x7e3db243ceb0)], [o.address(0x7e3db243d200)], [o.lat(0x7e3db243d550)], [o.lng(0x7e3db243d8a0)], [o.sort(0x7e3db243ddb0)]), partitions(p0) is_index_back=true, is_global_index=false, filter_before_indexback[true], range_key([o.cascadeident(0x7e3db2434680)], [o.id(0x7e3db24388d0)]), range(MIN ; MAX) Used Hint: ------------------------------------- /*+ */ Qb name trace: ------------------------------------- stmt_id:0, stmt_type:T_EXPLAIN stmt_id:1, SEL$1 > SEL$C6D21C0F > SEL$9E7FBB94 Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$9E7FBB94" ("c"@"SEL$1" ((("r"@"SEL$1" "ur"@"SEL$1") "s"@"SEL$1") "o"@"SEL$1"))) USE_HASH(@"SEL$9E7FBB94" ("ur"@"SEL$1" "s"@"SEL$1" "r"@"SEL$1" "o"@"SEL$1")) FULL(@"SEL$9E7FBB94" "c"@"SEL$1") USE_NL(@"SEL$9E7FBB94" "o"@"SEL$1") USE_NL(@"SEL$9E7FBB94" "s"@"SEL$1") PQ_DISTRIBUTE(@"SEL$9E7FBB94" "s"@"SEL$1" BC2HOST NONE) USE_MERGE(@"SEL$9E7FBB94" "ur"@"SEL$1") FULL(@"SEL$9E7FBB94" "r"@"SEL$1") INDEX(@"SEL$9E7FBB94" "ur"@"SEL$1" "userid") INDEX(@"SEL$9E7FBB94" "s"@"SEL$1" "primary") INDEX(@"SEL$9E7FBB94" "o"@"SEL$1" "cascadeident") OUTER_TO_INNER(@"SEL$1") PRED_DEDUCE(@"SEL$C6D21C0F") OPTIMIZER_FEATURES_ENABLE('4.3.2.0') END_OUTLINE_DATA */ Optimization Info: ------------------------------------- c: table_rows:1237 physical_range_rows:2480 logical_range_rows:1240 index_back_rows:0 output_rows:1240 table_dop:1 dop_method:Table DOP avaiable_index_name:[topid, parentid, cascadeident, ce_organization] pruned_index_name:[topid, parentid, cascadeident] stats info:[version=2025-07-01 22:00:07.837685, is_locked=0, is_expired=0] dynamic sampling level:0 estimation method:[OPTIMIZER STATISTICS, STORAGE] r: table_rows:92 physical_range_rows:93 logical_range_rows:93 index_back_rows:0 output_rows:90 table_dop:1 dop_method:Table DOP avaiable_index_name:[userid, ce_role] pruned_index_name:[userid] stats info:[version=2025-05-22 22:00:33.472259, is_locked=0, is_expired=0] dynamic sampling level:0 estimation method:[OPTIMIZER STATISTICS, STORAGE] ur: table_rows:8506 physical_range_rows:6 logical_range_rows:6 index_back_rows:6 output_rows:6 table_dop:1 dop_method:Table DOP avaiable_index_name:[organizationid, roleid, userid, ce_user_role_organization] pruned_index_name:[organizationid, roleid] unstable_index_name:[ce_user_role_organization] stats info:[version=2025-07-01 22:00:09.578817, is_locked=0, is_expired=0] dynamic sampling level:0 estimation method:[OPTIMIZER STATISTICS, STORAGE] s: table_rows:1237 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:[topid, parentid, cascadeident, ce_organization] pruned_index_name:[topid, parentid, cascadeident] stats info:[version=2025-07-01 22:00:07.837685, is_locked=0, is_expired=0] dynamic sampling level:0 estimation method:[OPTIMIZER STATISTICS] o: table_rows:1237 physical_range_rows:412 logical_range_rows:412 index_back_rows:412 output_rows:412 table_dop:1 dop_method:Table DOP avaiable_index_name:[topid, parentid, cascadeident, ce_organization] pruned_index_name:[topid, parentid] stats info:[version=2025-07-01 22:00:07.837685, is_locked=0, is_expired=0] dynamic sampling level:0 estimation method:[OPTIMIZER STATISTICS] Plan Type: DISTRIBUTED Parameters: :0 => ':C' :1 => 1 :2 => '%' :3 => 0 :4 => 1 Note: Degree of Parallelisim is 1 because of table property