17.00 - 17.05 - スター結合最適化を使用する結合計画およびファクト テーブルのsubquery 結合 - 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
日本語 (日本)

スター結合最適化を使用する結合計画およびファクト テーブルのsubquery結合に使用されるクエリー

この例では、次のクエリーを使用します。

     SELECT …
     WHERE widgets.color=COLOR.code
     AND   widgets.size=SIZE.code
     AND   widgets.options IN (SELECT OPTIONS.code);

最適化される結合計画

結合を最適化した場合には、大さなテーブルの非固有セカンダリ インデックスが結合列の集まり(color、size、およびoptions)で構成されていると、次の結合計画が生成されます。

操作 結合テーブル 処理時間合計(秒)
Spool 4:プロダクト ジョイン コピーcolor、直接size 0.31
Spool 6:積結合 ローカル4、コピーoptions 4.46
Spool 1:入れ子結合 コピー6、インデックスwidgets 22.73

完了時間

実行時間の見積もりは、27.40秒です。

性能向上係数の見積もりは、245です。

最適化する結合計画のEXPLAIN出力

この最適化された結合計画のEXPLAIN出力の一部を次に示します。

2) Next, we execute the following steps in parallel.
   a) We do an all-AMPs RETRIEVE step from TEST.Options by way of
      an all-rows scan with no residual conditions into Spool 2, which
      is redistributed by hash code to all AMPs. Then we do a SORT to
      order Spool 2 by the sort key in spool field1 eliminating
      duplicate rows. The size of Spool 2 is estimated to be 10 rows.
      The estimated time for this step is 0.19 seconds.
   b) We do an all-AMPs RETRIEVE step from TEST.Color by way of an
      all-rows scan with no residual conditions into Spool 3, which is
      duplicated on all AMPs. The size of Spool 3 is estimated to be 4
      rows. The estimated time for this step is 0.08 seconds.
3) We execute the following steps in parallel.
   a) We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of an
      all-rows scan, which is joined to TEST.Size. Spool 3 and
      TEST.Size are joined using a product join. The result goes into
      Spool 4, which is built locally on the AMPs. The size of Spool 4
      is estimated to be 20 rows. The estimated time for this step is
      0.23 seconds.
   b) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of
      an all-rows scan into Spool 5, which is duplicated on all AMPs.
      The size of Spool 5 is estimated to be 20 rows. The estimated 
      time for this step is 0.27 seconds.
4) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an
   all-rows scan, which is joined to Spool 5 (Last Use). Spool 4 and
   Spool 5 are joined using a product join. The result goes into Spool
   6, which is duplicated on all AMPs. The size of Spool 6 is estimated
   to be 400 rows. The estimated time for this step is 4.19 seconds.
5) We do an all-AMPs JOIN step from Spool 6 (Last Use) by way of an
   all-rows scan, which is joined to TEST.Widgets by way of index # 4.
   Spool 6 and TEST.Widgets are joined using a nested join. The result
   goes into Spool 1, which is built locally on the AMPs. The size of
   Spool 1 is estimated to be 200 rows. The estimated time for this
   step is 22.73 seconds.