Archives of the TeradataForum
Message Posted: Thu, 15 Aug 2001 @ 16:36:20 GMT
There really isn't enough data here to come to a firm conclusion. I say this because you write about a table with an eleven column UPI, but I do not see where it is used.
However, I do see two things that might account for the slow operation.
The first thing I would attempt is to eliminate the conversions because normally they are slow. All of your input values are CHAR. I am assuming that the columns in the tables are not CHAR. Therefore, Teradata needs to convert every column of every row to CHAR in order to make the comparison. THIS CAN BE VERY SLOW. I would find a way to make the input data the same type as the comparison. If needed, load them to a table and do the conversion there.
The second thing is easier, but not as large a potential for gain. It is to move acc_no to the first column (as shown below) in the volatile table. Normally, Teradata uses the first column as a NUPI whenever a PI or PK are not defined. This should cause a different distributiion.
CREATE VOLATILE TABLE XNET_CUST1 ,ACC_NO INTEGER (cust_id 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 FP.ACC_NO ,T.CUST_ID ,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 ;
I hope either of these, or both help you. Otherwise, we will probably need more detail information about the tables used in the requests.
Regards and good luck,
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|