Example: Bulk Insert - 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 INSERT operation performs a bulk insert. With partition level locking, the parser generates a static partition elimination list based on condition "PC = 4" (that is, positional matching of slppit1.pc = srct1.b = 4) and uses this list to place a PartitionRange lock.

The table definitions for this example are as follows:

CREATE TABLE HLSDS.SLPPIT1 (PI  INT, PC INT, X INT) PRIMARY INDEX (PI)
    PARTITION BY (RANGE_N(PC BETWEEN 1 AND 100 EACH 10));
CREATE TABLE HLSDS.SRCT1 (A INT, B INT, C INT) PRIMARY INDEX (A);

An EXPLAIN of the INSERT operation shows the condition PC=4 as WHERE B=4:

  Explain INSERT INTO HLSDS.SLPPIT1 SELECT A, B, C FROM HLSDS.SRCT1
  WHERE B = 4;

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.
  3) We lock HLSDS.srct1 for read, and we lock HLSDS.slppit1
      for write 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 ("HLSDS.srct1.b = 4") into
      Spool 1(all_amps), which is built locally on the AMPs.  
      Then we do a SORT to partition Spool 1 by rowkey.
      The size of Spool 1 is estimated
      with no confidence to be 1 row (25 bytes).
      The estimated time for this step is 0.03 seconds.
  5) We do an all-AMPs MERGE into HLSDS.slppit1 from Spool 1
     (Last Use). The size is estimated with no confidence 
      to be 1 row.  The estimated time for this step is 0.55 seconds.
  6) Finally, we send out an END TRANSACTION step to all AMPs 
      involved in processing the request.