Archives of the TeradataForum

Message Posted: Wed, 15 Aug 2001 @ 15:02:00 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

Below is a script i am trying to run but the explain states a run time of approximately 3hr 45mins. This is not acceptable to our DBA.

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
       (cust_id           INTEGER
       ,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
     where      T.ACC_NO = FP.ACC_NO
     and        fp.country_code not in ('44','353')
     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

	<Prev	Next>		<<First	<Prev	Next>	Last>>