While analyzing a Explain plan i found UnionAll like below. can some help me to understand why it is there as i have not Union in my query.
Gather partitions:all est_rows:1 alias:remote_0
Project [SUBQ_VWW_0.level2_desc, SUBQ_VWW_0.level3_desc, SUBQ_VWW_0.level4_desc, SUBQ_VWW_0.org_code, SUBQ_VWW_0.customer_number, SUBQ_VWW_0.customer_name, SUBQ_VWW_0.customer_class_code, SUBQ_VWW_0.trx_type_code, SUBQ_VWW_0.document_type_desc, SUBQ_VWW_0.customer_trx_id, SUBQ_VWW_0.trx_number, SUBQ_VWW_0.invoice_currency_code, SUBQ_VWW_0.cost_center_segment, SUBQ_VWW_0.balancing_segment, SUBQ_VWW_0.trx_date, SUBQ_VWW_0.due_date, SUBQ_VWW_0.invoice_amount, SUBQ_VWW_0.amount_remaining] est_rows:1 est_select_cost:410
TableScan 1tmp AS SUBQ_VWW_0 storage:list stream:yes
UnionAll
|—Project [r8.level2_desc, r8.level3_desc, r8.level4_desc, r8.org_code, r8.customer_number, r8.customer_name, r8.customer_class_code, r8.trx_type_code, r8.document_type_desc, r8.customer_trx_id, r8.trx_number, r8.invoice_currency_code, r8.cost_center_segment, r8.balancing_segment, r8.trx_date, r8.due_date, r8.invoice_amount, r8.amount_remaining]
| TableScan r8 storage:list stream:yes
| Broadcast [r7.i0, r7.level2_desc, r7.level3_desc, r7.level4_desc, r7.org_code, r7.customer_number, r7.customer_name, r7.customer_class_code, r7.trx_type_code, r7.document_type_desc, r7.customer_trx_id, r7.trx_number, r7.invoice_currency_code, r7.cost_center_segment, r7.balancing_segment, r7.trx_date, r7.due_date, r7.invoice_amount, r7.amount_remaining, s0.customer_trx_id AS customer_trx_id_20, r7.payment_status_flag, r7.source_system_name, r7.ORG_ID, r7.org_id_22, r7.source_system_name_23, r7.bill_to_custo…] AS r8 distribution:direct
| HashJoin [s0.customer_trx_id = r7.customer_trx_id] type:right
| |—HashTableBuild alias:r7
| | Broadcast [r5.level2_desc, r5.level3_desc, r5.level4_desc, t0.org_code, r5.customer_number, r5.customer_name, r5.customer_class_code, t0.trx_type_code, t0.document_type_desc, t0.customer_trx_id, t0.trx_number, t0.invoice_currency_code, t0.cost_center_segment, t0.balancing_segment, t0.trx_date, t0.due_date, t0.invoice_amount, t0.amount_remaining, t0.payment_status_flag, t0.source_system_name, t0.org_id AS ORG_ID, r5.org_id AS org_id_22, r5.source_system_name AS source_system_name_23, t0.bill_to_customer_id,…] AS r7 distribution:tree est_rows:1
| | HashJoin [t0.org_id = r5.org_id AND t0.source_system_name = r5.source_system_name AND t0.bill_to_customer_id = r5.customer_id AND t0.source_system_name = r5.source_system_name_10]
| | |—HashTableBuild alias:r5
| | | Broadcast [r3.level2_desc, r3.level3_desc, r3.level4_desc, r2.customer_number, r2.customer_name, r2.customer_class_code, r3.org_id, r3.source_system_name, r2.customer_id, r2.source_system_name AS source_system_name_10] AS r5 distribution:tree est_rows:1
| | | HashJoin [r3.source_system_name = r2.source_system_name]
| | | |—HashTableBuild alias:r3
| | | | Repartition [t2.level2_desc, t2.level3_desc, t2.level4_desc, t2.org_id, t2.source_system_name] AS r3 shard_key:[source_system_name] est_rows:1
| | | | Filter [t2.source_system_name = ‘GECARS’ AND t2.level2_desc = ‘Power’]
| | | | ColumnStoreScan analytics.xx_operating_unit_hierarchies AS t2, KEY op_u_hier_Key (org_id, hierarchy_type, source_system_name) USING CLUSTERED COLUMNSTORE est_table_rows:24,562 est_filtered:1
| | | TableScan r2 storage:list stream:yes est_table_rows:24
| | | Repartition [t1.customer_number, t1.customer_name, t1.customer_class_code, t1.customer_id, t1.source_system_name] AS r2 shard_key:[source_system_name] est_rows:24
| | | Filter [t1.source_system_name = ‘GECARS’ AND t1.customer_class_code IN (…)]
| | | ColumnStoreScan analytics.xx_ar_customers AS t1, KEY AR_Cust_Key (status, source_system_name, customer_class_code) USING CLUSTERED COLUMNSTORE est_table_rows:4,496,465 est_filtered:25
| | Filter [t0.payment_status_flag <> ‘Y’ AND t0.amount_remaining <> 0 AND t0.source_system_name = ‘GECARS’ AND CASE WHEN IFNULL(t0.source_system_name,’-99’) IN (…) THEN IFNULL(t0.org_id,-99) IN (…) WHEN t0.balancing_segment IN (…) THEN 0 WHEN t0.source_system_name IN (…) THEN 1 WHEN t0.source_system_name IN (…) THEN t0.balancing_segment IN (…) END ]
| | ColumnStoreScan analytics.xx_ar_invoices AS t0, KEY ar_inv_Key (payment_status_flag, source_system_name, org_id) USING CLUSTERED COLUMNSTORE est_table_rows:104,648,706 est_filtered:598,209
| TableScan 0tmp AS s0 storage:list stream:yes est_table_rows:446,186
| Project [r0.customer_trx_id, 0 AS ConstIntCol] est_rows:446,186 est_select_cost:1,920,524
| HashJoin [r1.dispute_id = r0.dispute_id]
| |—HashTableBuild alias:r1
| | Repartition [t4.dispute_id] AS r1 shard_key:[dispute_id] est_rows:287,197
| | Filter [t4.dispute_status_name <> ‘CLOSED’]
| | ColumnStoreScan analytics.xx_ar_collection_disputes AS t4, KEY ar_col_disp_Key (source_system_name, dispute_status_name, dispute_id) USING CLUSTERED COLUMNSTORE est_table_rows:415,137 est_filtered:287,197
| TableScan r0 storage:list stream:yes est_table_rows:673,065
| Repartition [t3.customer_trx_id, t3.dispute_id] AS r0 shard_key:[dispute_id] est_rows:673,065
| ColumnStoreScan analytics.xx_ar_collection_invoices AS t3, KEY ar_col_inv_Key (source_system_name, dispute_id, customer_trx_id) USING CLUSTERED COLUMNSTORE est_table_rows:673,065 est_filtered:673,065
±–Project [r9.level2_desc, r9.level3_desc, r9.level4_desc, r9.org_code, r9.customer_number, r9.customer_name, r9.customer_class_code, r9.trx_type_code, r9.document_type_desc, r9.customer_trx_id, r9.trx_number, r9.invoice_currency_code, r9.cost_center_segment, r9.balancing_segment, r9.trx_date, r9.due_date, r9.invoice_amount, r9.amount_remaining]
Filter [r9.source_system_name_23 = ‘GECARS’ AND r9.source_system_name_10 = r9.source_system_name_23 AND r9.source_system_name_10 = ‘GECARS’ AND r9.source_system_name = r9.source_system_name_10 AND r9.bill_to_customer_id = r9.customer_id AND r9.source_system_name_23 = r9.source_system_name AND r9.org_id_22 = r9.ORG_ID AND CASE WHEN IFNULL(r9.source_system_name,’-99’) IN (…) THEN IFNULL(r9.ORG_ID,-99) IN (…) WHEN r9.balancing_segment IN (…) THEN 0 WHEN r9.source_system_name IN (…) THEN 1 WHEN r9.s…]
HashGroupBy [COUNT(*) AS $0] groups:[r9.i0]
TableScan r9 storage:list stream:yes
Broadcast [r7.i0, r7.level2_desc, r7.level3_desc, r7.level4_desc, r7.org_code, r7.customer_number, r7.customer_name, r7.customer_class_code, r7.trx_type_code, r7.document_type_desc, r7.customer_trx_id, r7.trx_number, r7.invoice_currency_code, r7.cost_center_segment, r7.balancing_segment, r7.trx_date, r7.due_date, r7.invoice_amount, r7.amount_remaining, s0.customer_trx_id AS customer_trx_id_20, r7.payment_status_flag, r7.source_system_name, r7.ORG_ID, r7.org_id_22, r7.source_system_name_23, r7.bill_to_custo…] AS r8 distribution:direct
HashJoin [s0.customer_trx_id = r7.customer_trx_id] type:right
|—HashTableBuild alias:r7
| Broadcast [r5.level2_desc, r5.level3_desc, r5.level4_desc, t0.org_code, r5.customer_number, r5.customer_name, r5.customer_class_code, t0.trx_type_code, t0.document_type_desc, t0.customer_trx_id, t0.trx_number, t0.invoice_currency_code, t0.cost_center_segment, t0.balancing_segment, t0.trx_date, t0.due_date, t0.invoice_amount, t0.amount_remaining, t0.payment_status_flag, t0.source_system_name, t0.org_id AS ORG_ID, r5.org_id AS org_id_22, r5.source_system_name AS source_system_name_23, t0.bill_to_customer_id,…] AS r7 distribution:tree est_rows:1
| HashJoin [t0.org_id = r5.org_id AND t0.source_system_name = r5.source_system_name AND t0.bill_to_customer_id = r5.customer_id AND t0.source_system_name = r5.source_system_name_10]
| |—HashTableBuild alias:r5
| | Broadcast [r3.level2_desc, r3.level3_desc, r3.level4_desc, r2.customer_number, r2.customer_name, r2.customer_class_code, r3.org_id, r3.source_system_name, r2.customer_id, r2.source_system_name AS source_system_name_10] AS r5 distribution:tree est_rows:1
| | HashJoin [r3.source_system_name = r2.source_system_name]
| | |—HashTableBuild alias:r3
| | | Repartition [t2.level2_desc, t2.level3_desc, t2.level4_desc, t2.org_id, t2.source_system_name] AS r3 shard_key:[source_system_name] est_rows:1
| | | Filter [t2.source_system_name = ‘GECARS’ AND t2.level2_desc = ‘Power’]
| | | ColumnStoreScan analytics.xx_operating_unit_hierarchies AS t2, KEY op_u_hier_Key (org_id, hierarchy_type, source_system_name) USING CLUSTERED COLUMNSTORE est_table_rows:24,562 est_filtered:1
| | TableScan r2 storage:list stream:yes est_table_rows:24
| | Repartition [t1.customer_number, t1.customer_name, t1.customer_class_code, t1.customer_id, t1.source_system_name] AS r2 shard_key:[source_system_name] est_rows:24
| | Filter [t1.source_system_name = ‘GECARS’ AND t1.customer_class_code IN (…)]
| | ColumnStoreScan analytics.xx_ar_customers AS t1, KEY AR_Cust_Key (status, source_system_name, customer_class_code) USING CLUSTERED COLUMNSTORE est_table_rows:4,496,465 est_filtered:25
| Filter [t0.payment_status_flag <> ‘Y’ AND t0.amount_remaining <> 0 AND t0.source_system_name = ‘GECARS’ AND CASE WHEN IFNULL(t0.source_system_name,’-99’) IN (…) THEN IFNULL(t0.org_id,-99) IN (…) WHEN t0.balancing_segment IN (…) THEN 0 WHEN t0.source_system_name IN (…) THEN 1 WHEN t0.source_system_name IN (…) THEN t0.balancing_segment IN (…) END ]
| ColumnStoreScan analytics.xx_ar_invoices AS t0, KEY ar_inv_Key (payment_status_flag, source_system_name, org_id) USING CLUSTERED COLUMNSTORE est_table_rows:104,648,706 est_filtered:598,209
TableScan 0tmp AS s0 storage:list stream:yes est_table_rows:446,186
Project [r0.customer_trx_id, 0 AS ConstIntCol] est_rows:446,186 est_select_cost:1,920,524
HashJoin [r1.dispute_id = r0.dispute_id]
|—HashTableBuild alias:r1
| Repartition [t4.dispute_id] AS r1 shard_key:[dispute_id] est_rows:287,197
| Filter [t4.dispute_status_name <> ‘CLOSED’]
| ColumnStoreScan analytics.xx_ar_collection_disputes AS t4, KEY ar_col_disp_Key (source_system_name, dispute_status_name, dispute_id) USING CLUSTERED COLUMNSTORE est_table_rows:415,137 est_filtered:287,197
TableScan r0 storage:list stream:yes est_table_rows:673,065
Repartition [t3.customer_trx_id, t3.dispute_id] AS r0 shard_key:[dispute_id] est_rows:673,065
ColumnStoreScan analytics.xx_ar_collection_invoices AS t3, KEY ar_col_inv_Key (source_system_name, dispute_id, customer_trx_id) USING CLUSTERED COLUMNSTORE est_table_rows:673,065 est_filtered:673,065
What’s the query text?