Teradata：带UPI/USI的SET与MULTISET表性能对比及选型建议

Great question—this is a super common point of confusion when working with Teradata’s table types, so let’s break it down clearly.

First: Do SET and MULTISET tables with UPI/USI have identical performance? #

Short answer: Yes, for all practical purposes, when you have a Unique Primary Index (UPI) or Unique Secondary Index (USI) on a SET table, its performance will match that of a MULTISET table with the same unique index. Here’s why:

• The core performance hit of SET tables comes from the full-row duplicate check Teradata runs on every insert/update—scanning to see if an exact copy of the row already exists. But when you add a UPI or USI, that unique index enforces that the indexed column(s) can never have duplicate values.
• Since a duplicate row would require all columns (including the indexed ones) to match, the unique index already eliminates the possibility of full-row duplicates. Teradata recognizes this and skips the redundant full-row check for SET tables with unique indexes.
• Under the hood, both table types will use the unique index to validate uniqueness during writes, with no extra overhead for the SET table’s inherent "no duplicates" rule.

The only edge case where you might see a tiny, negligible difference is in rare metadata operations—but it won’t impact day-to-day query or load performance.

Second: If a table has a UPI, should you create it as SET or MULTISET? #

While performance is nearly identical, the better choice is almost always a SET table—and it’s about semantics and future-proofing, not speed:

• Semantic clarity: A SET table explicitly declares your intent: "this table should never contain duplicate rows." A MULTISET table, even with a UPI, semantically allows duplicates (even if the UPI prevents them in practice). Using SET makes your schema self-documenting for other developers.
• Future-proofing: If your requirements change later (e.g., you need to switch from a UPI to a Non-Unique Primary Index, NUPI), a SET table will automatically retain the "no duplicate rows" constraint. A MULTISET table would start allowing full-row duplicates immediately, which could lead to unintended data quality issues.
• No downsides: Since the UPI eliminates the SET table’s performance overhead, you get the data integrity guarantee of SET without any tradeoffs.

The only scenario where MULTISET might make sense is if you intentionally want to allow full-row duplicates in the future (but if you have a UPI, that’s impossible anyway—so this is an extremely rare edge case).

内容的提问来源于stack exchange，提问作者kev