Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 02 Feb 2006 @ 10:13:14 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


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



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023