Archives of the TeradataForum
Message Posted: Thu, 02 Feb 2006 @ 09:06:23 GMT
| Subj: | | Re: Use of Multiset for Large Table |
| |
| From: | | Joseph V D silva |
| | There is one key difference between a UPI on a set table and a UPI on a multiset table is that the insert of a duplicate row into a set
table with a UPI will not generate an error, while the insert of a duplicate row into a multiset table with a UPI will generate a Unique Prime Key
Error. | |
I think this difference is seen only with insert-select vs normal insert. for a normal insert both set and multiset errors out, whereas for
insert-select it errors out only for multiset table. For set table, the duplicates are just ignored and no errors are generated
Amazing ! :)
Wonder how it would be with fastload/mload ( I am lazy to try out :) )
Also from my experience with atleast one incident, I have found that UNION insert into an empty multiset table a better way to get performance
than a UNION ALL insert into an empty set table. ( The diff was a few mnts versus few hours !, typical values per PI being around 100 [yep !!] ).
Though both served the purpose of eliminating dup rows.
Is that probably because in the former case the dup rows are eliminated in one shot while sorted in the spool file ? ( does some one knows the
inside-story ? )
Joseph Vinish D'silva
| 
