15.00 - EXPLAIN for Query With More Complicated Predicate - Teradata Database

Teradata Database Design

Teradata Database
User Guide

EXPLAIN for Query With More Complicated Predicate

The following EXPLAIN report shows how the join index might be used in a query when an additional search condition is added on the join indexed rows.

     EXPLAIN SELECT o_orderdate, o_custkey, l_partkey, 
                    l_quantity, l_extendedprice
             FROM lineitem, orders
             WHERE l_orderkey = o_orderkey 
             AND o_orderdate > '2003-11-01';
     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 LOUISB.order_join_line for read.
     3) We do an all-AMPs RETRIEVE step from join index table
        LOUISB.order_join_line with a range constraint of (
        "LOUISB.order_join_line.Field_1026 > 971101") with a residual
        condition of ("(NOT (LOUISB.order_join_line.o_orderdate IS NULL ))
        AND (LOUISB.order_join_line.Field_1026 > 971101)") into Spool 1,
        which is built locally on the AMPs.  The input table will not be
        cached in memory, but it is eligible for synchronized scanning.
        The size of Spool 1 is estimated to be 1000 rows.  The estimated time
        for this step is 0.32 seconds.
     4) Finally, we send out an END TRANSACTION step to all AMPs involved
        in processing the request.