15.10 - Join Plan With Nested Join - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

Using nested joins, the Optimizer generates the following join plan:

 

Operation

Joined Tables

Total Processing Time

Spool 3:nested join

(Hashed table_1, index table_2)

0.24 seconds

Spool 1:rowID join

(Hashed spool_3, direct table_2)

0.22 seconds

The total estimated completion time is 0.46 seconds.

The estimated performance improvement factor is 9782.

The following EXPLAIN output is generated:

Explanation
--------------------------------------------------------------------
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
   redistributed by hash code to all AMPs. Then we do a SORT to order
   Spool 2 by row hash. The size of Spool 2 is estimated to be 2 rows.
   The estimated time for this step is 0.06 seconds.
3) We do a all-AMP JOIN step from Spool 2 (Last Use) by way of an
   all-rows scan, which is joined to test.tab2 by way of unique
   index #4 "test.tab2.y2 = test.tab1.y1" extracting row ids only.
   Spool 2 and test.tab2 are joined using a nested join. The result
   goes into Spool 3, which is redistributed by hash code to all AMPs.
   Then we do a SORT to order Spool 3 by row hash. The size of Spool 3
   is estimated to be 2 rows. The estimated time for this step is 0.18
   seconds.
4) We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of an
   all-rows scan, which is joined to test.tab2. Spool 3 and test.tab2
   are joined using a row id join. 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 0.22 seconds.
5) 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 0.46 seconds.