15.00 - EXPLAIN for Query With Base Table-Join Index Table Join - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

EXPLAIN for Query With Base Table-Join Index Table Join

The following EXPLAIN shows how the join index might be used in a query when join indexed rows are used to join with another base table.

     EXPLAIN SELECT o_orderdate, c_name, c_phone, l_partkey, l_quantity,
                    l_extendedprice
             FROM lineitem, orders, customer
             WHERE l_orderkey = o_orderkey 
             AND   o_custkey = c_custkey;
 
   Explanation
   --------------------------------------------------------------------------
     1) First, we lock a distinct LOUISB."pseudo table" for read on a
        Row Hash to prevent global deadlock for LOUISB.order_join_line.
     2) Next, we lock a distinct LOUISB."pseudo table" for read on a
        Row Hash to prevent global deadlock for LOUISB.customer.
     3) We lock LOUISB.order_join_line for read, and we lock LOUISB.customer
        for read.
     4) We do an all-AMPs RETRIEVE step from join index table
        LOUISB.order_join_line by way of an all-rows scan with a condition
        of ("NOT (LOUISB.order_join_line.o_orderdate IS NULL)") into Spool 2,
        which is redistributed by hash code to all AMPs.  Then we do a
        SORT to order Spool 2 by row hash.  The size of Spool 2 is
        estimated to be 1,000,000 rows.  The estimated time for this step
        is 1 minute and 53 seconds.
     5) We do an all-AMPs JOIN step from LOUISB.customer by way of a
        Row Hash match scan with no residual conditions, which is joined to
        Spool 2 (Last Use).  LOUISB.customer and Spool 2 are joined using
        a merge join, with a join condition of ("Spool_2.o_custkey =
        LOUISB.customer.c_custkey").  The result goes into Spool 1, which
        is built locally on the AMPs.  The size of Spool 1 is estimated to
        be 1,000,000 rows.  The estimated time for this step is 32.14
        seconds.
     6) Finally, we send out an END TRANSACTION step to all AMPs involved
        in processing the request.