 |
 |
Archives of the TeradataForum
Message Posted: Mon, 11 Feb 2002 @ 23:09:01 GMT
| Subj: | | Re: Insert data into table |
| |
| From: | | John Hall |
Although I haven't actually sat down and tried to measure it, I've never noticed a performance difference between a UPI and NUPI
SET table. In either case, the duplicate row check has to be done when you have a hash synonym. Although I can't remember hearing
it specifically stated anywhere, I suspect that the UPI check is actually performed as a function of the duplicate row check - you
only need to do the duplicate row check when there is a hash collision and since it is a byte-by-byte comparison of the two rows,
it's probably the perfect place to resolve a uniqueness violation for a UPI.
In the case of a UPI, there's no difference between SET or MULTISET tables - they are functionally the same thing and should
perform the same.
As already mentioned, there's a big performance gain when using a MULTISET table with a non-unique primary index instead of a SET
table. Again, this is because duplicate row checking is eliminated for the MULTISET table. Having said that, I would imagine that
the performance between a NUPI SET and MULTISET table is probably the same as long as there are no hash synonyms and without a hash
collision, there's no reason to perform the duplicate row check.
| |
