15.10 - Reasonable Indexed Join Plan With Star Join Optimization and a Fact Table NUSI - 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

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 3: Product Join

duplicated color, direct options

                            0.31

Spool 5: Product Join

duplicated size, direct 3

                            4.43

Spool 1: Nested Join

duplicated 5, index widgets

                          22.73

The total estimated execution time is 27.26 seconds.

The estimated performance improvement factor is 246.

The EXPLAIN output for this optimized join plan is as follows:

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 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 duplicated on all AMPs. The size of Spool 5 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 5 (Last Use) by way of an
   all-rows scan, which is joined to TEST.Widgets by way of index # 4.
   Spool 5 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.
6) 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 27.26 seconds.