Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 28 Oct 2015 @ 20:14:14 GMT

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

Subj:   Re: SET vs MULTISET tables with UPI/USI
From:   Prescott, Kyle

UNIQUE INDEXES will require the duplicate checks regardless of MULTISET or SET. SET will allow 1 and only 1 "SET" of the data - no duplicate rows.

MULTISET will allow MULTIPLE SETS of DATA, however you are negating that option by using UNIQUE INDEX structures.

Multiloads and such are faster with NUPI and MULTISET as it indeed does not check for duplicate row instances. FASTLOAD utility automatically eliminates duplicate rows regardless of MULTISET or SET options.

IF you want the most efficient of the options - a NO PRIMARY INDEX table is the fastest (has to be inherently MULTISET) as it will not check existence for duplicates and randomly places data on the AMPS - potentially eliminating HASH COLLISIONS. Caveat here as lumpy data from a skewed source table with a straight insert to NOPI table can still generate LUMPY results as the data never leaves the original AMP.

Kyle Prescott>Kyle Prescott
Technology Capability Manager
Unum - Infrastructure Services
Chattanooga, TN

  <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: 24 Jul 2020