No more spool while inserting into table

Database
Enthusiast

No more spool while inserting into table

Hello All,

 

When i try to insert i am encountering spool space error, however i am able to fetch data using select statement.

Please help me resolving the issue. thank you.

INSERT INTO VSERT_DATA.SGFDD51_T96_WRK_DCRTU
SELECT 'AP'                       /* GFDG73_PT_SLS_ANLYS_GRP_C */      ,P09.GFDP09_PREFIX_R      ,P09.GFDP09_BASE_R      ,P09.GFDP09_SUFFIX_R      ,T96.COUNTRY_ISO3_C      ,T96.MKT_CUSTOMER_ID_C      ,CASE T96.DEPOT_LOCATION_C
            WHEN 'DSHIP' THEN 'DSHIP'
            ELSE 'DEPOT'
       END                             (NAMED DEPOT_CD)
      ,T96.GFDT96_MNTHYR_Y      ,P09.GFDP09_PTHR_PRODUCT_C      ,P07.GFDP07_PART_X      ,P07.GFDP07_ENGG_PART_R      ,SUBSTR(M02.GFDM02_REG_C,1,2)
      ,M02.GFDM02_SUB_REG_C      ,M01.COUNTRY_ISO3_1_C      ,M01.OLEV1_SA_LEVEL1_C      ,M01.OLEV3_SA_LEVEL3_C      ,M01.MNMKT_MAIN_MKT_C      ,M01.MKT_CUST_TYPE_C      ,0                          /* MONEX_LOCAL_RATE_R     */      ,0                          /* MONEX_EURO_RATE_R      */      ,0                          /* MONEX_BUDG_RATE_R      */
--* ACTUAL COSTS FOR CORPORATE VIEW      ,SUM(GFDT96_GSPRPKGCST_A)
      ,SUM(GFDT96_GINHMTRCST_A)
      ,SUM(GFDT96_GINHLBRCST_A)
      ,SUM(GFDT96_GOTHPKGCST_A)
      ,SUM(GFDT96_GMTRCST_A)
      ,SUM(GFDT96_GLBRCST_A)
      ,SUM(GFDT96_GUPLFTCST_A)
      ,SUM(GFDT96_GCONSGNCST_A)--* GFDT96_GTOTCST_A EQUALS SUM OF ALL 8 COSTS      ,SUM(GFDT96_GSPRPKGCST_A +
           GFDT96_GINHMTRCST_A +
           GFDT96_GINHLBRCST_A +
           GFDT96_GOTHPKGCST_A +
           GFDT96_GMTRCST_A    +
           GFDT96_GLBRCST_A    +
           GFDT96_GUPLFTCST_A  +
           GFDT96_GCONSGNCST_A)

FROM VSERT_VIEW.SGFDT96 T96    ,VSERT_VIEW.SGFDM01 M01    ,VSERT_VIEW.SGFDP07 P07    ,VSERT_VIEW.SGFDP09 P09    ,VSERT_VIEW.SGFDM02 M02

WHERE T96.COUNTRY_ISO3_C            IN (SELECT COUNTRY_ISO3_C
                                        FROM   VSERT_VIEW.SGFDF04
                                        WHERE  CONTINENT_C = 'AP')

AND   T96.COUNTRY_ISO3_C            = P09.GFDP09_MRKT_C
AND  (TRIM(T96.SPRFXPRT_PREFIX_R)||
      TRIM(T96.SBASEPRT_NBR)||
      TRIM(T96.SUFXPRT_SUFFIX_NBR)) = (TRIM(P09.GFDP09_PREFIX_R)||
                                       TRIM(P09.GFDP09_BASE_R)||
                                       TRIM(P09.GFDP09_SUFFIX_R))

AND   P07.GFDP07_PREFIX_R           = P09.GFDP09_PREFIX_R
AND   P07.GFDP07_BASE_R             = P09.GFDP09_BASE_R
AND   P07.GFDP07_SUFFIX_R           = P09.GFDP09_SUFFIX_R

AND   P07.GFDP07_SLS_ANLYS_GRP_C    = 'AP'

AND   T96.COUNTRY_ISO3_C            = M01.COUNTRY_ISO3_C
AND   T96.MKT_CUSTOMER_ID_C         = M01.MKT_CUSTOMER_ID_C

--* REMOVE THIS IN THE FUTUREAND   M01.COUNTRY_ISO3_1_C          <> ' '
AND  (M01.COUNTRY_ISO3_1_C          = M02.GFDM02_SUB_REG_C OR
      M01.COUNTRY_ISO3_1_C          = M02.COUNTRY_ISO3_1_C)

--* ONLY SELECT PERIODS IN DATE WORKAND   T96.GFDT96_MNTHYR_Y / 100 IN (SELECT DISTINCT YEAR_C
                                    FROM   VSERT_DATA.SGFDD99_DATE_WRK)

