Explain spool product joins

Database
Enthusiast

Explain spool product joins

Explanation
1) First, we lock ProdBBYdb.TBEND_SA_PYMT for access, we lock
PRODBBYDB.TBEND_BU_FISC_DT for access, we lock
PRODBBYDB.TBEND_FI_FIN_PLAN for access, we lock
PRODBBYDB.TBEND_FI_TNDR_TYP_XREF for access, and we lock
PRODETLSTAGE.DTL for access.
2) Next, we do an all-AMPs RETRIEVE step from 52 partitions of
ProdBBYdb.TBEND_SA_PYMT with a condition of (
"(ProdBBYdb.TBEND_SA_PYMT.SLS_BSNS_DT <= DATE '2008-05-27') AND
(ProdBBYdb.TBEND_SA_PYMT.SLS_BSNS_DT >= DATE '2008-04-06')") into
Spool 8 (all_amps), which is built locally on the AMPs. Then we
do a SORT to order Spool 8 by row hash. The size of Spool 8 is
estimated with high confidence to be 46,707,403 rows. The
estimated time for this step is 10.76 seconds.
3) We do an all-AMPs JOIN step from Spool 8 (Last Use) by way of a
RowHash match scan, which is joined to PRODETLSTAGE.DTL by way of
a RowHash match scan with no residual conditions. Spool 8 and
PRODETLSTAGE.DTL are joined using a merge join, with a join
condition of ("SLS_KEY = PRODETLSTAGE.DTL.SLS_KEY"). The input
table PRODETLSTAGE.DTL will not be cached in memory. The result
goes into Spool 7 (all_amps), which is built locally on the AMPs.
The size of Spool 7 is estimated with low confidence to be
46,977,045 rows. The estimated time for this step is 8.14 seconds.
4) We execute the following steps in parallel.
1) We do an all-AMPs SUM step to aggregate from Spool 7 (Last
Use) by way of an all-rows scan, and the grouping identifier
in field 1. Aggregate Intermediate Results are computed
locally, then placed in Spool 9. The size of Spool 9 is
estimated with no confidence to be 35,232,784 rows. The
estimated time for this step is 34.04 seconds.
2) We do an all-AMPs RETRIEVE step from
PRODBBYDB.TBEND_FI_TNDR_TYP_XREF by way of an all-rows scan
with a condition of ("(NOT
(PRODBBYDB.TBEND_FI_TNDR_TYP_XREF.TNDR_TYP_CDE IS NULL )) AND
(NOT (PRODBBYDB.TBEND_FI_TNDR_TYP_XREF.TNDR_SUBTYP_CDE IS
NULL ))") into Spool 11 (all_amps), which is duplicated on
all AMPs. The size of Spool 11 is estimated with high
confidence to be 14,400 rows. The estimated time for this
step is 0.04 seconds.
5) We do an all-AMPs JOIN step from PRODBBYDB.TBEND_BU_FISC_DT by way
of an all-rows scan with a condition of (
"(PRODBBYDB.TBEND_BU_FISC_DT.CALNDR_DT <= DATE '2008-05-27') AND
(PRODBBYDB.TBEND_BU_FISC_DT.CALNDR_DT >= DATE '2008-04-06')"),
which is joined to Spool 11 (Last Use) by way of an all-rows scan.
PRODBBYDB.TBEND_BU_FISC_DT and Spool 11 are joined using a product
join, [color=#FF0000]with a join condition of ("(1=1)").[/color] The result goes into
Spool 12 (all_amps), which is duplicated on all AMPs. Then we do
a SORT to partition by rowkey. The size of Spool 12 is estimated
with low confidence to be 748,800 rows. The estimated time for
this step is 0.29 seconds.
6) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step from Spool 12 (Last Use) by way
of an all-rows scan, which is joined to 52 partitions of
ProdBBYdb.TBEND_SA_PYMT with a condition of (
"(ProdBBYdb.TBEND_SA_PYMT.SLS_BSNS_DT >= DATE '2008-04-06')
AND (ProdBBYdb.TBEND_SA_PYMT.SLS_BSNS_DT <= DATE
'2008-05-27')"). Spool 12 and ProdBBYdb.TBEND_SA_PYMT are
[color=#FF0000]joined using a product join,[/color] with a join condition of (
"(TNDR_SUBTYP_CDE = ProdBBYdb.TBEND_SA_PYMT.TNDR_SUBTYP_CDE)
AND ((TNDR_TYP_CDE = ProdBBYdb.TBEND_SA_PYMT.TNDR_TYP_CDE)
AND (CALNDR_DT = ProdBBYdb.TBEND_SA_PYMT.SLS_BSNS_DT ))")
enhanced by dynamic partition elimination. The input table
ProdBBYdb.TBEND_SA_PYMT will not be cached in memory, but it
is eligible for synchronized scanning. The result goes into
Spool 13 (all_amps), which is built locally on the AMPs.
Then we do a SORT to order Spool 13 by row hash. The size of
Spool 13 is estimated with low confidence to be 2,136,010
rows. The estimated time for this step is 5.58 seconds.
2) We do an all-AMPs RETRIEVE step from Spool 9 (Last Use) by
way of an all-rows scan into Spool 14 (all_amps), which is
redistributed by hash code to all AMPs. Then we do a SORT to
order Spool 14 by row hash. The size of Spool 14 is
estimated with no confidence to be 35,232,784 rows. The
estimated time for this step is 16.13 seconds.
7) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step from Spool 13 (Last Use) by way
of an all-rows scan, which is joined to Spool 14 (Last Use)
by way of an all-rows scan. Spool 13 and Spool 14 are joined
using an inclusion merge join, with a join condition of (
"(Field_4 = Field_4) AND ((SLS_KEY = SLS_KEY) AND
(SLS_BSNS_DT = SLS_BSNS_DT ))"). The result goes into Spool
15 (all_amps), which is redistributed by hash code to all
AMPs. Then we do a SORT to order Spool 15 by row hash. The
size of Spool 15 is estimated with index join confidence to
be 2,136,010 rows. The estimated time for this step is 2.05
seconds.
2) We do an all-AMPs RETRIEVE step from
PRODBBYDB.TBEND_FI_FIN_PLAN by way of an all-rows scan with
no residual conditions into Spool 16 (all_amps), which is
redistributed by hash code to all AMPs. Then we do a SORT to
order Spool 16 by row hash. The size of Spool 16 is
estimated with low confidence to be 5,280 rows. The
estimated time for this step is 0.01 seconds.
8) We do an all-AMPs JOIN step from Spool 15 (Last Use) by way of a
RowHash match scan, which is joined to Spool 16 (Last Use) by way
of a RowHash match scan. Spool 15 and Spool 16 are left outer
joined using a merge join, with a join condition of (
"(FISC_YR_NBR = FISC_YR_NBR) AND ((FISC_MTH_NBR = FISC_MTH_NBR)
AND ((TNDR_ID = TNDR_ID) AND ((TRANSLATE((( CASE WHEN (NOT
(PYMT_FIN_CDE IS NULL )) THEN (PYMT_FIN_CDE) ELSE (' 0') END
))USING LATIN_TO_UNICODE)(FLOAT, FORMAT
'-9.99999999999999E-999'))= (( CASE WHEN (NOT (PYMT_FIN_PLAN_NBR
IS NULL )) THEN (PYMT_FIN_PLAN_NBR) ELSE (0.) END) ))))"). The
result goes into Spool 4 (all_amps), which is built locally on the
AMPs. The size of Spool 4 is estimated with low confidence to be
9,146,506 rows. The estimated time for this step is 0.71 seconds.
9) We do an all-AMPs SUM step to aggregate from Spool 4 (Last Use) by
way of an all-rows scan, and the grouping identifier in field 1.
Aggregate Intermediate Results are computed globally, then placed
in Spool 17. The size of Spool 17 is estimated with no confidence
to be 6,859,880 rows. The estimated time for this step is 4.09
seconds.
10) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from Spool 17 (Last Use) by
way of an all-rows scan into Spool 2 (all_amps), which is
built locally on the AMPs. The size of Spool 2 is estimated
with no confidence to be 6,859,880 rows. The estimated time
for this step is 0.69 seconds.
2) We do an all-AMPs SUM step to aggregate from
PRODETLSTAGE.TBENW_SA_MTRX_DLY_DTL by way of an all-rows scan
with no residual conditions, and the grouping identifier in
field 1 locking for access. Aggregate Intermediate Results
are computed locally, then placed in Spool 20. The input
table will not be cached in memory, but it is eligible for
synchronized scanning. The size of Spool 20 is estimated with
high confidence to be 19,267,864 rows. The estimated time for
this step is 19.42 seconds.
11) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from Spool 20 (Last Use) by
way of an all-rows scan into Spool 1 (all_amps), which is
built locally on the AMPs. The size of Spool 1 is estimated
with high confidence to be 19,267,864 rows. The estimated
time for this step is 1.65 seconds.
2) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way
of an all-rows scan into Spool 23 (all_amps), which is
redistributed by hash code to all AMPs. Then we do a SORT to
order Spool 23 by row hash. The size of Spool 23 is estimated
with no confidence to be 6,859,880 rows. The estimated time
for this step is 1.72 seconds.
12) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step from Spool 23 (Last Use) by way of
a RowHash match scan, which is joined to PRODETLSTAGE.SLSGRP
by way of a RowHash match scan with a condition of (
"PRODETLSTAGE.SLSGRP.GROUP_SRNO = 1") locking
PRODETLSTAGE.SLSGRP for access. Spool 23 and
PRODETLSTAGE.SLSGRP are joined using a merge join, with a join
condition of ("PRODETLSTAGE.SLSGRP.SLS_KEY = SLS_KEY"). The
input table PRODETLSTAGE.SLSGRP will not be cached in memory.
The result goes into Spool 24 (all_amps), which is built
locally on the AMPs. The size of Spool 24 is estimated with
no confidence to be 6,859,880 rows. The estimated time for
this step is 5.35 seconds.
2) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way
of an all-rows scan into Spool 25 (all_amps), which is
redistributed by hash code to all AMPs. Then we do a SORT to
order Spool 25 by row hash. The size of Spool 25 is estimated
with high confidence to be 19,267,864 rows. The estimated
time for this step is 4.58 seconds.
13) We do an all-AMPs JOIN step from Spool 24 (Last Use) by way of a
RowHash match scan, which is joined to Spool 25 (Last Use) by way
of a RowHash match scan. Spool 24 and Spool 25 are joined using a
merge join, with a join condition of ("(SLS_KEY = SLS_KEY) AND
(SLS_KEY = SLS_KEY)"). The result goes into Spool 22 (group_amps),
which is built locally on the AMPs. The size of Spool 22 is
estimated with no confidence to be 69,933,876 rows. The estimated
time for this step is 5.93 seconds.
14) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 22 are sent back to the user as the result
of statement 1. The total estimated time is 1 minute and 49
seconds.
The first product join is clear as it is a small table join n need to improe performance
Can anybody clarify for the othr product join it is b/w spool and a large table ?
How cud it be improved ?
4 REPLIES
Enthusiast

Re: Explain spool product joins

Can anybody explain grouping identifier in field 1 ?
Wht does this mean ? Wht field or column it refers to ?
Enthusiast

Re: Explain spool product joins

This comes in the explain plan :

5) We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by
way of an all-rows scan,
Aggregate Intermediate Results are computed globally, then placed
in Spool 8. The size of Spool 8 is estimated with index join
confidence to be 839,862 rows. The estimated time for this step
is 0.24 seconds.

can anybody explain grouping identifier in field 1 ?What it is referring to ?
Enthusiast

Re: Explain spool product joins

5) We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by
way of an all-rows scan, and the
Aggregate Intermediate Results are computed globally, then placed
in Spool 8. The size of Spool 8 is estimated with index join
confidence to be 839,862 rows. The estimated time for this step
is 0.24 seconds.

GROUPING IDENTIFIED IN FIELD 1 CAN ANY BODY REPLY ?
Enthusiast

Re: Explain spool product joins

grouping identifier
in field 1

ex--
sel * from a group by filed1;

filed1 is column in ur query used for grouping or even ordering.