 |
 |
Archives of the TeradataForum
Message Posted: Thu, 02 Feb 2006 @ 10:13:14 GMT
| Subj: | | Re: Use of Multiset for Large Table |
| |
| From: | | Dieter Noeth |
Joseph V D'silva wrote:
| | 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 ? ) | |
If the optimizer knows that the data is unique (because there's a distinct or a unique column selected) and the target table is empty, it's
skipping the dup check.
Of course the optimizer is quite stupid, when the target table is empty: instead of dup checking it would be much more efficient to do a
distinct automatically.
Dieter
| |
