Help on SET/MULTISET.. plzz

Database
Teradata Employee

Help on SET/MULTISET.. plzz

Hi,

I have defined a table 'T1' in staging database which is loaded daily as Multiset.. and the application table 'T2' is defined as SET.. when I try to insert the data from T1 to T2 it takes very long time as it does the duplicate row checks... Can anyone suggest me of solution how should I load the tables without taking much of longer time..!! The table definitions need to remain same i.e. MULTISET & SET as they are.
5 REPLIES
Enthusiast

Re: Help on SET/MULTISET.. plzz

Hi

Any answers yet?
Enthusiast

Re: Help on SET/MULTISET.. plzz

Hi Indrajit,

Based on the criteria you have defined. I am sure there will be no remedy.Even if you remove duplicates from T1 and load it to T2 it will take the same time.The reason being that the nature of a SET table is to look into each and every record for duplicates before inserting it into a table.

The only possible alternative to override this SET table concept is to define the UPI and to remove any other indexes if exist. Since UPI has been defined, the index now will take care for duplicates and the table will not check for each row.

Best Solution:

Try changing the SET to MULITSET together with an UPI index on the table. This combination will work same as the SET table.Also try using Partition Primary Index.

Manoj.

Teradata Employee

Re: Help on SET/MULTISET.. plzz

What is the PI of the SET table, and how "non-unique" is it (typical/max rows per value)? Can you change the PI?

Can you define USI? Are there any duplicate rows being eliminated during INSERT/SELECT, and if so can you eliminate them yourself ahead of time?
Enthusiast

Re: Help on SET/MULTISET.. plzz

can anyone please give me the query which can do a primary index check on the multiset table.
I want to get the number of rows which are violating the PI defined on multiset table.
Enthusiast

Re: Help on SET/MULTISET.. plzz

To find duplicates in a table where Col1, Col2, and Col3 are *meant* to be unique, use this:

select Col1, Col2, Col3, count(*)

from Table1

group by Col1, Col2, Col3

having count(*) > 1

This will show your duplicates.

Rich