15.00 - EXPLAIN for Query With Simple Predicate - Teradata Database

Teradata Database Design

Teradata Database
User Guide

EXPLAIN for Query With Simple Predicate

The following EXPLAIN report shows how the newly created join index, order_join_line, might be used by the Optimizer.

     EXPLAIN SELECT o_orderdate, o_custkey, l_partkey, l_quantity,                     l_extendedprice
             FROM lineitem, orders
             WHERE l_orderkey = o_orderkey;
  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 ("NOT (LOUISB.order_join_line.o_orderdate IS NULL)") 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 1,000,000 rows.  The estimated time for
     this step is 4 minutes and 27 seconds.
  4) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.