15.00 - EXPLAIN for Query With Aggregation - Teradata Database

Teradata Database Design

Teradata Database
User Guide

EXPLAIN for Query With Aggregation

The following EXPLAIN shows how the join index might be used in a query when aggregation is performed on the join indexed rows.

     EXPLAIN SELECT l_partkey, AVG(l_quantity),
             FROM lineitem , orders
             WHERE l_orderkey = o_orderkey 
             AND   o_orderdate > '2003-11-01'
             GROUP BY l_partkey;
     1) First, we lock a distinct LOUISB."pseudo table" for read on a
        RowHash to prevent global deadlock for LOUISB.order_join_line.
     2) Next, we lock LOUISB.order_join_line for read.
     3) We do a SUM step to aggregate 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 ("(LOUISB.order_join_line.Field_1026 > 971101) AND (NOT
        (LOUISB.order_join_line.o_orderdate IS NULL ))"), and the grouping
        identifier in field 1.  Aggregate Intermediate Results are
        computed globally, then placed in Spool 3.  The input table will
        not be cached in memory, but it is eligible for synchronized
     4) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
        an all-rows scan into Spool 1, which is built locally on the AMPs.
        The size of Spool 1 is estimated to be 10 rows.  The estimated time
        for this step is 0.32 seconds.
     5) Finally, we send out an END TRANSACTION step to all AMPs involved
        in processing the request.