Archives of the TeradataForum
Message Posted: Sat, 11 Aug 2001 @ 04:29:17 GMT
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|