No more Spool space for the user : query optimization

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Fan

No more Spool space for the user : query optimization

Hi Team,

 

Request you to help me in tuning the query(provided below).

I was getting an error: No more spool space for the user  even after the user having 8TB of spool space

I have modified the query like (getting only required columns in the select query and adding the filter conditions before joining the tables)

 

 

SELECT
RAA.COLUMN1, RAA.COLUMN2,RAD.column1.... etc
FROM
(SELECT * FROM FP.TABLE_RAADDR WHERE FIN_PROD_CD = '1') RAA
INNER JOIN
(SELECT ACCT_STAT_CD,CURR_CR_LIM_AMT,OPN_TO_BUY_AMT,A_ID_VAL,B_ID,P_DT,P_FLG
FROM FP.TABLE_RADTL WHERE P_FLG = 'D' AND P_DT = CURRENT_DATE - 1) RAD
ON RAA.A_ID_VAL = RAD.A_ID_VAL AND RAA.SRC_B_ID = RAD.B_ID
LEFT OUTER JOIN
(SELECT SPCODE,CCODE FROM C1.SP WHERE (TRIM(CCODE) ='CAN' AND TRIM(SPCODE)<>'AS') OR TRIM(CCODE) ='USA') SP
ON TRIM(RAA.ST_CD)=SP.SPCODE
LEFT OUTER JOIN
(SELECT SPCODE,CCODE FROM C1.SP WHERE (TRIM(CCODE) ='CAN' AND TRIM(SPCODE)<>'AS') OR TRIM(CCODE) ='USA') SHIPG
ON TRIM(SHIPG_ST_CD)=SHIPG.SPCODE
--US DCN
LEFT OUTER JOIN (SELECT ACCT_NBR, P_FLG, P_DT, B_ID,DOMS_CUST_NBR,ACCT_STAT_TS_DT FROM FP.RADTL ) RAMS
ON RAMS.ACCT_NBR = RAD.A_ID_VAL
AND RAMS.P_FLG = RAD.P_FLG
AND RAMS.P_DT = RAD.P_DT
AND RAMS.B_ID = RAD.B_ID
--CA DCN
LEFT OUTER JOIN (SELECT LS_CNT_NBR_ID,S_CUS_ID,EFF_END_DT FROM FB.HCLCAN WHERE EFF_END_DT = '9999-12-31' ) HCL
ON RAA.A_ID_VAL = HCL.LS_CNT_NBR_ID
LEFT OUTER JOIN (SELECT S_CUS_ID,S_CUS_NBR,EFF_END_DT FROM FB.HCCAN WHERE EFF_END_DT = '9999-12-31')HCC
ON HCL.S_CUS_ID = HCC.S_CUS_ID
QUALIFY ROW_NUMBER() OVER (PARTITION BY RAA.A_ID_VAL ORDER BY RAMS.ACCT_STAT_TS_DT DESC, HCL.EFF_END_DT DESC) = 1;

3 REPLIES
Junior Contributor

Re: No more Spool space for the user : query optimization

Without further details like DDL, Explain or Querylog Steps it's hard to tell, but:

 

