I am working on Data warehouse query. Even the timerons cost is less the query is running for long time due to too much CPU cost I believe. I am attaching the query, explain plan and structure of tables.

The table DXMART.DXT_BL_REVN_FACT has approx. 25 millions table DXMART.DXT_CLNT_MBR has 2.8 millions records.

Below is the query: --explain all for WITH TEMP0 ( SRC_SYS_CD ,CTLNO_NUM ,CLNT_MBR_INT_ID ,CAL_DT ,ACCT_DT ,CLNT_MBR_GNDR_CD ,CLNT_MBR_BRTH_DT ,SRC_SYS_PROD_CD ,CONTRACT_TYP_CD ,PROD_FUND_ARGMT_CD ,BL_GRP_CD ,BL_BR_CD ,BLLN_CD ,MBR_GRP_CD ,PROD_CD ,CRNCY_CD ,DUE_LIV_NUM ,DUE_FEE_LIV_NUM ,DUE_VOL_AMT ,DUE_FEE_VOL_AMT ) AS ( SELECT DXMART.DXT_CLNT_MBR.SRC_SYS_CD ,DXMART.DXT_CLNT_MBR.CTLNO_NUM ,DXMART.DXT_CLNT_MBR.CLNT_MBR_INT_ID ,DXMART.DXT_BL_REVN_FACT.APPL_TO_DUE_DT ,DXMART.DXT_BL_REVN_FACT.ACCT_DT ,DXMART.DXT_CLNT_MBR.CLNT_MBR_GNDR_CD ,DXMART.DXT_CLNT_MBR.CLNT_MBR_BRTH_DT ,DXMART.DXT_BL_REVN_FACT.SRC_SYS_PROD_CD ,DXMART.DXT_BL_REVN_FACT.CONTRACT_TYP_CD ,DXMART.DXT_BL_REVN_FACT.PROD_FUND_ARGMT_CD ,DXMART.DXT_BL_REVN_FACT.BL_GRP_CD ,DXMART.DXT_BL_REVN_FACT.BL_BR_CD ,DXMART.DXT_BL_REVN_FACT.BLLN_CD ,DXMART.DXT_BL_REVN_FACT.MBR_GRP_CD ,DXMART.DXT_BL_REVN_FACT.PROD_CD ,DXMART.DXT_BL_REVN_FACT.CRNCY_CD ,DXMART.DXT_BL_REVN_FACT.DUE_LIV_NUM ,DXMART.DXT_BL_REVN_FACT.DUE_FEE_LIV_NUM ,DXMART.DXT_BL_REVN_FACT.DUE_VOL_AMT ,DXMART.DXT_BL_REVN_FACT.DUE_FEE_VOL_AMT FROM DXMART.DXT_BL_REVN_FACT

INNER JOIN DXMART.DXT_CLNT_MBR on

DXMART.DXT_BL_REVN_FACT.SRC_SYS_CD=DXMART.DXT_CLNT_MBR.SRC_SYS_CD AND DXMART.DXT_BL_REVN_FACT.CTLNO_NUM=DXMART.DXT_CLNT_MBR.CTLNO_NUM AND DXMART.DXT_BL_REVN_FACT.CLNT_MBR_INT_ID=DXMART.DXT_CLNT_MBR.CLNT_MBR_INT_ID

AND (DXMART.DXT_BL_REVN_FACT.APPL_TO_DUE_DT <= '01/01/2004' ) and (DXMART.DXT_CLNT_MBR.SRC_SYS_CD = 'ALL' OR CASE WHEN 'ALL' ='ALL' THEN DXMART.DXT_CLNT_MBR.SRC_SYS_CD END IN ('BAR','COM','GBS','MPG','LXG','LXT') ) AND (DXMART.DXT_CLNT_MBR.REC_EFF_DT <= '01/31/2004' AND DXMART.DXT_CLNT_MBR.REC_EN_DT >= '01/31/2004') AND (DXMART.DXT_CLNT_MBR.CLNT_MBR_BRTH_DT IS NOT NULL ) AND (DXMART.DXT_CLNT_MBR.CLNT_MBR_GNDR_CD IN ('M', 'F') )

INNER JOIN DXMART.DXT_CTLNO ON ( DXMART.DXT_CTLNO.CTLNO_NUM=DXMART.DXT_CLNT_MBR.CTLNO_NUM ) AND ( CASE WHEN DXMART.DXT_CTLNO.CTLNO_EN_DT IS NULL THEN 'INFORCE' ELSE 'LAPSE' END IN 'INFORCE' ) AND ( DXMART.DXT_CTLNO.REC_EFF_DT <= '01/31/2004' AND DXMART.DXT_CTLNO.REC_EN_DT >= '01/31/2004')

INNER JOIN DXMART.DXT_LKP_PRDCD_XREF ON ( DXMART.DXT_CLNT_MBR.SRC_SYS_CD=DXMART.DXT_LKP_PRDCD_XREF.SRC_SYS_CD AND DXMART.DXT_BL_REVN_FACT.SRC_SYS_PROD_CD=DXMART.DXT_LKP_PRDCD_XREF.SRC_SYS_PROD_CD AND COALESCE(DXMART.DXT_BL_REVN_FACT.CONTRACT_TYP_CD,'*')=DXMART.DXT_LKP_PRDCD_XREF.CONTRACT_TYP_CD AND COALESCE(DXMART.DXT_BL_REVN_FACT.PROD_FUND_ARGMT_CD,'*')=DXMART.DXT_LKP_PRDCD_XREF.PROD_FUND_ARGMT_CD AND (DXMART.DXT_LKP_PRDCD_XREF.REC_EFF_DT <= '01/31/2004' AND DXMART.DXT_LKP_PRDCD_XREF.REC_EN_DT >='01/31/2004') AND DXMART.DXT_LKP_PRDCD_XREF.PROD_FAM_CD <> 'LTC') ) ,TEMP1 ( SRC_SYS_CD ,CTLNO_NUM ,CLNT_MBR_INT_ID ,MAX_CAL_DT ) AS ( SELECT SRC_SYS_CD ,CTLNO_NUM ,CLNT_MBR_INT_ID ,MAX(CAL_DT) FROM TEMP0 GROUP BY SRC_SYS_CD ,CTLNO_NUM ,CLNT_MBR_INT_ID )

