![](//teradataforum.com/imgs/ptdfw1.gif) |
![](//teradataforum.com/imgs/ptdfw2.gif) |
Archives of the TeradataForum
Message Posted: Tue, 05 Jul 2011 @ 15:06:03 GMT
Subj: | | Re: Multiset with a UPI |
|
From: | | John_Wight |
Kartik Replied to previous post on UPI usage with Multiset tables:
| When I worked over at one of the GE accounts, it was a mandate to only use Multiset Tables. It was strongly recommended by consultants from
Teradata. So if we had to build a UPI we would build it over Multiset tables so yes you can find Multiset tables with UPI there :) | |
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
| |