スター結合最適化を使用する合理的なインデックス結合計画 - Advanced SQL Engine - Teradata Database

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

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
2020年6月
Language
日本語
Last Update
2021-03-30
dita:mapPath
ja-JP/ykx1561500561173.ditamap
dita:ditavalPath
ja-JP/ykx1561500561173.ditaval
dita:id
B035-1142
Product Category
Software
Teradata Vantage

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

操作 結合テーブル 処理時間合計(秒)
Spool 3:プロダクト ジョイン コピーcolor、直接options 0.31
Spool 5: プロダクト ジョイン コピーsize、直接3 1.62
Spool 1:マージ結合 ハッシュ5, 直接widgets 4.09

完了時間

実行時間の見積もりの合計は、5.80秒です。

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

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

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

2) Next, we do an all-AMPs RETRIEVE step from TEST.Color by way of an
   all-rows scan with no residual conditions into Spool 2, which is
   duplicated on all AMPs. The size of Spool 2 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 2 (Last Use) by way of an
      all-rows scan, which is joined to TEST.Options. Spool 2 and
      TEST.Options are joined using a product join. The result goes
      into Spool 3, which is built locally on the AMPs. The size of
      Spool 3 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 TEST.Size by way of an
      all-rows scan with no residual conditions into Spool 4, which is
      duplicated on all AMPs. The size of Spool 4 is estimated to be 20
      rows. The estimated time for this step is 0.24 seconds.
4) We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of an
   all-rows scan, which is joined to Spool 4 (Last Use). Spool 3 and
   Spool 4 are joined using a product join. The result goes into
   Spool 5, which is redistributed by hash code to all AMPs. Then we do
   a SORT to order Spool 5 by row hash. The size of Spool 5 is
   estimated to be 200 rows. The estimated time for this step is
   1.38 seconds.
5) We do an all-AMPs JOIN step from TEST.Widgets by way of an all-rows
   scan with no residual conditions, which is joined to Spool 5 (Last
   Use). TEST.Widgets and Spool 5 are joined using a merge join, with a
   join condition of (“(TEST.Widgets.size = Spool_5.code) AND
   ((TEST.Widgets.color = Spool_5.code) AND (TEST.Widgets.options =
   Spool_5.code ))“). 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 4.09 seconds.