17.10 - Optimized Method of Maintaining a Join Index During UPDATE Operations - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

Optimizations have been made for UPDATE requests that allow the affected join index rows to be located via direct access. For example, if an UPDATE 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.

Preconditions for Update Optimization

To use this optimized method (that is, the direct update approach), the following conditions must be present:

  • A primary or secondary access path to the join index.
  • If join_index_column_2 is defined, no modifications to join_index_column_1 columns.
  • No modifications to the join condition columns appearing in the join index definition.
  • No modifications to the primary index columns of the join index.

Example of an Optimal Method for Maintaining a Join Index During an UPDATE Request

The following is an example of an optimized method for maintaining join index during an UPDATE request:

     EXPLAIN
     UPDATE lineitem
     SET l_quantity=l_quantity - 5
     WHERE l_orderkey=10;
   
      *** Help information returned. 11 rows.
      *** Total elapsed time was 1 second.
   
Explanation
-----------------------------------------------------------------------
  1) First, we execute the following steps in parallel.
       1) We do a single-AMP UPDATE 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 UPDATE 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.