Without join optimization, the following join plan is generated independently of the type of index created on the collection of join columns (color, size, and options) of the large table:
| Operation | Joined Tables | Total Processing Time (seconds) |
|---|---|---|
| Spool 4: Product Join | duplicated options, direct size | 2.67 |
| Spool 5: Product Join | duplicated color, direct widgets | 6 660.00 This equates to 1 hour, 51 minutes. |
| Spool 1: Merge Join | duplicated 4, local 5 | 7.43 |
Completion Time
The total estimated completion time, including time for two product joins and a merge join, is 1 hour 52 minutes.
EXPLAIN Output for Unoptimized Join Plan
The following shows part of the EXPLAIN output that is generated without star join optimization, independently of the type of index created on the collection of join columns (color, size, and options) of the large table.
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.