GROUP BY 'AP'        ,P09.GFDP09_PREFIX_R        ,P09.GFDP09_BASE_R        ,P09.GFDP09_SUFFIX_R        ,T96.COUNTRY_ISO3_C        ,T96.MKT_CUSTOMER_ID_C        ,DEPOT_CD        ,T96.GFDT96_MNTHYR_Y        ,P09.GFDP09_PTHR_PRODUCT_C        ,P07.GFDP07_PART_X        ,P07.GFDP07_ENGG_PART_R        ,GFDM02_REG_C        ,M02.GFDM02_SUB_REG_C        ,M01.COUNTRY_ISO3_1_C        ,M01.OLEV1_SA_LEVEL1_C        ,M01.OLEV3_SA_LEVEL3_C        ,M01.MNMKT_MAIN_MKT_C        ,M01.MKT_CUST_TYPE_C
;

Ecplain:

1) First, we lockVSERT_DATA.SGFDD51_T96_WRK_DCRTU for write on a
     reserved RowHash to prevent global deadlock.
  2) Next, we lockVSERT_DATA.SGFDD99_DATE_WRK for access, we lock
    VSERT_DATA.SGFDD51_T96_WRK_DCRTU for write, we lock
    VSERT_DATA.SGFDT96_CORP_GPRFT_TBL in viewVSERT_VIEW.SGFDT96 for
     access, we lockVSERT_DATA.SGFDM02_MKT_RGN_TBL in view
    VSERT_VIEW.SGFDM02 for access, we lock
    VSERT_DATA.SGFDP09_PRT_ATTR_TBL in viewVSERT_VIEW.SGFDP09 for
     access, we lockVSERT_DATA.SGFDP07_PRT_MSTR_TBL in view
    VSERT_VIEW.SGFDP07 for access, we lockVSERT_DATA.SGFDM01_MKT_TBL in
     viewVSERT_VIEW.SGFDM01 for access, and we lock
    VSERT_DATA.SGFDF04_BCC_TBL for access.
  3) We do an all-AMPs SUM step to aggregate fromVSERT_DATA.SGFDF04 by
     way of an all-rows scan with a condition of (
     "GFIN_DATA.SGFDF04.CONTINENT_C = 'AP'"), grouping by field1 (
    VSERT_DATA.SGFDF04.COUNTRY_ISO3_C) locking for access.  Aggregate
     Intermediate Results are computed locally, then placed in Spool 9.
     The size of Spool 9 is estimated with no confidence to be 5 rows (
     100 bytes).  The estimated time for this step is 0.01 seconds.
  4) We do an all-AMPs RETRIEVE step from Spool 9 (Last Use) by way of
     an all-rows scan into Spool 8 (all_amps) (compressed columns
     allowed), which is duplicated on all AMPs.  The size of Spool 8 is
     estimated with no confidence to be 1,680 rows (26,880 bytes).
  5) We execute the following steps in parallel.
       1) We do an all-AMPs JOIN step from
         VSERT_DATA.SGFDT96_CORP_GPRFT_TBL in viewVSERT_VIEW.SGFDT96 by
          way of an all-rows scan with no residual conditions, which is
          joined to Spool 8 (Last Use) by way of an all-rows scan.
         VSERT_DATA.SGFDT96_CORP_GPRFT_TBL and Spool 8 are joined using
          a inclusion dynamic hash join, with a join condition of (
          "GFIN_DATA.SGFDT96_CORP_GPRFT_TBL.COUNTRY_ISO3_C =
          COUNTRY_ISO3_C").  The result goes into Spool 11 (all_amps)
          (compressed columns allowed) fanned out into 50 hash join
          partitions, which is built locally on the AMPs.  The size of
          Spool 11 is estimated with no confidence to be 40,250,363
          rows (4,789,793,197 bytes).  The estimated time for this step
          is 2.44 seconds.
       2) We do an all-AMPs RETRIEVE step from
         VSERT_DATA.SGFDD99_DATE_WRK by way of an all-rows scan with no
          residual conditions into Spool 14 (all_amps), which is
          redistributed by the hash code of (
         VSERT_DATA.SGFDD99_DATE_WRK.YEAR_C (FLOAT, FORMAT
          '-9.99999999999999E-999')) to all AMPs.  Then we do a SORT to
          order Spool 14 by the sort key in spool field1 eliminating
          duplicate rows.  The size of Spool 14 is estimated with high
          confidence to be 1 row (29 bytes).  The estimated time for
          this step is 0.01 seconds.
  6) We do an all-AMPs RETRIEVE step from Spool 14 (Last Use) by way of
     an all-rows scan into Spool 13 (all_amps) (compressed columns
     allowed) fanned out into 50 hash join partitions, which is
     duplicated on all AMPs.  The size of Spool 13 is estimated with
     high confidence to be 336 rows (10,080 bytes).
  7) We execute the following steps in parallel.
       1) We do an all-AMPs JOIN step from Spool 11 (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 11 and Spool 13 are joined
          using a inclusion hash join of 50 partitions, with a join
          condition of ("(GFDT96_MNTHYR_Y / 100 )= Field_2").  The
          result goes into Spool 15 (all_amps) (compressed columns
          allowed) fanned out into 2 hash join partitions, which is
          duplicated on all AMPs.  The size of Spool 15 is estimated
          with no confidence to be 2,131,920 rows (253,698,480 bytes).
          The estimated time for this step is 0.06 seconds.
       2) We do an all-AMPs RETRIEVE step from
         VSERT_DATA.SGFDP09_PRT_ATTR_TBL in viewVSERT_VIEW.SGFDP09 by
          way of an all-rows scan with no residual conditions into
          Spool 17 (all_amps) (compressed columns allowed) fanned out
          into 2 hash join partitions, which is built locally on the
          AMPs.  The size of Spool 17 is estimated with high confidence
          to be 3,203,056 rows (147,340,576 bytes).  The estimated time
          for this step is 0.04 seconds.
  8) We do an all-AMPs JOIN step from Spool 15 (Last Use) by way of an
     all-rows scan, which is joined to Spool 17 (Last Use) by way of an
     all-rows scan.  Spool 15 and Spool 17 are joined using a hash join
     of 2 partitions, with a join condition of ("(COUNTRY_ISO3_C =
     GFDP09_MRKT_C) AND ((((TRIM(BOTH FROM
     {LeftTable}.SPRFXPRT_PREFIX_R ))||(TRIM(BOTH FROM
     {LeftTable}.SBASEPRT_NBR )))||(TRIM(BOTH FROM
     {LeftTable}.SUFXPRT_SUFFIX_NBR )))= (((TRIM(BOTH FROM
     {RightTable}.GFDP09_PREFIX_R ))||(TRIM(BOTH FROM
     {RightTable}.GFDP09_BASE_R )))||(TRIM(BOTH FROM
     {RightTable}.GFDP09_SUFFIX_R ))))").  The result goes into Spool
     18 (all_amps) (compressed columns allowed), which is redistributed
     by the hash code of (GFIN_DATA.SGFDP09_PRT_ATTR_TBL.GFDP09_MRKT_C,
    VSERT_DATA.SGFDT96_CORP_GPRFT_TBL.MKT_CUSTOMER_ID_C) to all AMPs.
     Then we do a SORT to order Spool 18 by row hash.  The size of
     Spool 18 is estimated with no confidence to be 6,345 rows (
     812,160 bytes).  The estimated time for this step is 0.03 seconds.
  9) We execute the following steps in parallel.
       1) We do an all-AMPs JOIN step fromVSERT_DATA.SGFDM01 in view
         VSERT_VIEW.SGFDM01 by way of a RowHash match scan with a
          condition of ("GFIN_DATA.SGFDM01 in view
         VSERT_VIEW.SGFDM01.COUNTRY_ISO3_1_C <> ' '"), which is joined
          to Spool 18 (Last Use) by way of a RowHash match scan locking
         VSERT_DATA.SGFDM01 for access. VSERT_DATA.SGFDM01 and Spool 18
          are joined using a merge join, with a join condition of (
          "(GFDP09_MRKT_C =VSERT_DATA.SGFDM01.COUNTRY_ISO3_C) AND
          ((COUNTRY_ISO3_C =VSERT_DATA.SGFDM01.COUNTRY_ISO3_C) AND
          (MKT_CUSTOMER_ID_C =VSERT_DATA.SGFDM01.MKT_CUSTOMER_ID_C ))").
          The result goes into Spool 19 (all_amps) (compressed columns
          allowed), which is built locally on the AMPs.  The size of
          Spool 19 is estimated with no confidence to be 6,345 rows (
          875,610 bytes).  The estimated time for this step is 0.01
          seconds.
       2) We do an all-AMPs RETRIEVE step from
         VSERT_DATA.SGFDM02_MKT_RGN_TBL in viewVSERT_VIEW.SGFDM02 by
          way of an all-rows scan with no residual conditions into
          Spool 20 (all_amps) (compressed columns allowed), which is
          duplicated on all AMPs.  The size of Spool 20 is estimated
          with high confidence to be 80,976 rows (1,700,496 bytes).
          The estimated time for this step is 0.01 seconds.
 10) We do an all-AMPs JOIN step from Spool 19 (Last Use) by way of an
     all-rows scan, which is joined to Spool 20 (Last Use) by way of an
     all-rows scan.  Spool 19 and Spool 20 are joined using a product
     join, with a join condition of ("(COUNTRY_ISO3_1_C =
     COUNTRY_ISO3_1_C) OR (COUNTRY_ISO3_1_C = GFDM02_SUB_REG_C)").  The
     result goes into Spool 21 (all_amps) (compressed columns allowed),
     which is redistributed by the hash code of (
    VSERT_DATA.SGFDP09_PRT_ATTR_TBL.GFDP09_PREFIX_R,
    VSERT_DATA.SGFDP09_PRT_ATTR_TBL.GFDP09_BASE_R,
    VSERT_DATA.SGFDP09_PRT_ATTR_TBL.GFDP09_SUFFIX_R, 'AP') to all AMPs.
     Then we do a SORT to order Spool 21 by row hash.  The size of
     Spool 21 is estimated with no confidence to be 98,501 rows (
     14,184,144 bytes).  The estimated time for this step is 0.05
     seconds.
 11) We do an all-AMPs JOIN step fromVSERT_DATA.SGFDP07_PRT_MSTR_TBL in
     viewVSERT_VIEW.SGFDP07 by way of a RowHash match scan with a
     condition of ("GFIN_DATA.SGFDP07_PRT_MSTR_TBL in view
    VSERT_VIEW.SGFDP07.GFDP07_SLS_ANLYS_GRP_C = 'AP'"), which is joined
     to Spool 21 (Last Use) by way of a RowHash match scan.
    VSERT_DATA.SGFDP07_PRT_MSTR_TBL and Spool 21 are joined using a
     merge join, with a join condition of (
     "(GFIN_DATA.SGFDP07_PRT_MSTR_TBL.GFDP07_PREFIX_R = GFDP09_PREFIX_R)
     AND ((GFIN_DATA.SGFDP07_PRT_MSTR_TBL.GFDP07_BASE_R = GFDP09_BASE_R)
     AND (GFIN_DATA.SGFDP07_PRT_MSTR_TBL.GFDP07_SUFFIX_R =
     GFDP09_SUFFIX_R ))").  The result goes into Spool 6 (all_amps)
     (compressed columns allowed), which is built locally on the AMPs.
     The size of Spool 6 is estimated with no confidence to be
     46,345,637 rows (8,342,214,660 bytes).  The estimated time for
     this step is 1.06 seconds.

 

8 REPLIES 8
Highlighted
Enthusiast

Re: No more spool while inserting into table

Explian:

 

 12) We do an all-AMPs SUM step to aggregate from Spool 6 (Last Use) by
     way of an all-rows scan, grouping by field1 (
     'AP',VSERT_DATA.SGFDP09_PRT_ATTR_TBL.GFDP09_PREFIX_R     ,VSERT_DATA.SGFDP09_PRT_ATTR_TBL.GFDP09_BASE_R     ,VSERT_DATA.SGFDP09_PRT_ATTR_TBL.GFDP09_SUFFIX_R     ,VSERT_DATA.SGFDT96_CORP_GPRFT_TBL.COUNTRY_ISO3_C     ,VSERT_DATA.SGFDT96_CORP_GPRFT_TBL.MKT_CUSTOMER_ID_C ,( CASE WHEN     (VSERT_DATA.SGFDT96_CORP_GPRFT_TBL.DEPOT_LOCATION_C = 'DSHIP') THEN     ('DSHIP') ELSE ('DEPOT') END     ),VSERT_DATA.SGFDT96_CORP_GPRFT_TBL.GFDT96_MNTHYR_Y     ,VSERT_DATA.SGFDP09_PRT_ATTR_TBL.GFDP09_PTHR_PRODUCT_C     ,VSERT_DATA.SGFDP07_PRT_MSTR_TBL.GFDP07_PART_X     ,VSERT_DATA.SGFDP07_PRT_MSTR_TBL.GFDP07_ENGG_PART_R     ,VSERT_DATA.SGFDM02_MKT_RGN_TBL.GFDM02_REG_C     ,VSERT_DATA.SGFDM02_MKT_RGN_TBL.GFDM02_SUB_REG_C     ,VSERT_DATA.SGFDM01.COUNTRY_ISO3_1_C     ,VSERT_DATA.SGFDM01.OLEV1_SA_LEVEL1_C     ,VSERT_DATA.SGFDM01.OLEV3_SA_LEVEL3_C     ,VSERT_DATA.SGFDM01.MNMKT_MAIN_MKT_C     ,VSERT_DATA.SGFDM01.MKT_CUST_TYPE_C).  Aggregate Intermediate
     Results are computed globally, then placed in Spool 23.  The size
     of Spool 23 is estimated with no confidence to be 34,759,228 rows     (12,026,692,888 bytes).  The estimated time for this step is 1.02
     seconds.
 13) We do an all-AMPs RETRIEVE step from Spool 23 (Last Use) by way of
     an all-rows scan into Spool 3 (all_amps) (compressed columns
     allowed), which is redistributed by the hash code of ('AP',
    VSERT_DATA.SGFDP09_PRT_ATTR_TBL.GFDP09_PREFIX_R,
    VSERT_DATA.SGFDP09_PRT_ATTR_TBL.GFDP09_BASE_R,
    VSERT_DATA.SGFDP09_PRT_ATTR_TBL.GFDP09_SUFFIX_R,
    VSERT_DATA.SGFDT96_CORP_GPRFT_TBL.COUNTRY_ISO3_C,
    VSERT_DATA.SGFDT96_CORP_GPRFT_TBL.MKT_CUSTOMER_ID_C, TRANSLATE(((
     CASE WHEN (VSERT_DATA.SGFDT96_CORP_GPRFT_TBL.DEPOT_LOCATION_C =
     'DSHIP') THEN ('DSHIP') ELSE ('DEPOT') END ))USING
     UNICODE_TO_LATIN)(CHAR(5), CHARACTER SET LATIN, NOT CASESPECIFIC,
     NAMED DEPOT_LOCATION_C, FORMAT 'X(5)', NOT NULL),
    VSERT_DATA.SGFDT96_CORP_GPRFT_TBL.GFDT96_MNTHYR_Y) to all AMPs.
     Then we do a SORT to order Spool 3 by row hash.  The size of Spool
     3 is estimated with no confidence to be 34,759,228 rows (
     6,917,086,372 bytes).  The estimated time for this step is 4.85
     seconds.
 14) We do an all-AMPs MERGE step into
    VSERT_DATA.SGFDD51_T96_WRK_DCRTU from Spool 3 (Last Use).  The
     size is estimated with no confidence to be 34,759,228 rows.  The
     estimated time for this step is 4 minutes and 8 seconds.
 15) We spoil the parser's dictionary cache for the table.
 16) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> No rows are returned to the user as the result of statement 1.

 

 

