Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Sat, 23 Feb 2002 @ 09:43:39 GMT


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


Subj:   Re: NUPI considerations
 
From:   Dieter Nöth

  Performance would be greatly improved if I modified the large table to have a NUPI of col2, I'd be doing PI joins to the two tables. However the NUPI rows/value would be about 1000. There would be fairly even distribution across amps with each value have close to the same number of rows/value (no spiking)  


  My row size is fairly small ~50 bytes. I'll have to check with the application team doing the MLOADs, but they should be able to ensure uniqueness via the load (one of the PK columns is date).  


  Is your experience that using Multiset without adding a USI will still achieve good performance? It's a weekly MLOAD that adds 15 million rows at a time. I had some concerns with the NUPI Duplicate row check that would take place.  



Don't try a NUPI with 1000 rows/value _without_ Multiset, there will be too much duplicate row checks. If you add a USI to enforce uniqueness, it must be dropped before load. During load the table will be MULTISET, so no duplicate row checks, when you recreate the USI it will be SET again. And if you load a PK-duplicate, you'll get an error when you recreate the USI, so this is similar to loading a duplicate row. If there'll probably be no PK violation during load, don't create the USI, it's a large overhead in diskspace and maintenance.


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: 27 Dec 2016