CPUpdate - Advanced SQL Engine - Teradata Database

Database Utilities

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
xha1591998860283.ditamap
dita:ditavalPath
xha1591998860283.ditaval
dita:id
B035-1102
lifecycle
previous
Product Category
Teradata Vantage™

Determines how column partitions are updated.

Field Group

General

Valid Settings

Setting Description
0 Vantage uses the default setting, currently equivalent to 3.
1 Disables in-place updates. Column partition updates are performed as complex updates using a write lock.
2 An in-place update using a write lock is used if only one ROW-format column partition is updated. Other column partition updates are performed as complex updates using a write lock.
3 In-place updates using a write lock are performed if only ROW-format column partitions are updated. Other column partition updates are performed as complex updates using a write lock.

This is the default.

4 An in-place update using a write lock is used if only one ROW-format column partition is updated. In-place updates using an exclusive lock is used if there is more than one ROW-format column partition, but no COLUMN-format column partitions are updated. Other column partition updates are performed as complex updates using a write lock.

Default

3

Changes Take Effect

After the DBS Control Record has been written.

Usage Notes

The default (setting 3) provides the most concurrency for in-place updates if only ROW-format partitions are updated. However, queries using an access lock may encounter partial updates to a row if more than ROW-format column partition is being updated. That is, an update may have been applied to some of the columns but some columns may have not yet been updated.

When querying using an access lock, if you want rows processed by the query to be either non-updated or completely updated (as would occur for a non-columnar table), use setting 2 or 4.

Setting 2 allows for more concurrency while providing in-place updates if only one column partitions is updated and that column partition has ROW format. However, a complex update is performed if multiple ROW-format column partitions are updated. A complex update is one where the original row is logically deleted, the updated row is inserted, but the space for the original row is not reclaimed.

Setting 4 allows in-place update if multiple ROW-format column partition are updated (to allow space to be reclaimed), but it is done under an exclusive lock if more than one ROW-format column partition is updated to prevent queries using an access lock from seeing partially updated rows.

Setting 1 can be used to disable in-place updates if there is an unexpected problem with in-place update logic. In this case, contact the Teradata Support Center.

For all settings, a query using an access lock may see the non-updated row or the updated row as can occur for update of a non-columnar table. And, as for a non-columnar table, if the query sees the updated row, that updated row may be rolled back to the original row if the update transaction is aborted.

Related Information

For more information about column partitioning, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 and Teradata Vantage™ - Database Design, B035-1094.