Those TRIMs in JOIN & WHERE are bad, because the optimizer will probably loose statistics (and possible index access). Why do you need them? If there are actually leading spaces (for trailing you wouldn't need them) the data should be cleaned during load.

 

Are you sure about the LEFT JOINs?

You might be abe to join the tables used in ROW_NUMBER first in a Derived Table and the other tables later.

Depending on the actual number of rows and data types the ROW_NUMBER will result in a STAT step, which needs to keep the data twice in spool.

Fan

Re: No more Spool space for the user : query optimization

HI Doneth,

 

1. after removing where clause and added and clause  & also removed the trim function in the join conditions, I still get the same issue. No more spool space to the user, Request you to check the query, sharing the query, DDL's and explain plan of the query 

 

SELECT RAA.ACCDVAL
,RAA.SBOIDZ AS BOID
,RAA.ACNAME
,RAD.ACTSTCD
,RAA.ADDR1DET
,RAA.ADDR2DET
,RAA.CITY_NM
,trim(RAA.STATECDE) STATECDE
,CASE WHEN RAA.SBOIDZ = 11 THEN SUBSTR(RAA.PSTLCDE,1,5)
WHEN  RAA.SBOIDZ = 707 THEN RAA.PSTLCDE ELSE '' END AS ZIP_CD
,CASE WHEN  RAA.SBOIDZ = 11 THEN SUBSTR(RAA.PSTLCDE,7,10) ELSE '' END AS PLUS4_CD
,CASE WHEN trim(RAA.STATECDE) = 'AS' AND RAA.SBOIDZ=707 THEN 'CAN' ELSE SP.CTRYCDE END AS CTRY_CD
,RAA.EMAIL_ADDR_VAL
,RAA.HOME_PH_NBR_VAL
,RAA.SHIPG_ADDR_LN_1_VAL
,RAA.SHIPG_ADDR_LN_2_VAL
,RAA.SHIPG_CITY_NM
,TRIM(RAA.SHPGSTCD) AS SHPGSTCD
,CASE 
WHEN RAA.SBOIDZ = 11 THEN TRIM(SUBSTR(RAA.SHPPOCD,1,5)) 
WHEN  RAA.SBOIDZ = 707 THEN RAA.SHPPOCD ELSE '' END AS SHIPG_ZIP_CD
,CASE WHEN RAA.SBOIDZ = 11 THEN TRIM(SUBSTR(RAA.SHPPOCD,7,10)) ELSE '' END AS SHIPG_PLUS4_CD
,CASE WHEN trim(RAA.SHPPOCD) = 'AS' AND RAA.SBOIDZ=707 THEN 'CAN' ELSE SHIPG.CTRYCDE END AS SHIP_CTRY_CD
,RAA.EMPLYR_PH_NBR_VAL
,CASE WHEN RAA.SBOIDZ = 11 THEN RAMS.DOMS_CUST_NBR
WHEN RAA.SBOIDZ = 707 THEN HCC.SRC_CUST_NBR ELSE '0' END
AS DOMS_CUST_NBR
,RAA.DWSSID
,RAA.DWBUPD  AS TDWUPDT
,'Y' AS ENTITY_ID_FLG
,CASE WHEN RAD.ACTSTCD = 'A' THEN 'A' WHEN RAD.ACTSTCD IN ('8','I','D') THEN 'I' ELSE null END AS STAT_FLG
,RAD.CURR_CR_LIM_AMT
,RAD.OPN_TO_BUY_AMT  
FROM FP.FPRVACADDR RAA
INNER JOIN (SELECT ACTSTCD,CURR_CR_LIM_AMT,OPN_TO_BUY_AMT,ACCDVAL,BOID,PERDT,PEREDTFLG FROM FP.FPRVACDTL) RAD
ON RAA.ACCDVAL = RAD.ACCDVAL
AND RAA.SBOIDZ = RAD.BOID
AND RAD.PERDT = CURRENT_DATE - 1
AND RAD.PEREDTFLG = 'D'
AND RAA.FINPCD = '1'
LEFT OUTER JOIN  (SELECT * FROM CP.STPR WHERE (TRIM(CTRYCDE) ='CAN' AND TRIM(STPRCO)<>'AS') OR TRIM(CTRYCDE) ='USA') SP
ON TRIM(RAA.STATECDE)=SP.STPRCO
LEFT OUTER JOIN (SELECT * FROM CP.STPR WHERE (TRIM(CTRYCDE) ='CAN' AND TRIM(STPRCO)<>'AS') OR TRIM(CTRYCDE) ='USA') SHIPG
ON TRIM(SHPGSTCD)=SHIPG.STPRCO
LEFT OUTER JOIN (SELECT ACTNBR, PEREDTFLG, PERDT, BOID,DOMS_CUST_NBR,ACCT_STAT_TS_DT FROM FP.FPRAACDTL ) RAMS
ON RAMS.ACTNBR = RAD.ACCDVAL
AND RAMS.PEREDTFLG = RAD.PEREDTFLG
AND RAMS.PERDT = RAD.PERDT
AND RAMS.BOID = RAD.BOID
LEFT OUTER JOIN (SELECT LEASE_CNTRCT_NBR_ID,SRCUID,EFF_END_DT FROM FB.FBHLCULECAN WHERE EFF_END_DT = '9999-12-31' ) HCL
ON RAA.ACCDVAL = HCL.LEASE_CNTRCT_NBR_ID 
LEFT OUTER JOIN (SELECT SRCUID,SRC_CUST_NBR,EFF_END_DT FROM FB.FBHLCUCAN WHERE EFF_END_DT = '9999-12-31')HCC
ON HCL.SRCUID = HCC.SRCUID
QUALIFY ROW_NUMBER() OVER (PARTITION BY RAA.ACCDVAL ORDER BY RAMS.ACCT_STAT_TS_DT DESC, HCL.EFF_END_DT DESC) = 1;



REPLACE VIEW FP.FPRVACADDR AS LOCKING ROW FOR ACCESS
SELECT
CAST(11 AS INTEGER) AS SBOIDZ,
ACCDVAL,
FINPCD,
ACNAME,
ADDR1DET,
ADDR2DET,
CITY_NM,
STATECDE,
PSTLCDE,
HOME_PH_NBR_VAL,
SHIPG_ADDR_LN_1_VAL,
SHIPG_ADDR_LN_2_VAL,
SHIPG_CITY_NM,
SHPGSTCD,
SHPPOCD,
EMPLYR_PH_NBR_VAL,
EMAIL_ADDR_VAL,
HOME_PH_TYPE_IND,
DWSSID,
DWBUPD
FROM FB.FBRVACADDR
 where eff_end_dt='9999-12-31'
UNION
SELECT
CAST(707 AS INTEGER) AS SBOIDZ,
ACCDVAL,
FINPCD,
ACNAME,
ADDR1DET,
ADDR2DET,
CITY_NM,
STATECDE,
PSTLCDE,
HOME_PH_NBR_VAL,
SHIPG_ADDR_LN_1_VAL,
SHIPG_ADDR_LN_2_VAL,
SHIPG_CITY_NM,
SHPGSTCD,
SHPPOCD,
EMPLYR_PH_NBR_VAL,
EMAIL_ADDR_VAL,
HOME_PH_TYPE_IND,
DWSSID,
DWBUPD,
FROM FB.FBRVACADCAN
 where eff_end_dt='9999-12-31';

 



 CREATE MULTISET TABLE FB.FBRVACADCAN ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      ACCDVAL VARCHAR(20) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
      EFF_STRT_DT DATE FORMAT 'YYYY-MM-DD' NOT NULL,
      EFF_END_DT DATE FORMAT 'YYYY-MM-DD' NOT NULL COMPRESS (DATE '9999-12-31'),
      FINPCD CHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL COMPRESS '1',
      ACNAME VARCHAR(120) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
      ADDR1DET VARCHAR(120) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
      ADDR2DET VARCHAR(120) CHARACTER SET UNICODE NOT CASESPECIFIC,
      CITY_NM VARCHAR(120) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
      STATECDE VARCHAR(9) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
      PSTLCDE VARCHAR(30) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
      EMAIL_ADDR_VAL VARCHAR(180) CHARACTER SET UNICODE NOT CASESPECIFIC,
	  EMPLYR_PH_NBR_VAL VARCHAR(60) CHARACTER SET UNICODE NOT CASESPECIFIC,
	  HOME_PH_NBR_VAL VARCHAR(60) CHARACTER SET UNICODE NOT CASESPECIFIC,
	  SHIPG_ADDR_LN_1_VAL VARCHAR(40) CHARACTER SET UNICODE NOT CASESPECIFIC,
      SHIPG_ADDR_LN_2_VAL VARCHAR(40) CHARACTER SET UNICODE NOT CASESPECIFIC,
      SHIPG_CITY_NM VARCHAR(30) CHARACTER SET UNICODE NOT CASESPECIFIC,
      SHPGSTCD CHAR(3) CHARACTER SET UNICODE NOT CASESPECIFIC,
      SHPPOCD VARCHAR(10) CHARACTER SET UNICODE NOT CASESPECIFIC,
	  DWBUPD TIMESTAMP(6) NOT NULL,
      DWSSID INTEGER NOT NULL COMPRESS 3714 )
