この削除操作では、ターゲット テーブル パーティション列についての間接制約(slppit1.pc = srct1.b AND srct1.b = 10)を使用して、PartitionRangeロックがテーブルslppit1に設定されるように、単一のパーティション排除リストを生成します。
この例のテーブル定義は、以下のとおりです。
CREATE TABLE HLSDS.SLPPIT1 (PI INT, PC INT, X INT, Y INT)
PRIMARY INDEX (PI)
PARTITION BY (RANGE_N(PC BETWEEN 1 AND 10 EACH 1));
CREATE TABLE HLSDS.SRCT1 (A INT, B INT, C INT) PRIMARY INDEX (A);
DELETE文のEXPLAINは、パーティション ロックを示します。
Explain DELETE HLSDS.SLPPIT1 FROM HLSDS.SRCT1
WHERE SLPPIT1.PC = SRCT1.B AND SRCT1.B = 10;
1) First, we lock HLSDS.SRCT1 for read on a reserved rowHash to prevent
global deadlock.
2) Next, we lock HLSDS.SLPPIT1 for write on a reserved rowHash in a
single partition to prevent global deadlock, and we lock
HLSDS.SLPPIT1 for read on a reserved rowHash in a single partition
to prevent global deadlock.
3) We lock HLSDS.SRCT1 for read, we lock HLSDS.SLPPIT1 for write on a
single partition, and we lock HLSDS.SLPPIT1 for read on a single
partition.
4) We do an all-AMPs RETRIEVE step from HLSDS.SRCT1 by way of an
all-rows scan with a condition of ("(NOT (HLSDS.SRCT1.A IS NULL ))
AND (HLSDS.SRCT1.B = 10)") into Spool 2 (all_amps), which is
duplicated on all AMPs. The size of Spool 2 is estimated with no
confidence to be 4 rows (68 bytes). The estimated time for this
step is 0.07 seconds.
5) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an
all-rows scan, which is joined to a single partition of
HLSDS.SLPPIT1 with a condition of ("HLSDS.SLPPIT1.PC = 10") with
a residual condition of ("HLSDS.SLPPIT1.PC = 10"). Spool 2 and
HLSDS.SLPPIT1 are joined using a product join, with a join
condition of ("HLSDS.SLPPIT1.PC = B"). The result goes into
Spool 1 (all_amps), which is built locally on the AMPs. Then we
do a SORT to partition Spool 1 by rowkey and the sort key in spool
field1 eliminating duplicate rows. The size of Spool 1 is
estimated with no confidence to be 1 row (18 bytes). The estimated
time for this step is 0.06 seconds.
6) We do an all-AMPs MERGE DELETE to HLSDS.SLPPIT1 from Spool 1
(Last Use) via the row id. The size is estimated with no confidence
to be 1 row. The estimated time for this step is 0.81 seconds.
7) Finally, we send out an END TRANSACTION step to all AMPs involved in
processing the request.