Updating a Column-Partitioned Table - Teradata Database

Updating a Column‑Partitioned Table

Consider the following information before updating data in a column‑partitioned table.

An UPDATE request uses a scan, an index, or a rowID spool to access a column‑partitioned table and select the qualifying rows for the update.

An UPDATE request is processed in the following way.

a Selects the rows to be updated.

b Transforms columns to rows.

c Deletes the old row without recovering the space and marks its delete bit in the delete column partition.

Note: Both LOB space and index space is recovered.

d Reinserts the updated rows, transforming them from rows to columns and appending the column values to their corresponding combined partitions.

Teradata Database recovers the space from the column‑partitioned table when it deletes all of the rows at the end of a transaction or when it deletes the entire row partition that contains the deleted rows at the end of a transaction.

Note: If the columns being updated are only in column partitions of a table with ROW format (and the columns being updated are not primary AMP index, primary index, or partitioning columns), the update is made in place instead of as a delete of the old row and an insert of the new row.

Teradata Database also updates the columns in the column‑partitioned table that are used in a secondary or join index.