15.00 - Multiple NUSI Access and Composite NUSI Access - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Multiple NUSI Access and Composite NUSI Access

Multiple secondary indexes are a good choice when NUSIs are not highly selective by themselves, but in combination they are highly selective: the number of rows returned is a small fraction of the total number of rows in the table.

For example, consider the following query.

     SELECT last_name, first_name, salary_amount
     FROM employee
     WHERE department_number = 500
     AND   job_code = 2147;

The conditions have NUSIs defined on them.

     CREATE INDEX (department_number) ON EMPLOYEE;

If both indexes have low selectivity, then the Optimizer uses bit mapping (see “NUSI Bit Mapping” on page 479 for details).


IF …

THEN the Optimizer …

both columns are indexed

most often specifies a hash lookup in their NUSI subtables, calling for a non-duplicate spool file of the rowIDs, and then uses the result to access the base table.

one column is indexed

always specifies a full‑table scan.

A composite set selection is one for which multiple logically related conditions apply.

An ORed composite set selection is one for which any predicate condition in the WHERE clause can evaluate to TRUE in order to retrieve rows that match the condition specified by the predicate.

An ANDed composite set selection is one for which all predicate conditions in the WHERE clause must evaluate to TRUE. If any condition evaluates to FALSE, then no row is retrieved for that composite set of conditions.

A composite condition set can reference any number of indexes, including none. The Optimizer selects an index for the query plan only when values for all of its components are supplied in the condition.

When a condition references an indexed column set, then the Optimizer selects that index for the query plan and uses it to access the table. Any remaining, or residual, conditions are applied to the intermediate result set to accomplish additional selectivity.

The following table explains how the Optimizer uses secondary indexes in the same scenario.


The Optimizer uses …


a single secondary index

the cost of accessing the secondary index for the qualified rowids plus the cost of reading the data blocks in the table that contain the qualified rowids is lower than the cost of scanning the table to find the qualified rows. Typically this is the case when the index selectivity is high.

The number of data blocks that need to be accessed depends on row size and data block size. Optimizer estimates the number of data blocks to read based on the number of qualified rowids computed from the index selectivity, the table row size and data block size.

multiple secondary indexes

their combined selectivity is high.