PRIMARY INDEX NUPI_RVLVG_ACCT_ADDR_CAN ( ACCDVAL );

CREATE MULTISET TABLE FB.FBRVACADDR ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      ACCDVAL VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      EFF_STRT_DT DATE FORMAT 'YYYY-MM-DD' NOT NULL,
      EFF_END_DT DATE FORMAT 'YYYY-MM-DD' NOT NULL COMPRESS (DATE '9999-12-31'),
      FINPCD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('1  ','2  ','51 ','98 '),
      ACNAME VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,
      ADDR1DET VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,
      ADDR2DET VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,
      CITY_NM VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      STATECDE CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
      PSTLCDE VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
      HOME_PH_NBR_VAL VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
      SHIPG_ADDR_LN_1_VAL VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,
      SHIPG_ADDR_LN_2_VAL VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,
      SHIPG_CITY_NM VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      SHPGSTCD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,
      SHPPOCD VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
      EMPLYR_PH_NBR_VAL VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
      EMAIL_ADDR_VAL VARCHAR(60) CHARACTER SET UNICODE NOT CASESPECIFIC,
      DWSSID INTEGER NOT NULL COMPRESS 0 ,
      DWBUPD TIMESTAMP(6) NOT NULL)
PRIMARY INDEX NUPI_RVLVG_ACCT_ADDR ( ACCDVAL );


CREATE MULTISET TABLE FP.FPRVACDTL ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      PERDT DATE FORMAT 'YYYY-MM-DD' NOT NULL,
      PEREDTFLG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      ACCDVAL VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      RVLVG_ACCT_EFF_STRT_DT DATE FORMAT 'YYYY-MM-DD' NOT NULL,
      RVLVG_ACCT_FIN_EFF_STRT_DT DATE FORMAT 'YYYY-MM-DD',
      SRC_SYS_ID CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ('CG','VP'),
      ACTSTCD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('8','9','A','D','F','I','P','T','Z'),
      CURR_CR_LIM_AMT DECIMAL(18,4) NOT NULL DEFAULT 0.0000  COMPRESS (0.0000 ,12000.0000 ,1200.0000 ,1600.0000 ,2000.0000 ,2400.0000 ,3200.0000 ,3600.0000 ,4000.0000 ,4400.0000 ,50000.0000 ,5200.0000 ,5600.0000 ,6000.0000 ,7200.0000 ,7600.0000 ,20000.0000 ,100000.0000 ,10000.0000 ,5250.0000 ,7250.0000 ,1650.0000 ,500.0000 ,1700.0000 ,2100.0000 ,2500.0000 ,2900.0000 ,4100.0000 ,4500.0000 ,6100.0000 ,6500.0000 ,2150.0000 ,4150.0000 ,6600.0000 ,75000.0000 ,7000.0000 ,9000.0000 ,35000.0000 ,1000.0000 ,2200.0000 ,25000.0000 ,2600.0000 ,3000.0000 ,3400.0000 ,15000.0000 ,4200.0000 ,4600.0000 ,5000.0000 ,5400.0000 ,3100.0000 ,3500.0000 ,3900.0000 ,5100.0000 ,5500.0000 ,7100.0000 ,7500.0000 ,1500.0000 ,1900.0000 ,2700.0000 ,7150.0000 ,1550.0000 ,3150.0000 ,5150.0000 ),
      OPN_TO_BUY_AMT DECIMAL(18,4) NOT NULL DEFAULT 0.0000  COMPRESS (0.0000 ,12000.0000 ,1200.0000 ,1600.0000 ,2000.0000 ,3200.0000 ,3600.0000 ,4000.0000 ,50000.0000 ,5200.0000 ,5600.0000 ,6000.0000 ,7200.0000 ,7600.0000 ,20000.0000 ,100000.0000 ,-80.0000 ,10000.0000 ,1.0000 ,4999.9900 ,5250.0000 ,7250.0000 ,1650.0000 ,500.0000 ,1700.0000 ,2100.0000 ,2500.0000 ,4100.0000 ,4500.0000 ,-60.0000 ,7499.9900 ,2150.0000 ,4150.0000 ,6600.0000 ,75000.0000 ,7000.0000 ,9000.0000 ,35000.0000 ,1000.0000 ,2200.0000 ,25000.0000 ,2600.0000 ,3000.0000 ,15000.0000 ,4200.0000 ,4600.0000 ,5000.0000 ,9999.9900 ,-0.0100 ,-70.0000 ,3100.0000 ,3500.0000 ,5100.0000 ,5500.0000 ,7100.0000 ,7500.0000 ,1500.0000 ,1900.0000 ,7150.0000 ,-50.0000 ,1550.0000 ,3150.0000 ,5150.0000 ),
      BOID INTEGER NOT NULL DEFAULT 11  COMPRESS (11 ,808 ,8270 ,3696 ,1401 ,909 ,707 ,202 ,3535 ) )
