how to Optimize Qquery - No spool space

Database
Enthusiast

how to Optimize Qquery - No spool space

Hi, i need help in understanding & optimizing query to avoid no spool issues.

following is the query & explain plan.

Explain plan is showing no/low confidence on join, how to over come it

any suggestions would be of great help, thank you.

 

 SELECT P01.PTHR_SRV_PART_R      
              ,P01.PTHR_MOTORCRAFT_R    
              ,P05C.PTHR_PRODUCT_1_C    
              ,P02C.PTHR_DESC_X         
              ,P05B.PTHR_PRODUCT_1_C                                        
              ,P02B.PTHR_DESC_X                                             
         FROM RSMU_VIEW.SGFDM06 M06                                         
             ,RSMU_VIEW.SGFDP01 P01                                         
             ,RSMU_VIEW.SGFDP02 P02B                                        
             ,RSMU_VIEW.SGFDP02 P02C                                        
             ,RSMU_VIEW.SGFDP05 P05A                                        
             ,RSMU_VIEW.SGFDP05 P05B                                        
             ,RSMU_VIEW.SGFDP05 P05C                                        
             ,SRET_DATA.SOESA05_PART_DMY                                    
        WHERE P01.PTHR_SRV_PART_R      = A05_SVC_PART                       
              AND   M06.COUNTRY_ISO3_C       = 'USA'                        
              AND   M06.SPRFXPRT_PREFIX_R    = P01.SPRFXPRT_PREFIX_R        
              AND   M06.SBASEPRT_NBR         = P01.SBASEPRT_NBR             
              AND   M06.SUFXPRT_SUFFIX_NBR   = P01.SUFXPRT_SUFFIX_NBR       
              AND   P01.PTHR_PRODUCT_C       = P05A.PTHR_PRODUCT_C          
              AND   P05A.PTHR_HIRCHY_C       = '2'                          
              AND   P05A.PTHR_PRODUCT_1_C    = P05B.PTHR_PRODUCT_C          
              AND   P05B.PTHR_HIRCHY_C       = '3'                          
              AND   P05B.PTHR_PRODUCT_1_C    = P05C.PTHR_PRODUCT_C          
              AND   P05C.PTHR_HIRCHY_C       = '4'                          
              AND   P02B.COUNTRY_ISO3_C       = 'USA'                     
              AND   P02B.PTHR_HIRCHY_C        = '4'                       
              AND   P05B.PTHR_PRODUCT_1_C    = P02B.PTHR_PRODUCT_C        
              AND   P02C.COUNTRY_ISO3_C       = 'USA'                     
              AND   P02C.PTHR_HIRCHY_C        = '5'                       
              AND   P05C.PTHR_PRODUCT_1_C    = P02C.PTHR_PRODUCT_C        
              ORDER BY 1,2,3,4,5,6                                        
              GROUP BY 1,2,3,4,5,6                                        
;

