Archives of the TeradataForum

Message Posted: Fri, 19 Dec 2003 @ 10:47:45 GMT

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

Using NUSI or not is a question which the optimizer would try resolving based on how many datablocks would get accessed. Since it is a nusi we aren't considered about how many amps gets accessed because it is an all-amp operation. So though we have a NUSI defined, if the optimizer finds out that accessing rows for a particular NUSI value would more or less cover all the base table datablocks(a full table scan) then it would be better of for it to go for a full table scan, because a NUSI access always involves first accessing the NUSI subtable and then fetching the base table rows.

So if a column(s) is to be considered for a NUSI one should considered to what extent the column(s) tends towards containing less number of rows per NUSI value.

An Example :

If you had :

10000 rows each of size 20 bytes.

Size of a datablock = 2000 bytes

# of rows/datablock = 2000/20 = 100 rows/datablock

# of datablocks = 10000/100 = 100 datablocks.

Suppose the NUSI had 10 distinct values and there is an equal distribution of each of these values. Then :

# of rows per NUSI value = 10000/10 = 1000 rows - (A)

So considering 1000 rows/NUSI value and 100 datablocks - the likehood of scanning all the base table datablocks for a given NUSI value is more - A FULL TABLE SCAN. Here not only would you land up doing a full table scan of the base subtable but you would also be accessing the NUSI subtable datablocks. So in such case a FULL TABLE SCAN would be better than a NUSI access.

Thereby according to Equation (A) if the number of rows per NUSI value where to decrease then it is less likely we would land up scanning all the base table datablocks and thereby a NUSI access would work out better than a full table scan.

Narayan

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