All Forums UDA
adash-7422 33 posts Joined 01/09
18 May 2009
Performance issue with the query

I have the following table:CREATE SET TABLE db.table2,NO FALLBACK ,NO BEFORE JOURNAL,NO AFTER JOURNAL,CHECKSUM = DEFAULT(Id DECIMAL(18,0),name VARCHAR(500) CHARACTER SET UNICODE NOT CASESPECIFIC,name2 VARCHAR(4000) CHARACTER SET UNICODE NOT CASESPECIFIC,icd VARCHAR(20) CHARACTER SET UNICODE NOT CASESPECIFIC,Total_units DECIMAL(18,0))PRIMARY INDEX ( Id );I want to insert 18m records of table1 into this table by this qry:INSERT INTO db.table2SELECT Id,name,name2,icd,SUM(qty)FROM db.table1GROUP BY Id,name,name2,icd;But this insert query doesn't come out at all. Any suggestion o improve its performance would be appreciated.The db.table1 definition is:CREATE SET TABLE db.table1 ,NO FALLBACK ,NO BEFORE JOURNAL,NO AFTER JOURNAL,CHECKSUM = DEFAULT(P_Id DECIMAL(18,0),Id DECIMAL(18,0),Qty DECIMAL(9,2),name VARCHAR(500) CHARACTER SET UNICODE NOT CASESPECIFIC,name2 VARCHAR(4000) CHARACTER SET UNICODE NOT CASESPECIFIC,icd VARCHAR(20) CHARACTER SET UNICODE NOT CASESPECIFIC)PRIMARY INDEX ( P_Id );

visakhcr 46 posts Joined 11/07
19 May 2009

Do you mean to say that the insert is taking too much time or are you saying that you are running out of spool space? Can you try the select statement alone? Also, can you post the Explain plan?

Regards,
BB

adash-7422 33 posts Joined 01/09
19 May 2009

The insert is taking too much of time....

Fred 1096 posts Joined 08/04
19 May 2009

My guess would be the same ID is present in many different rows, either in the existing table or in the subquery or both. Duplicate row checking will be required for INSERT into a non-empty SET table, even if the subquery alone is guaranteed not to return duplicates; the new rows potentially could duplicate existing rows.Consider changing the PI or making Table2 MULTISET.

emilwu 72 posts Joined 12/07
20 May 2009

change primary index to those columns in group by

dnoeth 4628 posts Joined 11/04
22 May 2009

Ahem, Teradata is not Oracle and posting a PL/SQL script over here is quite useless :-)18m rows should be a piece of cake, unless there's a bad PI on a SET table, as Fred already pointed out.Dieter

Dieter

adash-7422 33 posts Joined 01/09
24 May 2009

Thank u all!

You must sign in to leave a comment.