Spool space issue while Inserting into Table

General

Spool space issue while Inserting into Table

Hi All,

When I tried to run the belong query it showed a spool space issue. However, I am able to run the select part. I have collected all the stats that are required and tried but no use. When gone through the explain plan it shows 15minutes to merge all-AMPs into Target table. Please help me in this.

Query:-

INSERT INTO TEMP_SCHEMA.R_TLFR_TERMN_DET

(

TLFR_CORP_ID,

CORP_GROUP,

CNTCT_NAME,

CNTCT_NUM,

CORP_NAME,

CORP_ADDR,

CORP_CITY,

CORP_ST,

CORP_ZIPCODE,

TERM_ANI_TRKGP_PROD,

PROD,

ACCT_NUM,

REMIT_ID,

TERM_BLPYR_ID,

LOC_NAME,

LOC_ADDR,

LOC_CITY,

LOC_ST,

LOC_ZIPCODE,

TLFR_NUM,

NUM_DESC,

DATE_INSTLD,

LOG_TERM,

DNIS,

TRKGP,

PLAN,

SW,

PLAN_DESC,

ROTG_PLAN_ID,

CROSS_CORP,

ECR,

INTL_INBND,

TOD,

DOW,

PCT_ALLCTN,

PAYPHN_BLOCKING,

SRC_SYS,

OPT_IND,

R_INVNT_DTL_MTCH_KEY

)

SELECT ISA.SVC_ACCT_NUM AS "TOLL FREE CORP ID",

NULL /*NULLIF(SAAIR.ALT_ID,' ')*/ AS "CORP GROUP",

CASE WHEN POSITION('  ' IN SVC.PARTY_NAME) > 1

THEN SUBSTR(SVC.PARTY_NAME,1,POSITION('  ' IN SVC.PARTY_NAME))||TRIM(BOTH ' ' FROM SUBSTR(SVC.PARTY_NAME,POSITION('  ' IN SVC.PARTY_NAME),(CHARACTER_LENGTH(SVC.PARTY_NAME)-POSITION('  ' IN SVC.PARTY_NAME))+1))

ELSE SVC.PARTY_NAME END AS "CONTACT NAME",

'('||SUBSTR(SVC.CNTCT_TEL_NUM,1,3)||')  '||SUBSTR(SVC.CNTCT_TEL_NUM,4,3)||'-'||SUBSTR(SVC.CNTCT_TEL_NUM,7,4) AS "CONTACT NUMBER",

ISA.SVC_ACCT_NAME AS "CORP NAME",

NULL /* NULLIF(SVA.LINE_1_TEXT||' '||SVA.LINE_2_TEXT||' '||SVA.LINE_3_TEXT,'') */ AS "CORP ADDRESS",

NULL /* SVA.CITY_NAME */ AS "CORP CITY",

NULL /* NULLIF(SVA.TERR_CODE,' ') */ AS "CORP STATE",

NULL /* SVA.POSTAL_NUM */ AS "CORP ZIPCODE",

CASE WHEN (SI.SRC_SYS_ID = 945 AND TP.TERMN_TYPE_CODE = 'STS') THEN '(999) 999-9999'

WHEN (SI.SRC_SYS_ID = 945 AND NULLIF(TP.ANI_DIGITS_NUM,'') IS NULL) THEN TP.TRNK_ID

WHEN (SI.SRC_SYS_ID = 945 AND TP.TERMN_TYPE_CODE <> 'STS' AND NULLIF(TP.ANI_DIGITS_NUM,'') IS NOT NULL) THEN

CASE WHEN SUBSTR(TP.ANI_DIGITS_NUM,1,3) = '001' THEN '('||SUBSTR(TP.ANI_DIGITS_NUM,4,3)||')  '||SUBSTR(TP.ANI_DIGITS_NUM,7,3)||'-'||SUBSTR(TP.ANI_DIGITS_NUM,10,4)

WHEN CHARACTER_LENGTH(TP.ANI_DIGITS_NUM) = 10 THEN '('||SUBSTR(TP.ANI_DIGITS_NUM,1,3)||')  '||SUBSTR(TP.ANI_DIGITS_NUM,4,3)||'-'||SUBSTR(TP.ANI_DIGITS_NUM,7,4)

ELSE TP.ANI_DIGITS_NUM END

WHEN SI.SRC_SYS_ID <> 945 THEN

