Archives of the TeradataForum
Message Posted: Wed, 01 Feb 2006 @ 16:44:39 GMT
| Subj: | | Re: Use of Multiset for Large Table |
| |
| From: | | Victor Sokovin |
| | You would never put a UPI on a multiset table - doesn't make any sense because it would not allow duplicate rows. If you have a UPI on a
set table Teradata does not have to go though the duplicate row checking because a duplicate is not allowed in the PI. If the PI cannot be a
duplicate the row cannot be a duplicate. | |
Sure; but UPI might be considered in the following situation.
Suppose we have a large table in the end-user area of the DWH. It is SET and it has a performance problem with ETL processes. We change it to
MULTISET but still do not want to have duplicate rows there because having duplicate rows in the end-user area is probably not a good idea. Then
UPI might potentially be considered. Unfortunately, it might be difficult to find a good candidate UPI if it was not meant to be in the data
model.
Another tool would be USI but they can be worse than SET as far as performance is concerned.
So, the point made is a few recent discussions is to have a multiset table but no duplicate rows in it (like in SET tables). My feeling is that
it is often too difficult to achieve this on the database level so the responsibility should be delegated to ETL and QA processes.
Regards,
Victor
| 
