15.00 - Secondary Indexes - Teradata Database

Teradata Database SQL Fundamentals

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

Secondary Indexes

Secondary indexes are never required for Teradata Database tables, but they can often improve system performance.

You create secondary indexes explicitly using the CREATE TABLE and CREATE INDEX statements. Teradata Database can implicitly create unique secondary indexes; for example, when you use a CREATE TABLE statement that specifies a primary index, Teradata Database implicitly creates unique secondary indexes on column sets that you specify using PRIMARY KEY or UNIQUE constraints.

Creating a secondary index causes Teradata Database to build a separate internal subtable to contain the index rows, thus adding another set of rows that requires updating each time a table row is inserted, deleted, or updated.

Nonunique secondary indexes (NUSIs) can be specified as either hash-ordered or value-ordered. Value-ordered NUSIs are limited to a single numeric-valued (including DATE) sort key whose size is four or fewer bytes.

Secondary index subtables are also duplicated whenever a table is defined with FALLBACK.

After the table is created and usage patterns have developed, additional secondary indexes can be defined with the CREATE INDEX statement.

Unique and Nonunique Secondary Indexes

Teradata Database processes USIs and NUSIs very differently.

Consider the following statements that define a USI and a NUSI.

 

Secondary Index

Statement

USI

CREATE UNIQUE INDEX (customer_number) 
ON customer_table;

NUSI

CREATE INDEX (customer_name) 
ON customer_table;

The following table highlights differences in the build process for the preceding statements.

 

USI Build Process

NUSI Build Process

Each AMP accesses its subset of the base table rows.

Each AMP accesses its subset of the base table rows.

Each AMP copies the secondary index value and appends the RowID for the base table row.

Each AMP builds a spool file containing each secondary index value found followed by the RowID for the row it came from.

Each AMP creates a Row Hash on the secondary index value and puts all three values onto the BYNET.

For hash-ordered NUSIs, each AMP sorts the RowIDs for each secondary index value into ascending order.

For value-ordered NUSIs, the rows are sorted by NUSI value order.

The appropriate AMP receives the data and creates a row in the index subtable.

If the AMP receives a row with a duplicate index value, an error is reported.

For hash-ordered NUSIs, each AMP creates a row hash value for each secondary index value on a local basis and creates a row in its portion of the index subtable.

For value-ordered NUSIs, storage is based on NUSI value rather than the row hash value for the secondary index.

Each row contains one or more RowIDs for the index value.

Consider the following statements that access a USI and a NUSI.

 

Secondary Index

Statement

USI

SELECT * FROM customer_table 
WHERE customer_number=12;

NUSI

SELECT * FROM customer_table 
WHERE customer_name = 'SMITH';

The following table identifies differences for the access process of the preceding statements.

 

USI Access Process

NUSI Access Process

The supplied index value hashes to the corresponding secondary index row.

A message containing the secondary index value is broadcast to every AMP.

The retrieved base table RowID is used to access the specific data row.

For a hash-ordered NUSI, each AMP creates a local row hash and uses it to access its portion of the index subtable to see if a corresponding row exists.

Value-ordered NUSI index subtable values are scanned only for the range of values specified by the query.

The process is complete.

This is typically a two-AMP operation.

If an index row is found, the AMP uses the RowID or value order list to access the corresponding base table rows.

 

The process is complete.

This is always an all-AMP operation, with the exception of a NUSI that is defined on the same columns as the primary index.

Note: The NUSI is not used if the estimated number of rows to be read in the base table is equal to or greater than the estimated number of data blocks in the base table; in this case, a full table scan is done, or, if appropriate, partition scans are done.

NUSIs and Covering

The Optimizer aggressively pursues NUSIs when they cover a query. Covered columns can be specified anywhere in the query, including the select list, the WHERE clause, aggregate functions, GROUP BY clauses, expressions, and so on. Presence of a WHERE condition on each indexed column is not a prerequisite for using a NUSI to cover a query.

Value-Ordered NUSIs

Value-ordered NUSIs are very efficient for range conditions, and more so when strongly selective or when combined with covering. Because the NUSI rows are sorted by data value, it is possible to search only a portion of the index subtable for a given range of key values.

Value-ordered NUSIs have the following limitations.

  • The sort key is limited to a single numeric or DATE column.
  • The sort key column must be four or fewer bytes.
  • The following query is an example of the sort of SELECT statement for which value-ordered NUSIs were designed.

       SELECT *
       FROM Orders
       WHERE o_date BETWEEN DATE '1998-10-01' AND DATE '1998-10-07';

    Multiple Secondary Indexes and Composites

    Database designers frequently define multiple secondary indexes on a table.

    For example, the following statements define two secondary indexes on the EMPLOYEE table:

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

    The WHERE clause in the following query specifies the columns that have the secondary indexes defined on them:

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

    Whether the Optimizer chooses to include one, all, or none of the secondary indexes in its query plan depends entirely on their individual and composite selectivity.

    For more information on multiple and composite secondary index access, and other aspects of index selection, see Database Design.

    NUSI Bit Mapping

    Bit mapping is a technique used by the Optimizer to effectively link several weakly selective indexes in a way that creates a result that drastically reduces the number of base rows that must be accessed to retrieve the desired data. The process determines common rowIDs among multiple NUSI values by means of the logical intersection operation.

    Bit mapping is significantly faster than the three-part process of copying, sorting, and comparing rowID lists. Additionally, the technique dramatically reduces the number of base table I/Os required to retrieve the requested rows.

     

    For more information on …

    See …

    when Teradata Database performs NUSI bit mapping

    Database Design

    how NUSI bit maps are computed

    using the EXPLAIN modifier to determine if bit mapping is being used for your indexes

  • Database Design
  • SQL Data Manipulation Language
  • Secondary Index Properties

  • Can enhance the speed of data retrieval.
  • Because of this, secondary indexes are most useful in decision support applications.

  • Do not affect data distribution.
  • Can be a maximum of 32 defined per table.
  • Can be composed of as many as 64 columns.
  • For a value-ordered NUSI, only a single numeric or DATE column of four or fewer bytes may be specified for the sort key.
  • For a hash-ordered covering index, only a single column may be specified for the hash ordering.
  • Can be created or dropped dynamically as data usage changes or if they are found not to be useful for optimizing data retrieval performance.
  • Require additional disk space to store subtables.
  • Require additional I/Os on inserts and deletes.
  • Because of this, secondary indexes might not be as useful in OLTP applications.

  • Should not be defined on columns whose values change frequently.
  • Should not include columns that do not enhance selectivity.
  • Should not use composite secondary indexes when multiple single column indexes and bit mapping might be used instead.
  • Composite secondary index is useful if it reduces the number of rows that must be accessed.
  • The Optimizer does not use composite secondary indexes unless there are explicit values for each column in the index.
  • Most efficient for selecting a small number of rows.
  • Can be unique or nonunique.
  • NUSIs can be hash-ordered or value-ordered, and can optionally include covering columns.
  • Cannot be partitioned, but can be defined on a table with a partitioned primary index.
  • USI and NUSI Properties

     

    USI

    NUSI

  • Guarantee that each complete index value is unique.
  • Any access using the index is a two-AMP operation.
  • Useful for locating rows having a specific value in the index.
  • Can be hash-ordered or value-ordered.
  • Value-ordered NUSIs are particularly useful for enhancing the performance of range queries.

  • Can include covering columns.
  • Any access using the index is an all-AMP operation.
  • Related Topics

    See “SQL Data Definition Language Statement Syntax” of SQL Data Definition Language under “CREATE TABLE” and “CREATE INDEX” for more information.