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

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
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.