SET Vs MULTISET

Database
Enthusiast

SET Vs MULTISET

I understand that a SET table has an overheard compared to a MULTISET table. The question I have is if we have a SET table with a Unique Index on it, does it help to make it a MULTISET or it does not matter?

3 REPLIES
Teradata Employee

Re: SET Vs MULTISET

Hello Kamin,

SET Table with NUPI has the overhead of checking for Duplicate Rows. With a UPI or USI, this overhead is removed as the Uniqueness Check is restricted to the UPI/USI Column(s). In my opinion, SET Table with UPI/USI has no DML overhead as compared to MultiSet with UPI/USI.

Thanks,
Smarak
Teradata Employee

Re: SET Vs MULTISET

Some duplicate row checking must still be done even with a unique index, since SET table semantics call for duplicate rows to be quietly eliminated (no error thrown) in multi-row operations such as INSERT/SELECT. 

Checking is done against rows with the same RowHash. Having UPI means that usually there would be no more than one row to check, so the overhead is minimal. That's not true for USI.

Enthusiast

Re: SET Vs MULTISET

Hi All,

I am confused with the usage of multiset table in real time.

what exactly i mean is 

where do we require the duplicate rows and what is the use of complete duplicate row in real time.

why we store complete duplicate records and uses of it???

In which environment the complete duplicate record is useful??

please make me clear with this concept with any real time scenario usage of complete duplicate records.

Thanks All.

RATHOD.