15.00 - NUSIs and Block Size - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

NUSIs and Block Size

Effective use of a NUSI requires that fewer rows qualify than there are data blocks in the base table.

Consider, for example, 100-byte rows. With a average block size of 31.5 KB, each multirow data block contains approximately 315 rows. This means fewer than one in 315 rows (that is, less than 0.32%) can qualify for a given NUSI value if the index is to be effective.

When the average block size is 63.5 KB, fewer than one in 635 rows (that is, less than 0.16%) can qualify for the NUSI to be effective.

When the average block size is 127.5 KB, fewer than one in 1275 rows (that is, less than 0.08%) can qualify for the NUSI to be effective. When the average block size is 1KB, fewer than one in 10 rows (that is, less than 10%) can qualify for the NUSI to be effective. This becomes even more significant if your system uses 1MB data blocks.

To reset the global absolute-maximum size for data blocks, see “PermDBSize” in Utilities: Volume 1 (A-K).