Archives of the TeradataForum

Message Posted: Sun, 20 Dec 2015 @ 07:53:50 GMT

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

You are only going to define a NUSI on a NUPI of a partitioned table. The reason you would do this is to avoid an all partition probe when the query only qualifies based on the NUPI column and does not also qualify on the partitioning column. In this case the optimizer knows all the rows are on a single AMP, but it does not know which partition. So all partitions must be probed. If you have thousands of partitions this can take some time. So to avoid the all partition probe you can optionally specify a NUSI on the NUPI column. The rowid stored in the NUSI subtable will contain the partition number. since the optimizer knows that the NUSI is on the NUPI it will be a single AMP NUSI access. Also be aware that the NUSI will only be used if the estimated number of rows is less than the number of partitions. If the estimated number of rows is greater than the number of partitions, the NUSI will not be used since all partitions will probably have to be probed anyways.

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