16.10 - Join Plan Without Nested Join - Teradata Database

Teradata Database SQL Request and Transaction Processing

Teradata Database
June 2017
Programming Reference
User Guide

About a Join Plan Without Nested Join

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 EXPLAIN output is generated when nested joins are not used:

1) First, we lock test.tab1 for read, and we lock test.tab2 for read.
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 
4) Finally, we send out an END TRANSACTION step to all AMPs involved in
   processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
   statement 1. The total estimated time is 1 hour and 15 minutes.