Archives of the TeradataForum

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

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

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>>