Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 31 Jan 2006 @ 23:36:54 GMT


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


Subj:   Re: When to create a secondary index....
 
From:   Robert D Meunier

I can tell you when it won't use a NUSI.

As a NUSI is always going to require at least two I/Os per AMP, at least one to read the sub-table and at least one to fetch from the base- table you can pretty much guarantee that a NUSI won't be used by the optimizer unless your data occupies more then 2 blocks per AMP. You can estimate how many rows it'll take by calculating the row length for a table and then dividing that into the blocksize for the table. Multiply this number by 2 (2 blocks) and then multiply by the number of AMPs. This gives you a number of rows, of evenly distributed data, below which the optimizer will never decide to use a NUSI.

NUSI Threshold is greater then (tableBlockSize/estimatedTableRowSize * 2 * numberOfAMPS).

As you can tell the number of rows will then be dependent on your configuration. If you have 100 AMPs the number will be greatly different then if you have 3000 AMPs.

You will still need to run explains to see if the NUSI will be used, but this avoids even attempting to use NUSI on tables where they will never be used. I also use this formula to determine where I can remove existing NUSIs from systems that have grown in size (number of AMPs) without the data in specific tables growing at the same rate.


Robert Meunier



     
  <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