Considerations for Indexing - Teradata Database

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 nonunique.

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 193 and “Example 3” on page 193 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.