Teradata Employee

Re: No more spool while inserting into table

Most likely, the redistribution in step 13 (to match the PI of the target table) is highly skewed.

Perhaps one Customer has many Parts for the same Year/Month?

Enthusiast

Re: No more spool while inserting into table

Hi Fred, thank you for suggestion.

 

following is the DDL, do i need to add any other column(s) to PI ?

 

CREATE MULTISET TABLE VSERT_DATA.SGFDD51_T96_WRK_DCRTU ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO     (
      GFDG73_PT_SLS_ANLYS_GRP_C CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      SPRFXPRT_PREFIX_R CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      SBASEPRT_NBR CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      SUFXPRT_SUFFIX_NBR CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      COUNTRY_ISO3_C CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      MKT_CUSTOMER_ID_C CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      DEPOT_LOCATION_C CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      MNTHYR_R INTEGER NOT NULL,
      PTHR_PRODUCT_C CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      PROD_PART_DESC_X VARCHAR(34) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      PROD_PART_XREF_R VARCHAR(22) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      GFDM02_REG_C CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      GFDM02_SUB_REG_C CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      COUNTRY_ISO3_1_C CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      OLEV1_SA_LEVEL1_C CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      OLEV3_SA_LEVEL3_C CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      MNMKT_MAIN_MKT_C CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      MKT_CUST_TYPE_C CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      MONEX_LOCAL_RATE_R DECIMAL(11,9) NOT NULL,
      MONEX_EURO_RATE_R DECIMAL(11,9) NOT NULL,
      MONEX_BUDG_RATE_R DECIMAL(11,9) NOT NULL,
      GFDT96_GSPRPKGCST_A FLOAT NOT NULL,
      GFDT96_GINHMTRCST_A FLOAT NOT NULL,
      GFDT96_GINHLBRCST_A FLOAT NOT NULL,
      GFDT96_GOTHPKGCST_A FLOAT NOT NULL,
      GFDT96_GMTRCST_A FLOAT NOT NULL,
      GFDT96_GLBRCST_A FLOAT NOT NULL,
      GFDT96_GUPLFTCST_A FLOAT NOT NULL,
      GFDT96_GCONSGNCST_A FLOAT NOT NULL,
      GFDT96_GTOTCST_A FLOAT NOT NULL)UNIQUE PRIMARY INDEX ( GFDG73_PT_SLS_ANLYS_GRP_C ,SPRFXPRT_PREFIX_R ,SBASEPRT_NBR ,SUFXPRT_SUFFIX_NBR ,COUNTRY_ISO3_C ,MKT_CUSTOMER_ID_C ,DEPOT_LOCATION_C ,MNTHYR_R );
