16.10 - Maintaining a Join Index for DELETE, INSERT, and UPDATE Operations - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Programming Reference
User Guide
featnum
B035-1142-161K

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:

     CREATE JOIN INDEX OrderJoinLine AS
     SELECT (l_orderkey,o_orderdate,o_custkey,o_totalprice),
            (l_partkey,l_quantity,l_extendedprice,l_shipdate)
     FROM lineitem
     LEFT OUTER JOIN Ordertbl ON l_orderkey=o_orderkey
     ORDER BY o_orderdate
     PRIMARY INDEX (l_orderkey);