Archives of the TeradataForum
Message Posted: Wed, 15 Dec 2010 @ 10:08:40 GMT
We have a query as below :
INSERT INTO DB.TBL SELECT COUNT(*) OVER (ORDER BY TBL3.ID ASC ROWS UNBOUNDED PRECEDING ) CNT , CNT ,NR ,DETAIL_ID ,CODE , DESC1 ,DESC2 FROM DB.TBL3;
In both the source and the target tables, we have same PI i.e., the "ID". But in the target table it is being populated with the above count. Also the number of records in the source table at any time will not more than a LAKH..
We have (index level) stats collected on the PI of the source table.
The issue is, the above query consumes more than 2K to 3K secs per run. But the number of runs of the query varies per day. It'll be exeucted even more than 100 times a day. so the CPU usage will be the (CPU per run * No. of runs per day), which may even cross a million at times.
So we are looking into optimize its usage.
We have replaced the below :
COUNT(*) OVER (ORDER BY TBL3.ID ASC ROWS UNBOUNDED PRECEDING ) CNT
ROW_NUMBER() OVER (ORDER BY TBL3._ID) ROW_NR
But there was no improvement...
Expecting your help...
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|