Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 11 Oct 2001 @ 20:42:18 GMT

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

Subj:   Re: Value Ordered Indexes
From:   Michael Larkins

Hi Joe:

You have indeed run into one of the interesting aspects of using a NUSI.

Back in the "good" old days of writing COBOL and such, when using an ISAM (Indexed sequential access method), the rule of thumb was that if you were accessing more than 15-20% of the records, never ever use the index. That was when it was a single monolithic file.

On Teradata, it should never ever be a monolithic file. It should allows be spread across all the AMP processors. So, in Teradata, if you are going after more then 2 or 3% of the rows, the optimizer will probably not use the NUSI. Reason, a full table scan is faster. Remember, an index access takes a minimum of two reads - one for the index block and one for the data block. When, reading two data blocks, it can have a very large number of data rows without doing an indexed read - faster. Also remember that I/O is always, always the slowest thing any computer must do.

Just a thought. Good luck, Mike

  <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