1) First, we lock SRET_DATA.SOESA05_PART_DMY for access, we lock
RSMU_DATA.SGFDM06_MDSMT_TBL in view RSMU_VIEW.SGFDM06 for access,
we lock RSMU_DATA.SGFDP05_PDSTR_TBL in view RSMU_VIEW.SGFDP05 for
access, we lock RSMU_DATA.SGFDP02_PTHR_TBL in view
RSMU_VIEW.SGFDP02 for access, and we lock
RSMU_DATA.SGFDP01_PROD_TBL in view RSMU_VIEW.SGFDP01 for access.
2) Next, we do an all-AMPs RETRIEVE step from RSMU_DATA.SGFDP05 in
view RSMU_VIEW.SGFDP05 by way of an all-rows scan with a condition
of ("RSMU_DATA.SGFDP05 in view RSMU_VIEW.SGFDP05.PTHR_HIRCHY_C =
'4'") locking for access into Spool 3 (all_amps), which is
redistributed by the hash code of (
RSMU_DATA.SGFDP05.PTHR_PRODUCT_C, RSMU_DATA.SGFDP05.PTHR_HIRCHY_C,
'USA') to all AMPs. Then we do a SORT to order Spool 3 by row
hash. The size of Spool 3 is estimated with high confidence to be
81 rows (2,349 bytes). The estimated time for this step is 0.00
seconds.
3) We do an all-AMPs JOIN step from RSMU_DATA.SGFDP02F in view
RSMU_VIEW.SGFDP02 by way of a RowHash match scan with a condition
of ("(RSMU_DATA.SGFDP02F in view RSMU_VIEW.SGFDP02.PTHR_HIRCHY_C =
'4') AND (RSMU_DATA.SGFDP02F in view
RSMU_VIEW.SGFDP02.COUNTRY_ISO3_C = 'USA')"), which is joined to
Spool 3 (Last Use) by way of a RowHash match scan locking
RSMU_DATA.SGFDP02F for access. RSMU_DATA.SGFDP02F and Spool 3 are
joined using a merge join, with a join condition of (
"(PTHR_PRODUCT_C = RSMU_DATA.SGFDP02F.PTHR_PRODUCT_C) AND
(RSMU_DATA.SGFDP02F.PTHR_HIRCHY_C = PTHR_HIRCHY_C)"). The result
goes into Spool 4 (all_amps), which is redistributed by the hash
code of (RSMU_DATA.SGFDP05.PTHR_PRODUCT_1_C,
RSMU_DATA.SGFDP02F.COUNTRY_ISO3_C, '5') to all AMPs. Then we do a
SORT to order Spool 4 by row hash. The size of Spool 4 is
estimated with low confidence to be 81 rows (6,723 bytes). The
estimated time for this step is 0.01 seconds.
4) We do an all-AMPs JOIN step from RSMU_DATA.SGFDP02F in view
RSMU_VIEW.SGFDP02 by way of a RowHash match scan with a condition
of ("(RSMU_DATA.SGFDP02F in view RSMU_VIEW.SGFDP02.PTHR_HIRCHY_C =
'5') AND (RSMU_DATA.SGFDP02F in view
RSMU_VIEW.SGFDP02.COUNTRY_ISO3_C = 'USA')"), which is joined to
Spool 4 (Last Use) by way of a RowHash match scan locking
RSMU_DATA.SGFDP02F for access. RSMU_DATA.SGFDP02F and Spool 4 are
joined using a merge join, with a join condition of (
"(PTHR_PRODUCT_1_C = RSMU_DATA.SGFDP02F.PTHR_PRODUCT_C) AND
(COUNTRY_ISO3_C = RSMU_DATA.SGFDP02F.COUNTRY_ISO3_C)"). The
result goes into Spool 5 (all_amps), which is duplicated on all
AMPs. The size of Spool 5 is estimated with low confidence to be
5,040 rows (655,200 bytes). The estimated time for this step is
0.01 seconds.
5) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of
an all-rows scan, which is joined to
RSMU_DATA.SGFDM06_MDSMT_TBL in view RSMU_VIEW.SGFDM06 by way
of an all-rows scan with a condition of (
"RSMU_DATA.SGFDM06_MDSMT_TBL in view
RSMU_VIEW.SGFDM06.COUNTRY_ISO3_C = 'USA'"). Spool 5 and
RSMU_DATA.SGFDM06_MDSMT_TBL are joined using a product join,
with a join condition of (
"(RSMU_DATA.SGFDM06_MDSMT_TBL.COUNTRY_ISO3_C = COUNTRY_ISO3_C)
AND (RSMU_DATA.SGFDM06_MDSMT_TBL.COUNTRY_ISO3_C =
COUNTRY_ISO3_C)"). The result goes into Spool 6 (all_amps),
which is redistributed by the hash code of (
RSMU_DATA.SGFDM06_MDSMT_TBL.SPRFXPRT_PREFIX_R,
RSMU_DATA.SGFDM06_MDSMT_TBL.SBASEPRT_NBR,
RSMU_DATA.SGFDM06_MDSMT_TBL.SUFXPRT_SUFFIX_NBR) to all AMPs.
Then we do a SORT to order Spool 6 by row hash. The size of
Spool 6 is estimated with no confidence to be 232,186 rows (
33,899,156 bytes). The estimated time for this step is 4.51
seconds.
2) We do an all-AMPs RETRIEVE step from RSMU_DATA.SGFDP05 in
view RSMU_VIEW.SGFDP05 by way of an all-rows scan with a
condition of ("RSMU_DATA.SGFDP05 in view
RSMU_VIEW.SGFDP05.PTHR_HIRCHY_C = '3'") locking for access
into Spool 7 (all_amps), which is duplicated on all AMPs.
The size of Spool 7 is estimated with high confidence to be
85,344 rows (2,304,288 bytes). The estimated time for this
step is 0.01 seconds.
6) We do an all-AMPs JOIN step from Spool 7 (Last Use) by way of an
all-rows scan, which is joined to RSMU_DATA.SGFDP05 in view
RSMU_VIEW.SGFDP05 by way of an all-rows scan with a condition of (
"RSMU_DATA.SGFDP05 in view RSMU_VIEW.SGFDP05.PTHR_HIRCHY_C = '2'")
locking RSMU_DATA.SGFDP05 for access. Spool 7 and
RSMU_DATA.SGFDP05 are joined using a dynamic hash join, with a
join condition of ("RSMU_DATA.SGFDP05.PTHR_PRODUCT_1_C =
PTHR_PRODUCT_C"). The result goes into Spool 8 (all_amps), which
is duplicated on all AMPs. Then we do a SORT to order Spool 8 by
the hash code of (RSMU_DATA.SGFDP05.PTHR_PRODUCT_1_C,
RSMU_DATA.SGFDP05.PTHR_PRODUCT_C,
RSMU_DATA.SGFDP05.PTHR_PRODUCT_1_C). The size of Spool 8 is
estimated with low confidence to be 231,504 rows (6,482,112 bytes).
The estimated time for this step is 0.02 seconds.
7) We do an all-AMPs JOIN step from RSMU_DATA.SGFDP01_PROD_TBL in
view RSMU_VIEW.SGFDP01 by way of a RowHash match scan with no
residual conditions, which is joined to Spool 6 (Last Use) by way
of a RowHash match scan. RSMU_DATA.SGFDP01_PROD_TBL and Spool 6
are joined using a merge join, with a join condition of (
"(SPRFXPRT_PREFIX_R = RSMU_DATA.SGFDP01_PROD_TBL.SPRFXPRT_PREFIX_R)
AND ((SBASEPRT_NBR = RSMU_DATA.SGFDP01_PROD_TBL.SBASEPRT_NBR) AND
(SUFXPRT_SUFFIX_NBR =
RSMU_DATA.SGFDP01_PROD_TBL.SUFXPRT_SUFFIX_NBR ))"). The result
goes into Spool 9 (all_amps), which is built locally on the AMPs.
Then we do a SORT to order Spool 9 by the hash code of (
RSMU_DATA.SGFDP01_PROD_TBL.PTHR_PRODUCT_C,
RSMU_DATA.SGFDP05.PTHR_PRODUCT_C,
RSMU_DATA.SGFDP02F.PTHR_PRODUCT_C). The size of Spool 9 is
estimated with no confidence to be 232,186 rows (40,632,550 bytes).
The estimated time for this step is 0.03 seconds.
8) We do an all-AMPs JOIN step from Spool 8 (Last Use) by way of a
RowHash match scan, which is joined to Spool 9 (Last Use) by way
of a RowHash match scan. Spool 8 and Spool 9 are joined using a
merge join, with a join condition of ("(PTHR_PRODUCT_1_C =
PTHR_PRODUCT_C) AND ((PTHR_PRODUCT_1_C = PTHR_PRODUCT_C) AND
(PTHR_PRODUCT_C = PTHR_PRODUCT_C ))"). The result goes into Spool
10 (all_amps), which is redistributed by the hash code of (
RSMU_DATA.SGFDP01_PROD_TBL.PTHR_SRV_PART_R) to all AMPs. Then we
do a SORT to order Spool 10 by row hash. The size of Spool 10 is
estimated with no confidence to be 5,748 rows (925,428 bytes).
The estimated time for this step is 0.01 seconds.
9) We do an all-AMPs JOIN step from SRET_DATA.SOESA05_PART_DMY by way
of a RowHash match scan with no residual conditions, which is
joined to Spool 10 (Last Use) by way of a RowHash match scan.
SRET_DATA.SOESA05_PART_DMY and Spool 10 are joined using a merge
join, with a join condition of ("PTHR_SRV_PART_R =
SRET_DATA.SOESA05_PART_DMY.A05_SVC_PART"). The result goes into
Spool 1 (group_amps), which is built locally on the AMPs. Then we
do a SORT to order Spool 1 by the sort key in spool field1 (
RSMU_DATA.SGFDP01_PROD_TBL.PTHR_SRV_PART_R,
RSMU_DATA.SGFDP01_PROD_TBL.PTHR_MOTORCRAFT_R,
RSMU_DATA.SGFDP05.PTHR_PRODUCT_1_C, RSMU_DATA.SGFDP02F.PTHR_DESC_X,
RSMU_DATA.SGFDP05.PTHR_PRODUCT_1_C, RSMU_DATA.SGFDP02F.PTHR_DESC_X)
eliminating duplicate rows. The size of Spool 1 is estimated with
no confidence to be 5,748 rows (1,822,116 bytes). The estimated
time for this step is 0.02 seconds.
10) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.

 

