Archives of the TeradataForum

Message Posted: Tue, 05 Jul 2011 @ 16:20:14 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Multiset with a UPI

From:		Dieter Noeth

John_Wight wrote:

If we really think about it, Multiset tables allow us to have DUPLICATE rows ('undefined' in set theory and hence in the relational model!!).

But allowed in Standard SQL :-(

And this is probably the reason why they have been added to TD, to be compatible to Standard SQL.

At least you got a choice between set and multiset, e.g. Oracle and MS SQL Server table are always multiset, you have to add a unique constraint/index.

As such, TD will not do a Duplicate Row check! This is great if we are loading a lot of rows (Utility or I/S) into a table with a NUPI PI and that those values have a high number of synonyms!

For me this is the main reason to use multiset tables. The other one is the staging area where you actually might get duplicates in your input data.

HOWEVER, the down side of it is - we are now 'IN CHARGE' of 'row integrity' (i.e. - not storing duplicate rows, unless that is we want them - but usually we don't. Now, if we define a uniqueness constraint on a table through a UPI or a USI, they automatically check for any duplicate 'key' values over the columns that define the PI - in either SET or MULTISET tables. Hence, we can not get a Duplicate Row!!! If that is the case, then why use a Multiset?

#1: Fred already mentioned the difference for INSERT/SELECTs, silently discarding vs. error.

#2: The logical PK from the LDM might not be usefull for access/joins, so implementing it as a USI will add overhead without any reward. If you load large amounts of rows using SQL you *must* check for PK violations before/during the INSERT/UPDATE otherwise you'll get a rollback. Why doing the same check for uniqueness a 2nd time (USI)?

It's similar for FKs, who actually implements FKs in a warehouse?

Dieter


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference