Teradata: MULTISET vs SET Table performance

Question

So SET tables involve a performance impact, as for every new row inserted or updated in the table, Teradata checks if a duplicate row already exists in the table, which can be a serious issue if there is a large number of records. However, we can improve its performance by defining a unique constraint on any column in the SET table, such as a Unique Primary Index (UPI). This helps us avoid the additional overhead of performing the duplicate row check, as the UPI guarantees that there will be no duplicate rows.

1. Does this mean that a SET table with a UPI/USI will have the same performance as a MULTISET table with a UPI/USI? Please explain.

2. And if your table has a Unique Primary Index, should you create it as a SET or a MULTISET table?

Answer 1

There will be no performance difference for a SET/MULTISET table with a UPI, there's just a difference regarding Insert/Select: a SET table silently ignores duplicate rows (#rows inserted less than #rows selected) while a MULTISET table throws an error (Duplicate row checks are done before checking for uniqueness).

But adding a USI will not prevent duplicate row checks, a new row has to be inserted into the base table first to create it's ROWID before it's inserted into the USI subtable.