17.10 - Reasonable Unindexed Join With Join Optimization - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

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 is shown below.

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.