|
Archives of the TeradataForumMessage Posted: Mon, 13 Aug 2001 @ 14:08:00 GMT
Hi, Below is a script i am trying to run but the explain states a run time of approximately 104 hours ! Obviously, this is not acceptable. The ICID_TIS_ACCOUNT and ACC_INFO tables have UPI's of ACC_no and have 2.7m and 1.6m rows respectively. The FP_TRAFFIC_SUM table has 177m rows with a UPI spread over 11 cols and separate NUSI's of STD and TELNO, ACC_no, Country_code. Finally the Country_code table has 223 rows and a UPI of Country_code. Stats are only collected on the 3 NUSI's. The Export is about 2m rows. Any ideas would help. Thanks Trevor Price - SSE, Eircom, Dublin DROP TABLE CDW_PROD_TEMP.XNET_CUST; CREATE TABLE CDW_PROD_TEMP.XNET_CUST, NO FALLBACK ( CUST_ID INTEGER ,ACC_NO INTEGER ,ACC_STAT CHAR(1) ) UNIQUE PRIMARY INDEX(ACC_NO); INSERT INTO CDW_PROD_TEMP.XNET_CUST SELECT CA.CUST_ID ,CA.ACC_NO ,AI.ACC_STAT FROM CDW_PROD_BV.ICID_TIS_ACCOUNT_V CA JOIN CDW_PROD_BV.ACC_INFO_V AI ON CA.ACC_NO = AI.ACC_NO WHERE AI.CHANNEL_CODE IN ('CA','KA','BA') AND CA.CUST_ID IN (SELECT CA.CUST_ID FROM CDW_PROD_BV.ICID_TIS_ACCOUNT_V CA JOIN CDW_PROD_BV.ACC_INFO_V AI ON CA.ACC_NO = AI.ACC_NO WHERE AI.ACC_STAT IN ('D','A','R','T') AND AI.CHANNEL_CODE IN ('CA','KA','BA') GROUP BY 1) ; CREATE VOLATILE TABLE XNET_CUST1 (ACC_NO INTEGER ,STD CHAR(4) ,TELNO CHAR(7) ,COUNTRY_DESC CHAR(20) ,SUM_CALLS INTEGER ,SUM_DURATION INTEGER ,YEAR_MONTH INTEGER) ON COMMIT PRESERVE ROWS; INSERT INTO XNET_CUST1 SELECT T.CUST_ID ,FP.ACC_NO ,FP.STD ,FP.TELNO ,FP.COUNTRY_CODE ,SUM(FP.SUM_CALLS) ,(SUM(FP.SUM_DURATION(DECIMAL (15)))) / 60 ,FP.YEAR_MONTH FROM CDW_PROD_TEMP.XNET_CUST T JOIN CDW_PROD_BV.FP_TRAFFIC_SUM_V FP ON T.ACC_NO = FP.ACC_NO GROUP BY 1,2,3,4,5,8 ; .EXPORT DATA DDNAME = OUTPUT /* LRECL = 84 */ .IMPORT DATA DD=PARAM; USING YEAR_MTH (CHAR (6)) SELECT T1.CUST_ID (CHAR(11)) ,T1.ACC_NO (CHAR(08)) ,T1.STD (CHAR(04)) ,T1.TELNO (CHAR(07)) ,CC.COUNTRY_DESC (CHAR(20)) ,T1.SUM_CALLS (CHAR(11)) ,T1.SUM_DURATION (CHAR(16)) ,T1.YEAR_MONTH (CHAR(6)) ,'X' (CHAR(1)) FROM XNET_CUST1 T1 JOIN CDW_PROD_BV.COUNTRY_CODE_V CC ON T1.COUNTRY_CODE = CC.COUNTRY_CODE WHERE T1.YEAR_MONTH >= :YEAR_MTH (INTEGER) ; SELECT '999999999999TRAILER' (CHAR(19)) ,'TOTAL RECORDS FOR REPORT1 EXCL 44,353' (CHAR(37)) ,COUNT(*) (CHAR(28)) FROM XNET_CUST1 ; .IF ERRORCODE <> 0 THEN .QUIT ERRORCODE
| ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
Copyright 2016 - All Rights Reserved | ||||||||||||||||||||||||||||||||||||||||||||||||
Last Modified: 15 Jun 2023 | ||||||||||||||||||||||||||||||||||||||||||||||||