Archives of the TeradataForum

Message Posted: Sat, 11 Aug 2001 @ 04:29:17 GMT

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

Hello Claybourne:

You are experimenting with one of the least understood areas of Teradata - NUSI processing. Here is what I have found to be true:

1. Without STATISTICS, the optimizer will never use a NUSI

2. Composite column (more than one) will almost never be used. It is better to have 2 single column NUSIs than 1 two column NUSI. Multiple NUSIs used in the same WHERE clause might be used in bitmapping of the weakly selective values into a subset that is highly selective.

3. A highly selective NUSI seems to be less than 3% of the data values. Remember, STATISTICS will be required. I believe this percentage is reasonable in Teradata. Way back when I was writing COBOL programs against ISAM files, the rule was: if you are going after more than 20% of the records, DO NOT USE THE INDEX because it is too slow. Since Teradata distributes the rows across AMPs, there is no way that anywhere near 20% could be the break-even point. Hence, about 3% is the break-even since a small percentage of all the rows exist on each AMP.

Add my 2 cents to what you have already discovered and you probably have a pretty accurate picture of NUSI processing.

Regards,

Mike

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