Join Plan With Star Join Optimization and Fact Table Subquery Join - Advanced SQL Engine - Teradata Database

SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
uqf1592445067244.ditamap
dita:ditavalPath
uqf1592445067244.ditaval
dita:id
B035-1142
lifecycle
previous
Product Category
Teradata Vantage™

Query Used for a Join Plan With Star Join Optimization and Fact Table Subquery Join

The following query is used for this example:

     SELECT …
     WHERE widgets.color=COLOR.code
     AND   widgets.size=SIZE.code
     AND   widgets.options IN (SELECT OPTIONS.code);

Optimized Join Plan

With join optimization, the following join plan is generated when the collection of join columns (color, size, and options) makes up a nonunique secondary index of the large table:

Operation Joined Tables Total Processing Time (seconds)
Spool 4: Product Join duplicated color, direct size                             0.31
Spool 6: Product Join local 4, duplicated options                             4.46
Spool 1: Nested Join duplicated 6, index widgets                           22.73

Completion Time

The total estimated completion time is 27.40 seconds.

The estimated performance improvement factor is 245.

EXPLAIN Output for Optimized Join Plan

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

2) Next, we execute the following steps in parallel.
   a) We do an all-AMPs RETRIEVE step from TEST.Options 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 the sort key in spool field1 eliminating
      duplicate rows. The size of Spool 2 is estimated to be 10 rows.
      The estimated time for this step is 0.19 seconds.
   b) We do an all-AMPs RETRIEVE step from TEST.Color by way of an
      all-rows scan with no residual conditions into Spool 3, which is
      duplicated on all AMPs. The size of Spool 3 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 3 (Last Use) by way of an
      all-rows scan, which is joined to TEST.Size. Spool 3 and
      TEST.Size are joined using a product join. The result goes into
      Spool 4, which is built locally on the AMPs. The size of Spool 4
      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 Spool 2 (Last Use) by way of
      an all-rows scan into Spool 5, which is duplicated on all AMPs.
      The size of Spool 5 is estimated to be 20 rows. The estimated 
      time for this step is 0.27 seconds.
4) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an
   all-rows scan, which is joined to Spool 5 (Last Use). Spool 4 and
   Spool 5 are joined using a product join. The result goes into Spool
   6, which is duplicated on all AMPs. The size of Spool 6 is estimated
   to be 400 rows. The estimated time for this step is 4.19 seconds.
5) We do an all-AMPs JOIN step from Spool 6 (Last Use) by way of an
   all-rows scan, which is joined to TEST.Widgets by way of index # 4.
   Spool 6 and TEST.Widgets are joined using a nested join. 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 22.73 seconds.