Locks and Concurrency | CREATE JOIN INDEX | Teradata Vantage - Locks and Concurrency - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
jbg1472252759029
lifecycle
latest
Product Category
Teradata Vantage™

Join index maintenance is optimized to use localized rowhash or rowkey-level locking whenever possible. Table-level locks are applied when the maintenance is performed using all-AMPs operations such as spool merges.

When the table being updated contains a join index, an EXPLAIN of the UPDATE request illustrates whether an all-AMPs operation and table-level locking are used at the time of the update, or a single-AMP operation with rowhash or rowkey-level locking.

The following is a list of the conditions that support single-AMP updating and rowhash or rowkey-level locking on join indexes when the base table is updated a row-at-a-time. These optimizations may not be applied to complicated indexes when cardinality estimates are made with low confidence or when the index is defined on three or more tables with all its join conditions based on nonunique and unindexed columns.

When a row-at-a-time INSERT on the base table is being performed, inserts that specify an equality constraint on the primary index column set of a table with joins between some non-primary index columns of the table and the primary index columns of another table are optimized to use rowhash-level locks.

As long as the number of rows that qualify is within a 10% of the number of AMPs threshold, and the number of rows resulting from join operations is also within this threshold, this INSERT request does not incur any table-level locks.

If a single-table non-covering join index is defined on a table, the join that is processed by duplicating the qualified rows to join with another table can be processed as follows:

  1. A single-AMP retrieve from the first table by way of an equality condition into Spool 2. Spool 2 is hash redistributed by the expression on the right-hand side of the equality condition and qualifies as a group-AMPs spool.
  2. A few-AMPs join from Spool 2 to the join index is based on an equality condition, with the result going into Spool 3. Spool 3 is redistributed by a ROWID condition on the first table and also qualifies as a group-AMPs spool.
  3. A few-AMPs join back from Spool 3 to the first table on ROWID.

As long as the number of rows that qualify the join is within the 10% threshold, no table-level locks are incurred for the DELETE request.

For more information, see Teradata Vantage™ - Database Design, B035-1094.

When a row-at-a-time UPDATE request on the base table is being performed, the following restrictions apply:
  • The value for the primary index of the join index must be specified in the WHERE clause predicate of the request.
  • The UPDATE request cannot change the primary index of the join index.
  • When it is cost effective to access the affected join index rows by means of a NUSI, it is done using rowhash locks and a direct update step. If only a few rows are updated (a few-AMPs operation), rowhash READ locks are placed on the NUSI subtable for the index rows that are read. Rowhash locks are also applied to the base table using the rowID values extracted from the index rows.

For more information, see Teradata Vantage™ - Database Design, B035-1094.

When a row-at-a-time DELETE request on the base table is performed, the following restrictions apply:
  • The value for the primary index of the join index must be specified in the WHERE clause predicate of the DELETE request.
  • The deleted row must not be from the inner table of an outer join in the CREATE JOIN INDEX statement with the following exceptions:
    • The outer join condition in the join index is specified on a UPI column from the inner table.
    • The outer join condition in the join index is specified on a NUPI column from the inner table.
  • When it is cost effective to access the affected join or hash index rows by means of a NUSI, it is done using rowhash-level locks and a direct delete step. If only a few rows are deleted (a few-AMPs operation), rowhash-level READ locks are placed on the NUSI subtable for the index rows that are read. Rowhash-level locks are also applied to the base table using the rowID values extracted from the index rows.

Under all other conditions, a single-row UPDATE operation causes a table-level WRITE lock to be placed on the join index.

If table-level locks are reported in the EXPLAIN text, then consider using set processing approaches with one or more secondary indexes as an alternative.

For more information, see Teradata Vantage™ - Database Design, B035-1094.

For information about how locks are placed on join indexes for tactical queries, see Teradata Vantage™ - Database Design, B035-1094.

Be aware that you cannot drop a join index to enable Teradata MultiLoad or Teradata FastLoad batch loads until any requests that access that index complete processing. Requests place READ locks on any join indexes they access, and the database defers processing of any DROP JOIN INDEX requests against locked indexes until their READ locks have all been released.