15.10 - Reasonable Unindexed Join Without Join Optimization - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

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

The total estimated completion time is 3 hours 28 minutes.

The following EXPLAIN output is generated without LT/ST optimization:

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