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

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

The following table compares the access mechanisms for the two primary index types over a range of typical operations and conditions.

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.
  • 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 the affected row partition, checking primary index and residual conditions.
  • Row-key lock.
Constraint on the partitioning columns.
  • Single-AMP and direct access to the first data block of rows with the same row hash value in each of the non-eliminated row partitions.
  • Scan all rows with the same row hash value in the affected row partitions, checking primary index and residual conditions.
  • RowHash-in-All-Partitions lock, which is equivalent to a RowHash lock for a non-row-partitioned table.
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.

Otherwise:
  • Single-AMP and direct access to the first data block of rows with same row hash value in each populated row partition.
  • Scan all rows with the same row hash value in each row partition, checking primary index and residual conditions.
  • RowHash-in-All-Partitions lock.

    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.
  • All-AMP, but only scan a single row partition on each AMP.
  • Scan all rows in the affected row partition checking for any residual conditions.
  • All-AMP partition-range lock (single partition).
Constraints on the partitioning columns.
  • All-AMP, but only scan non-eliminated row partitions.
  • Scan all rows in the affected row partitions checking for any residual conditions.
  • All-AMP table lock.
Otherwise:
  • All-AMP, full-file scan checking each row for any residual conditions.
  • All-AMP table lock.
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.

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:

  • When a static partition elimination list is available, a partition-range lock may be used.

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

  • If the table has a defined index, an all-AMP single-writer lock may be needed. The single-writer lock prevents concurrent writes but allows concurrent reads on other partitions.
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:
  • All-AMP table lock if UPDATE modifies a partitioning column.
  • If an all-AMP partition-range lock is used, an additional all-AMP single-writer lock may be added if index maintenance is needed. This single-writer lock prevents concurrent writes but allows concurrent reads on other partitions.

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.

See also Single-AMP Queries and Partitioned Tables and All-AMP Tactical Queries and Partitioned Tables for design issues specific to PPI support for tactical queries.