15.10 - General Method of Maintaining a Join Index During UPDATE Operations - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

The following is an example of a general method of maintaining join index during an UPDATE request.

Note the following items in the EXPLAIN output:

 

This step …

Does this …

5

reproduces the affected portion of the join index rows.

6.1

deletes the corresponding rows from the join index.

6.2

reproduces the affected portion of the join index rows and makes the necessary modifications to form the new rows.

7.1

inserts the newly modified rows into the join index.

     EXPLAIN 
     UPDATE lineitem
     SET l_extendedprice=l_extendedprice * .80
     WHERE l_partkey=50;
   
      *** Help information returned. 47 rows. 
      *** Total elapsed time was 3 seconds.
   
Explanation
-----------------------------------------------------------------------
  1) First, we lock a distinct df2."pseudo table" for write on a
     RowHash to prevent global deadlock for df2.orderjoinline.
  2) Next, we lock a distinct df2."pseudo table" for read on a
     RowHash to prevent global deadlock for df2.ordertbl.
  3) We lock a distinct df2."pseudo table" for write on a RowHash to
     prevent global deadlock for df2.lineitem.
  4) We lock df2.orderjoinline for write, we lock df2.ordertbl
     for read, and we lock df2.lineitem for write.
  5) We do an all-AMPs JOIN step from df2.lineitem by way of a
     RowHash match scan with a condition of (
     "df2.lineitem.l_partkey = 50"), which is joined to
     df2.ordertbl.  df2.lineitem and df2.ordertbl are
     left outer joined using a merge join, with a join condition of (
     "df2.lineitem.l_orderkey = df2.ordertbl.o_orderkey").  The
     input tables df2.lineitem and df2.ordertbl will not be
     cached in memory, but they are eligible for synchronized scanning.
     The result goes into Spool 1, which is built locally on the AMPs.
     Then we do a SORT to order Spool 1 by row hash.  The result spool
     file will not be cached in memory.  The size of Spool 1 is
     estimated to be 100,000 rows.  The estimated time for this step is
     6 minutes and 14 seconds.
  6) We execute the following steps in parallel.
       1) We do a MERGE DELETE to df2.OrderJoinLine from Spool 1
          (Last Use).
       2) We do an all-AMPs JOIN step from df2.lineitem by way of a
          RowHash match scan with a condition of (
          "df2.lineitem.l_partkey = 50"), which is joined to
          df2.ordertbl.  df2.lineitem and df2.ordertbl are
          left outer joined using a merge join, with a join condition
          of ("df2.lineitem.l_orderkey = df2.ordertbl.o_orderkey").
          The input tables df2.lineitem and df2.ordertbl will not
          be cached in memory, but they are eligible for synchronized
          scanning.  The result goes into Spool 2, which is built
          locally on the AMPs.  Then we do a SORT to order Spool 2 by
          join index.  The result spool file will not be cached in
          memory.  The size of Spool 2 is estimated to be 100,000 rows.
          The estimated time for this step is 5 minutes and 7 seconds.
  7) We execute the following steps in parallel.
       1) We do a MERGE into df2.orderjoinline from Spool 2 (Last
          Use).
       2) We do an all-AMPs UPDATE from df2.lineitem by way of an
          all-rows scan with a condition of (
          "df2.lineitem.l_partkey = 50").
  8) We spoil the parser's dictionary cache for the table.
  9) 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.