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

Teradata Database Design

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

Row‑Partitioned and Nonpartitioned Primary Index Access for Typical Operations

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

 

Operation

Nonpartitioned Table

Partitioning Where PI Includes All Row Partitioning Columns

Partitioning Where 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-hash 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-hash 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.
  • Row-hash lock.
  • 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.

    (continued)

    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-hash lock.

    (continued)

    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.
  • Row-hash 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 table lock.
  • 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‑hash locks.

    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.

    (continued)

    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-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 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-hash lock.

    INSERT … SELECT

    Sort inserts as needed and merge into table.

    All-AMP table lock.

    DELETE

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

    Same as selection.

    Same as selection.

    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.

    See also “Single-AMP Queries and Partitioned Tables” on page 905 and “All‑AMP Tactical Queries and Partitioned Tables” on page 909 for design issues specific to PPI support for tactical queries.