Optimizations have been made for update statements that allow the affected join index rows to be located via direct access. For example, if a DELETE request specifies a search condition on the primary or secondary of a join index, the affected join index rows are not reproduced. Instead, the join index may be directly searched for the qualifying rows and modified accordingly.
To use this optimized method (that is, the direct update approach), the following conditions must be present.
The following is an example of an optimized method for maintaining join index during a DELETE request:
EXPLAIN DELETE FROM lineitem
WHERE l_orderkey=10;
*** Help information returned. 11 rows.
*** Total elapsed time was 2 seconds.
Explanation
-----------------------------------------------------------------------
1) First, we execute the following steps in parallel.
1) We do a single-AMP DELETE from join index table
df2.OrderJoinLine by way of the primary index
"df2.OrderJoinLine.l_orderkey = 10" with a residual
condition of ("df2.OrderJoinLine.l_orderkey = 10").
2) We do a single-AMP DELETE from df2.lineitem by way of the
primary index "df2.lineitem.l_orderkey = 10" with no
residual conditions.
2) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> No rows are returned to the user as the result of statement 1.