PRIMARY INDEX NUPI_RVLVG_ACCT_MTH_END ( ACCDVAL )
PARTITION BY ( RANGE_N(PERDT  BETWEEN DATE '1996-07-01' AND DATE '2016-12-30' EACH INTERVAL '1' MONTH ,
DATE '2017-01-29' AND DATE '2020-12-31' EACH INTERVAL '1' MONTH ,
 NO RANGE OR UNKNOWN),CASE_N(
(( CASE WHEN (PEREDTFLG =  'C') THEN (1 ) ELSE (0 ) END ))=  1 ,
(( CASE WHEN (PEREDTFLG =  'D') THEN (2 ) ELSE (0 ) END ))=  2 ,
(( CASE WHEN (PEREDTFLG =  'F') THEN (3 ) ELSE (0 ) END ))=  3 ,
 NO CASE OR UNKNOWN) );

 
 
 
 CREATE SET TABLE CP.STPR ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      CTRYCDE CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      STPRCO CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, 
CONSTRAINT XUPI_STPR PRIMARY KEY ( CTRYCDE ,STPRCO ))
;



CREATE MULTISET TABLE FP.FPRAACDTL ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      ACCDVAL VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      PERDT DATE FORMAT 'YYYY-MM-DD' NOT NULL,
      PEREDTFLG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      ACTNBR VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
      BOID INTEGER NOT NULL COMPRESS (11 ,808 ,8270 ,3696 ,1401 ,909 ,707 ,202 ,3535 ),
      DOMS_CUST_NBR VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
      ACCT_STAT_TS_DT TIMESTAMP(6))
PRIMARY INDEX NUPI_FPRAACDTL ( ACCDVAL );


CREATE MULTISET TABLE FB.FBHLCULECAN ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      SRC_LEASE_ID VARCHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      EFF_STRT_DT DATE FORMAT 'yyyy-mm-dd' NOT NULL,
      EFF_END_DT DATE FORMAT 'yyyy-mm-dd' NOT NULL,
      SRCUID DECIMAL(18,0) NOT NULL,
      LEASE_CNTRCT_NBR_ID VARCHAR(19) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( SRC_LEASE_ID )
INDEX NUPI_FBHLCULECAN ( SRC_LEASE_ID ,EFF_STRT_DT );


CREATE MULTISET TABLE FB.FBHLCUCAN ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      SRCUID DECIMAL(18,0) NOT NULL,
      EFF_STRT_DT DATE FORMAT 'yyyy-mm-dd' NOT NULL,
      EFF_END_DT DATE FORMAT 'yyyy-mm-dd' NOT NULL COMPRESS (DATE '9999-12-31'),
      SRC_CUST_NBR VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC FORMAT 'X(10)' NOT NULL )
PRIMARY INDEX NUPI_FBHLCUCAN ( SRCUID ,EFF_STRT_DT );

 


