15.10 - General Method of Maintaining a Join Index During Simple DELETE 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

This topic describes a general method by which Teradata Database maintains a join index during simple delete operations.

The following is an example of a general case method for maintaining a join index during a simple DELETE 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.

             8

Inserts new nonmatching outer join rows into the join index.

     EXPLAIN
     DELETE FROM ordertbl
     WHERE o_custkey = 1001;
   
      *** Help information returned. 37 rows. 
      *** Total elapsed time was 2 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 write on a
     RowHash to prevent global deadlock for df2.Ordertbl.
  3) We lock a distinct df2."pseudo table" for read on a RowHash to
     prevent global deadlock for df2.lineitem.
  4) We lock df2.OrderJoinLine for write, we lock df2.Ordertbl
     for write, and we lock df2.lineitem for read.
  5) We do an all-AMPs JOIN step from df2.Ordertbl by way of a
     RowHash match scan with a condition of (
     "df2.Ordertbl.o_custkey = 1001"), which is joined to
     df2.lineitem.  df2.Ordertbl and df2.lineitem are joined
     using a merge join, with a join condition of (
     "df2.lineitem.l_orderkey = df2.Ordertbl.o_orderkey").  The
     input tables df2.Ordertbl and df2.lineitem 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 5 seconds.
  6) We execute the following steps in parallel.
       1) We do a MERGE DELETE to df2.OrderJoinLine from Spool 1.
       2) We do an all-AMPs DELETE from df2.Ordertbl by way of an
          all-rows scan with a condition of 
          ("df2.Ordertbl.o_custkey = 1001").
  7) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of
     an all-rows scan into Spool 2, which is redistributed by hash code
     to all 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 35 minutes and 59 seconds.
  8) We do a MERGE into df2.OrderJoinLine from Spool 2 (Last Use).
  9) We spoil the parser's dictionary cache for the table.
 10) 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.