CREATE JOIN INDEX Examples | Teradata Vantage - Example: Creating and Using a Simple Join Index - Advanced SQL Engine - Teradata Database

This example set uses the following table definitions.

    CREATE TABLE customer (
          c_custkey    INTEGER,
          c_name       CHARACTER(26),
          c_address    VARCHAR(41),
          c_nationkey  INTEGER,
          c_phone      CHARACTER(16),
          c_acctbal    DECIMAL(13,2),
          c_mktsegment CHARACTER(21),
          c_comment    VARCHAR(127))
    UNIQUE PRIMARY INDEX (c_custkey);
    
    CREATE TABLE orders (
          o_orderkey      INTEGER NOT NULL,
          o_custkey       INTEGER,
          o_orderstatus   CHARACTER(1),
          o_totalprice    DECIMAL(13,2) NOT NULL,
          o_orderdate     DATE FORMAT 'yyyy-mm-dd' NOT NULL,
          o_orderpriority CHARACTER(21),
          o_clerk         CHARACTER(16),
          o_shippriority  INTEGER,
          o_commment      VARCHAR(79))
    UNIQUE PRIMARY INDEX (o_orderkey);
    
    CREATE TABLE lineitem (
          l_orderkey      INTEGER NOT NULL,
          l_partkey       INTEGER NOT NULL,
          l_suppkey       INTEGER,
          l_linenumber    INTEGER,
          l_quantity      INTEGER NOT NULL,
          l_extendedprice DECIMAL(13,2) NOT NULL,
          l_discount      DECIMAL(13,2),
          l_tax           DECIMAL(13,2),
          l_returnflag    CHARACTER(1),
          l_linestatus    CHARACTER(1),
          l_shipdate      DATE FORMAT 'yyyy-mm-dd',
          l_commitdate    DATE FORMAT 'yyyy-mm-dd',
          l_receiptdate   DATE FORMAT 'yyyy-mm-dd',
          l_shipinstruct  VARCHAR(25),
          l_shipmode      VARCHAR(10),
          l_comment       VARCHAR(44))
    PRIMARY INDEX (l_orderkey);

The following statement defines a join index on these tables. Subsequent examples demonstrate the effect of this join index on how the Optimizer processes various queries.

    CREATE JOIN INDEX order_join_line AS 
    SELECT (l_orderkey, o_orderdate, o_custkey, o_totalprice),
           (l_partkey, l_quantity, l_extendedprice, l_shipdate)
    FROM lineitem 
    LEFT JOIN orders ON l_orderkey = o_orderkey
    ORDER BY o_orderdate
    PRIMARY INDEX (l_orderkey);
    
     *** Index has been created. 
     *** Total elapsed time was 15 seconds.

The following query is provided as an example to show how the newly created join index, order_join_line, can be used by the Optimizer. An EXPLAIN of the SELECT statement includes a RETRIEVE step from join index table order_join_line by way of an all-rows scan with a condition of ("NOT (order_join_line.o_orderdate IS NULL)").

    EXPLAIN SELECT o_orderdate, o_custkey, l_partkey, l_quantity,
    l_extendedprice
    FROM lineitem, orders
    WHERE l_orderkey = o_orderkey;

    Explanation
    ---------------------------------------------------------------------------
      1) First, we lock ORDER_JOIN_LINE for read on a reserved RowHash
         to prevent global deadlock.
      2) Next, we lock ORDER_JOIN_LINE for read.
      3) We do an all-AMPs RETRIEVE step from tom.ORDER_JOIN_LINE by way of
         an all-rows scan with a condition of ("NOT
         (ORDER_JOIN_LINE.o_orderdate IS NULL)") into Spool 1
         (group_amps), which is built locally on the AMPs.  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.
      -> The contents of Spool 1 are sent back to the user as the result of
         statement 1.

The following query is provided as an example to show how the join index might be used when an additional search condition is added on the join indexed rows. An EXPLAIN of the SELECT statement includes a RETRIEVE step from join index table      order_join_line with a range constraint of (      "order_join_line.Field_1026 > 971101") with a residual      condition of ("(NOT (order_join_line.o_orderdate IS NULL ))      AND (order_join_line.Field_1026 > 971101)."

    SELECT o_orderdate, o_custkey, l_partkey, l_quantity,
                   l_extendedprice
    FROM lineitem, orders
    WHERE l_orderkey = o_orderkey 
    AND o_orderdate > '1997-11-01';

The following query is provided as an example to show how the join index might be used when aggregation is performed on the join indexed rows. An EXPLAIN of the SELECT statement includes a SUM step to aggregate from join index table order_join_line with a range constraint of ("order_join_line.Field_1026 > 971101") with a residual condition of "(order_join_line.Field_1026 > 971101) AND (NOT (order_join_line.o_orderdate IS NULL ))") and the grouping identifier.

    SELECT l_partkey, avg(l_quantity), AVG(l_extendedprice)
    FROM lineitem, orders
    WHERE l_orderkey = o_orderkey 
    AND o_orderdate > '1997-11-01'
    GROUP BY l_partkey;

The following query is provided as an example to show how the join index might be used in a query when join indexed rows are used to join with another base table. An EXPLAIN of the SELECT statement includes a RETRIEVE step from join index table order_join_line with a condition of ("NOT (order_join_line.o_orderdate IS NULL)") and a SORT to order by row hash. This is followed by a JOIN step from customer using a merge join, with a join condition of ("o_custkey =customer.c_custkey").

    SELECT o_orderdate, c_name, c_phone, l_partkey, l_quantity,
                   l_extendedprice
    FROM lineitem, orders, customer
    WHERE l_orderkey = o_orderkey 
    AND o_custkey = c_custkey;

The following query is provided as an example to show how the join index might be used in a query of a single table. An EXPLAIN of the SELECT statement includes a RETRIEVE step from join index table order_join_line with a condition of ("order_join_line.l_partkey = 1001").

    SELECT l_orderkey, l_partkey, l_quantity, l_extendedprice
    FROM lineitem
    WHERE l_partkey = 1001;