Row-Partitioned and Nonpartitioned Primary Index Access for Typical Operations - Teradata Database

Operation

Nonpartitioned Table

Row-Partitioned Table
(PI Includes All Row Partitioning Columns

Row-Partitioned Table (PI Does Not Include All Partitioning Columns

Selection with an equality (= or IN) constraint on the primary index columns

Single-AMP and direct access to the first data block of rows with the same row hash value.

Scan all rows with the same row hash value checking the primary index and residual conditions.

Row-hash lock.

Single-AMP and direct access to the first data block of rows with the same row hash value for only the specified row partition.

Scan all rows with the same row hash value in this row partition, checking primary index and residual conditions.

Row-key lock.

Equality constraint on the partitioning columns.

Constraint on the partitioning columns.

Selection with an equality (= or IN) constraint on the primary index columns

(continued)

Single-AMP and direct access to the first data block of rows with the same row hash value.

Scan all rows with the same row hash value checking the primary index and residual conditions.

Row-hash lock.

Single-AMP and direct access to the first data block of rows with the same row hash value for only the specified row partition.

Scan all rows with the same row hash value in this row partition, checking primary index and residual conditions.

Row-key lock.

Otherwise:

Teradata Database might use a secondary index to reduce the number of rows accessed.

Selection without an equality constraint on the primary index columns and no applicable secondary, hash, or join index

All‑AMP, full-file scan, checking each row for any residual conditions.

All-AMP table lock.

Equality constraints on all the partitioning columns.

Constraints on the partitioning columns.

Otherwise:

Joins

A wide variety of join possibilities exist for nonpartitioned primary‑indexed tables.

In many cases, Teradata Database applies an all‑AMPs table lock; however, in some cases it applies row‑hash locks.

A wide variety of join possibilities exist for PPI table joins.

Joins on partitioning columns might be optimized in some cases using dynamic row partitioning elimination on the AMPs.

In many cases, Teradata Database applies an all-AMP table lock; however, in some cases it applies row‑key locks. The lock on source tables of a join is the same as the lock for a SELECT request.

Joins (continued)

A wide variety of join possibilities exist for nonpartitioned primary‑indexed tables.

In many cases, Teradata Database applies an all‑AMPs table lock; however, in some cases it applies row‑hash locks.

Tables can be joined in the same manner as a table with a nonpartitioned primary index and can also take advantage of row partition elimination possibilities when possible.

For a non-direct join to a PPI table or a direct equality join on the primary index columns and the row partitioning columns, tables can be joined in the same manner as a table with a nonpartitioned primary index and can also take advantage of row partition elimination possibilities when possible.

For a direct equality join on the primary index columns, but not on all the partitioning columns, several optimizations might be invoked; however, they might use more memory and processing time to handle the row partitions.

In those cases where a direct join would are chosen for a nonpartitioned primary index table, the Optimizer might estimate that a non‑direct join would have lower cost for a PPI table and use it instead.

INSERT

Single-AMP and direct access to the first data block of rows with the same row hash value.

If a SET table and there are no unique indexes, verify that no duplicate rows within this same row hash value exist.

If a unique primary index, verify the uniqueness within this same row hash value.

Append to the end of the rows with the same row hash value.

Update secondary indexes and validate uniqueness for USIs.

Row-hash lock.

Single-AMP and direct access to the first data block of rows with the same row hash value in the specified row partition.

If a SET table and there are no unique indexes, verify that no duplicate rows within this same row hash value exist in this row partition.

If a unique primary index, verify the uniqueness within this same row hash value in this row partition.

Append to the end of the rows with the same row hash value in this row partition.

Update secondary indexes and validate uniqueness for USIs.

Row-key lock.

Single-AMP and direct access to the first data block of rows with the same row hash value in the specified row partition.

If a SET table and there are no unique indexes, verify that no duplicate row within this same row hash value exist in this row partition.

Append to the end of the rows with the same row hash value in this row partition.

Update secondary indexes and validate uniqueness for USIs.

Row-key lock.

INSERT … SELECT

Sort inserts as needed and merge into table.

An all-AMP table lock is typically used, but other locks may be used depending on the conditions:

Note: Except for a few special cases, partition-range locks are limited to a single partition.

DELETE

Same as selection plus secondary, hash, and join index maintenance.

Same as selection.

If all-AMP partition-range lock is used, an additional all-AMP single-writer lock may be added if the table has indexes defined. This single-writer lock prevents concurrent writes but allows concurrent reads on other partitions.

Same as selection.

If all-AMP partition-range lock is used, an additional all-AMP single-writer lock may be added if the table has indexes defined. This single-writer lock prevents concurrent writes but allows concurrent reads on other partitions.

UPDATE (Non‑Upsert form)

Same as selection unless index columns are updated, plus secondary, hash, and join index maintenance.

If index columns are updated, this is treated as an unreasonable update. That is, for an update of primary index columns, the updated rows must be deleted and reinserted based on the new values (most likely on a different AMP) using an all-AMP table lock.

Same as selection unless index columns are updated:

If index columns are updated, this is treated as an unreasonable update. That is, for an update of primary index columns, the updated rows must be deleted and reinserted based on the new values (most likely on a different AMP) using an all-AMP table lock.

Same as selection unless index or partitioning columns are updated.

If index or partitioning columns are updated, this is treated as an unreasonable update. That is, for an update of primary index or row partitioning columns, updated rows must be deleted and reinserted based on the new values using an all-AMP table lock.

If a primary index column is updated, the row will most likely be reinserted on a different AMP.

If partitioning columns are updated without updating any primary index columns, a row remains on the same AMP.

UPDATE (Upsert form)

This can be a more complex operation and is not described here. For a specific UPSERT operation, refer to the EXPLAIN report for the request.

MERGE

This can be a more complex operation and is not described here. For a specific MERGE operation, refer to the EXPLAIN report for the request.