Archives of the TeradataForum

Message Posted: Wed, 15 Dec 2010 @ 10:08:40 GMT

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

Hi Gurus,

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

with :

     ROW_NUMBER() OVER (ORDER BY TBL3._ID)  ROW_NR

But there was no improvement...

Expecting your help...

Regards,

Anusha

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