15.00 - EXPLAIN for Query Against Single Table - Teradata Database

Teradata Database Design

Teradata Database
User Guide

EXPLAIN for Query Against Single Table

The following EXPLAIN report shows how the join index might be used in a query of a single table.

    EXPLAIN SELECT l_orderkey, l_partkey, l_quantity, l_extendedprice
            FROM lineitem
            WHERE l_partkey = 1001;
     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 by way of an all-rows scan with a condition
        of ("LOUISB.order_join_line.l_partkey = 1001") 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 100 rows.  The estimated time for this step
        is 59.60 seconds.
     4) Finally, we send out an END TRANSACTION step to all AMPs involved
        in processing the request.