15.00 - Teradata Database Index Comparisons - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Teradata Database Index Comparisons

The following table summarizes the similarities and differences among Teradata Database primary, secondary, join, and hash indexes.

 

Attribute

Primary Index

Secondary Index

Join Index

Hash Index

Must be defined

 

 

Yes

If the table is not a NoPI, a column‑partitioned NoPI, or a global temporary trace table. If the join index is not a column-partitioned join index.

No

This excludes system‑defined USIs for PRIMARY KEY and UNIQUE constraints that are required for those constraints if they are not Referential Constraint‑associated.

 

 

No

 

No

 

No

If the table or join index is a NoPI, a column‑partitioned NoPI, or a global temporary trace table.

Maximum per table

1

32

The combined number of secondary, hash, and join indexes defined on a table cannot exceed 32.

This limit includes the system‑defined secondary indexes used to implement PRIMARY KEY and UNIQUE constraints for tables.

Any PRIMARY KEY or UNIQUE NOT NULL constraint that is not defined on a primary index column set is implemented as a USI.

A multicolumn NUSI that specifies an ORDER BY clause counts as two consecutive indexes in this calculation.

Minimum per table

1

If the table has a primary index.

0

 

0

If the table is a NoPI or column-partitioned table. If the join index is a column-partitioned join index.

Maximum number of columns

64

64

You can specify 64 columns per referenced base table in a multitable join index definition if the index is not row compressed.

Unique type supported

Yes

No

Nonunique type supported

Yes

Affects base table row partitioning

Yes

No

Affects index subtable row distribution

Not applicable.

There are no primary index subtables.

Primary indexes are stored in-line as row data.

Unique

Yes

Not applicable

Nonpartitioned and PPI join and hash index rows are distributed on their primary indexes, just like base table rows.

Column‑partitioned NoPI join index rows are not distributed on their primary index value because they have no primary index.

Nonunique

No

Can be partitioned within an AMP

Yes

This is only true for row partitioning.

No

Yes

The primary index for a join index can be a PPI if the join index is not row compressed.

Row compressed join indexes cannot have a partitioned primary index.

Column‑partitioned NoPI join indexes cannot have a primary index, but they can be row‑partitioned.

No

Can be created and dropped dynamically

No, if the table is populated.

Yes, if the table is not populated.

Yes

Enhances access performance

Yes

Supports multiple data types

XML, BLOB, CLOB, ARRAY, VARRAY, Period, and JSON data types are not supported for any kind of index.

Geospatial data types are only supported for NUSIs.

Yes

Stored in a separate file structure

No

Yes

Maintenance processing overhead

No

Yes