17.00 - 17.05 - 単一DELETE操作の実行時に結合インデックスを保守するための一般的な方法 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLリクエストおよびトランザクション処理

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
2020年6月
Content Type
プログラミング リファレンス
ユーザー ガイド
Publication ID
B035-1142-170K-JPN
Language
日本語 (日本)

単一DELETE操作の実行時に結合インデックスを保守するための一般的な方法の例

次は、単一DELETEリクエストの実行時に結合インデックスを保守するための一般的な方法の例です。

EXPLAIN出力では、次の項目に注意してください。

ステップ 実行内容
5 結合インデックス行の影響を受ける部分を再生成する。
6.1 結合インデックスから対応する行を削除する。
8 新しい不一致の外部結合行を結合インデックスに挿入する。
     EXPLAIN
     DELETE FROM ordertbl
     WHERE o_custkey = 1001;

EXPLAIN出力の一部を次に示します。

  5) We do an all-AMPs JOIN step in TD_MAP1 from DB1.lineitem by way
     of a RowHash match scan with no residual conditions, which is
     joined to 84 partitions of DB1.Ordertbl by way of a RowHash match
     scan with a condition of ("DB1.ordertbl.o_custkey = 1001").
     DB1.lineitem and DB1.Ordertbl are joined using a sliding-window
     merge join, with a join condition of ("DB1.lineitem.l_orderkey =
     DB1.Ordertbl.o_orderkey").  The result goes into Spool 1
     (all_amps), which is built locally on the AMPs.  Then we do a SORT
     to order Spool 1 by the hash code of (DB1.Ordertbl.o_orderdate).
     The size of Spool 1 is estimated with low confidence to be 2 rows
     (106 bytes).  The estimated time for this step is 0.17 seconds.
  6) We execute the following steps in parallel.
       1) We do an all-AMPs MERGE DELETE to DB1.ORDERJOINLINE from
          Spool 1.  The size is estimated with low confidence to be 2
          rows.  The estimated time for this step is 4.60 seconds.
       2) We do an all-AMPs DELETE step in TD_MAP1 from 84 partitions
          of DB1.ordertbl with a condition of (
          "DB1.ordertbl.o_custkey = 1001").  The size is estimated
          with low confidence to be 1 row.  The estimated time for this
          step is 0.05 seconds.
  7) We do an all-AMPs RETRIEVE step in TD_Map1 from Spool 1 (Last Use)
     by way of an all-rows scan into Spool 2 (all_amps), which is
     redistributed by the hash code of (DB1.lineitem.l_orderkey) to
     all AMPs in TD_Map1.  Then we do a SORT to order Spool 2 by join
     index.  The size of Spool 2 is estimated with low confidence to be
     2 rows (106 bytes).  The estimated time for this step is 0.16
     seconds.
  8) We do an all-AMPs MERGE step in TD_MAP1 into DB1.ORDERJOINLINE
     from Spool 2 (Last Use).  The size is estimated with low
     confidence to be 2 rows.  The estimated time for this step is 1.34
     seconds.
  9) We spoil the parser's dictionary cache for the table.
 10) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> No rows are returned to the user as the result of statement 1.