17.10 - General Methods of Maintaining a Join Index During Joined DELETE 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)

This topic describes some general methods by which Vantage 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. Vantage 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.

Vantage 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, Vantage 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.