Join Index for DELETE, INSERT, and UPDATE Operations | VantageCloud Lake - Maintaining a Join Index for DELETE, INSERT, and UPDATE Operations - Teradata Vantage

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, weigh the overhead cost associated with maintaining the join indexes during updates 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.

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.

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);