例: バルク挿入 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLデータ操作言語

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
2021年7月
Language
日本語
Last Update
2021-09-23
dita:mapPath
ja-JP/vjt1596846980081.ditamap
dita:ditavalPath
ja-JP/wrg1590696035526.ditaval
dita:id
B035-1146
Product Category
Software
Teradata Vantage

このINSERT操作は、バルク挿入を実行します。パーティション レベル ロックにより、解析プログラムは静的パーティション排除リストを条件"PC = 4"(位置的一致slppit1.pc = srct1.b = 4)に基づいて生成し、このリストを使用してPartitionRangeロックを配置します。

この例のテーブル定義は次のとおりです。

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

INSERT操作のEXPLAINは、条件PC=4をWHERE B=4として示します。

  Explain INSERT INTO HLSDS.SLPPIT1 SELECT A, B, C FROM HLSDS.SRCT1
  WHERE B = 4;
  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.