This INSERT operation performs a bulk insert. With partition level locking, the parser generates a static partition elimination list based on condition "PC = 4" (that is, positional matching of slppit1.pc = srct1.b = 4) and uses this list to place a PartitionRange lock.
The table definitions for this example are as follows:
CREATE TABLE HLSDS.SLPPIT1 (PI INT, PC INT, X INT) PRIMARY INDEX (PI)
PARTITION BY (RANGE_N(PC BETWEEN 1 AND 100 EACH 10));
CREATE TABLE HLSDS.SRCT1 (A INT, B INT, C INT) PRIMARY INDEX (A);
An EXPLAIN of the INSERT operation shows the condition PC=4 as WHERE B=4:
Explain INSERT INTO HLSDS.SLPPIT1 SELECT A, B, C FROM HLSDS.SRCT1
WHERE B = 4;
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.
3) We lock HLSDS.srct1 for read, and we lock HLSDS.slppit1
for write 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 ("HLSDS.srct1.b = 4") 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 (25 bytes).
The estimated time for this step is 0.03 seconds.
5) We do an all-AMPs MERGE into HLSDS.slppit1 from Spool 1
(Last Use). The size is estimated with no confidence
to be 1 row. The estimated time for this step is 0.55 seconds.
6) Finally, we send out an END TRANSACTION step to all AMPs
involved in processing the request.