16.20 - Multiple Secondary Indexes and Composites - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Fundamentals

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-02
dita:mapPath
zce1519094756513.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval

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.

Related Topics

For more information on multiple and composite secondary index access, and other aspects of index selection, see Teradata Vantage™ - Database Design, B035-1094.