CASE WHEN SUBSTR(TP.ANI_DIGITS_NUM,1,3) = '001' THEN '('||SUBSTR(TP.ANI_DIGITS_NUM,4,3)||')  '||SUBSTR(TP.ANI_DIGITS_NUM,7,3)||'-'||SUBSTR(TP.ANI_DIGITS_NUM,10,4)

WHEN CHARACTER_LENGTH(TP.ANI_DIGITS_NUM) = 10 THEN '('||SUBSTR(TP.ANI_DIGITS_NUM,1,3)||')  '||SUBSTR(TP.ANI_DIGITS_NUM,4,3)||'-'||SUBSTR(TP.ANI_DIGITS_NUM,7,4)

ELSE TP.ANI_DIGITS_NUM END

ELSE 'UNKNOWN' END AS "TERM ANI TRUNK GROUP",

NULL AS "PRODUCT",

NULLIF(TP.CUST_SVC_CODE,' ') AS "ACCOUNT NUMBER",

NULLIF(SAAIR_BLP.ALT_ID,' ') AS "REMIT ID",

NULL /* NULLIF(ISA_CM.ALT_ID,' ') */ AS "TERM BILL PAYER ID",

NULL /* TRIM(BOTH ' ' FROM CASE WHEN SI.SRC_SYS_ID = 945 THEN ISA.SVC_ACCT_NAME ELSE ISA_CM.SVC_INST_ADDR_NAME END) */ AS "LOCATION NAME",

NULL /* TRIM(BOTH ' ' FROM CASE WHEN SI.SRC_SYS_ID = 945 THEN NULLIF(SVA.LINE_1_TEXT||' '||SVA.LINE_2_TEXT||' '||SVA.LINE_3_TEXT,' ')

ELSE NULLIF(ISA_CM.LINE_1_TEXT,' ') END) */ AS "LOCATION ADDRESS",

NULL /* TRIM(BOTH ' ' FROM CASE WHEN SI.SRC_SYS_ID = 945 THEN NULLIF(SVA.CITY_NAME,' ')

ELSE NULLIF(ISA_CM.CITY_NAME,' ') END) */ AS "LOCATION CITY",

NULL /* TRIM(BOTH ' ' FROM CASE WHEN SI.SRC_SYS_ID = 945 THEN NULLIF(SVA.TERR_CODE,' ')

ELSE NULLIF(ISA_CM.TERR_CODE,' ') END) */ AS "LOCATION STATE",

NULL /* TRIM(BOTH ' ' FROM CASE WHEN SI.SRC_SYS_ID = 945 THEN NULLIF(SVA.POSTAL_NUM,' ')

ELSE NULLIF(ISA_CM.POSTAL_NUM,' ') END) */ AS "LOCATION ZIPCODE",

TF.TOLL_FREE_NUM AS "TOLL FREE NUMBER",

TF.NUM_DESC AS "NUMBER DESCRIPTION",

SISH.START_DATE AS "DATE INSTALLED",

TP.LGCL_TERMN_ID AS "LOG TERM",

TP.DNIS_OTPLS_DIGITS_NUM AS "DNIS",

TP.TRNK_ID AS "TRUNK GROUP",

RP.ROTG_PLAN_LVL_CODE AS "PLAN",

TP.SW_VAL_TEXT AS "SWITCH",

RP.ROTG_PLAN_DESC AS "PLAN DESCRIPTION",

RP.SRC_ROTG_PLAN_ID AS "ROUTING PLAN ID",

RP.CROSS_CORP_IND AS "CROSS CORP",

CASE WHEN NULLIF(RP.ECR_IND,'') IS NULL THEN 'N'

WHEN RP.ECR_IND = 'N' THEN 'NR'

WHEN RP.ECR_IND = 'R' THEN 'R'

ELSE 'N' END AS "ECR",

RP.INTL_INBND_IND AS "INTERNATIONAL INBOUND",

RP.TOD_IND AS "TIME OF DAY",

RP.DOW_IND AS "DAY OF WEEK",

RP.PCT_ALLCTN_IND AS "PERCENT ALLOCATION",

RP.PAYBLOCK_IND AS "PAYPHONE BLOCKING",

CASE WHEN SI.SRC_SYS_ID = 945 THEN 'IXPLUS'

WHEN SI.SRC_SYS_ID = 946 THEN 'NETCAP' END AS "SOURCE SYSTEM",

