17.10 - 例: SLPPI結合インデックスおよびパフォーマンス - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLデータ定義言語 構文規則および例

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
2021年7月
Content Type
プログラミング リファレンス
Publication ID
B035-1144-171K-JPN
Language
日本語 (日本)

次の例では、非圧縮PPI結合インデックスが問合わせパフォーマンスに役立つしくみを示しています。

物理データ モデルで定義された次のファクト テーブルおよびディメンション テーブルを持つスター スキーマがあると想定します。

  • sale_date、store_id、prod_id、およびamountという列があるsalesという名前のファクト テーブル。
  • 3つのディメンション テーブルは次のとおりです。
    • dayofmth、mth、およびyrという列と、yrとmthで定義されたプライマリ インデックスを持つcalendar。
    • prod_idとprod_categoryという列を持つproduct。
    • store_id、area、division、およびbusiness_unitという列を持つorg。

次のsales_summaryという名前の結合インデックスのような毎日のサマリー データを持つ集約結合インデックスを作成し、アドホックな問合わせに応答します。

     CREATE JOIN INDEX sales_summary AS
       SELECT sale_date, prod_category, division, 
              SUM(amount) AS daily_sales
       FROM calendar AS c, product AS p, org AS o, sales AS s
       WHERE c.dayofmth = s.sale_date
       AND   p.prod_id = s.prod_id
       AND   o.store_id = s.store_id
       AND   s.sale_date BETWEEN DATE '1991-01-01' AND DATE '2006-12-31'
       GROUP BY sale_date, prod_category, division
     PRIMARY INDEX(sale_date, prod_category, division)
     PARTITION BY RANGE_N(sale_date BETWEEN DATE '1991-01-01' 
                                    AND     DATE '2006-12-31' 
                                    EACH INTERVAL '1' MONTH);

広範囲の問合わせでは、特に、ディメンション テーブルのサブセットでの問合わせをカバーするために広範囲な結合インデックスとして使用する結合インデックスを使用可能にするファクト テーブルとディメンション テーブルの間で定義された、外部キーとプライマリ キーの関係がある場合に、この結合インデックスを活用できます。

最適化ルーチンが、sale_date列に関する等価性または範囲制約を持つ問合わせに応えるときに使用すれば、この結合インデックスで大幅なパフォーマンス向上を実現できます。システムは、静的行パーティション排除やRowkeyベースのマージ結合などの最適化を、PPI基本テーブルに適用するのと同じ方法でPPI結合インデックスに適用します。

例えば、次の問合わせの実行には、合計192のパーティションのうち12のパーティションにだけアクセスする必要があるので、ディスク読み込みの93.75%を節約することができます。それ以外は同一の、フル テーブル スキャンが必要な非パーティション プライマリ インデックス結合インデックスと比べた場合、経過時間も同じ比率で削減されます。

     SELECT prod_category, SUM(amount) AS daily_sales
     FROM calendar c, product p, sales s
     WHERE c.dayofmth = s.sale_date
     AND   p.prod_id = s.prod_id 
     AND   sale_date BETWEEN DATE '2006-01-01' AND DATE '2006-12-31'
     GROUP BY prod_category;

結合インデックスは問合わせ応答時間を大幅に向上させますが、インデックスが定義されている基本テーブルで挿入、削除、更新が発生したときの、保守のオーバーヘッドも考慮する必要があります。

非圧縮PPI結合インデックスは、PPI関連最適化により保守パフォーマンスが飛躍的に向上することがあります。例えば、結合インデックスのDATE列でのパーティション化は、時間シーケンスに従ってトランザクション データがsalesテーブルに挿入される場合、挿入パフォーマンスも支援します。

結合インデックスを持つ基本テーブルへの行のロードには、Teradata Parallel Transporter UPDATEオペレータ、MultiLoad、およびFastLoadを使用できません。この制限により、次の次善策のいずれかを使用する必要があります。

  • salesへの行のロードにTeradata Parallel Data Pumpユーティリティを使用する
  • ステージング テーブルへの行のロードにFastLoadを使用し、次にエラー ロギングでのINSERT ... SELECTリクエストまたはMERGEリクエストのいずれかを使用して、salesへの行のロードを行なう

挿入された行は、適切なパーティションに対応してデータ ブロック内でクラスタ化されるため、システムが読み取りおよび書き込みする必要があるデータ ブロック数は、挿入される行がすべてのデータ ブロック間に散在する非パーティション プライマリ インデックス結合インデックスと比較すると削減されることになります。

一方で、非パーティション プライマリ インデックスで行なう場合より、PPI結合インデックスの保守の効率が下がる可能性もあります。例えば、結合インデックスのプライマリ インデックスにパーティション列が含まれておらず、DELETEまたはUPDATE文がプライマリ インデックスでの制約を指定している場合、システムは、非パーティション プライマリ インデックスで定義される結合インデックスの高速プライマリ インデックス アクセスとは対照的に、PPI結合インデックスを保守するためにAMPのすべてのパーティションを調査しなければなりません。