Example: Delete Rows with an Equality Constraint on a 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 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;

Result:

  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.