CASE WHEN SI.SRC_SYS_ID = 945 THEN '1'

WHEN SI.SRC_SYS_ID = 946 THEN '2/3' END AS "OPTION",

ISA.SVC_ACCT_SK

FROM  BASE_SCHEMA.TOLL_FREE_LINE TF

INNER JOIN BASE_SCHEMA.SVC_INST SI

ON SI.SVC_INST_ID = TF.TOLL_FREE_NUM AND

SI.SVC_INST_TYPE_CODE IN ('TF1','TF2') AND

SI.SRC_SYS_ID=946 AND

SI.SRC_SYS_ID = TF.SRC_SYS_ID

INNER JOIN (SEL SISH.SVC_ACCT_SK, SISH.SVC_INST_SK, SISH.START_DATE

FROM BASE_SCHEMA.SVC_INST_STAT_HSTRY SISH

INNER JOIN BASE_SCHEMA.STAT_CODE_REF SCR

ON SCR.SRC_SYS_ID=946 AND

SCR.STAT_SK = SISH.STAT_SK AND

SCR.STAT_CODE IN ('W','A') AND

SISH.END_DATE IS NULL) SISH

ON SISH.SVC_INST_SK = SI.SVC_INST_SK

INNER JOIN BASE_SCHEMA.INV_SVC_ACCT ISA

ON ISA.SVC_ACCT_SK = SISH.SVC_ACCT_SK AND

ISA.SVC_ACCT_TYPE_CODE='CORE_CORP_ID'

/*INNER JOIN (SEL SAAIR.SVC_ACCT_SK, SAAIR.END_DATE, ALT_ID, ALT_ID_TYPE_CODE

FROM BASE_SCHEMA.SVC_ACCT_ALT_IDN_RELSP SAAIR

INNER JOIN BASE_SCHEMA.ALT_IDN AIN

ON AIN.ALT_ID_TYPE_CODE = 'NASP' AND

AIN.ALT_IDN_SK = SAAIR.ALT_IDN_SK AND

AIN.ALT_ID = '30LMTD') SAAIRN                                                                          -------------------------------- GIVE NASP ID HERE.

ON SAAIRN.SVC_ACCT_SK = ISA.SVC_ACCT_SK AND

SAAIRN.END_DATE IS NULL*/

LEFT OUTER JOIN (SEL PRT.PARTY_NAME, PRT.CNTCT_TEL_NUM, SVC.SVC_ACCT_SK

FROM BASE_SCHEMA.SVC_ACCT_CNTCT SVC

INNER JOIN BASE_SCHEMA.PARTY PRT

ON PRT.PARTY_SK = SVC.PARTY_SK) SVC

ON SVC.SVC_ACCT_SK = ISA.SVC_ACCT_SK

LEFT OUTER JOIN BASE_SCHEMA.ROTG_PLAN RP

ON RP.SVC_ACCT_SK = ISA.SVC_ACCT_SK AND

RP.TOLL_FREE_SK = TF.TOLL_FREE_SK

LEFT OUTER JOIN (SELECT RPTPR.ROTG_PLAN_SK, TP.*

FROM BASE_SCHEMA.ROTG_PLAN_TERMN_PT_RELSP RPTPR

INNER JOIN BASE_SCHEMA.TERMN_PT TP

ON TP.TERMN_PT_SK = RPTPR.TERMN_PT_SK) TP

ON TP.ROTG_PLAN_SK = RP.ROTG_PLAN_SK

LEFT OUTER JOIN (SEL SAAIR.SVC_ACCT_SK, SAAIR.END_DATE, AI.ALT_ID, AI.ALT_ID_TYPE_CODE

FROM (SELECT SVC_ACCT_SK,END_DATE,ALT_IDN_SK FROM BASE_SCHEMA.SVC_ACCT_ALT_IDN_RELSP WHERE END_DATE IS NULL) SAAIR

INNER JOIN BASE_SCHEMA.ALT_IDN AI

ON AI.ALT_IDN_SK = SAAIR.ALT_IDN_SK AND

AI.ALT_ID_TYPE_CODE ='CORE_BILLPAYER_ID') SAAIR_BLP

ON SAAIR_BLP.SVC_ACCT_SK = ISA.SVC_ACCT_SK

Explain:-