This query is optimized using type 2 profile T2_Linux64, profileid 21.
1) First, we lock FP.FPRVACDTL for access on a single
partition, we lock FP.FPRAACDTL for access, we lock
FB.FBRVACADCAN in view
FP.FPRVACADDR for access, we lock
FB.FBRVACADDR in view FP.FPRVACADDR
for access, we lock FB.FBHLCULECAN for access,
we lock FB.FBHLCUCAN for access, and we lock
CP.STPR for access.
2) Next, we do an all-AMPs RETRIEVE step from
FB.FBRVACADDR in view FP.FPRVACADDR
by way of an all-rows scan with a condition of (
"(FB.FBRVACADDR in view
FP.FPRVACADDR.EFF_END_DT = DATE '9999-12-31') AND
(FB.FBRVACADDR in view
FP.FPRVACADDR.FINPCD = '1 ')") into Spool 1
(all_amps), which is redistributed by the hash code of (
FB.FBRVACADDR.LAST_MNTNC_OF_PH_NBR_DT,
FB.FBRVACADDR.LAST_MNTNC_OF_EMAIL_DT,
FB.FBRVACADDR.LAST_MNTNC_OF_ADDR_DT,
FB.FBRVACADDR.DWBUPD,
FB.FBRVACADDR.DWSSID,
FB.FBRVACADDR.DW_LD_GRP_VAL, 'E',
FB.FBRVACADDR.EMBSR_NM,
FB.FBRVACADDR.EMP_PH_STAT_CD,
FB.FBRVACADDR.EMP_PH_CONSENT_DT,
FB.FBRVACADDR.EMP_PH_CONSENT_CD,
FB.FBRVACADDR.EMP_PH_TYPE_IND,
FB.FBRVACADDR.MOBILE_PH_STAT_CD,
FB.FBRVACADDR.MOBILE_PH_CONSENT_DT,
FB.FBRVACADDR.MOBILE_PH_CONSENT_CD,
FB.FBRVACADDR.MOBILE_PH_TYPE_IND,
FB.FBRVACADDR.HOME_PH_STAT_CD,
FB.FBRVACADDR.HOME_PH_CONSENT_DT,
FB.FBRVACADDR.HOME_PH_CONSENT_CD,
FB.FBRVACADDR.HOME_PH_TYPE_IND,
FB.FBRVACADDR.EMAIL_ADDR_VAL,
FB.FBRVACADDR.LAST_MNTNC_DT,
FB.FBRVACADDR.FAX_PH_NBR_VAL,
FB.FBRVACADDR.MEMO_2_VAL,
FB.FBRVACADDR.USER_3_VAL,
FB.FBRVACADDR.MEMO_1_VAL,
FB.FBRVACADDR.EMPLYR_PH_NBR_VAL,
FB.FBRVACADDR.EMPLYR_NM,
FB.FBRVACADDR.DSGNE_2_NM,
FB.FBRVACADDR.DSGNE_1_NM,
FB.FBRVACADDR.SHPPOCD,
FB.FBRVACADDR.SHPGSTCD,
FB.FBRVACADDR.SHIPG_CITY_NM,
FB.FBRVACADDR.SHIPG_ADDR_LN_2_VAL,
FB.FBRVACADDR.SHIPG_ADDR_LN_1_VAL,
FB.FBRVACADDR.HOME_PH_NBR_VAL,
FB.FBRVACADDR.PSTLCDE,
FB.FBRVACADDR.STATECDE,
FB.FBRVACADDR.CITY_NM,
FB.FBRVACADDR.ADDR2DET,
FB.FBRVACADDR.ADDR1DET,
FB.FBRVACADDR.ACNAME,
FB.FBRVACADDR.BIRTH_DT,
FB.FBRVACADDR.OWNSHP_IND_NBR,
FB.FBRVACADDR.FILE_GNRTN_DT,
FB.FBRVACADDR.SEQ_NBR,
FB.FBRVACADDR.LOGO_CD,
FB.FBRVACADDR.FINPCD,
FB.FBRVACADDR.ACCDVAL, 11) to all AMPs. The
size of Spool 1 is estimated with no confidence to be 13,333,325
rows (21,079,986,825 bytes). The estimated time for this step is
0.80 seconds.
3) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from
FB.FBRVACADCAN in view
FP.FPRVACADDR by way of an all-rows scan with a
condition of ("(FB.FBRVACADCAN in view
FP.FPRVACADDR.EFF_END_DT = DATE '9999-12-31')
AND (FB.FBRVACADCAN in view
FP.FPRVACADDR.FINPCD = '1')") into Spool 1
(all_amps), which is redistributed by the hash code of (NULL,
NULL, NULL,
FB.FBRVACADCAN.DWBUPD,
FB.FBRVACADCAN.DWSSID,
TRANSLATE((FB.FBRVACADCAN in view
FP.FPRVACADDR.DW_LD_GRP_VAL )USING
UNICODE_TO_LATIN),
TRANSLATE((FB.FBRVACADCAN in view
FP.FPRVACADDR.LANG_IND_CD )USING
LATIN_TO_UNICODE),
TRANSLATE((FB.FBRVACADCAN in view
FP.FPRVACADDR.EMBSR_NM )USING UNICODE_TO_LATIN),
FB.FBRVACADCAN.EMP_PH_STAT_CD,
FB.FBRVACADCAN.EMP_PH_CONSENT_DT,
FB.FBRVACADCAN.EMP_PH_CONSENT_CD,
FB.FBRVACADCAN.EMP_PH_TYPE_IND,
FB.FBRVACADCAN.MOBILE_PH_STAT_CD,
FB.FBRVACADCAN.MOBILE_PH_CONSENT_DT,
FB.FBRVACADCAN.MOBILE_PH_CONSENT_CD,
FB.FBRVACADCAN.HOME_PH_TYPE_IND,
FB.FBRVACADCAN.HOME_PH_STAT_CD,
FB.FBRVACADCAN.HOME_PH_CONSENT_DT,
FB.FBRVACADCAN.HOME_PH_CONSENT_CD,
FB.FBRVACADCAN.MOBILE_PH_TYPE_IND,
FB.FBRVACADCAN in view
FP.FPRVACADDR.EMAIL_ADDR_VAL,
FB.FBRVACADCAN.LAST_MNTNC_DT,
TRANSLATE((FB.FBRVACADCAN in view
FP.FPRVACADDR.FAX_PH_NBR_VAL )USING
UNICODE_TO_LATIN), FB.FBRVACADCAN in view
FP.FPRVACADDR.MEMO_2_VAL,
FB.FBRVACADCAN.USER_3_VAL,
FB.FBRVACADCAN in view
FP.FPRVACADDR.MEMO_1_VAL,
TRANSLATE((FB.FBRVACADCAN in view
FP.FPRVACADDR.EMPLYR_PH_NBR_VAL )USING
UNICODE_TO_LATIN),
TRANSLATE((FB.FBRVACADCAN in view
FP.FPRVACADDR.EMPLYR_NM )USING
UNICODE_TO_LATIN),
TRANSLATE((FB.FBRVACADCAN in view
FP.FPRVACADDR.DSGNE_2_NM )USING
UNICODE_TO_LATIN),
TRANSLATE((FB.FBRVACADCAN in view
FP.FPRVACADDR.DSGNE_1_NM )USING
UNICODE_TO_LATIN),
TRANSLATE((FB.FBRVACADCAN in view
FP.FPRVACADDR.SHPPOCD )USING
UNICODE_TO_LATIN),
TRANSLATE((FB.FBRVACADCAN in view
FP.FPRVACADDR.SHPGSTCD )USING
UNICODE_TO_LATIN),
TRANSLATE((FB.FBRVACADCAN in view
FP.FPRVACADDR.SHIPG_CITY_NM )USING
UNICODE_TO_LATIN),
TRANSLATE((FB.FBRVACADCAN in view
FP.FPRVACADDR.SHIPG_ADDR_LN_2_VAL )USING
UNICODE_TO_LATIN),
TRANSLATE((FB.FBRVACADCAN in view
FP.FPRVACADDR.SHIPG_ADDR_LN_1_VAL )USING
UNICODE_TO_LATIN),
TRANSLATE((FB.FBRVACADCAN in view
FP.FPRVACADDR.HOME_PH_NBR_VAL )USING
UNICODE_TO_LATIN),
TRANSLATE((FB.FBRVACADCAN in view
FP.FPRVACADDR.PSTLCDE )USING
UNICODE_TO_LATIN),
TRANSLATE((FB.FBRVACADCAN in view
FP.FPRVACADDR.STATECDE )USING UNICODE_TO_LATIN),
TRANSLATE((FB.FBRVACADCAN in view
FP.FPRVACADDR.CITY_NM )USING UNICODE_TO_LATIN),
TRANSLATE((FB.FBRVACADCAN in view
FP.FPRVACADDR.ADDR2DET )USING
UNICODE_TO_LATIN),
TRANSLATE((FB.FBRVACADCAN in view
FP.FPRVACADDR.ADDR1DET )USING
UNICODE_TO_LATIN),
TRANSLATE((FB.FBRVACADCAN in view
FP.FPRVACADDR.ACNAME )USING UNICODE_TO_LATIN),
FB.FBRVACADCAN.BIRTH_DT,
FB.FBRVACADCAN.OWNSHP_IND_NBR, NULL, NULL,
TRANSLATE((FB.FBRVACADCAN in view
FP.FPRVACADDR.LOGO_CD )USING UNICODE_TO_LATIN),
TRANSLATE((FB.FBRVACADCAN in view
FP.FPRVACADDR.FINPCD )USING
UNICODE_TO_LATIN),
TRANSLATE((FB.FBRVACADCAN in view
FP.FPRVACADDR.ACCDVAL )USING
UNICODE_TO_LATIN), 707) to all AMPs. Then we do a SORT to
order Spool 1 by the sort key in spool field1 eliminating
duplicate rows. The size of Spool 1 is estimated with no
confidence to be 13,333,549 rows (21,080,340,969 bytes). The
estimated time for this step is 0.42 seconds.
2) We do an all-AMPs RETRIEVE step from CP.STPR by
way of an all-rows scan with a condition of ("((TRIM(BOTH
FROM CP.STPR.CTRYCDE ))= 'USA') OR
(((TRIM(BOTH FROM CP.STPR.CTRYCDE ))=
'CAN') AND ((TRIM(BOTH FROM
CP.STPR.STPRCO ))<> 'AS'))") into
Spool 4 (all_amps) (compressed columns allowed), which is
duplicated on all AMPs. The size of Spool 4 is estimated
with low confidence to be 266,112 rows (5,588,352 bytes).
The estimated time for this step is 0.01 seconds.
3) We do an all-AMPs RETRIEVE step from
FB.FBHLCULECAN by way of an all-rows scan
with a condition of ("(NOT
(FB.FBHLCULECAN.LEASE_CNTRCT_NBR_ID IS
NULL )) AND (FB.FBHLCULECAN.EFF_END_DT =
DATE '9999-12-31')") into Spool 5 (all_amps) (compressed
columns allowed), which is redistributed by the hash code of
(FB.FBHLCULECAN.LEASE_CNTRCT_NBR_ID) to
all AMPs. The size of Spool 5 is estimated with no
confidence to be 237,559 rows (7,839,447 bytes). The
estimated time for this step is 0.03 seconds.
4) We do an all-AMPs JOIN step from Spool 4 by way of an all-rows
scan, which is joined to Spool 1 (Last Use) by way of an all-rows
scan with a condition of ("RAA.FINPCD = '1 '"). Spool 4 and
Spool 1 are right outer joined using a dynamic hash join, with
condition(s) used for non-matching on right table ("NOT (STATECDE IS
NULL)"), with a join condition of ("(TRIM(BOTH FROM
{RightTable}.STATECDE ))= STPRCO"). The result goes into
Spool 6 (all_amps) (compressed columns allowed), which is
redistributed by the hash code of (ACCDVAL) to all AMPs. The
size of Spool 6 is estimated with no confidence to be 13,497,372
rows (2,901,934,980 bytes). The estimated time for this step is
0.54 seconds.
5) We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of an
all-rows scan, which is joined to Spool 6 (Last Use) by way of an
all-rows scan. Spool 5 and Spool 6 are right outer joined using a
single partition hash join, with condition(s) used for
non-matching on right table ("NOT (ACCDVAL IS NULL)"), with a
join condition of ("ACCDVAL = LEASE_CNTRCT_NBR_ID"). The
result goes into Spool 9 (all_amps) (compressed columns allowed),
which is built locally on the AMPs. Then we do a SORT to
partition Spool 9 by rowkey. The size of Spool 9 is estimated
with no confidence to be 13,500,291 rows (3,132,067,512 bytes).
The estimated time for this step is 0.13 seconds.
6) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step from a single partition of
FP.FPRVACDTL with a condition of (
"FP.FPRVACDTL.PERDT = DATE
'2018-10-26'
, FP.FPRVACDTL.PEREDTFLG
= 'D'") with a residual condition of (
"(FP.FPRVACDTL.PERDT = DATE
'2018-10-26') AND
(FP.FPRVACDTL.PEREDTFLG = 'D')"),
which is joined to Spool 9 (Last Use) by way of a RowHash
match scan. FP.FPRVACDTL and Spool 9 are
joined using a rowkey-based merge join, with a join condition
of ("(ACCDVAL = FP.FPRVACDTL.ACCDVAL)
AND (SBOIDZ = FP.FPRVACDTL.BOID)"). The
result goes into Spool 13 (all_amps) (compressed columns
allowed), which is built locally on the AMPs. The size of
Spool 13 is estimated with no confidence to be 9,020,619 rows
(2,363,402,178 bytes). The estimated time for this step is
0.19 seconds.
2) We do an all-AMPs RETRIEVE step from
FB.FBHLCUCAN by way of an all-rows scan with a
condition of ("FB.FBHLCUCAN.EFF_END_DT = DATE
'9999-12-31'") into Spool 14 (all_amps) (compressed columns
allowed), which is redistributed by the hash code of (
FB.FBHLCUCAN.SRCUID) to all AMPs. The
size of Spool 14 is estimated with no confidence to be
458,298 rows (13,290,642 bytes). The estimated time for this
step is 0.04 seconds.
7) We do an all-AMPs JOIN step from Spool 4 (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 4 and Spool 13 are right outer joined using
a single partition hash join, with condition(s) used for
non-matching on right table ("NOT (SHPGSTCD IS NULL)"), with a
join condition of ("(TRIM(BOTH FROM {RightTable}.SHPGSTCD ))=
STPRCO"). The result goes into Spool 15 (all_amps)
(compressed columns allowed), which is redistributed by the hash
code of (FB.FBHLCULECAN.SRCUID) to all
AMPs. The size of Spool 15 is estimated with no confidence to be
9,155,349 rows (2,417,012,136 bytes). The estimated time for this
step is 0.49 seconds.
8) We do an all-AMPs JOIN step from Spool 14 (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 14 and Spool 15 are right outer joined using
a single partition hash join, with condition(s) used for
non-matching on right table ("NOT (SRCUID IS NULL)"), with a
join condition of ("SRCUID = SRCUID"). The result goes
into Spool 17 (all_amps) (compressed columns allowed) fanned out
into 5 hash join partitions, which is redistributed by the hash
code of (FP.FPRVACDTL.ACCDVAL,
FP.FPRVACDTL.PEREDTFLG,
FP.FPRVACDTL.PERDT,
FP.FPRVACDTL.BOID) to all AMPs. The size of
Spool 17 is estimated with no confidence to be 39,674,735 rows (
10,474,130,040 bytes). The estimated time for this step is 1.23
seconds.
9) We do an all-AMPs RETRIEVE step from FP.FPRAACDTL
by way of an all-rows scan with a condition of ("NOT
(FP.FPRAACDTL.ACTNBR IS NULL)") into Spool 20
(all_amps) (compressed columns allowed) fanned out into 5 hash
join partitions, which is redistributed by the hash code of (
FP.FPRAACDTL.PERDT,
FP.FPRAACDTL.PEREDTFLG,
FP.FPRAACDTL.ACTNBR,
FP.FPRAACDTL.BOID) to all AMPs. The size of Spool
20 is estimated with high confidence to be 183,143,575 rows (
8,790,891,600 bytes). The estimated time for this step is 1.28
seconds.
10) We do an all-AMPs JOIN step from Spool 17 (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 17 and Spool 20 are left outer joined using
a hash join of 5 partitions, with a join condition of ("(ACTNBR
= ACCDVAL) AND ((PEREDTFLG = PEREDTFLG) AND
((PERDT = PERDT) AND (BOID = BOID )))"). The result
goes into Spool 3 (all_amps) (compressed columns allowed), which
is built locally on the AMPs. The size of Spool 3 is estimated
with no confidence to be 845,180,389 rows (225,663,163,863 bytes).
The estimated time for this step is 5.66 seconds.
11) We do an all-AMPs STAT FUNCTION step from Spool 3 (Last Use) by
way of an all-rows scan into Spool 23 (Last Use), which is
redistributed by hash code to all AMPs. The result rows are put
into Spool 2 (group_amps), which is built locally on the AMPs.
The size is estimated with no confidence to be 9,020,619 rows (
2,661,082,605 bytes).
12) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 2 are sent back to the user as the result of
statement 1.
BEGIN RECOMMENDED STATS FOR FINAL PLAN->
-- "COLLECT STATISTICS COLUMN (PERDT ,PEREDTFLG
,ACCDVAL ,
BOID) ON FP.FPRVACDTL" (High
Confidence)
-- "COLLECT STATISTICS COLUMN (PERDT ,PEREDTFLG
,ACTNBR ,
BOID) ON FP.FPRAACDTL" (High
Confidence)
-- "COLLECT STATISTICS COLUMN (SRCUID) ON
FB.FBHLCULECAN" (High Confidence)
-- "COLLECT STATISTICS COLUMN (SHPGSTCD) ON
FB.FBRVACADDR" (High Confidence)
-- "COLLECT STATISTICS COLUMN (EFF_END_DT) ON
FB.FBHLCUCAN" (High Confidence)
-- "COLLECT STATISTICS COLUMN (PEREDTFLG ,PERDT) ON
FP.FPRVACDTL" (Medium Confidence)
-- "COLLECT STATISTICS COLUMN (LEASE_CNTRCT_NBR_ID) ON
FB.FBHLCULECAN" (High Confidence)
-- "COLLECT STATISTICS COLUMN (STATECDE) ON
FB.FBRVACADDR" (High Confidence)
-- "COLLECT STATISTICS COLUMN (EFF_END_DT) ON
FB.FBHLCULECAN" (High Confidence)
-- "COLLECT STATISTICS COLUMN (FINPCD ,EFF_END_DT) ON
FB.FBRVACADCAN" (Medium Confidence)
-- "COLLECT STATISTICS COLUMN (FINPCD) ON
FB.FBRVACADCAN" (High Confidence)
-- "COLLECT STATISTICS COLUMN (EFF_END_DT) ON
FB.FBRVACADCAN" (High Confidence)
-- "COLLECT STATISTICS COLUMN (FINPCD ,EFF_END_DT) ON
FB.FBRVACADDR" (Medium Confidence)
-- "COLLECT STATISTICS COLUMN (FINPCD) ON
FB.FBRVACADDR" (High Confidence)
<- END RECOMMENDED STATS FOR FINAL PLAN
BEGIN RECOMMENDED STATS FOR OTHER PLANS ->
-- "COLLECT STATISTICS COLUMN (PARTITION ,ACCDVAL) ON
FP.FPRVACDTL" (High Confidence)
-- "COLLECT STATISTICS COLUMN (PARTITION ,PERDT
,PEREDTFLG ,
ACCDVAL) ON FP.FPRVACDTL"
(High Confidence)
<- END RECOMMENDED STATS FOR OTHER PLANS

 

