17.10 - 最適化ルーチンでの参照制約の使用例 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLデータ定義言語 詳細トピック

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
2021年7月
Content Type
プログラミング リファレンス
Publication ID
B035-1184-171K-JPN
Language
日本語 (日本)

参照制約が定義されるとき、いかに最適化ルーチンがパフォーマンスの良いクエリー計画を生成するかを理解するには、以下のテーブル定義を考慮してください。 2番目のテーブルと3番目のテーブルとの間の唯一の相違は、tb2は、b内に列aを参照している列tb1上に参照制約を定義していますが、tb3は、その制約を定義していないことです。

     CREATE TABLE tb1 (
       a INTEGER NOT NULL PRIMARY KEY,
       b INTEGER,
       c INTEGER);

     CREATE TABLE tb2 (
       a INTEGER NOT NULL,
       b INTEGER,
       c INTEGER,
     CONSTRAINT ref1
     FOREIGN KEY (b) REFERENCES WITH NO CHECK OPTION tb1(a));

     CREATE TABLE tb3 (
       a INTEGER NOT NULL,
       b INTEGER,
       c INTEGER);

以下のEXPLAINレポートは、参照制約が定義されていないときのテーブルtb1tb3上のクエリー計画を示しています。 特に、ステップ5の結合に注目してください。

     EXPLAIN
     SELECT tb1.a, tb3.a, MAX(tb3.c)
     FROM tb1, tb3
     GROUP BY tb1.a, tb3.a
     WHERE tb1.a = tb3.b
     ORDER BY 1;
    
    Explanation
    -------------------------------------------------------------------
  1) First, we lock MyDB.tb3 for read on a reserved RowHash to prevent
     global deadlock.
  2) Next, we lock MyDB.tb1 for read on a reserved RowHash to prevent
     global deadlock.
  3) We lock MyDB.tb3 for read, and we lock MyDB.tb1 for read.
  4) We do an all-AMPs RETRIEVE step from MyDB.tb3 by way of an all-rows
     scan with a condition of ("NOT (MyDB.tb3.b IS NULL)") into Spool 4
     (all_amps), which is redistributed by the hash code of (MyDB.tb3.b)
     to all AMPs.  Then we do a SORT to order Spool 4 by row hash.  The
     size of Spool 4 is estimated with low confidence to be 4 rows (
     100 bytes).  The estimated time for this step is 0.03 seconds.
  5) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of a
     RowHash match scan, which is joined to MyDB.tb1 by way of a RowHash
     match scan with no residual conditions.  Spool 4 and MyDB.tb1 are
     joined using a merge join, with a join condition of (""MyDB.tb1.a =
     b"").  The result goes into Spool 3 (all_amps), which is built"
     locally on the AMPs.  The size of Spool 3 is estimated with index
     join confidence to be 4 rows (108 bytes).  The estimated time for
     this step is 0.11 seconds.
  6) We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by
     way of an all-rows scan , grouping by field1 ( MyDB.tb1.a
     ,MyDB.tb3.a).  Aggregate Intermediate Results are computed locally,
     then placed in Spool 5.  The size of Spool 5 is estimated with low
     confidence to be 4 rows (164 bytes).  The estimated time for this
     step is 0.08 seconds.
  7) We do an all-AMPs RETRIEVE step from Spool 5 (Last Use) by way of
     an all-rows scan into Spool 1 (group_amps), which is built locally
     on the AMPs.  Then we do a SORT to order Spool 1 by the sort key
     in spool field1 (MyDB.tb1.a).  The size of Spool 1 is estimated
     with low confidence to be 4 rows (132 bytes).  The estimated time
     for this step is 0.08 seconds.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 0.29 seconds.

以下のEXPLAINは、同じクエリーに対するクエリー計画を示していますが、今回は、テーブルtb1およびtb2で、テーブルtb2の列bとテーブルtb1の列aとの間に定義されている参照制約を使用しています。 以前のクエリー計画のステップ5での結合が不必要になり、除去可能であることを最適化ルーチンが認識したことに注目してください。 tb1.atb2.bが明示的に標準参照整合性制約を宣言した場合に同じクエリー計画が生成されます。

     EXPLAIN
     SELECT tb1.a, tb2.a, MAX(tb2.c)
     FROM tb1, tb2
     GROUP BY tb1.a, tb2.a
     WHERE tb1.a = tb2.b
     ORDER BY 1;
    
 Explanation
    -------------------------------------------------------------------
  1) First, we lock MyDB.tb2 for read on a reserved RowHash to prevent
     global deadlock.
  2) Next, we lock MyDB.tb2 for read.
  3) We do an all-AMPs SUM step to aggregate from MyDB.tb2 by way of an
     all-rows scan with a condition of ("NOT (MyDB.tb2.b IS NULL)")
     , grouping by field1 ( MyDB.tb2.b ,MyDB.tb2.a).  Aggregate
     Intermediate Results are computed locally, then placed in Spool 3.
     The size of Spool 3 is estimated with low confidence to be 4 rows
     (164 bytes).  The estimated time for this step is 0.07 seconds.
  4) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
     an all-rows scan into Spool 1 (group_amps), which is built locally
     on the AMPs.  Then we do a SORT to order Spool 1 by the sort key
     in spool field1 (MyDB.tb2.b).  The size of Spool 1 is estimated
     with low confidence to be 4 rows (132 bytes).  The estimated time
     for this step is 0.08 seconds.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 0.15 seconds.