Use of Multiset tables?

Database
Enthusiast

Use of Multiset tables?

All,

can someone plz describe the use of multiset tables? scenario from the real world problem will benefit a lot. When we need to store duplicate rows in database?

Regards,
Raheel
6 REPLIES
Enthusiast

Re: Use of Multiset tables?

The temporal features of TD13.10 require the use of multiset tables to house pseudo duplicate rows. Temporal tables also require the use of a NUPI, as the same primary index may occur in multiple rows with different attributes during separate validity intervals.
Enthusiast

Re: Use of Multiset tables?

this is fine..
my is question is very simple ... i just wanna know any business scenario where bussiness really need the duplicate records??
Junior Contributor

Re: Use of Multiset tables?

As Teradata is a Relational DBMS and Relational Model requires a Primary Key, there should be no business reason for duplicates.
The only place where duplicate rows are usefull is the staging area as raw data might have dups.

Dieter
Enthusiast

Re: Use of Multiset tables?

in other words, we never create multiset tables in target database ... right?
Junior Contributor

Re: Use of Multiset tables?

No, of course we do :-)

You talked about "business scenario where bussiness really need the duplicate records"

But most multiset tables are created due to performance reasons.
Primary Keys which are only used for logical correctness but not for access/join might not be physically implemented (as Unique Primary/Secondary Index) because they're already checked in your ETL process.

Now if your target table is SET and has a non-unique Primary Index the database has to check all rows with the same RowHash value for duplicates using a logical PK across *all* columns. This is a huge overhead especially if all the rows with the same PI value don't fit into a single datablock.

Dieter
Enthusiast

Re: Use of Multiset tables?

Deiter, Thanks for an explainatory reply.. really helpful :-)