,TEMP2 ( SRC_SYS_CD ,CTLNO_NUM ,CLNT_MBR_INT_ID ,MAX_CAL_DT ,MAX_ACCT_DT ) AS ( SELECT B.SRC_SYS_CD ,B.CTLNO_NUM ,B.CLNT_MBR_INT_ID ,B.MAX_CAL_DT ,MAX(A.ACCT_DT) FROM TEMP0 A ,TEMP1 B WHERE B.SRC_SYS_CD = A.SRC_SYS_CD AND B.CTLNO_NUM=A.CTLNO_NUM AND B.CLNT_MBR_INT_ID=A.CLNT_MBR_INT_ID AND B.MAX_CAL_DT=A.CAL_DT AND ( A.ACCT_DT <= '01/31/2004' ) GROUP BY B.SRC_SYS_CD ,B.CTLNO_NUM ,B.CLNT_MBR_INT_ID ,B.MAX_CAL_DT )

,TEMP3 ( SRC_SYS_CD ,BUY_OUT_IND ,AGE ,MALE_LIV ,FEMALE_LIV ,TOT_LIV_NUM ,MAL_VOL ,FEMALE_VOL ,TOT_VOL_AMT ) AS ( SELECT TEMP0.SRC_SYS_CD ,CASE WHEN B.CTLNO_NUM IS NULL THEN 'N' ELSE 'Y' END ,(Year(TEMP0.CAL_DT)-Year(TEMP0.CLNT_MBR_BRTH_DT) + CASE WHEN SIGN(DAYS(DATE(CONCAT(SUBSTR(CHAR(TEMP0.CAL_DT,USA),1,6),'1600'))) - DAYS(DATE (CONCAT(SUBSTR(CHAR(TEMP0.CLNT_MBR_BRTH_DT,USA),1,6),'1600')))) = -1 THEN -1 Else 0 END) ,SUM(CASE (TEMP0.CLNT_MBR_GNDR_CD) WHEN 'M' THEN (TEMP0.DUE_LIV_NUM+TEMP0.DUE_FEE_LIV_NUM) ELSE 0 END) ,SUM(CASE (TEMP0.CLNT_MBR_GNDR_CD) WHEN 'F' THEN (TEMP0.DUE_LIV_NUM+TEMP0.DUE_FEE_LIV_NUM) ELSE 0 END) ,SUM(TEMP0.DUE_LIV_NUM+TEMP0.DUE_FEE_LIV_NUM) ,SUM(CASE (TEMP0.CLNT_MBR_GNDR_CD) WHEN 'M' THEN (TEMP0.DUE_FEE_VOL_AMT+TEMP0.DUE_VOL_AMT) ELSE 0 END) ,SUM(CASE (TEMP0.CLNT_MBR_GNDR_CD) WHEN 'F' THEN (TEMP0.DUE_FEE_VOL_AMT+TEMP0.DUE_VOL_AMT) ELSE 0 END) ,SUM(TEMP0.DUE_FEE_VOL_AMT+TEMP0.DUE_VOL_AMT) FROM TEMP0

INNER JOIN TEMP2 ON ( TEMP2.SRC_SYS_CD = TEMP0.SRC_SYS_CD ) AND ( TEMP2.CTLNO_NUM = TEMP0.CTLNO_NUM ) AND ( TEMP2.CLNT_MBR_INT_ID = TEMP0.CLNT_MBR_INT_ID ) AND ( TEMP2.MAX_CAL_DT = TEMP0.CAL_DT ) AND ( TEMP2.MAX_ACCT_DT = TEMP0.AcCT_DT )

INNER JOIN DXMART.DXT_CLNT_SEG ON ( DXMART.DXT_CLNT_SEG.CTLNO_NUM=TEMP0.CTLNO_NUM ) AND ( DXMART.DXT_CLNT_SEG.REC_EFF_DT <= '01/31/2004' AND DXMART.DXT_CLNT_SEG.REC_EN_DT >= '01/31/2004') AND ( ('ALL' in 'ALL') OR (CASE DXMART.DXT_CLNT_SEG.CASE_SEG_CD WHEN 'M' THEN 'MID-MARKET' WHEN 'P' THEN 'NCSC' WHEN 'N' THEN 'NAO' ELSE 'UNKNOWN' END IN 'ALL' ) )

LEFT OUTER JOIN DXMART.DXT_CLNT_STRUC ON ( TEMP0.CTLNO_NUM=DXMART.DXT_CLNT_STRUC.CTLNO_NUM AND TEMP0.BL_GRP_CD=DXMART.DXT_CLNT_STRUC.BL_GRP_CD AND COALESCE(TEMP0.BL_BR_CD,'*')=DXMART.DXT_CLNT_STRUC.BL_BR_CD AND TEMP0.BLLN_CD=DXMART.DXT_CLNT_STRUC.BLLN_CD AND TEMP0.MBR_GRP_CD=DXMART.DXT_CLNT_STRUC.MBR_GRP_CD AND TEMP0.SRC_SYS_CD=DXMART.DXT_CLNT_STRUC.SRC_SYS_CD AND TEMP0.SRC_SYS_PROD_CD=DXMART.DXT_CLNT_STRUC.SRC_SYS_PROD_CD AND COALESCE(TEMP0.CONTRACT_TYP_CD,'*')=DXMART.DXT_CLNT_STRUC.CONTRACT_TYP_CD AND COALESCE(TEMP0.PROD_FUND_ARGMT_CD,'*')=DXMART.DXT_CLNT_STRUC.PROD_FUND_ARGMT_CD AND TEMP0.CRNCY_CD=DXMART.DXT_CLNT_STRUC.CRNCY_CD AND (DXMART.DXT_CLNT_STRUC.STRUC_EFF_DT <= TEMP0.CAL_DT AND DXMART.DXT_CLNT_STRUC.STRUC_EN_DT >=TEMP0.CAL_DT) AND (DXMART.DXT_CLNT_STRUC.REC_EFF_DT <= '01/31/2004' AND DXMART.DXT_CLNT_STRUC.REC_EN_DT >= '01/31/2004') )

LEFT OUTER JOIN DXMART.DXT_BUYOUT B ON TEMP0.CTLNO_NUM = B.CTLNO_NUM AND COALESCE(DXMART.DXT_CLNT_STRUC.ACCT_CS_CD,'*******') = B.ACCT_CS_CD AND (B.EFF_START_DT <= TEMP0.CAL_DT AND B.EFF_EN_DT >= TEMP0.CAL_DT) AND (B.REC_EFF_DT <='01/31/2004' AND B.REC_EN_DT >= '01/31/2004')

WHERE ('Y' = 'Y' OR 'N' = 'Y')

GROUP BY TEMP0.SRC_SYS_CD ,CASE WHEN B.CTLNO_NUM IS NULL THEN 'N' ELSE 'Y' END ,(Year(TEMP0.CAL_DT)-Year(TEMP0.CLNT_MBR_BRTH_DT) + CASE WHEN SIGN(DAYS(DATE(CONCAT(SUBSTR(CHAR(TEMP0.CAL_DT,USA),1,6),'1600'))) - DAYS(DATE (CONCAT(SUBSTR(CHAR(TEMP0.CLNT_MBR_BRTH_DT,USA),1,6),'1600')))) = -1 THEN -1 Else 0 END)

)

SELECT * FROM TEMP3 ;

Any hint why this is running for long time and how to reduced the elapsed time and CPU time.

Regards, Diwakar

-- DIWAKAR N UBNARE (diwakar.ubnare@prudential.com), August 20, 2003