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