15.00 - Using NUSIs - Teradata Database

Teradata Database Design

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

Using NUSIs

When using NUSIs there should be fewer rows that satisfy the NUSI qualification condition than there are data blocks in the table. Whether the Optimizer uses a NUSI depends on the percent of rows that satisfy the NUSI qualification condition and the overhead of reading the NUSI table, as follows.

 

Qualifying Rows

                                                        Result

< 1 per block

NUSI access is generally faster than full‑table scan. As the number of qualified rows approaches the number of data blocks, the additional cost of reading the NUSI subtable can make NUSI access more costly.

For example, if there are 100 rows per block and 1 in 1000 rows qualify, the Optimizer reads 1 in every 10 blocks. NUSI access is faster.

>= 1 per block

A full‑table scan is faster than NUSI access.

For example, if there are 100 rows per block and 1% of the data qualifies, the Optimizer reads almost every block. A full‑table scan might be faster.

In some cases, the values of a NUSI are distributed unevenly throughout a table. At other times, some values might represent a large percent of the table, while other values have few instances. When values are distributed unevenly and statistics are available on the NUSI that allow the Optimizer to see the values distribution, Teradata Database can use a NUSI as follows.

  • Perform a full‑table scan for queries on values that represent a large percentage of table.
  • Use a NUSI for queries on the remaining values.
  • You can use a request like the following to report secondary index non‑uniqueness.

         .SET RETLIMIT 20

         SELECT index_column_set, COUNT(*)
         FROM table_name
         GROUP BY 1
         ORDER BY 2 desc;