結合を最適化した場合には、大さなテーブルの固有セカンダリ インデックスが結合列の集まり(color、size、およびoptions)で構成されていると、次の結合計画が生成されます。
| 操作 | 結合テーブル | 処理時間合計(秒) |
|---|---|---|
| Spool 3: プロダクト ジョイン | コピーcolor、直接options | 0.31 |
| Spool 5: プロダクト ジョイン | コピーsize、直接3 | 1.62 |
| Spool 6:入れ子結合 | ハッシュ5、インデックスwidgets | 2.71 |
| Spool 1:行ID結合 | ハッシュ6、インデックスwidgets | 5.65 |
完了時間
実行時間の見積もりの合計は、10.07 秒です。
性能向上係数の見積もりは、667です。
最適化する結合計画の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 a all-AMP JOIN step from Spool 5 (Last Use) by way of an all-rows scan, which is joined to TEST.Widgets by way of unique index # 4 extracting row ids only. Spool 5 and TEST.Widgets are joined using a nested join. The result goes into Spool 6, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 6 by row hash. The size of Spool 6 is estimated to be 200 rows. The estimated time for this step is 2.71 seconds. 6) 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. Spool 6 and TEST.Widgets are joined using a row id 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 5.65 seconds.