17.10 - Reasonable Indexed Join Plan With Star 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 join optimization, the following join plan is generated when the collection of join columns (color, size, and options) makes up the primary index of the large table:

Operation Joined Tables Total Processing Time (seconds)
Spool 3: Product Join duplicated color, direct options                             0.31
Spool 5: Product Join duplicated size, direct 3                             1.62
Spool 1: Merge Join hashed 5, direct widgets                             4.09

Completion Time

Total estimated execution time is 5.80 seconds.

The estimated performance improvement factor is 1158.

EXPLAIN Output for Optimized Join Plan

Part of the EXPLAIN output for this optimized join plan is shown below.

2) Next, we do an all-AMPs RETRIEVE step from TEST.Color 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 execute the following steps in parallel.
   a) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an
      all-rows scan, which is joined to TEST.Options. Spool 2 and
      TEST.Options are joined using a product join. The result goes
      into Spool 3, which is built locally on the AMPs. The size of
      Spool 3 is estimated to be 20 rows. The estimated time for this
      step is 0.23 seconds.
   b) We do an all-AMPs RETRIEVE step from TEST.Size by way of an
      all-rows scan with no residual conditions into Spool 4, which is
      duplicated on all AMPs. The size of Spool 4 is estimated to be 20
      rows. The estimated time for this step is 0.24 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 Spool 4 (Last Use). Spool 3 and
   Spool 4 are joined using a product join. The result goes into
   Spool 5, which is redistributed by hash code to all AMPs. Then we do
   a SORT to order Spool 5 by row hash. The size of Spool 5 is
   estimated to be 200 rows. The estimated time for this step is
   1.38 seconds.
5) We do an all-AMPs JOIN step from TEST.Widgets by way of an all-rows
   scan with no residual conditions, which is joined to Spool 5 (Last
   Use). TEST.Widgets and Spool 5 are joined using a merge join, with a
   join condition of (“(TEST.Widgets.size = Spool_5.code) AND
   ((TEST.Widgets.color = Spool_5.code) AND (TEST.Widgets.options =
   Spool_5.code ))“). The result goes into Spool 1, which is built
   locally on the AMPs. The size of Spool 1 is estimated to be 200 
   rows. The estimated time for this step is 4.09 seconds.