16.10 - About Choosing a Primary Indexing Method for a Column-Partitioned Table or Join Index - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

Column-partitioned tables and join indexes can have one of these primary indexing methods:

  • No primary index (NoPI)
  • Primary index (PI)
  • Primary AMP index (PA)

NoPI is covered in the following topic NoPI Tables, Column-Partitioned NoPI Tables, and Column-Partitioned NoPI Join Indexes.

Benefits and Considerations for Column-Partitioned Tables with a Primary Index

Column-partitioned tables with a primary index provide these benefits:

  • Single-AMP access
  • More efficient aggregations than NoPI
  • Direct merge hash (and rowkey) join to table when a join is on the PI columns

Customers may be interested in this indexing method if they want to implement only a few partitions, for example, to vertically partition a PI table into frequently used columns and rarely used columns. Another use may be to partition rows frequently used in WHERE and SELECT clauses. This method provides the advantages of a PI table but reduces the amount of data read for most requests.

Considerations include the following:

  • Load times may increase. INSERT-SELECT requires row redistribution and sort by combined partition/hash value of the source relation if the source relation does not have the same PI columns.
  • Row header compression and autocompression may be less effective than using column-partitioned primary AMP index or column-partitioned NoPI.
  • The number of table headers increases (compared with column-partitioned NoPI tables), but for wide rows and many rows per value the effect is negligible. The increase in row headers may become excessive as you add more column partitions, especially if the PI is unique or nearly unique.
  • Sliding window joins are not supported.
  • Other limitations apply to all column-partitioned tables. Column-partitioning is not supported for set tables, queue tables, global temporary tables, volatile tables, derived tables, multitable or aggregate join indexes, compressed join indexes, hash indexes, or secondary indexes.

Benefits and Considerations for Column-Partitioned Tables with a Primary AMP Index

Column-partitioned tables with a primary AMP index provide these benefits:

  • Single-AMP access
  • Local access when joining on the primary AMP index columns
  • Better performance for aggregations than column-partitioned NoPI tables when grouping by the primary AMP index columns.
  • More efficient joins than column-partitioned NoPI tables:
    • Allows a local dynamic hash or product join when performing an equality join on primary AMP index columns and the other relation has the same primary AMP index or PI columns.
    • Allows a dynamic hash or product join with redistribution instead of duplication of the other relation when performing an equality join on primary AMP index columns and the other relation does not have the same primary AMP index or PI columns.
    • Reduces memory usage, CPU, and I/O.
    • Allows for the other relation to be much larger.

Customers may be interested in this indexing method to replace already existing column-partitioned NoPI tables or if they are interested in implementing column-partitioning for the first time.

Considerations include the following:

  • Load times may increase. INSERT-SELECT requires row redistribution if the source does not have the same primary AMP index or PI columns.
  • Row header compression and autocompression are similar to that on column-partitioned NoPI tables.
  • A column-partitioned primary AMP index may not be defined as UNIQUE, but it can have a USI or primary key on the same columns.
  • Other limitations apply to all column-partitioned tables. Column-partitioning is not supported for set tables, queue tables, global temporary tables, volatile tables, derived tables, multitable or aggregate join indexes, compressed join indexes, hash indexes, or secondary indexes.