スター結合の最適化が適用されないと、次の結合計画が生成されます。
| 操作 | 結合テーブル | 処理時間合計(秒) |
|---|---|---|
| Spool 3:マージ結合 | コピーoptions、直接size | 0.46 |
| Spool 4:プロダクト ジョイン | コピーcolor、直接widgets | 12 491.00 これは、3時間28分11秒と同等です。 |
| Spool 1:マージ結合 | コピー3、ローカル4 | 21.05 |
完了時間
実行時間の見積もりの合計は、3時間28分です。
最適化しない結合計画のEXPLAIN出力
LT/ST最適化なしで生成された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 6 rows. The estimated time for this step is 0.11 seconds. b) We do an all-AMPs JOIN step from TEST.Options by way of an all-rows scan with no residual conditions, which is joined to TEST.Size. TEST.Options and TEST.Size are joined using a merge join, with a join condition of (“TEST.Widgets.description = TEST.Options.description“). The result goes into Spool 3, which is duplicated on all AMPs. Then we do a SORT to order Spool 3 by row hash. The size of Spool 3 is estimated to be 20 rows. The estimated time for this step is 0.46 seconds. 3) 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 4, which is built locally on the AMPs. Then we do a SORT to order Spool 4 by row hash. The size of Spool 4 is estimated to be 500,000 rows. The estimated time for this step is 3 hours and 28 minutes. 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 merge join, with a join condition of (“(Spool_4.options = Spool_3.code) AND (Spool_4.size = Spool_3.code)“). The result goes into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated to be 555 rows. The estimated time for this step is 21.05 seconds.