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

結合を最適化しないと、大さなテーブルの結合列の集まり(color、size、およびoptions)に作成されたインデックスのタイプに関係なく、次の結合計画が生成されます。

完了時間

2つのプロダクト ジョインと1つのマージ結合を含む実行時間の見積もりの合計は、1時間52分であることに注意してください。

最適化しない結合計画のEXPLAIN出力

大きなテーブルの結合列の集まり(color、size、およびoptions)に作成したインデックスのタイプに関係なく、スター結合の結合の最適化を適用せずに生成されるEXPLAIN出力の一部を次に示します。

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