15.00 - Row Allocation for INSERT Operations Into Nonpartitioned NoPI Tables and Column-Partitioned Tables - Teradata Database

Teradata Database Design

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

Row Allocation for INSERT Operations Into Nonpartitioned NoPI Tables and Column‑Partitioned Tables

Column-partitioned tables and join indexes are primarily used for running analytics and undertaking data mining analyses where they are loaded with an INSERT … SELECT request and then remain fairly static, with occasional minor maintenance. When the table is or its row partitions are deleted, the anticipated scenario starts over again. Column‑partitioned tables are not intended for OLTP or other activities in which a table is frequently updated. While you can insert individual rows into NoPI tables and column‑partitioned tables, singleton inserts into these types of tables should not be undertaken frequently.

As is true for all insert operations into nonpartitioned NoPI tables and column‑partitioned tables, Teradata Database randomly distributes the individual rows or blocks of rows to the AMPs or copies them locally because they do not have a primary index that can be used to hash‑distribute them across the AMPs.

The anticipated usage for column‑partitioned tables is roughly 88% large INSERT … SELECT operations or array-INSERTs into an empty table or empty row partitions, 2% singleton INSERTs, 7% updates, and 3% deletions.