16.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
16.10
created_date
June 2017
category
Programming Reference
User Guide
featnum
B035-1142-161K

This topic describes some general methods by which Teradata Database maintains join indexes during join delete operations on a base table.

About Join Index Maintenance for Joined DELETE Operations

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;

Join Delete From a Primary-Indexed Table With a Primary-Indexed Join Index

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.	

Join Delete From a Column-Partitioned NoPI Table With a Primary-Indexed Join Index

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.	

Join Delete From a Column-Partitioned NoPI Table With a Column-Partitioned NoPI Join Index

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.