15.00 - Table Access Summary - Teradata Database

Teradata Database Design

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

Table Access Summary

The following table characterizes 9 access methods available to the Optimizer for accessing tables. Definitions for the access method abbreviations used in that table are provided in the following table.

 

Access Method Abbreviation

Definition

UPI

Unique Primary Index

UPPI

Unique Partitioned Primary Index

If you row-partition a unique primary index, all the partitioning columns must be included in the index definition.

NUPI

Nonunique Primary Index

NUPPI

Nonunique Partitioned Primary Index

USI

Unique Secondary Index

NUSI

Nonunique Secondary Index

JI

Join Index

HI

Hash Index

FTS

Full‑Table Scan

With or without partition elimination.

For many table join operations, a join index is often a more efficient means for accessing data. See “Join Indexes” on page 215 and Chapter 11: “Join and Hash Indexes.”

 

Table Access Summary

Access Method

Relative Efficiency

Number of AMPs Accessed

Number of Rows Returned

Spool Space Required by Query?

UPI

Very efficient.

1

1

No.

UPPI

Very efficient.

The relative efficiency is the same for single‑level and multilevel PPI tables.

1

1

No.

NUPI

 

Non‑unique PPI

Efficient when selectivity is high and skew is low and if limited to one internal row partition by row partition elimination.

Otherwise, performance degrades as a function of the number of internal row partitions that must be accessed.

The relative efficiency is the same for single‑level and multilevel PPI tables.

1

Multiple.

Depends.

  • If a query returns a single response, then no spool is required.
  • If a query returns more rows than can be handled by a single response, then spool is required.
  • USI

    Very efficient.

    USI access is normally the only way to access a single row in a NoPI or column‑partitioned table without a full‑table scan; however, if a NUSI on a NoPI or column‑partitioned table only indexes a single row, then it can also be used.

    2

    The maximum number of AMPs accessed for a USI row retrieval is two. If a base table row and its USI row happen to hash to the same AMP, then the retrieval only accesses one AMP.

    1

    No.

    NUSI

    Efficient when the number of rows accessed is less than the number of data blocks in the table.

    NUSI access is the only way to access a selected multirow set in a NoPI table, column‑partitioned table, or column‑partitioned join index without a full‑table scan unless an appropriate join index exists.

  • One if the NUSI is defined on the same column set as the NUPPI.
  • All otherwise.
  • Multiple.

    Yes.

    Join index

    Very efficient.

  • If the join index is accessed by its primary index, the number of AMPs accessed is 1.
  • If the join index is not accessed by its primary index, the number of AMPs accessed is all.
  •  

  • If the join index is accessed by its primary index, multiple rows are accessed
  • If the join index is not accessed by its primary index, all AMPs are accessed using a full‑table scan.
  •  

    Depends.

  • If the join index covers the query, no spool is required.
  • If the join index partially covers the query and its definition specifies either a ROWID, the UPI for the base table, or a USI on that table, spool is required.
  • You can only specify ROWID in the outermost SELECT of the CREATE JOIN INDEX statement. See “CREATE JOIN INDEX” in SQL Data Definition Language Detailed Topics.

    Join index with a nonunique PPI

    Efficient when selectivity is high and skew is low and if limited to one internal partition by row partition elimination.

    More efficient if there is row or column partition elimination.

    Otherwise, performance degrades as a function of the number of internal row partitions that must be accessed.

    1

    Multiple.

    Depends.

  • If a query returns a single response, no spool is required.
  • If a query returns more rows than can be handled by a single response, spool is required.
  • Hash index

    Very efficient.

    You cannot define a hash index on a NoPI table.

    Depends on the primary index.

    Multiple.

    Depends.

  • If the hash index covers the query, no spool is required.
  • If the hash index partially covers the query, spool is required.
  • Full-table scan

    Efficient because the system touches each row and data block only once.

    If an nonpartitioned NoPI table or column‑partitioned NoPI table has no secondary or join indexes, a full‑table scan is the only way its rows can be accessed.

    This also applies to a column‑partitioned NoPI join index that has no secondary indexes.

    All.

    All.

    Yes.

    Can require spool as large as the table being scanned.