15.10 - Reasonable Indexed Join Plan Without Star 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 join optimization, the following join plan is generated independently of the type of index created on the collection of join columns (color, size, and options) of the large table:

 

          Operation

              Joined Tables

     Total Processing Time (seconds)

Spool 4: Product Join

duplicated options, direct size

                               2.67

Spool 5: Product Join

duplicated color, direct widgets

                        6 660.00

This equates to 1 hour, 51 minutes.

Spool 1: Merge Join

duplicated 4, local 5

                               7.43

Note that the total estimated completion time, including time for two product joins and a merge join, is 1 hour 52 minutes.

The following EXPLAIN output is generated without star join optimization, independently of the type of index created on the collection of join columns (color, size, and options) of the large table:

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