EXPLAIN for Query With Complicated Predicates - Teradata Database

Teradata Database Design

Teradata Database
Release Number
English (United States)
Last Update
Product Category

EXPLAIN for Query With Complicated Predicates

The following EXPLAIN shows how the Optimizer might use the join index for a query that accesses all three of the base tables defined in the index.

    EXPLAIN SELECT l_orderkey, o_orderdate, o_totalprice, l_partkey,  
                   l_quantity, l_extendedprice, l_shipdate
            FROM lineitem, orders, customer
            WHERE l_orderkey = o_orderkey 
            AND   o_custkey = c_custkey 
            AND   c_nationkey = 10;
    *** Help information returned. 16 rows. 
    *** Total elapsed time was 1 second.
     1) First, we lock a distinct LOUISB."pseudo table" for read on a
        Row Hash to prevent global deadlock for LOUISB.cust_order_join_line.
     2) Next, we lock LOUISB.cust_order_join_line for read.
     3) We do an all-AMPs RETRIEVE step from join index table
        LOUISB.cust_order_join_line by way of an all-rows scan with a
        condition of ("LOUISB.cust_order_join_line.c_nationkey = 10") 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 result spool file will not be cached in memory.
        The size of Spool 1 is estimated to be 200 rows.  The estimated time
        for this step is 3 minutes and 57 seconds.
     4) Finally, we send out an END TRANSACTION step to all AMPs involved
        in processing the request.