Performance issue with the query

UDA
Enthusiast

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.table2
SELECT Id,
name,
name2,
icd,
SUM(qty)
FROM db.table1
GROUP 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 );
6 REPLIES
Enthusiast

Re: Performance issue with the query

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?
Enthusiast

Re: Performance issue with the query

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

Re: Performance issue with the query

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.
Enthusiast

Re: Performance issue with the query

change primary index to those columns in group by
Junior Contributor

Re: Performance issue with the query

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
Enthusiast

Re: Performance issue with the query

Thank u all!