All Forums Database
idg_tddevex 18 posts Joined 10/06
18 Oct 2006
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.

XTUPIE 42 posts Joined 11/05
26 Oct 2006

HiAny answers yet?

Regards
Divvy

Manoj_Adikar 4 posts Joined 10/05
27 Oct 2006

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.

Fred 1096 posts Joined 08/04
27 Oct 2006

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?

soujanya.kota 6 posts Joined 02/09
16 Apr 2009

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.

rtefft 11 posts Joined 03/09
16 Apr 2009

To find duplicates in a table where Col1, Col2, and Col3 are *meant* to be unique, use this:select Col1, Col2, Col3, count(*)from Table1group by Col1, Col2, Col3having count(*) > 1This will show your duplicates.Rich

You must sign in to leave a comment.