15) We execute the following steps in parallel. 

      1) We do an all-AMPs RETRIEVE step from EDW_OFFSHORE_BASE.SVC by

         way of an all-rows scan with no residual conditions into Spool

         2 (all_amps), which is redistributed by the hash code of (

         EDW_OFFSHORE_BASE.SVC.PARTY_SK) to all AMPs.  Then we do a

         SORT to order Spool 2 by row hash.  The size of Spool 2 is

         estimated with high confidence to be 5,556,970 rows (

         161,152,130 bytes).  The estimated time for this step is 3.13

         seconds. 

      2) We do an all-AMPs RETRIEVE step from EDW_OFFSHORE_BASE.AI by

         way of an all-rows scan with a condition of (

         "EDW_OFFSHORE_BASE.AI.ALT_ID_TYPE_CODE = 'CORE_BILLPAYER_ID'")

         into Spool 3 (all_amps), which is built locally on the AMPs. 

         The size of Spool 3 is estimated with low confidence to be

         1,502,707 rows (58,605,573 bytes).  The estimated time for

         this step is 2.43 seconds. 

      3) We do an all-AMPs RETRIEVE step from

         EDW_OFFSHORE_BASE.SVC_ACCT_ALT_IDN_RELSP by way of an all-rows

         scan with a condition of (

         "EDW_OFFSHORE_BASE.SVC_ACCT_ALT_IDN_RELSP.END_DATE IS NULL")

         into Spool 4 (all_amps), which is redistributed by the hash

         code of (EDW_OFFSHORE_BASE.SVC_ACCT_ALT_IDN_RELSP.ALT_IDN_SK)

         to all AMPs.  The size of Spool 4 is estimated with low

         confidence to be 21,679,682 rows (628,710,778 bytes).  The

         estimated time for this step is 7.87 seconds. 

 16) We execute the following steps in parallel. 

      1) We do an all-AMPs JOIN step from EDW_OFFSHORE_BASE.PRT by way

         of a RowHash match scan with no residual conditions, which is

         joined to Spool 2 (Last Use) by way of a RowHash match scan. 

         EDW_OFFSHORE_BASE.PRT and Spool 2 are joined using a merge

         join, with a join condition of (

         "EDW_OFFSHORE_BASE.PRT.PARTY_SK = PARTY_SK").  The result goes

         into Spool 5 (all_amps), which is redistributed by the hash

         code of (EDW_OFFSHORE_BASE.SVC.SVC_ACCT_SK) to all AMPs.  Then

         we do a SORT to order Spool 5 by row hash.  The size of Spool

         5 is estimated with low confidence to be 5,556,970 rows (

         889,115,200 bytes).  The estimated time for this step is 3.82

         seconds. 

      2) We do an all-AMPs RETRIEVE step from EDW_OFFSHORE_BASE.TP by

         way of an all-rows scan with no residual conditions into Spool

         6 (all_amps) fanned out into 13 hash join partitions, which is

         built locally on the AMPs.  The size of Spool 6 is estimated

         with low confidence to be 14,148,432 rows (962,093,376 bytes). 

         The estimated time for this step is 4.18 seconds. 

      3) We do an all-AMPs RETRIEVE step from EDW_OFFSHORE_BASE.RPTPR

         by way of an all-rows scan with no residual conditions into

         Spool 7 (all_amps) fanned out into 13 hash join partitions,

         which is redistributed by the hash code of (

         EDW_OFFSHORE_BASE.RPTPR.TERMN_PT_SK) to all AMPs.  The size of

         Spool 7 is estimated with low confidence to be 25,060,176 rows

         (726,745,104 bytes).  The estimated time for this step is

         20.81 seconds. 

 17) We do an all-AMPs JOIN step from Spool 6 (Last Use) by way of an

     all-rows scan, which is joined to Spool 7 (Last Use) by way of an

     all-rows scan.  Spool 6 and Spool 7 are joined using a hash join

     of 13 partitions, with a join condition of ("TERMN_PT_SK =

     TERMN_PT_SK").  The result goes into Spool 8 (all_amps), which is

     redistributed by the hash code of (

     EDW_OFFSHORE_BASE.RPTPR.ROTG_PLAN_SK) to all AMPs.  Then we do a

     SORT to order Spool 8 by row hash.  The size of Spool 8 is

     estimated with low confidence to be 25,657,944 rows (

     1,744,740,192 bytes).  The estimated time for this step is 13.75

     seconds. 

 18) We do an all-AMPs RETRIEVE step from EDW_OFFSHORE_BASE.SCR by way

     of an all-rows scan with a condition of (

     "(EDW_OFFSHORE_BASE.SCR.SRC_SYS_ID = 946) AND

     ((EDW_OFFSHORE_BASE.SCR.STAT_CODE = 'A') OR

     (EDW_OFFSHORE_BASE.SCR.STAT_CODE = 'W'))") into Spool 9 (all_amps),

     which is duplicated on all AMPs.  The size of Spool 9 is estimated

     with low confidence to be 864 rows (21,600 bytes).  The estimated

     time for this step is 0.05 seconds. 

 19) We do an all-AMPs JOIN step from Spool 9 (Last Use) by way of an

     all-rows scan, which is joined to EDW_OFFSHORE_BASE.SISH by way of

     an all-rows scan with a condition of (

     "EDW_OFFSHORE_BASE.SISH.END_DATE IS NULL").  Spool 9 and

     EDW_OFFSHORE_BASE.SISH are joined using a single partition hash_

     join, with a join condition of ("STAT_SK =

     EDW_OFFSHORE_BASE.SISH.STAT_SK").  The result goes into Spool 10

     (all_amps), which is built locally on the AMPs into 8 hash join

     partitions.  The size of Spool 10 is estimated with low confidence

     to be 16,090,810 rows (595,359,970 bytes).  The estimated time for

     this step is 34.54 seconds. 

 20) We do an all-AMPs JOIN step from EDW_OFFSHORE_BASE.ISA by way of a

     RowHash match scan with a condition of (

     "EDW_OFFSHORE_BASE.ISA.SVC_ACCT_TYPE_CODE = 'CORE_CORP_ID'"),

     which is joined to Spool 5 (Last Use) by way of a RowHash match

     scan.  EDW_OFFSHORE_BASE.ISA and Spool 5 are left outer joined

     using a merge join, with a join condition of ("SVC_ACCT_SK =

     EDW_OFFSHORE_BASE.ISA.SVC_ACCT_SK").  The result goes into Spool

     11 (all_amps), which is duplicated on all AMPs into 8 hash join

     partitions.  The size of Spool 11 is estimated with low confidence

     to be 160,335,072 rows (34,151,370,336 bytes).  The estimated time

     for this step is 22.73 seconds. 

 21) We do an all-AMPs JOIN step from Spool 10 (Last Use) by way of an

     all-rows scan, which is joined to Spool 11 (Last Use) by way of an

     all-rows scan.  Spool 10 and Spool 11 are joined using a hash join

     of 8 partitions, with a join condition of ("SVC_ACCT_SK =

     SVC_ACCT_SK").  The result goes into Spool 12 (all_amps), which is

     redistributed by the hash code of (

     EDW_OFFSHORE_BASE.ISA.SVC_ACCT_SK) to all AMPs.  Then we do a SORT

     to order Spool 12 by row hash.  The size of Spool 12 is estimated

     with low confidence to be 1,442,336 rows (330,294,944 bytes).  The

     estimated time for this step is 7.28 seconds. 

 22) We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of an

     all-rows scan, which is joined to Spool 4 (Last Use) by way of an

     all-rows scan.  Spool 3 and Spool 4 are joined using a single

     partition hash join, with a join condition of ("ALT_IDN_SK =

     ALT_IDN_SK").  The result goes into Spool 13 (all_amps), which is

     redistributed by the hash code of (

     EDW_OFFSHORE_BASE.SVC_ACCT_ALT_IDN_RELSP.SVC_ACCT_SK) to all 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 7,542,576 rows (

     294,160,464 bytes).  The estimated time for this step is 1.74

     seconds. 

 23) We do an all-AMPs JOIN step from Spool 12 (Last Use) by way of a

     RowHash match scan, which is joined to Spool 13 (Last Use) by way

     of a RowHash match scan.  Spool 12 and Spool 13 are left outer

     joined using a merge join, with a join condition of ("SVC_ACCT_SK

     = SVC_ACCT_SK").  The result goes into Spool 14 (all_amps), which

     is redistributed by the hash code of (

     EDW_OFFSHORE_BASE.SISH.SVC_INST_SK) to all AMPs.  Then we do a

     SORT to order Spool 14 by row hash.  The size of Spool 14 is

     estimated with low confidence to be 1,744,285 rows (430,838,395

     bytes).  The estimated time for this step is 0.81 seconds. 

 24) We execute the following steps in parallel. 

      1) We do an all-AMPs JOIN step from EDW_OFFSHORE_BASE.SI by way

         of a RowHash match scan with a condition of (

         "(EDW_OFFSHORE_BASE.SI.SRC_SYS_ID = 946) AND

         ((EDW_OFFSHORE_BASE.SI.SVC_INST_TYPE_CODE = 'TF2') OR

         (EDW_OFFSHORE_BASE.SI.SVC_INST_TYPE_CODE = 'TF1'))"), which is

         joined to Spool 14 (Last Use) by way of a RowHash match scan. 

         EDW_OFFSHORE_BASE.SI and Spool 14 are joined using a merge

         join, with a join condition of ("(SRC_SYS_ID =

         EDW_OFFSHORE_BASE.SI.SRC_SYS_ID) AND (SVC_INST_SK =

         EDW_OFFSHORE_BASE.SI.SVC_INST_SK)").  The input table

         EDW_OFFSHORE_BASE.SI will not be cached in memory, but it is

         eligible for synchronized scanning.  The result goes into

         Spool 15 (all_amps), which is redistributed by the hash code

         of (EDW_OFFSHORE_BASE.SCR.SRC_SYS_ID,

         EDW_OFFSHORE_BASE.SI.SRC_SYS_ID,

         EDW_OFFSHORE_BASE.SI.SVC_INST_ID) to all AMPs into 3 hash join

         partitions.  The size of Spool 15 is estimated with low

         confidence to be 1,744,285 rows (511,075,505 bytes).  The

         estimated time for this step is 48.15 seconds. 

      2) We do an all-AMPs RETRIEVE step from EDW_OFFSHORE_BASE.TF by

         way of an all-rows scan with a condition of (

         "EDW_OFFSHORE_BASE.TF.SRC_SYS_ID = 946") into Spool 16

         (all_amps) fanned out into 3 hash join partitions, which is

         redistributed by the hash code of (

         EDW_OFFSHORE_BASE.TF.TOLL_FREE_NUM,

         EDW_OFFSHORE_BASE.TF.SRC_SYS_ID,

         EDW_OFFSHORE_BASE.TF.SRC_SYS_ID) to all AMPs.  The size of

         Spool 16 is estimated with low confidence to be 37,944,808

         rows (1,821,350,784 bytes).  The estimated time for this step

         is 11.84 seconds. 

 25) We do an all-AMPs JOIN step from Spool 15 (Last Use) by way of an

     all-rows scan, which is joined to Spool 16 (Last Use) by way of an

     all-rows scan.  Spool 15 and Spool 16 are joined using a hash join

     of 3 partitions, with a join condition of ("(SRC_SYS_ID =

     SRC_SYS_ID) AND ((SRC_SYS_ID = SRC_SYS_ID) AND ((SVC_INST_ID =

     TOLL_FREE_NUM) AND (SRC_SYS_ID = SRC_SYS_ID )))").  The result

     goes into Spool 17 (all_amps), which is redistributed by the hash

     code of (EDW_OFFSHORE_BASE.ISA.SVC_ACCT_SK,

     EDW_OFFSHORE_BASE.TF.Toll_Free_SK) to all AMPs.  Then we do a SORT

     to order Spool 17 by row hash.  The size of Spool 17 is estimated

     with low confidence to be 1,723,498 rows (463,620,962 bytes).  The

     estimated time for this step is 2.19 seconds. 

 26) We do an all-AMPs JOIN step from EDW_OFFSHORE_BASE.RP by way of a

     RowHash match scan with a condition of ("NOT

     (EDW_OFFSHORE_BASE.RP.Toll_Free_SK IS NULL)"), which is joined to

     Spool 8 (Last Use) by way of a RowHash match scan. 

     EDW_OFFSHORE_BASE.RP and Spool 8 are left outer joined using a

     merge join, with a join condition of ("ROTG_PLAN_SK =

     EDW_OFFSHORE_BASE.RP.ROTG_PLAN_SK").  The result goes into Spool

     18 (all_amps), which is redistributed by the hash code of (

     EDW_OFFSHORE_BASE.RP.SVC_ACCT_SK,

     EDW_OFFSHORE_BASE.RP.Toll_Free_SK) to all AMPs.  Then we do a SORT

     to order Spool 18 by row hash.  The size of Spool 18 is estimated

     with low confidence to be 25,839,515 rows (2,506,432,955 bytes). 

     The estimated time for this step is 21.57 seconds. 

 27) We do an all-AMPs JOIN step from Spool 17 (Last Use) by way of a

     RowHash match scan, which is joined to Spool 18 (Last Use) by way

     of a RowHash match scan.  Spool 17 and Spool 18 are left outer

     joined using a merge join, with a join condition of (

     "(SVC_ACCT_SK = SVC_ACCT_SK) AND (Toll_Free_SK = Toll_Free_SK)"). 

     The result goes into Spool 1 (all_amps), which is redistributed by

     the hash code of (EDW_OFFSHORE_BASE.ISA.SVC_ACCT_NUM

     (VARCHAR(100), CHARACTER SET LATIN, NOT CASESPECIFIC, NAMED

     TLFR_CORP_ID, FORMAT 'X(100)', TITLE 'TollFree/Corp ID', NULL)) to

     all AMPs.  Then we do a SORT to order Spool 1 by row hash.  The

     size of Spool 1 is estimated with low confidence to be 3,478,442

     rows (4,699,375,142 bytes).  The estimated time for this step is

     4.33 seconds. 

 28) We do an all-AMPs MERGE into EDW_OFFSHORE_TEMP.R_TLFR_TERMN_DET

     from Spool 1 (Last Use).  The size is estimated with low

     confidence to be 3,478,442 rows.  The estimated time for this step

     is 15 minutes and 24 seconds. 

 29) We spoil the parser's dictionary cache for the table. 

 30) 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. 

