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:
- 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.
- 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.
- 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.
- 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.
- 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.