15.00 - 15.10 - Secondary Indexes - Teradata Database

Teradata Database Introduction to Teradata

Product
Teradata Database
Release Number
15.00
15.10
Content Type
User Guide
Publication ID
B035-1091-151K
Language
English (United States)
Last Update
2018-09-25

Secondary Indexes (SIs) allow access to information in a table by alternate paths, and can improve performance by avoiding full table scans.

Although SIs add to table overhead, in terms of disk space and maintenance, you can drop and recreate SIs as needed.

SIs:

  • Do not affect the distribution of rows across AMPs.
  • Can be unique or nonunique.
  • Can be created for complex data types, such as geospatial data.
  • Are used by the Optimizer when the indexes can improve query performance.
  • Can be useful for NoPI tables.
  • Comparison of Primary and Secondary Indexes

    The following table provides a brief comparison of PI and SI features.

     

    Feature

    Primary

    Secondary

    Can be unique or nonunique

    Both

    Both

    Affects row distribution

    Yes

    No

    Create and drop dynamically

    No

    Yes

    Improves access

    Yes

    Yes

    Create using multiple data types

    Yes

    Yes

    Requires separate physical structure

    No

    Yes, a subtable

    Requires extra processing overhead

    No

    Yes