このINSERT操作では、単一の書き込み側ロックが使用されます。単一の書き込み側ロックは、パーティション ロック テーブルのインデックスの保守を必要とするバルクDML操作に対して追加されます。ロックによりテーブルに対する同時書き込み操作が行なわれず、ロックされないパーティションに対する同時読み取り操作が行なわれます。
この例のテーブル定義は次のとおりです。
CREATE TABLE HLSDS.SLPPIT3 (PI INT, PC INT, USI INT, X INT, Y INT)
PRIMARY INDEX (PI)
PARTITION BY
(RANGE_N(PC BETWEEN 1 AND 1000 EACH 1, NO RANGE, UNKNOWN))
UNIQUE INDEX (USI);
CREATE TABLE HLSDS.SRCT3 (A INT, B INT, C INT, D INT, E INT);
INSERT操作のEXPLAINは、条件PC=100をWHERE B=100として示します。
Explain INSERT HLSDS.SLPPIT3 SELECT * FROM HLSDS.SRCT3 WHERE B = 100;
1) First, we lock HLSDS.srct3 for read on a reserved RowHash
to prevent global deadlock.
2) Next, we lock HLSDS.slppit3 for single writer on a
reserved RowHash in all partitions to serialize concurrent
updates on the partition-locked table to
prevent global deadlock.
3) We lock HLSDS.slppit3 for write on a reserved RowHash
in a single partition to prevent global deadlock.
4) We lock HLSDS.srct3 for read, we lock HLSDS.slppit3 for single
writer to serialize concurrent updates on the partition-locked
table, and we lock HLSDS.slppit3 for write on a single partition.
5) We do an all-AMPs RETRIEVE step from HLSDS.srct3 by way of an
all-rows scan with a condition of ("HLSDS.srct3.b = 100") into
Spool 1 (all_amps), which is built locally on the AMPs.
Then we do a SORT to partition Spool 1 by rowkey.
The size of Spool 1 is estimated
with no confidence to be 1 row (33 bytes).
The estimated time for this step is 0.03 seconds.
6) We do an all-AMPs MERGE into HLSDS.slppit3 from Spool 1
(Last Use). The size is estimated with
no confidence to be 1 row.
The estimated time for this step is 1.73 seconds.
7) Finally, we send out an END TRANSACTION step to all AMPs
involved in processing the request.