Example: Updating a Nonpartitioned Column with an Equality Constraint - 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 example shows an update of a nonpartitioned column with an equality constraint on the partitioning column. With partition level locking, an all-AMP PartitionRange lock is used. The partition list contains 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 UPDATE statement shows the partition lock:

  Explain UPDATE HLSDS.SLPPIT1 SET X = 3 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 UPDATE from a single partition of HLSDS.SLPPIT1
      with a condition of ("HLSDS.SLPPIT1.PC = 10") with a residual
      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.05 seconds.
  4) Finally, we send out an END TRANSACTION step to all AMPs involved
      in processing the request.