Without using a nested join, the Optimizer generates the following join plan:
| Operation | Joined Tables | Total Processing Time |
|---|---|---|
| Spool 1:product join | duped table_1, direct table_2 |
1 hour 15 minutes |
The total estimated completion time is 1 hour 15 minutes.
EXPLAIN Output for Unoptimized Join Plan
The following partial EXPLAIN output is generated when nested joins are not used:
2) Next, we do an all-AMPs RETRIEVE step from test.tab1 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 do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an all-rows scan, which is joined to test.tab2. Spool 2 and test.tab2 are joined using a product join, with a join condition of (“Spool_2.y1 = test.tab2.y2"). The result goes into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated to be 2 rows. The estimated time for this step is 1 hour and 15 minutes.