Archives of the TeradataForum

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

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

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