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.