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