15.00 - Considerations for Indexing - Teradata Database

Teradata Database Design

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

Considerations for Indexing

You should evaluate the following considerations when assigning data types to index columns.

  • Suitability of the type family for indexing
  • Numeric types are best because they are compact and often tend toward uniqueness in business data.
  • Character and byte types are poor because they tend not to be compact and because character and byte data naturally tends to be non‑unique.
  • XML, BLOB, CLOB, Period, ARRAY, VARRAY, and JSON data type columns cannot be used to define any type of index.
  • Hash collisions
  • “Example 2” on page 244 and “Example 3” on page 245 under the topic “Hash-Related Functions” address this issue.

  • Storage space
  • Greater space requirements for a column translate to fewer data rows per block, which results in slower full‑table scans.

  • Join and predicate conversions
  • Any time tables are joined on a column, whether indexed or not, the columns must have the same data type in both tables. If the types do not agree, one must be converted, which results in a performance hit. The same is true for comparisons made in WHERE clause predicates.