16.10 - Multiple NUSI Access - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

Database designers frequently define multiple NUSIs on a table. Whether the Optimizer chooses to include one, all, or none of them in its query plan depends entirely on their individual and composite selectivity.

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;
     CREATE INDEX (job_code) ON EMPLOYEE;

If both indexes have low selectivity, then the Optimizer uses bit mapping (see NUSI Bit Mapping 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 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 … WHEN …
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.

Example NUSI Definitions

Consider the following two NUSIs created on an employee table.

     CREATE INDEX (department_number) ON employee;
     CREATE INDEX (job_code) ON employee;

These examples examine how these dual NUSIs on the employee table might be used by the Optimizer when they are used as ANDed and ORed conditions in a WHERE clause.

AND Equality Condition

Consider the following simple query with an ANDed predicate set.

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

Whether the Optimizer includes these NUSIs in its query plan varies depending on their relative selectivity.

IF … THEN the Optimizer …
only one of the NUSIs is strongly selective uses that NUSI alone in its query plan.
both NUSIs are weakly selective individually, but strongly selective when combined creates a bit-mapped intersection of their common rowIDs and uses them in its query plan (see NUSI Bit Mapping).
both NUSIs are weakly selective both when examined individually and when combined selects neither for its query plan and instead specifies a full-table scan when no other index option provides a less costly estimate.

OR Equality Condition

Consider the following simple query with an ORed predicate set.

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

If both columns are indexed, as they are in this particular case, then a possible Optimizer plan would include the following stages.

  1. Use their hash codes to find the indexed rows in both NUSI tables.
  2. Retrieve the rowIDs of any qualifying base table rows and place them into single spool.
  3. Sort the rowIDs to eliminate duplicates.
  4. Use the resulting spool to access the qualified base table rows.

This processing could be done on one NUSI if the set of ORed predicates is specified on the same indexed column, or extended to more than two NUSIs, of course, but the likelihood of that being a profitable exercise for the Optimizer to undertake vanishes as the number of rows that qualify the ORed predicate set increases. A full-table scan could take less time to perform than looking up the NUSI or multiple NUSIs for each value in the ORed predicate set, spool production, and duplicate elimination required by this method.

Queries Using BETWEEN, LESS THAN, GREATER THAN, or LIKE Operators

     CREATE INDEX (hire_date) ON employee;
     SELECT last_name, first_name, hire_date
     FROM employee
     WHERE hire_date BETWEEN 880101 AND 881231;
     SELECT last_name, first_name, hire_date
     FROM employee
     WHERE hire_date < 880101;
     SELECT last_name, first_name, hire_date
     FROM employee
     WHERE hire_date > 881231;

The following process outlines the approach taken to respond to these requests. This technique can be very efficient for a selective ANDed predicate when there is a composite NUSI that contains all the predicate columns. Optimizer chooses to use the NUSI if the savings in the number of data blocks to read is greater than the overhead of scanning the NUSI and writing and reading a rowID spool.

  1. The Optimizer determines whether it is more efficient to do a full-table scan of the base table rows or to scan the secondary index subtable to get the rowIDs of the qualifying base table rows. Note that ordering the NUSI values can reduce the index scan time.

    For purposes of this example, assume that the NUSI access method is the more efficient technique to pursue.

  2. The access plan specifies to place those rowIDs into a spool.
  3. The access plan specifies to use the resulting rowIDs to access the base table rows.