2 REPLIES 2
Teradata Employee

Re: how to Optimize Qquery - No spool space

What are the statistics on the tables?

 

Specifically, do you have stats on all of the join and qualification columsn in the Where clause?

 

thanks

 

dave

 

       WHERE P01.PTHR_SRV_PART_R      = A05_SVC_PART                       
              AND   M06.COUNTRY_ISO3_C       = 'USA'                        
              AND   M06.SPRFXPRT_PREFIX_R    = P01.SPRFXPRT_PREFIX_R        
              AND   M06.SBASEPRT_NBR         = P01.SBASEPRT_NBR             
              AND   M06.SUFXPRT_SUFFIX_NBR   = P01.SUFXPRT_SUFFIX_NBR       
              AND   P01.PTHR_PRODUCT_C       = P05A.PTHR_PRODUCT_C          
              AND   P05A.PTHR_HIRCHY_C       = '2'                          
              AND   P05A.PTHR_PRODUCT_1_C    = P05B.PTHR_PRODUCT_C          
              AND   P05B.PTHR_HIRCHY_C       = '3'                          
              AND   P05B.PTHR_PRODUCT_1_C    = P05C.PTHR_PRODUCT_C          
              AND   P05C.PTHR_HIRCHY_C       = '4'                          
              AND   P02B.COUNTRY_ISO3_C       = 'USA'                     
              AND   P02B.PTHR_HIRCHY_C        = '4'                       
              AND   P05B.PTHR_PRODUCT_1_C    = P02B.PTHR_PRODUCT_C        
              AND   P02C.COUNTRY_ISO3_C       = 'USA'                     
              AND   P02C.PTHR_HIRCHY_C        = '5'                       
              AND   P05C.PTHR_PRODUCT_1_C    = P02C.PTHR_PRODUCT_C     

 

