例: 単純な結合インデックスの作成および使用 - Teradata Database - Teradata Vantage NewSQL Engine - 例: 単一結合インデックスの作成および使用、CREATE JOIN INDEX SQL文。

ここでは、次のテーブルの定義を使用します。

    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);

次の文は、上記のテーブルに結合インデックスを定義します。以下の例は、結合インデックスの結果において最適化ルーチンがさまざまな問合わせを処理する方法を示しています。

    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.

次の問合わせは、新しく作成された結合インデックスのorder_join_lineが最適化ルーチンによって使用される方法を示すために例として提供されています。SELECT文のEXPLAINには、("NOT (order_join_line.o_orderdate IS NULL)")という条件を使用した全行スキャンによる結合インデックス テーブルのorder_join_lineからの取得ステップ)が含まれます。

    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.

次の問合わせは、結合インデックス行に検索条件が追加されたときに、結合インデックスが使用される方法を示すために例として提供されています。SELECT文のEXPLAINには、("order_join_line.Field_1026 > 971101")という残余条件を使用した、("(NOT(order_join_line.o_orderdate IS NULL))AND (order_join_line.Field_1026 > 971101)という範囲制約付きの結合インデックス テーブルのorder_join_lineからの取得ステップ)が含まれます。

    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';

次のクエリーは、結合インデックス行に対して集約が実行されるときに、結合インデックスが使用される方法を示すために例として提供されています。SELECT文のEXPLAINには、("order_join_line.Field_1026 > 971101")という残余条件とグループ化識別子を使用した、"(order_join_line.Field_1026 > 971101) AND (NOT (order_join_line.o_orderdate IS NULL ))")という範囲制約付きの結合インデックス テーブルのorder_join_lineから集約するためのSUMステップ)が含まれます。

    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;

次の問合わせは、結合インデックス行が別の基本テーブルと結合するために使用されるときに、問合わせで結合インデックスが使用される方法を示すために例として提供されています。SELECT文のEXPLAINには、"NOT (order_join_line.o_orderdate IS NULL)"という条件と"NOT order_join_line.o_orderdate IS NULL"行ハッシュで順序付けるSORTを使用した結合インデックス テーブルのorder_join_lineからの取得ステップ)が含まれます。この後に"o_custkey =customer.c_custkey"という結合条件のマージ結合を使用したcustomerからの結合ステップが続きます。

    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;

次の問合わせは、単一テーブルの問合わせで結合インデックスが使用される方法を示すために例として提供されています。SELECT文のEXPLAINには、"order_join_line.l_partkey = 1001"という条件を使用した結合インデックス テーブルのorder_join_lineからの取得ステップ)が含まれます。

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