15.10 - General Methods of Maintaining a Join Index During Joined 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 some general methods by which Teradata Database maintains join indexes during join delete operations on a base table.

A join delete is done to a base table when a FROM clause specifies more than one table with some join conditions such as the following generic example of a deletion from a base table on which a join index is defined.

     DELETE delete_table_name 
     FROM join_table_name_1, join_table_name_n 
     WHERE condition;

A join delete happens when the deletion is from a table using a spool of qualified rows. Teradata Database uses the following plan for a join delete to a primary‑indexed base table with a primary‑indexed join index.

  6) We do an all-AMPs JOIN step (No Sum) from Spool 2 (Last Use) by
     way of a RowHash match scan, which is joined to Spool 3 (Last Use)
     by way of a RowHash match scan.  Spool 2 and Spool 3 are joined
     using a merge join, with a join condition of ("Spool_3.col2 =
     Spool_2.y1").  The result goes into Spool 1 (all_amps), which is
     redistributed by hash code to all AMPs with hash fields (
     "Spool_3.Field_1025").  Then we do a SORT to order Spool 1 by row
     hash.  The size of Spool 1 is estimated with no confidence to be 2
     rows (50 bytes).  Spool Asgnlist:
     "Field_1025" = "Spool_3.Field_1025",
     "Field_1026" = "{RightTable}.Field_1026",
     "Field_1027" = "{RightTable}.Field_1027".
     The estimated time for this step is 0.03 seconds.
  7) We do an all-AMPs MERGE DELETE to DRCP_DELETE.t_pi from Spool 1.
     The size is estimated with no confidence to be 2 rows.  The
     estimated time for this step is 0.76 seconds.
  8) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of
     an all-rows scan into Spool 4 (all_amps), which is redistributed
     by hash code to all AMPs with hash fields ("Spool_1.col3").
     Then we do a SORT to order Spool 4 by row hash.  The size of Spool
     4 is estimated with no confidence to be 2 rows (42 bytes).  Spool
     Asgnlist: "col2",
     "Spool_1.col3".
   9) We do an all-AMPs MERGE DELETE to drcp_delete.NONCPJI from Spool 4
     (Last Use).  The size is estimated with no confidence to be 2 rows.
     The estimated time for this step is 0.74 seconds.	

For a column‑partitioned NoPI base table with a primary‑indexed join index, it could be very costly to retrieve all the columns from the column‑partitioned table as was demonstrated in step 6 of the previous example, so in this case only the row ID of the column‑partitioned table is spooled, and it is then used for the merge delete operation on the column‑partitioned table.

Teradata Database can then join this row ID spool with the column‑partitioned table to provide the qualified join index rows that are used to make the merge delete operation on the primary‑indexed join index, as the following EXPLAIN text indicates in step 8.

  6) We do an all-AMPs JOIN step (No Sum) from Spool 2 (Last Use) by
     way of a RowHash match scan, which is joined to Spool 3 (Last Use)
     by way of a RowHash match scan.  Spool 2 and Spool 3 are joined
     using a merge join, with a join condition of ("Spool_3.cpcol2 =
     Spool_2.y1").  The result goes into Spool 1 (all_amps), which is
     redistributed by hash code to all AMPs with hash fields (
     "Spool_3.Field_1").  Then we do a SORT to order Spool 1 by row
     hash.  The size of Spool 1 is estimated with no confidence to be 2
     rows (50 bytes).  Spool Asgnlist:
     "Field_1" = "Spool_3.Field_1"
     The estimated time for this step is 0.03 seconds.
  7) We do an all-AMPs JOIN step (No Sum) from Spool 1 (Last Use), which 
     is joined DRCP_DELETE.cpt.  Spool 1 and DRCP_DELETE.cpt are joined
     using a rowid join, with a join condition of ("Spool_1.Field_1 =
     cpt.ROWID").  The result goes into Spool X (all_amps), which is
     redistributed by hash code to all AMPs with hash fields (
     "Spool_X.cpcol3").  Then we do a SORT to order Spool X by row
     hash.  The size of Spool 1 is estimated with no confidence to be 2
     rows (50 bytes).  Spool Asgnlist: 
     "Spool_x.col2",
     "Spool_X.col3".
     The estimated time for this step is 0.03 seconds.
  8) We do an all-AMPs MERGE DELETE to DRCP_DELETE.cpt from Spool 1 via the
     rowid.  The size is estimated with no confidence to be 2 rows.  The
     estimated time for this step is 0.76 seconds.
  9) We do an all-AMPs MERGE DELETE to drcp_delete.NONCPJI from Spool X
     (Last Use).  The size is estimated with no confidence to be 2 rows.
     The estimated time for this step is 0.74 seconds.	

The join delete happens when the deletion is from a table using a spool of qualified rows. For this example, Teradata Database only needs to add the row ID values from the base table to the qualified row spool (Spool 4) in step 7 (boldface text) rather than the rows themselves to delete the qualified rows. The same join index spool is used to do a merge delete operation on the column‑partitioned join index as the following EXPLAIN text demonstrates.

  6) We do an all-AMPs JOIN step (No Sum) from Spool 2 (Last Use) by
     way of a RowHash match scan, which is joined to Spool 3 (Last Use)
     by way of a RowHash match scan.  Spool 2 and Spool 3 are joined
     using a merge join, with a join condition of ("Spool_3.cpcol2 =
     Spool_2.y1").  The result goes into Spool 1 (all_amps), which is
     redistributed by hash code to all AMPs with hash fields (
     "Spool_3.Field_1") and Field1 ("Spool_3.Field_1").  Then we do a
     SORT to order Spool 1 by row hash and the sort key in spool field1
     eliminating duplicate rows.  The size of Spool 1 is estimated with
     no confidence to be 4 rows (72 bytes).  Spool Asgnlist:
     "Field_1" = "Spool_3.Field_1".
     The estimated time for this step is 0.13 seconds.
  7) We do an all-AMPs MERGE DELETE to DRCP_DELETE.cpt from Spool 1
     (Last Use) via the row id.  Also, deleted rows are inserted in 
     Spool 4.  The size is estimated with no confidence to be 4 rows.
     The estimated time for this step is 15.04 seconds.
  8) We do an all-AMPs RETRIEVE step from Spool 4 (Last Use) by way of
     an all-rows scan into Spool 5 (all_amps), which is redistributed
     by hash code to all AMPs with hash fields ("Spool_4.ROWID").
     Then we do a SORT to order Spool 5 by row hash.  The size of Spool
     5 is estimated with no confidence to be 4 rows (84 bytes).  Spool
     Asgnlist: "Spool_4.ROWID"
     The estimated time for this step is 0.10 seconds.
  9) We do an all-AMPs MERGE DELETE to drcp_delete.CPJ1 from Spool 5
     (Last Use) using FTS_RowIDMatch.  The size is estimated with no 
     confidence to be 4 rows.
     The estimated time for this step is 10.53 seconds.