Tags (1)
Highlighted
Enthusiast

Re: how to Optimize Qquery - No spool space

Hi Dave, i switcehd on 

DIAGNOSTIC VERBOSEEXPLAIN ON FOR SESSION;

and collect all stats recommended by it. I believe all stats are upto date.

follwoing is esplain plan after collecting recommended stats  

1) First, we lock SRET_DATA.SOESA05_PART_DMY for access, we lock
RSMU_DATA.SGFDM06_MDSMT_TBL in view RSMU_VIEW.SGFDM06 for access,
we lock RSMU_DATA.SGFDP05_PDSTR_TBL in view RSMU_VIEW.SGFDP05 for
access, we lock RSMU_DATA.SGFDP02_PTHR_TBL in view
RSMU_VIEW.SGFDP02 for access, and we lock
RSMU_DATA.SGFDP01_PROD_TBL in view RSMU_VIEW.SGFDP01 for access.
2) Next, we do an all-AMPs RETRIEVE step from RSMU_DATA.SGFDP05 in
view RSMU_VIEW.SGFDP05 by way of an all-rows scan with a condition
of ("RSMU_DATA.SGFDP05 in view RSMU_VIEW.SGFDP05.PTHR_HIRCHY_C =
'4'") locking for access into Spool 3 (all_amps), which is
redistributed by hash code to all AMPs to all AMPs with hash
fields ("'5','USA',RSMU_DATA.SGFDP05.PTHR_PRODUCT_1_C"). Then we
do a SORT to order Spool 3 by row hash. The size of Spool 3 is
estimated with high confidence to be 81 rows (2,349 bytes). Spool
AsgnList:
"PTHR_PRODUCT_C" = "PTHR_PRODUCT_C",
"PTHR_HIRCHY_C" = "PTHR_HIRCHY_C",
"PTHR_PRODUCT_1_C" = "RSMU_DATA.SGFDP05.PTHR_PRODUCT_1_C",
"Field_1028" = "NULL ,'USA','5'".
The estimated time for this step is 0.01 seconds.
3) We do an all-AMPs JOIN step (No Sum) from RSMU_DATA.SGFDP02F in
view RSMU_VIEW.SGFDP02 by way of a RowHash match scan with a
condition of ("(RSMU_DATA.SGFDP02F in view
RSMU_VIEW.SGFDP02.PTHR_HIRCHY_C = '5') AND (RSMU_DATA.SGFDP02F in
view RSMU_VIEW.SGFDP02.COUNTRY_ISO3_C = 'USA')"), which is joined
to Spool 3 (Last Use) by way of a RowHash match scan locking
RSMU_DATA.SGFDP02F for access. RSMU_DATA.SGFDP02F and Spool 3 are
joined using a merge join, with a join condition of (
"Spool_3.PTHR_PRODUCT_1_C = RSMU_DATA.SGFDP02F.PTHR_PRODUCT_C").
The result goes into Spool 4 (all_amps), which is redistributed by
hash code to all AMPs to all AMPs with hash fields (
"Spool_3.PTHR_PRODUCT_C ,Spool_3.PTHR_HIRCHY_C
,RSMU_DATA.SGFDP02F.COUNTRY_ISO3_C"). Then we do a SORT to order
Spool 4 by row hash. The size of Spool 4 is estimated with low
confidence to be 74 rows (5,624 bytes). Spool AsgnList:
"COUNTRY_ISO3_C" = "RSMU_DATA.SGFDP02F.COUNTRY_ISO3_C",
"PTHR_HIRCHY_C" = "Spool_3.PTHR_HIRCHY_C",
"PTHR_PRODUCT_C" = "Spool_3.PTHR_PRODUCT_C",
"PTHR_PRODUCT_1_C" = "{RightTable}.PTHR_PRODUCT_1_C",
"PTHR_DESC_X" = "{LeftTable}.PTHR_DESC_X".
The estimated time for this step is 0.01 seconds.
4) We do an all-AMPs JOIN step (No Sum) from RSMU_DATA.SGFDP02F in
view RSMU_VIEW.SGFDP02 by way of a RowHash match scan with a
condition of ("(RSMU_DATA.SGFDP02F in view
RSMU_VIEW.SGFDP02.PTHR_HIRCHY_C = '4') AND (RSMU_DATA.SGFDP02F in
view RSMU_VIEW.SGFDP02.COUNTRY_ISO3_C = 'USA')"), which is joined
to Spool 4 (Last Use) by way of a RowHash match scan locking
RSMU_DATA.SGFDP02F for access. RSMU_DATA.SGFDP02F and Spool 4 are
joined using a merge join, with a join condition of (
"(RSMU_DATA.SGFDP02F.COUNTRY_ISO3_C = Spool_4.COUNTRY_ISO3_C) AND
((RSMU_DATA.SGFDP02F.PTHR_HIRCHY_C = Spool_4.PTHR_HIRCHY_C) AND
(Spool_4.PTHR_PRODUCT_C = RSMU_DATA.SGFDP02F.PTHR_PRODUCT_C ))").
The result goes into Spool 5 (all_amps), which is duplicated on
all AMPs. The size of Spool 5 is estimated with low confidence to
be 7,056 rows (917,280 bytes). Spool AsgnList:
"PTHR_PRODUCT_C" = "{RightTable}.PTHR_PRODUCT_C",
"PTHR_PRODUCT_C" = "{LeftTable}.PTHR_PRODUCT_C",
"COUNTRY_ISO3_C" = "{RightTable}.COUNTRY_ISO3_C",
"COUNTRY_ISO3_C" = "{LeftTable}.COUNTRY_ISO3_C",
"PTHR_PRODUCT_1_C" = "{RightTable}.PTHR_PRODUCT_1_C",
"PTHR_DESC_X" = "{RightTable}.PTHR_DESC_X",
"PTHR_DESC_X" = "{LeftTable}.PTHR_DESC_X".
The estimated time for this step is 0.01 seconds.
5) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step (No Sum) from Spool 5 (Last Use)
by way of an all-rows scan, which is joined to
RSMU_DATA.SGFDM06_MDSMT_TBL in view RSMU_VIEW.SGFDM06 by way
of an all-rows scan with a condition of (
"RSMU_DATA.SGFDM06_MDSMT_TBL in view
RSMU_VIEW.SGFDM06.COUNTRY_ISO3_C = 'USA'"). Spool 5 and
RSMU_DATA.SGFDM06_MDSMT_TBL are joined using a product join,
with a join condition of (
"(RSMU_DATA.SGFDM06_MDSMT_TBL.COUNTRY_ISO3_C =
Spool_5.COUNTRY_ISO3_C) AND
(RSMU_DATA.SGFDM06_MDSMT_TBL.COUNTRY_ISO3_C =
Spool_5.COUNTRY_ISO3_C)"). The result goes into Spool 6
(all_amps), which is redistributed by hash code to all AMPs
to all AMPs with hash fields (
"RSMU_DATA.SGFDM06_MDSMT_TBL.SUFXPRT_SUFFIX_NBR
,RSMU_DATA.SGFDM06_MDSMT_TBL.SBASEPRT_NBR
,
RSMU_DATA.SGFDM06_MDSMT_TBL.SPRFXPRT_PREFIX_R"). The size
of Spool 6 is estimated with no confidence to be 325,061 rows
(47,458,906 bytes). Spool AsgnList:
"SPRFXPRT_PREFIX_R" =
"RSMU_DATA.SGFDM06_MDSMT_TBL.SPRFXPRT_PREFIX_R",
"SBASEPRT_NBR" = "RSMU_DATA.SGFDM06_MDSMT_TBL.SBASEPRT_NBR",
"SUFXPRT_SUFFIX_NBR" =
"RSMU_DATA.SGFDM06_MDSMT_TBL.SUFXPRT_SUFFIX_NBR",
"PTHR_PRODUCT_C" = "{LeftTable}.PTHR_PRODUCT_C",
"PTHR_PRODUCT_C" = "{LeftTable}.PTHR_PRODUCT_C",
"PTHR_PRODUCT_1_C" = "{LeftTable}.PTHR_PRODUCT_1_C",
"PTHR_DESC_X" = "{LeftTable}.PTHR_DESC_X",
"PTHR_DESC_X" = "{LeftTable}.PTHR_DESC_X".
The estimated time for this step is 5.07 seconds.
2) We do an all-AMPs RETRIEVE step from RSMU_DATA.SGFDP05 in
view RSMU_VIEW.SGFDP05 by way of an all-rows scan with a
condition of ("RSMU_DATA.SGFDP05 in view
RSMU_VIEW.SGFDP05.PTHR_HIRCHY_C = '3'") locking for access
into Spool 7 (all_amps), which is duplicated on all AMPs with
hash fields ("RSMU_DATA.SGFDP05.PTHR_PRODUCT_C"). The size
of Spool 7 is estimated with high confidence to be 85,344
rows (2,304,288 bytes). Spool AsgnList:
"PTHR_PRODUCT_C" = "RSMU_DATA.SGFDP05.PTHR_PRODUCT_C",
"PTHR_PRODUCT_1_C" = "PTHR_PRODUCT_1_C".
The estimated time for this step is 0.01 seconds.
6) We do an all-AMPs JOIN step (No Sum) from Spool 7 (Last Use) by
way of an all-rows scan, which is joined to RSMU_DATA.SGFDP05 in
view RSMU_VIEW.SGFDP05 by way of an all-rows scan with a condition
of ("RSMU_DATA.SGFDP05 in view RSMU_VIEW.SGFDP05.PTHR_HIRCHY_C =
'2'") locking RSMU_DATA.SGFDP05 for access. Spool 7
is used as the hash table and RSMU_DATA.SGFDP05
is used as the probe table in a joined using a dynamic hash join,
with a join condition of ("RSMU_DATA.SGFDP05.PTHR_PRODUCT_1_C =
Spool_7.PTHR_PRODUCT_C"). The result goes into Spool 8 (all_amps),
which is duplicated on all AMPs with hash fields (
"Spool_7.PTHR_PRODUCT_1_C ,RSMU_DATA.SGFDP05.PTHR_PRODUCT_C
,
Spool_7.PTHR_PRODUCT_1_C"). Then we do a SORT to order Spool 8
by row hash. The size of Spool 8 is estimated with low confidence
to be 672,672 rows (18,834,816 bytes). Spool AsgnList:
"PTHR_PRODUCT_1_C" = "Spool_7.PTHR_PRODUCT_1_C",
"PTHR_PRODUCT_C" = "RSMU_DATA.SGFDP05.PTHR_PRODUCT_C",
"Field_4" = "NULL ,Spool_7.PTHR_PRODUCT_1_C".
The estimated time for this step is 0.02 seconds.
7) We do an all-AMPs JOIN step (No Sum) from
RSMU_DATA.SGFDP01_PROD_TBL in view RSMU_VIEW.SGFDP01 by way of an
all-rows scan with no residual conditions, which is joined to
Spool 6 (Last Use) by way of an all-rows scan.
RSMU_DATA.SGFDP01_PROD_TBL is used as the hash table and Spool 6
is used as the probe table in a joined using a single partition
classical hash join, with a join condition of (
"(Spool_6.SPRFXPRT_PREFIX_R =
RSMU_DATA.SGFDP01_PROD_TBL.SPRFXPRT_PREFIX_R) AND
((Spool_6.SBASEPRT_NBR = RSMU_DATA.SGFDP01_PROD_TBL.SBASEPRT_NBR)
AND (Spool_6.SUFXPRT_SUFFIX_NBR =
RSMU_DATA.SGFDP01_PROD_TBL.SUFXPRT_SUFFIX_NBR ))"). The result
goes into Spool 9 (all_amps), which is built locally on the AMPs
with hash fields ("Spool_6.PTHR_PRODUCT_C ,Spool_6.PTHR_PRODUCT_C
,RSMU_DATA.SGFDP01_PROD_TBL.PTHR_PRODUCT_C"). Then we do a SORT
to order Spool 9 by row hash. The size of Spool 9 is estimated
with no confidence to be 325,061 rows (56,885,675 bytes). Spool
AsgnList:
"PTHR_SRV_PART_R" = "{LeftTable}.PTHR_SRV_PART_R",
"PTHR_PRODUCT_C" = "RSMU_DATA.SGFDP01_PROD_TBL.PTHR_PRODUCT_C",
"PTHR_PRODUCT_C" = "Spool_6.PTHR_PRODUCT_C",
"PTHR_PRODUCT_C" = "Spool_6.PTHR_PRODUCT_C",
"PTHR_MOTORCRAFT_R" = "{LeftTable}.PTHR_MOTORCRAFT_R",
"PTHR_PRODUCT_1_C" = "{RightTable}.PTHR_PRODUCT_1_C",
"PTHR_DESC_X" = "{RightTable}.PTHR_DESC_X",
"PTHR_DESC_X" = "{RightTable}.PTHR_DESC_X".
The estimated time for this step is 0.05 seconds.
8) We do an all-AMPs JOIN step (No Sum) from Spool 8 (Last Use) by
way of a RowHash match scan, which is joined to Spool 9 (Last Use)
by way of a RowHash match scan. Spool 8 and Spool 9 are joined
using a merge join, with a join condition of (
"(Spool_8.PTHR_PRODUCT_1_C = Spool_9.PTHR_PRODUCT_C) AND
((Spool_8.PTHR_PRODUCT_1_C = Spool_9.PTHR_PRODUCT_C) AND
(Spool_9.PTHR_PRODUCT_C = Spool_8.PTHR_PRODUCT_C ))"). The result
goes into Spool 10 (all_amps), which is redistributed by hash code
to all AMPs to all AMPs with hash fields (
"Spool_9.PTHR_SRV_PART_R"). Then we do a SORT to order Spool 10
by row hash. The size of Spool 10 is estimated with no confidence
to be 16,700 rows (2,688,700 bytes). Spool AsgnList:
"PTHR_SRV_PART_R" = "Spool_9.PTHR_SRV_PART_R",
"PTHR_MOTORCRAFT_R" = "{RightTable}.PTHR_MOTORCRAFT_R",
"PTHR_PRODUCT_1_C" = "{RightTable}.PTHR_PRODUCT_1_C",
"PTHR_DESC_X" = "{RightTable}.PTHR_DESC_X",
"PTHR_PRODUCT_1_C" = "{LeftTable}.PTHR_PRODUCT_1_C",
"PTHR_DESC_X" = "{RightTable}.PTHR_DESC_X".
The estimated time for this step is 0.02 seconds.
9) We do an all-AMPs JOIN step (Local sum) from
SRET_DATA.SOESA05_PART_DMY by way of a RowHash match scan with no
residual conditions, which is joined to Spool 10 (Last Use) by way
of a RowHash match scan. SRET_DATA.SOESA05_PART_DMY and Spool 10
are joined using a merge join, with a join condition of (
"Spool_10.PTHR_SRV_PART_R =
SRET_DATA.SOESA05_PART_DMY.A05_SVC_PART"). The result goes into
Spool 1 (group_amps), which is built locally on the AMPs with hash
fields ("Spool_10.PTHR_DESC_X ,Spool_10.PTHR_PRODUCT_1_C
,Spool_10.PTHR_DESC_X ,
Spool_10.PTHR_PRODUCT_1_C
,Spool_10.PTHR_MOTORCRAFT_R ,Spool_10.PTHR_SRV_PART_R") and Field1
("Spool_10.PTHR_SRV_PART_R ,Spool_10.PTHR_MOTORCRAFT_R
,Spool_10.PTHR_PRODUCT_1_C ,Spool_10.PTHR_DESC_X
,Spool_10.PTHR_PRODUCT_1_C ,Spool_10.PTHR_DESC_X"). Then we do a
SORT to order Spool 1 by the sort key in spool field1 eliminating
duplicate rows (ordered nulls first). The size of Spool 1 is
estimated with no confidence to be 16,700 rows (5,293,900 bytes).
Spool AsgnList:
"Field_1" = "Spool_10.PTHR_SRV_PART_R ,Spool_10.PTHR_MOTORCRAFT_R
,Spool_10.PTHR_PRODUCT_1_C ,Spool_10.PTHR_DESC_X
,Spool_10.PTHR_PRODUCT_1_C ,Spool_10.PTHR_DESC_X",
"Field_2" = "{RightTable}.PTHR_SRV_PART_R
,{RightTable}.PTHR_MOTORCRAFT_R ,{RightTable}.PTHR_PRODUCT_1_C
,{RightTable}.PTHR_DESC_X ,{RightTable}.PTHR_PRODUCT_1_C
,{RightTable}.PTHR_DESC_X,".
The estimated time for this step is 0.02 seconds.
10) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 5.20 seconds.