Example: Delete Rows with an Equality Constraint on a Partitioning Column - Teradata Database - Teradata Vantage NewSQL Engine

SQL Data Manipulation Language

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-03
dita:mapPath
fbo1512081269404.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

This is an example of deleting rows with an equality constraint on partitioning column. With partition level locking, an all-AMPs partition range lock is placed. The partition range has a single partition pair.

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

An EXPLAIN of the DELETE statement shows the partition lock:

  Explain DELETE HLSDS.SLPPIT1 WHERE PC = 10;
  1) First, we lock HLSDS.SLPPIT1 for write on a reserved rowHash in a
      single partition to prevent global deadlock.
  2) Next, we lock HLSDS.SLPPIT1 for write on a single partition.
  3) We do an all-AMPs DELETE of a single partition from HLSDS.SLPPIT1
      with a condition of ("HLSDS.SLPPIT1.PC = 10"). The size is
      estimated with no confidence to be 1 row.  The estimated time for
      this step is 0.03 seconds.
  4) Finally, we send out an END TRANSACTION step to all AMPs involved in
      processing the request.