16.10 - Reasonable Unindexed Join With Join Optimization - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Programming Reference
User Guide
featnum
B035-1142-161K

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

The following EXPLAIN output is generated:

Explanation
--------------------------------------------------------------------
1) First, we lock TEST.Options for read, we lock TEST.Size for read, we
   lock TEST.Color for read, and we lock TEST.Widgets for read.
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.
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 2 hours and 9 minutes.