Archives of the TeradataForum

Message Posted: Tue, 05 Jul 2011 @ 15:06:03 GMT

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

Kartik Replied to previous post on UPI usage with Multiset tables:

Yes, and I would agree with your premise.

I thought Multiset tables were really nice when they first came out (V2R2??) but had to do a lot of personal testing to find out (what I thought was) the best way to use them. And, yes, there are a lot of folks that are not fully aware of their properties. When I was a TD PS 'guy', I witnessed (what I thought) the brutal misuse of MULTISET tables on both sides.

If we really think about it, Multiset tables allow us to have DUPLICATE rows ('undefined' in set theory and hence in the relational model!!). 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! 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? Again, if I m wrong, PLEASE anyone, let me know!

When we look at all the 'options' there are available to load and manipulate data in an EDW, we need to take them all into account. i.e. If I have a Multiset STAGE table to load, I can't have a USI if wanting to use an Fload or Mload, etc. etc. Again, it's horses for courses and a good database practitioner should know how their particular DBMS 'works', all the options/tools they have available and the best way to apply them in any given situation. Not saying I fit all those categories, but is this not what we should strive for?

JK

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