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

次の例では、以下のcustomer表とorders表に基づいて単純な行圧縮結合インデックスを作成します。

     CREATE TABLE customer (
       c_custkey    INTEGER,
       c_name       CHARACTER(26) not null,
       c_address    VARCHAR(41),
       c_nationkey  INTEGER,
       c_phone      CHARACTER(16),
       c_acctbal    DECIMAL(13,2),
       c_mktsegment CHARACTER(21),
       c_comment    VARCHAR(127))
     PRIMARY INDEX( c_custkey );
     CREATE TABLE orders (
       o_orderkey      INTEGER,
       o_date          DATE FORMAT 'yyyy-mm-dd',
       o_status        CHARACTER(1),       
       o_custkey       INTEGER,
       o_totalprice    DECIMAL(13,2),
       o_orderpriority CHARACTER(21),
       o_clerk         CHARACTER(16),
       o_shippriority  INTEGER,
       o_comment       VARCHAR(79))
     UNIQUE PRIMARY INDEX(o_orderkey);

次の結合問合わせをcustomerとordersに対して頻繁に実行するとします。

     SELECT o_custkey, c_name, o_status, o_date, o_comment
     FROM orders, customer 
     WHERE o_custkey = c_custkey;

結合インデックスを使用しない場合、この問合わせの一般的な実行計画は、orders表をスプール ファイルに再配置し、スプールをo_custkeyでソートしてから、スプール ファイルとcustomer表との間でマージ結合を実行するというステップになります。

ord_cust_idxのような結合インデックスがcustomerとordersに対して定義されていれば、その場合の実行計画は結合インデックスをスキャンするだけです。

結合インデックス定義内の固定列セットはo_custkeyとc_name(オレンジで強調表示された対応する結果行)で、結合インデックス定義内の反復列セットはo_status、o_date、およびo_comment(青で強調表示された対応する結果行)です。

     CREATE JOIN INDEX ord_cust_idx AS
       SELECT (o_custkey,  c_name 
), (
 o_status, o_date, o_comment 
) 
       FROM orders, customer 
       WHERE o_custkey = c_custkey;

この定義によれば、ord_cust_idxは行圧縮の結合インデックスであり、o_custkeyとc_nameが固定列セットで、o_status、o_date、およびo_commentが反復列セットです。

基本テーブルのcustomerとordersに次のデータが含まれているとします。

行圧縮結合インデックスord_cust_idx内の対応する行は、次のとおりです。

固定セットの列がord_cust_idxの最初の列に入っており、反復セットの列がord_cust_idxの2番目の列に入っていることに注意してください。これは、結合インデックス定義に定義されているとおりです。

内部結合によって生成された結合の結果には、一致しない行が保存されないという短所があります。この例の最初の部分で、order(注文)5003に関する情報がord_cust_idxに格納されないため、インデックスを使用して解決できる問合わせのタイプが限られています。結合インデックスをできるだけ多くの問合わせに利用できるようにするには、可能な限り外部結合を使用して結合インデックスを定義するようにします。そうすることで、インデックスの生成に使用したときよりも少ない結合条件でも、結合条件は十分に問合わせを実行できます。

ord_cust_idxの定義を変更して、次のCREATE JOIN INDEXリクエストのような外部結合を使用することにしたとします。

     CREATE JOIN INDEX ord_cust_idx AS
     SELECT (o_custkey, c_name), (o_status, o_date, o_comment)
     FROM orders LEFT OUTER JOIN customer ON o_custkey = c_custkey;

その結果、ord_cust_idxの行は次のようになります。

左外部結合を使用してord_cust_idxを定義し直すことにより、次の問合わせも結合インデックスのみを使用して解決できます。

     SELECT o_status, o_date, o_comment
     FROM orders;