With star join optimization, the following join plan is generated:
| Operation | Joined Tables | Total Processing Time (seconds) |
|---|---|---|
| Spool 2: Merge Join | Direct options, direct size | 0.44 |
| Spool 3: Product Join | Direct color, duplicated 2 | 1.24 |
| Spool 1: Merge Join | Local widgets, duplicated 3 | 7,761.00 This equates to 2 hours, 9 minutes, 21 seconds. |
Completion Time
The total estimated completion time is 2 hours 9 minutes.
The estimated performance improvement factor is 1.6.
EXPLAIN Output for Optimized Join Plan
Part of the generated EXPLAIN output follows.
2) Next, 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.Size.description = TEST.Options.description“). The result goes into Spool 2, which is duplicated on all AMPs. The size of Spool 2 is estimated to be 20 rows. The estimated time for this step is 0.44 seconds. 3) We execute the following steps in parallel. a) We do an all-AMPs JOIN step from TEST.Color by way of an all-rows scan with no residual conditions, which is joined to Spool 2 (Last Use). TEST.Color and Spool 2 are joined using a product join. 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 60 rows. The estimated time for this step is 1.24 seconds. b) We do an all-AMPs RETRIEVE step from TEST.Widgets by way of an all-rows scan with no residual conditions 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 1,000,000 rows. The estimated time for this step is 2 hours and 9 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.color = Spool_3.code) AND ((Spool_4.size = Spool_3.code AND (Spool_4.options = 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 556 rows. The estimated time for this step is 21.94 seconds.