Tags (1)
6 REPLIES

Re: Spool space issue while Inserting into Table

The record count when running the select alone is 67901628.

Teradata Employee

Re: Spool space issue while Inserting into Table

Are the PI values for the target (looks like EDW_OFFSHORE_BASE.ISA.SVC_ACCT_NUM) highly skewed?

Even if not skewed, the redistribution by target PI to prepare for the MERGE step will require enough spool space to hold two copies of the data. The optimizer is estimating 3.5 million rows will occupy 4.7GB. If the real cardinality is 68 million then the real size of the spool file would be almost 20x that value.

Re: Spool space issue while Inserting into Table

Hi Fred,

Thanks for replying.

The PI for INV_SVC_ACCT is SVC_ACCT_SK (Surrogate Key). The target table PI was TLFR_CORP_ID but now it is changed to TLFR_NUM which is being populated from TOLL_FREE_LINE table.

The code is still simplified by the use of different temp tables that now holds the data after using common joins (like ACCT_DTL which holds data related to account like address, contact etc.,.). I also wrote two inserts which in turn will try to load the data in smaller chunks (i.e., 25 million on an avg). But still I face this spool issue at one insert (the one which has to load 25 million records).

Now, Overall record count should be 43 million.

N/A

Re: Spool space issue while Inserting into Table

Hi Kalyan,

as Fred already wrote, you should check if SVC_ACCT_NUM is highly skewed (which will, btw, also result in a skewed target table):

Just remove all other columns from your query and run

SELECT ISA.SVC_ACCT_NUM AS "TOLL FREE CORP ID", count(*)

from ...

group by 1

order by 1 desc

Dieter

Re: Spool space issue while Inserting into Table

Hi ,

i have a query . In that left join is costing more and its showing 8 min in plan. 

One of the  joining column is highly skewed with a single value. 

can anybody suggest how can i make that join work fast.

SELECT

COL1,

COL2,

TRIM(COL3) COL3 ,

FROM TABLE  A 

LEFT JOIN

(

SELECT

COL1 ,

COL2,

CAST(COL3 AS VARCHAR(50)) COL3,

FROM TABLE B 

)   SALES_ORDER_CSCC 

ON TABLE A .COL3  = TABLE B.COL3  

TABLE A.COL3 is skewed with 'UNKNOWN'

Thanks,

Suresh.

Re: Spool space issue while Inserting into Table

Hi,

Sorry, was held up with something else. Yes, there was skew for that. I have dropped the target table and then recreated it with TLFR_NUM column which was near to Unique values. The query ran fine.

Thanks a lot for all the suggestions.