15.00 - Maintenance Cost for Column-Partitioned Tables and Join Indexes - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Maintenance Cost for Column‑Partitioned Tables and Join Indexes

Although rows in a NoPI tables and column‑partitioned tables are not hashed by a primary index, they are all assigned a unique row identifier that includes an internally generated hash bucket number. This enables fallback and index maintenance on nonpartitioned NoPI and column‑partitioned tables to work very much the same as they do for primary‑indexed tables, so the maintenance cost for fallback and indexes for the two table types is usually comparable.

There might be some degradation for index maintenance if there are insufficient available column partition contexts for collecting index values from multiple column partitions.

Deleting or updating a row in a column‑partitioned table requires at least one column partition scan of all row partitions that are not eliminated if there is row partitioning, but there is no applicable secondary index defined for the table.

A column‑partitioned table is not intended to undergo a significant amount of maintenance after being initially populated. If there is a need for a significant amount of maintenance, you should verify that performance is acceptable, and if it is not, you should not use a column‑partitioned table for the workload.