I am trying to understand the EXPLAIN of the below query and I could understand everything except the product join that is happening at the
end.
Could someone help me understand why a product join is happening in the end.
This is just a part of the query. I have few more LEFT JOINS and the query is running out of spool (due to the product Joins happening)
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock BAKTHRO.PLM_PROD_SPEC in view PLM_SPEC_ALLERGEN_VW for access, and we lock BAKTHRO.A in view PLM_SPEC_ALLERGEN_VW for
access.
| |
| | 2) | Next, we do a single-AMP SUM step to aggregate from BAKTHRO.P in view RPT_SPEC_WEIGHT by way of the primary index "BAKTHRO.P in view
RPT_SPEC_WEIGHT.PRODUCT_GN_NBR = '2874.80469 '" with no residual conditions , grouping by field1 (BAKTHRO.P.PRODUCT_GN_NBR) locking row for
access. Aggregate Intermediate Results are computed locally, then placed in Spool 5. The size of Spool 5 is estimated with high confidence to be
1 row (43 bytes). The estimated time for this step is 0.00 seconds.
| |
| | 3) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do a single-AMP RETRIEVE step from Spool 5 (Last Use) by way of the hash value of "BAKTHRO.P in view RPT_SPEC_WEIGHT.PRODUCT_GN_NBR =
'2874.80469 '" into Spool 3 (used to materialize view, derived table or table function A11) (one-amp), which is built locally on that AMP. The
size of Spool 3 is estimated with high confidence to be 1 row (36 bytes). The estimated time for this step is 0.01 seconds.
| | |
| | |
| | 2) | We do an all-AMPs SUM step to aggregate from BAKTHRO.PLM_PROD_SPEC in view PLM_SPEC_ALLERGEN_VW by way of an all-rows scan with a
condition of ("NOT (BAKTHRO.PLM_PROD_SPEC in view PLM_SPEC_ALLERGEN_VW.PRODUCT_GN_NBR IS NULL)"), grouping by field1 (
BAKTHRO.PLM_PROD_SPEC.PRODUCT_GN_NBR). Aggregate Intermediate Results are computed globally, then placed in Spool 8. The size of Spool 8 is
estimated with high confidence to be 46,716 rows (2,055,504 bytes). The estimated time for this step is 0.14 seconds.
| | |
| | 4) | We do an all-AMPs RETRIEVE step from Spool 8 (Last Use) by way of an all-rows scan into Spool 1 (used to materialize view, derived table or
table function MAJOR) (all_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with high confidence to be 46,716 rows
(1,728,492 bytes). The estimated time for this step is 0.03 seconds.
| |
| | 5) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of an all-rows scan into Spool 12 (all_amps), which is redistributed by
the hash code of (BAKTHRO.PLM_PROD_SPEC.PRODUCT_GN_NBR, BAKTHRO.PLM_PROD_SPEC.MAJOR_VER) to all AMPs. The size of Spool 12 is estimated with high
confidence to be 46,716 rows (1,354,764 bytes). The estimated time for this step is 0.05 seconds.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from BAKTHRO.MINOR in view PLM_SPEC_ALLERGEN_VW by way of an all-rows scan with no residual conditions
locking for access into Spool 13 (all_amps), which is redistributed by the hash code of (BAKTHRO.MINOR.PRODUCT_GN_NBR, BAKTHRO.MINOR.MAJOR_VER) to
all AMPs. The size of Spool 13 is estimated with high confidence to be 98,112 rows (3,237,696 bytes). The estimated time for this step is 0.11
seconds.
| | |
| | 6) | We do an all-AMPs JOIN step from Spool 12 (Last Use) by way of an all-rows scan, which is joined to Spool 13 (Last Use) by way of an all-
rows scan. Spool 12 and Spool 13 are joined using a single partition hash join, with a join condition of ("(PRODUCT_GN_NBR = PRODUCT_GN_NBR) AND
(MAJOR_VER = MAJOR_VER)"). The result goes into Spool 11 (all_amps), which is built locally on the AMPs. The size of Spool 11 is estimated with
low confidence to be 64,282 rows (2,249,870 bytes). The estimated time for this step is 0.04 seconds.
| |
| | 7) | We do an all-AMPs SUM step to aggregate from Spool 11 (Last Use) by way of an all-rows scan , grouping by field1
(BAKTHRO.MINOR.PRODUCT_GN_NBR ,BAKTHRO.PLM_PROD_SPEC.MAJOR_VER). Aggregate Intermediate Results are computed globally, then placed in Spool 14.
The size of Spool 14 is estimated with low confidence to be 46,716 rows (2,662,812 bytes). The estimated time for this step is 0.08 seconds.
| |
| | 8) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs RETRIEVE step from Spool 14 (Last Use) by way of an all-rows scan into Spool 2 (used to materialize view, derived table
or table function CUR) (all_amps), which is built locally on the AMPs. The size of Spool 2 is estimated with low confidence to be 46,716 rows
(1,915,356 bytes). The estimated time for this step is 0.03 seconds.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from BAKTHRO.SPEC in view PLM_SPEC_ALLERGEN_VW by way of a traversal of index # 4 extracting row ids only
with a residual condition of ("(BAKTHRO.SPEC in view PLM_SPEC_ALLERGEN_VW.PRODUCT_GN_NBR = '2874.80469 ') AND ((NOT (BAKTHRO.SPEC in view
PLM_SPEC_ALLERGEN_VW.SPEC_ID IS NULL )) AND (NOT (BAKTHRO.SPEC in view PLM_SPEC_ALLERGEN_VW.PRODUCT_GN_NBR IS NULL )))") locking for access into
Spool 19 (all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 19 by row id eliminating duplicate rows. The size of
Spool 19 is estimated with high confidence to be 0 row. The estimated time for this step is 0.03 seconds.
| | |
| | 9) | We do an all-AMPs JOIN step from BAKTHRO.SPEC in view PLM_SPEC_ALLERGEN_VW by way of row ids from Spool 19 (Last Use) with a residual
condition of ("BAKTHRO.SPEC in view PLM_SPEC_ALLERGEN_VW.SNAPSHOT_IND = 'NO '"), which is joined to BAKTHRO.A in view PLM_SPEC_ALLERGEN_VW by way
of a RowHash match scan with no residual conditions. BAKTHRO.SPEC and BAKTHRO.A are joined using a merge join, with a join condition of
("BAKTHRO.A.spec_id = BAKTHRO.SPEC.SPEC_ID"). The result goes into Spool 20 (all_amps), which is duplicated on all AMPs. The size of Spool 20 is
estimated with low confidence to be 1,584 rows (52,272 bytes). The estimated time for this step is 0.06 seconds.
| |
| | 10) | We do an all-AMPs JOIN step from Spool 20 (Last Use) by way of an all-rows scan, which is joined to Spool 2 (Last Use) by way of an all-
rows scan. Spool 20 and Spool 2 are joined using a single partition hash_ join, with a join condition of ("(PRODUCT_GN_NBR = PRODUCT_GN_NBR) AND
((MAJOR_VER = MAJOR_VER) AND (MINOR_VER = MINOR_VER ))"). The result goes into Spool 21 (all_amps), which is duplicated on all AMPs. The size of
Spool 21 is estimated with low confidence to be 1,584 rows (44,352 bytes). The estimated time for this step is 0.03 seconds.
| |
| | 11) | We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of the hash value of "BAKTHRO.P in view RPT_SPEC_WEIGHT.PRODUCT_GN_NBR =
'2874.80469 '" with an additional condition of ("A11.PRODUCT_GN_NBR = '2874.80469 '"), which is joined to Spool 21 (Last Use) by way of an all-
rows scan. Spool 3 and Spool 21 are left outer joined using a product join, with condition(s) used for non-matching on left table
("PRODUCT_GN_NBR = '2874.80469 '"), with a join condition of ("PRODUCT_GN_NBR = PRODUCT_GN_NBR"). The result goes into Spool 18 (all_amps), which
is built locally on the AMPs. The size of Spool 18 is estimated with low confidence to be 33 rows (495 bytes). The estimated time for this step
is 0.02 seconds.
| |
| | 12) | We do an all-AMPs SUM step to aggregate from Spool 18 (Last Use) by way of a cylinder index scan. Aggregate Intermediate Results are
computed globally, then placed in Spool 22. The size of Spool 22 is estimated with high confidence to be 1 row (23 bytes). The estimated time
for this step is 0.03 seconds.
| |
| | 13) | We do an all-AMPs RETRIEVE step from Spool 22 (Last Use) by way of an all-rows scan into Spool 16 (group_amps), which is built locally on
the AMPs. The size of Spool 16 is estimated with high confidence to be 1 row (25 bytes). The estimated time for this step is 0.01 seconds.
| |
| | 14) | Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.
| |
| | -> | The contents of Spool 16 are sent back to the user as the result of statement 1. The total estimated time is 0.57 seconds.
| |