Junior Contributor

Re: No more Spool space for the user : query optimization

Would be easier if you knew which step actually spooled out, either from QryLogSteps or Viewpoint's QueryMonitor.

 

This FPRVACADDR view is quite bad, as it's based on DINSTINCT the optimizer has to materialize it first. And column elimination can't be applied, thus it will need lots of spool because all the VarChars are expanded to Char.

Assuming there's some unique column(s) selected from each base table it's impossible to have duplicate rows and you can switch to UNION ALL instead.
It's strange anyway that you got two tables with (almost) the same columns, but different datatypes (Latin vs. Unicode) and different lenght. Caution, the UNION will truncate the longer Unicode columns (and fail if there are any non-Latin characters).

 

You also got lots of "no confidence" indicating statistics are missing.

Of course you can't collect all those stats proposed by DIAGNOSTIC HELPSTATS, but columns like EFF_END_DT/STATECDE/SHPGSTCD are probably used a lot and should have stats.

Switch on DIAGNOSTIC VERBOSEEXPLAIN ON FOR SESSION, which will show missing stats for each step. Really helpful when you know which step showed totally wrong estimates.

 

Btw, there's a gap in your partition definition:

PARTITION BY ( RANGE_N(PERDT  BETWEEN DATE '1996-07-01' AND DATE '2016-12-30' EACH INTERVAL '1' MONTH ,
DATE '2017-01-29' AND DATE '2020-12-31' EACH INTERVAL '1' MONTH ,
 NO RANGE OR UNKNOWN)

2016-12-31 to 2017-01-28 are stored in NO RANGE and the following monthly partitions are no longer based on calendar months.

 

And your CASE_N looks strange, too:

 

CASE_N(
(( CASE WHEN (PEREDTFLG =  'C') THEN (1 ) ELSE (0 ) END ))=  1 ,
(( CASE WHEN (PEREDTFLG =  'D') THEN (2 ) ELSE (0 ) END ))=  2 ,
(( CASE WHEN (PEREDTFLG =  'F') THEN (3 ) ELSE (0 ) END ))=  3 ,
 NO CASE OR UNKNOWN) 

instead of a simple

 

 

CASE_N(
PEREDTFLG = 'C',
PEREDTFLG = 'D',
PEREDTFLG = 'F',
NO CASE OR UNKNOWN)

 

 

I know it's not easy to modify the DDL, but some of the other PIs also look as they should be reviewed...