Teradata Employee

Re: No more spool while inserting into table

With a UPI (which includes the Part info), it's probably not skewing in the final redistribution after all. Maybe you need to compare the EXPLAIN for the INSERT that fails with the one for the SELECT that works, and see if something else is different besides that final redistribution & merge.

Enthusiast

Re: No more spool while inserting into table

Fred, Thank you for valuable suggestions.

Following are the differences observed on explain plans besides final redistribution & merge.

the differences are mainly observed at join conditions

 

Explain (with Insert):

 8) We do an all-AMPs JOIN step from Spool 15 (Last Use) by way of an
     all-rows scan, which is joined to Spool 17 (Last Use) by way of an
     all-rows scan.  Spool 15 and Spool 17 are joined using a hash join
     of 2 partitions, with a join condition of ("(COUNTRY_ISO3_C =
     GFDP09_MRKT_C) AND ((((TRIM(BOTH FROM
     {LeftTable}.SPRFXPRT_PREFIX_R ))||(TRIM(BOTH FROM
     {LeftTable}.SBASEPRT_NBR )))||(TRIM(BOTH FROM
     {LeftTable}.SUFXPRT_SUFFIX_NBR )))= (((TRIM(BOTH FROM
     {RightTable}.GFDP09_PREFIX_R ))||(TRIM(BOTH FROM
     {RightTable}.GFDP09_BASE_R )))||(TRIM(BOTH FROM
     {RightTable}.GFDP09_SUFFIX_R ))))").  The result goes into Spool
     18 (all_amps) (compressed columns allowed), which is redistributed
     by the hash code of (VSERT_DATA.SGFDP09_PRT_ATTR_TBL.GFDP09_MRKT_C,
     VSERT_DATA.SGFDT96_CORP_GPRFT_TBL.MKT_CUSTOMER_ID_C) to all AMPs.
     Then we do a SORT to order Spool 18 by row hash.  The size of
     Spool 18 is estimated with no confidence to be 6,345 rows (
     812,160 bytes).  The estimated time for this step is 0.03 seconds.
  9) We execute the following steps in parallel.
       1) We do an all-AMPs JOIN step from VSERT_DATA.SGFDM01 in view
          VSERT_VIEW.SGFDM01 by way of a RowHash match scan with a
          condition of ("VSERT_DATA.SGFDM01 in view
          VSERT_VIEW.SGFDM01.COUNTRY_ISO3_1_C <> ' '"), which is joined
          to Spool 18 (Last Use) by way of a RowHash match scan locking
          VSERT_DATA.SGFDM01 for access.  VSERT_DATA.SGFDM01 and Spool 18
          are joined using a merge join, with a join condition of (
          "(GFDP09_MRKT_C = VSERT_DATA.SGFDM01.COUNTRY_ISO3_C) AND
          ((COUNTRY_ISO3_C = VSERT_DATA.SGFDM01.COUNTRY_ISO3_C) AND
          (MKT_CUSTOMER_ID_C = VSERT_DATA.SGFDM01.MKT_CUSTOMER_ID_C ))").
          The result goes into Spool 19 (all_amps) (compressed columns
          allowed), which is built locally on the AMPs.  The size of
          Spool 19 is estimated with no confidence to be 6,345 rows (
          875,610 bytes).  The estimated time for this step is 0.01
          seconds.
       2) We do an all-AMPs RETRIEVE step from
          VSERT_DATA.SGFDM02_MKT_RGN_TBL in view VSERT_VIEW.SGFDM02 by
          way of an all-rows scan with no residual conditions into
          Spool 20 (all_amps) (compressed columns allowed), which is
          duplicated on all AMPs.  The size of Spool 20 is estimated
          with high confidence to be 80,976 rows (1,700,496 bytes).
          The estimated time for this step is 0.01 seconds.
 10) We do an all-AMPs JOIN step from Spool 19 (Last Use) by way of an
     all-rows scan, which is joined to Spool 20 (Last Use) by way of an
     all-rows scan.  Spool 19 and Spool 20 are joined using a product
     join, with a join condition of ("(COUNTRY_ISO3_1_C =
     COUNTRY_ISO3_1_C) OR (COUNTRY_ISO3_1_C = GFDM02_SUB_REG_C)").  The
     result goes into Spool 21 (all_amps) (compressed columns allowed),
     which is redistributed by the hash code of (
     VSERT_DATA.SGFDP09_PRT_ATTR_TBL.GFDP09_PREFIX_R,
     VSERT_DATA.SGFDP09_PRT_ATTR_TBL.GFDP09_BASE_R,
     VSERT_DATA.SGFDP09_PRT_ATTR_TBL.GFDP09_SUFFIX_R, 'AP') to all AMPs.
     Then we do a SORT to order Spool 21 by row hash.  The size of
     Spool 21 is estimated with no confidence to be 98,501 rows (
     14,184,144 bytes).  The estimated time for this step is 0.05
     seconds.
 11) We do an all-AMPs JOIN step from VSERT_DATA.SGFDP07_PRT_MSTR_TBL in
     view VSERT_VIEW.SGFDP07 by way of a RowHash match scan with a
     condition of ("VSERT_DATA.SGFDP07_PRT_MSTR_TBL in view
     VSERT_VIEW.SGFDP07.GFDP07_SLS_ANLYS_GRP_C = 'AP'"), which is joined
     to Spool 21 (Last Use) by way of a RowHash match scan.
     VSERT_DATA.SGFDP07_PRT_MSTR_TBL and Spool 21 are joined using a
     merge join, with a join condition of (
     "(VSERT_DATA.SGFDP07_PRT_MSTR_TBL.GFDP07_PREFIX_R = GFDP09_PREFIX_R)
     AND ((VSERT_DATA.SGFDP07_PRT_MSTR_TBL.GFDP07_BASE_R = GFDP09_BASE_R)
     AND (VSERT_DATA.SGFDP07_PRT_MSTR_TBL.GFDP07_SUFFIX_R =
     GFDP09_SUFFIX_R ))").  The result goes into Spool 6 (all_amps)
     (compressed columns allowed), which is built locally on the AMPs.
     The size of Spool 6 is estimated with no confidence to be
     46,345,637 rows (8,342,214,660 bytes).  The estimated time for
     this step is 1.06 seconds.
 12) We do an all-AMPs SUM step to aggregate from Spool 6 (Last Use) by
     way of an all-rows scan, grouping by field1 (
     'AP',VSERT_DATA.SGFDP09_PRT_ATTR_TBL.GFDP09_PREFIX_R     ,VSERT_DATA.SGFDP09_PRT_ATTR_TBL.GFDP09_BASE_R     ,VSERT_DATA.SGFDP09_PRT_ATTR_TBL.GFDP09_SUFFIX_R     ,VSERT_DATA.SGFDT96_CORP_GPRFT_TBL.COUNTRY_ISO3_C     ,VSERT_DATA.SGFDT96_CORP_GPRFT_TBL.MKT_CUSTOMER_ID_C ,( CASE WHEN     (VSERT_DATA.SGFDT96_CORP_GPRFT_TBL.DEPOT_LOCATION_C = 'DSHIP') THEN     ('DSHIP') ELSE ('DEPOT') END     ),VSERT_DATA.SGFDT96_CORP_GPRFT_TBL.GFDT96_MNTHYR_Y     ,VSERT_DATA.SGFDP09_PRT_ATTR_TBL.GFDP09_PTHR_PRODUCT_C     ,VSERT_DATA.SGFDP07_PRT_MSTR_TBL.GFDP07_PART_X     ,VSERT_DATA.SGFDP07_PRT_MSTR_TBL.GFDP07_ENGG_PART_R     ,VSERT_DATA.SGFDM02_MKT_RGN_TBL.GFDM02_REG_C     ,VSERT_DATA.SGFDM02_MKT_RGN_TBL.GFDM02_SUB_REG_C     ,VSERT_DATA.SGFDM01.COUNTRY_ISO3_1_C     ,VSERT_DATA.SGFDM01.OLEV1_SA_LEVEL1_C     ,VSERT_DATA.SGFDM01.OLEV3_SA_LEVEL3_C     ,VSERT_DATA.SGFDM01.MNMKT_MAIN_MKT_C     ,VSERT_DATA.SGFDM01.MKT_CUST_TYPE_C).  Aggregate Intermediate
     Results are computed globally, then placed in Spool 23.  The size
     of Spool 23 is estimated with no confidence to be 34,759,228 rows     (12,026,692,888 bytes).  The estimated time for this step is 1.02
     seconds.

 Explian(with Select)

7) We do an all-AMPs JOIN step from Spool 13 (Last Use) by way of an
     all-rows scan, which is joined to Spool 15 (Last Use) by way of an
     all-rows scan.  Spool 13 and Spool 15 are joined using a hash join
     of 2 partitions, with a join condition of ("((((TRIM(BOTH FROM
     {LeftTable}.SPRFXPRT_PREFIX_R ))||(TRIM(BOTH FROM
     {LeftTable}.SBASEPRT_NBR )))||(TRIM(BOTH FROM
     {LeftTable}.SUFXPRT_SUFFIX_NBR )))= (((TRIM(BOTH FROM
     {RightTable}.GFDP09_PREFIX_R ))||(TRIM(BOTH FROM
     {RightTable}.GFDP09_BASE_R )))||(TRIM(BOTH FROM
     {RightTable}.GFDP09_SUFFIX_R )))) AND (COUNTRY_ISO3_C =
     GFDP09_MRKT_C)").  The result goes into Spool 16 (all_amps)
     (compressed columns allowed), which is redistributed by the hash
     code of (VSERT_DATA.SGFDP09_PRT_ATTR_TBL.GFDP09_MRKT_C,
     VSERT_DATA.SGFDT96_CORP_GPRFT_TBL.MKT_CUSTOMER_ID_C) to all AMPs.
     Then we do a SORT to order Spool 16 by row hash.  The size of
     Spool 16 is estimated with no confidence to be 6,345 rows (
     812,160 bytes).  The estimated time for this step is 0.03 seconds.
  8) We execute the following steps in parallel.
       1) We do an all-AMPs JOIN step from VSERT_DATA.SGFDM01 in view
          VSERT_VIEW.SGFDM01 by way of a RowHash match scan with a
          condition of ("VSERT_DATA.SGFDM01 in view
          VSERT_VIEW.SGFDM01.COUNTRY_ISO3_1_C <> ' '"), which is joined
          to Spool 16 (Last Use) by way of a RowHash match scan locking
          VSERT_DATA.SGFDM01 for access.  VSERT_DATA.SGFDM01 and Spool 16
          are joined using a merge join, with a join condition of (
          "(GFDP09_MRKT_C = VSERT_DATA.SGFDM01.COUNTRY_ISO3_C) AND
          ((MKT_CUSTOMER_ID_C = VSERT_DATA.SGFDM01.MKT_CUSTOMER_ID_C)
          AND (COUNTRY_ISO3_C = VSERT_DATA.SGFDM01.COUNTRY_ISO3_C ))").
          The result goes into Spool 17 (all_amps) (compressed columns
          allowed), which is built locally on the AMPs.  The size of
          Spool 17 is estimated with no confidence to be 6,345 rows (
          875,610 bytes).  The estimated time for this step is 0.01
          seconds.
       2) We do an all-AMPs RETRIEVE step from
          VSERT_DATA.SGFDM02_MKT_RGN_TBL in view VSERT_VIEW.SGFDM02 by
          way of an all-rows scan with no residual conditions into
          Spool 18 (all_amps) (compressed columns allowed), which is
          duplicated on all AMPs.  The size of Spool 18 is estimated
          with high confidence to be 80,976 rows (1,700,496 bytes).
          The estimated time for this step is 0.01 seconds.
  9) We do an all-AMPs JOIN step from Spool 17 (Last Use) by way of an
     all-rows scan, which is joined to Spool 18 (Last Use) by way of an
     all-rows scan.  Spool 17 and Spool 18 are joined using a product
     join, with a join condition of ("(COUNTRY_ISO3_1_C =
     GFDM02_SUB_REG_C) OR (COUNTRY_ISO3_1_C = COUNTRY_ISO3_1_C)").  The
     result goes into Spool 19 (all_amps) (compressed columns allowed),
     which is redistributed by the hash code of (
     VSERT_DATA.SGFDP09_PRT_ATTR_TBL.GFDP09_SUFFIX_R,
     VSERT_DATA.SGFDP09_PRT_ATTR_TBL.GFDP09_BASE_R,
     VSERT_DATA.SGFDP09_PRT_ATTR_TBL.GFDP09_PREFIX_R, 'AP') to all AMPs.
     Then we do a SORT to order Spool 19 by row hash.  The size of
     Spool 19 is estimated with no confidence to be 98,501 rows (
     14,184,144 bytes).  The estimated time for this step is 0.05
     seconds.
 10) We do an all-AMPs JOIN step from VSERT_DATA.SGFDP07_PRT_MSTR_TBL in
     view VSERT_VIEW.SGFDP07 by way of a RowHash match scan with a
     condition of ("VSERT_DATA.SGFDP07_PRT_MSTR_TBL in view
     VSERT_VIEW.SGFDP07.GFDP07_SLS_ANLYS_GRP_C = 'AP'"), which is joined
     to Spool 19 (Last Use) by way of a RowHash match scan.
     VSERT_DATA.SGFDP07_PRT_MSTR_TBL and Spool 19 are joined using a
     merge join, with a join condition of (
     "(VSERT_DATA.SGFDP07_PRT_MSTR_TBL.GFDP07_SUFFIX_R = GFDP09_SUFFIX_R)
     AND ((VSERT_DATA.SGFDP07_PRT_MSTR_TBL.GFDP07_BASE_R = GFDP09_BASE_R)
     AND (VSERT_DATA.SGFDP07_PRT_MSTR_TBL.GFDP07_PREFIX_R =
     GFDP09_PREFIX_R ))").  The result goes into Spool 4 (all_amps)
     (compressed columns allowed), which is built locally on the AMPs.
     The size of Spool 4 is estimated with no confidence to be
     46,345,637 rows (8,342,214,660 bytes).  The estimated time for
     this step is 1.06 seconds.
 11) We do an all-AMPs SUM step to aggregate from Spool 4 (Last Use) by
     way of an all-rows scan, grouping by field1 (
     'AP',VSERT_DATA.SGFDP09_PRT_ATTR_TBL.GFDP09_PREFIX_R     ,VSERT_DATA.SGFDP09_PRT_ATTR_TBL.GFDP09_BASE_R     ,VSERT_DATA.SGFDP09_PRT_ATTR_TBL.GFDP09_SUFFIX_R     ,VSERT_DATA.SGFDT96_CORP_GPRFT_TBL.COUNTRY_ISO3_C     ,VSERT_DATA.SGFDT96_CORP_GPRFT_TBL.MKT_CUSTOMER_ID_C ,( CASE WHEN     (VSERT_DATA.SGFDT96_CORP_GPRFT_TBL.DEPOT_LOCATION_C = 'DSHIP') THEN     ('DSHIP') ELSE ('DEPOT') END     ),VSERT_DATA.SGFDT96_CORP_GPRFT_TBL.GFDT96_MNTHYR_Y     ,VSERT_DATA.SGFDP09_PRT_ATTR_TBL.GFDP09_PTHR_PRODUCT_C     ,VSERT_DATA.SGFDP07_PRT_MSTR_TBL.GFDP07_PART_X     ,VSERT_DATA.SGFDP07_PRT_MSTR_TBL.GFDP07_ENGG_PART_R     ,VSERT_DATA.SGFDM02_MKT_RGN_TBL.GFDM02_REG_C     ,VSERT_DATA.SGFDM02_MKT_RGN_TBL.GFDM02_SUB_REG_C     ,VSERT_DATA.SGFDM01.COUNTRY_ISO3_1_C     ,VSERT_DATA.SGFDM01.OLEV1_SA_LEVEL1_C     ,VSERT_DATA.SGFDM01.OLEV3_SA_LEVEL3_C     ,VSERT_DATA.SGFDM01.MNMKT_MAIN_MKT_C     ,VSERT_DATA.SGFDM01.MKT_CUST_TYPE_C).  Aggregate Intermediate
     Results are computed globally, then placed in Spool 21.  The size
     of Spool 21 is estimated with no confidence to be 34,759,228 rows     (12,026,692,888 bytes).  The estimated time for this step is 1.02
     seconds.

 

Teradata Employee

Re: No more spool while inserting into table

Well, those minor differences should have no effect on spool space required. And the optimizer thinks the final redistribution before merge is smaller than the preceding GROUP BY.

Are the estimates "reasonably" correct? Maybe start looking at stats to see if you can get something better than "no confidence".

Enthusiast

Re: No more spool while inserting into table

Hi Fred,

 

Will defining secondary indexes on specific main tables with no SI and then collecting stats on them will help?

 

some of the main tables have SI defined already.

Teradata Employee

Re: No more spool while inserting into table

There's nothing here that makes me think secondary indexes would help with the spool issue at all. And note that the EXPLAIN does not show use of any existing secondary indexes, either.

 

You don't need an index to collect stats.