Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 08 Sep 2003 @ 13:08:07 GMT


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


Subj:   Re: What is the difference between Hash Ordered NUSI and an HASH Index ?
 
From:   Figge, Jason

NUSIs are not hash redistributed and will result in an all-amp operation when searching for a party_id. A Hash Index, on the other hand, is effectively a duplicate of the original table (in your case a duplicate of only the fields party_id, acct_id and acct_src) that is re- keyed and therefore re-distributed by party_id.

Additionally, the Optimizer may choose to ignore a NUSI if the selectivity is low or statistics haven't been collected. If you intend to access more that the three fields in the Hash Index, then you may wish to include the ROWID, as this allows Teradata to jump to the original record in the base table for more values. Without it, Teradata may not used the Hash Index.

Jason



     
  <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