Maintaining a Join Index for DELETE, INSERT, and UPDATE Operations | Vantage - 17.10 - Maintaining a Join Index for DELETE, INSERT, and UPDATE Operations - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Request and Transaction Processing

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
English (United States)

As with other indexes (for example, secondary indexes), join indexes are automatically maintained by the system when DELETE, INSERT, or UPDATE requests are issued against the underlying base tables of a join index.

Overhead Costs of Maintaining a Join Index

When considering the use of join indexes, carefully analyze the overhead cost associated with maintaining them during updates and weigh these costs against the benefits to query performance.

Join indexes are maintained by generating additional AMP steps in the execution plan.

The general case method involves first reproducing the affected portion of the join index. This is accomplished by re-executing the join query, as defined in the join index, using only those base table rows that are relevant to the update at hand.

The entire join index result is not reproduced for each update request.

FOR this category of database operation … Join indexes are maintained in this way …
DELETE The corresponding rows in the join index are located and then deleted with a Merge Delete step.
INSERT Newly formed join result rows are added to the join index with a Merge step.
UPDATE The necessary modifications are performed and the corresponding rows in the join index are then replaced by first deleting the old rows (with a Merge Delete) and then inserting the new rows (with a Merge).

Join indexes defined with outer joins usually require additional steps to maintain unmatched rows.

As with secondary indexes, updates can cause a physical row of a compressed join index to split into multiple rows. Each newly formed row has the same fixed column value but contains a different list of repeated column values.

The system does not automatically recombine such rows, so the compressed join index must be dropped and recreated to recombine them.

Join Index Definition for Examples

The examples in the following subsections assume the presence of the following join index:

SELECT (l_orderkey,o_orderdate,o_custkey,o_totalprice),
FROM lineitem
LEFT OUTER JOIN Ordertbl ON l_orderkey=o_orderkey
ORDER BY o_orderdate
PRIMARY INDEX (l_orderkey);