 |
 |
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.
| |
