次の例では、非圧縮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のすべてのパーティションを調査しなければなりません。