17.10 - Reasonable Unindexed Join Without 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)

Without star join optimization, the following join plan is generated:

Operation Joined Tables Total Processing Time (seconds)
Spool 3: Merge Join direct options, direct size                             0.46
Spool 4: Product Join duplicated color, direct widgets                    12 491.00

This equates to 3 hours, 28 minutes, 11 seconds.

Spool 1: Merge Join duplicated 3, local 4                           21.05

Completion Time

The total estimated completion time is 3 hours 28 minutes.

EXPLAIN Output for Unoptimized Join Plan

Part of the EXPLAIN output, generated without LT/ST optimization, is shown below.

2) Next, we execute the following steps in parallel.
   a) 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 6
      rows. The estimated time for this step is 0.11 seconds.
   b) 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.Widgets.description =
      TEST.Options.description“). 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 20 rows. The
      estimated time for this step is 0.46 seconds.
3) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an
   all-rows scan, which is joined to TEST.Widgets. Spool 2 and
   TEST.Widgets are joined using a product join, with a join condition
   of (“TEST.Widgets.color = Spool_2.code”). The result goes 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
   500,000 rows. The estimated time for this step is 3 hours and 28
   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.options = Spool_3.code) AND (Spool_4.size =
   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 555
   rows. The estimated time for this step is 21.05 seconds.