Example: Delete Rows with an Indirect Constraint on the Target Table Partitioning Column - Analytics Database - Teradata Vantage

This delete operation uses an indirect constraint on target table partitioning column, that is slppit1.pc = srct1.b AND srct1.b = 10, to generate a single partition elimination list so a PartitionRange lock can be placed on table slppit1.

The table definition for this example is as follows:

  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);

An EXPLAIN of the DELETE statement shows the partition lock:

  Explain DELETE HLSDS.SLPPIT1 FROM HLSDS.SRCT1
            WHERE SLPPIT1.PC = SRCT1.B AND SRCT1.B = 10;

Result:

  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.