 |
 |
Archives of the TeradataForum
Message Posted: Tue, 05 Jul 2011 @ 18:47:41 GMT
| Subj: | | Re: Multiset with a UPI |
| |
| From: | | Walter, Todd |
| | If we have only "SET" option in the tables then the system should do duplicate check of all the rows in the table before insert the new row
to the table, This is going to give bad performance in pushing the data into the system. By having the "Multiset" option with UPI and USI will
avoid the duplicate check and also maintains the uniqueness and does the loading faster. | |
To be technical - Teradata will not check all rows in the table for uniqueness. It will only check those rows which have the same values in the
(N)UPI columns. This is why it is incorrect to assume that there will be a performance improvement from setting MULTISET on a table which has a
UPI. Since the rows can be determined to be unique entirely by checking to see if there is another row with the same UPI, no row level uniqueness
check will be done. Further, since the uniqueness check on a table with a NUPI is done within the same NUPI value only, there will be a
performance advantage only if there are many duplicates within the NUPI or one or more highly skewed NUPI values.
| |
