17.10 - Defining and Using a Simple Join Index With an n-way Join Result - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Design

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update

The following example shows the creation of a join index defined with an n-way join result and then shows how the Optimizer uses the join index to process a query on the base tables for which it is defined.

Join Index Definition

The following statement defines a join index with a three-table join using both natural and outer joins.

     CREATE JOIN INDEX cust_order_join_line AS
      SELECT (l_orderkey, o_orderdate, c_nationkey, o_totalprice),  
             (l_partkey, l_quantity, l_extendedprice, l_shipdate)
      FROM (lineitem LEFT JOIN orders ON l_orderkey = o_orderkey)
      INNER JOIN customer ON o_custkey = c_custkey
      PRIMARY INDEX (l_orderkey);
     *** Index has been created.
     *** Total elapsed time was 20 seconds.

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 LOUISB.cust_order_join_line for read on a
        reserved Row Hash to prevent a global deadlock.
     2) Next, 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.
     3) Finally, we send out an END TRANSACTION step to all AMPs involved
        in processing the request.