17.00 - 17.05 - 行パーティションPI結合インデックスに関する指針 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLデータ定義言語 詳細トピック

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Release Date
2020年6月
Content Type
プログラミング リファレンス
Publication ID
B035-1184-170K-JPN
Language
日本語 (日本)

非圧縮結合インデックスに対する行パーティション プライマリ インデックスを作成できますが、結合インデックスにORDER BYオプションを含めることができないなど、いくつかの制約があります。行パーティションPI結合インデックスは、結合インデックスと行パーティションPIの利点を利用することでクエリーのパフォーマンスを改善できます。次の2つのシナリオは、行パーティションPI付き非圧縮結合インデックスが役立つ可能性を示しています。

シナリオA

物理データ モデルの次のテーブルを持つスター スキーマがあると想定します。

ディメンション モデル テーブルの種類 対応する物理テーブルおよび列セット
ファクト sales
  • sale_date
  • store_id
  • prod_id
  • amount
ディメンション
  • calendar

    dayofmth

    mth

  • yr
PRIMARY INDEX(yr, mth)
  • product

    prod_id

  • prod_category
  • org

    store_id

    area

  • division
  • business_unit

毎日のサマリー データで単一レベル行パーティション集約結合インデックスを作成し、次のようなデータベース スキーマへのアドホックのクエリーに応えます。

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
  GROUP BY sale_date, prod_category, division
PRIMARY INDEX(sale_date, prod_category, division)
PARTITION BY RANGE_N(sale_date BETWEEN DATE '1990-01-01'
                               AND     DATE '2005-12-31'
                               EACH INTERVAL '1' MONTH);

この結合インデックスは、さまざまなクエリーで活用できます。特に、ファクト テーブルとディメンション テーブルの間に外部キーとプライマリ キーのリレーションシップが存在し、それによって結合インデックスを広範に使用してディメンション テーブルのサブセットに対するクエリーをカバーできる場合は有用です。結合インデックスによるクエリーのカバー結合インデックス定義がクエリーよりも多くのテーブルを参照する際の、結合インデックスによるカバーの制限、および追加のテーブルを持つ結合インデックスがクエリーをカバーできるかどうかに関するルールを参照してください。ブロード結合インデックスについては、<Teradata Vantage™ - データベースの設計、B035-1094>および<Teradata Vantage™- SQLリクエストおよびトランザクション処理、B035-1142>を参照してください。

このPPI結合インデックスをsale_date列に対する等価制約または範囲制約を持つクエリーへの応答に使用すると、大幅なパフォーマンス向上を実現できます。

行パーティション排除などの行パーティション基本テーブルの最適化は、基本テーブルに適用されるのと同じ方法で結合インデックスに適用されます。

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

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

SELECT文のEXPLAINには、SALES_SUMMARYの12のパーティションから集約するためのSUMステップが含まれています。

シナリオB

シナリオAで使ったものと同じスキーマで、シングルレベルの行パーティションPI単一テーブル結合インデックスをファクト テーブルに作成します。ファクト テーブルには、1つ以上のディメンション テーブルの外部キーが含まれているため、対応するディメンション テーブル セットへの結合やより高い集約レベルへのロールアップに使用することができます。次に、単一テーブル結合インデックスの例を示します。

CREATE JOIN INDEX sji AS
  SELECT sale_date, prod_id, SUM(amount) AS sales_amount
  FROM Sales
  GROUP BY sale_date, prod_id
PRIMARY INDEX(sale_date, prod_id)
PARTITION BY RANGE_N(sale_date BETWEEN DATE '1990-01-01'
                               AND     DATE '2005-12-31'
                               EACH INTERVAL '1' MONTH);

月レベルの売上げ総額に関するクエリーの場合、この結合インデックスは最適化ルーチンによってcalendarテーブルに結合され、ロールアップされます。

SELECT yr, mth, SUM(amount)
FROM sales, Calendar
WHERE sale_date=dayofmth
AND sale_date BETWEEN DATE '2005-01-01' AND DATE '2005-12-31'
GROUP BY yr, mth;

SELECT文のEXPLAINには、結合インデックスsjiの12のパーティションからのRETRIEVEステップが含まれています。行パーティション排除がsjiに適用されて、アクセスされる行パーティションの数が192の行パーティションのうち12に減り、フル テーブル スキャンが必要な非パーティション単一テーブル結合インデックスに比べて結合がはるかに速くなります。データベースは、動的な行パーティション排除や行キー ベースのマージ結合など、その他の行パーティション関連の結合最適化を、行パーティション基本テーブルに適用されるように、行パーティション結合インデックスに適用できます。

結合インデックスはクエリー応答時間を大幅に向上させますが、インデックスが定義されている基本テーブルで挿入、削除、更新が行なわれたときの、保守のオーバーヘッドを考慮する必要があります。行パーティション結合インデックスは、行パーティション関連の最適化により、保守パフォーマンスが飛躍的に向上することがあります。例えば、シナリオAおよびBで定義されている結合インデックスについて、データ保守文の行パーティション列に等価または範囲制約がある場合、データベースは行パーティション排除を適用できます。

例:

DELETE sales
        WHERE sale_date BETWEEN DATE '1999-01-01'
                        AND     DATE '1999-12-31';
DELETE文のEXPLAINには、以下のステップが含まれています。
  • SALES_SUMMARYの12のパーティションからのDELETE。("(SALES_SUMMARY.sale_date ≥ DATE '1999-01-01') AND (SALES_SUMMARY.sale_date ≤ DATE '1999-12-31')")の条件付き
  • SJIの12のパーティションからのDELETE。("(SJI.sale_date ≥ DATE '1999-01-01') AND (SJI.sale_date ≤ DATE '1999-12-31')")の条件付き

結合インデックスのDATE列でのパーティション化は、時間シーケンスに従ってトランザクション データがsalesに挿入される場合、挿入パフォーマンスを支援します。結合インデックスを使用する実テーブルではTeradata MultiLoadおよびTeradata FastLoadがサポートされないため、salesへのデータのロードには他のバッチ ロード方式を使用する必要があります。例えば、Teradata Parallel Data Pumpを使用するか、またはステージング テーブルへのTeradata FastLoadの後にエラー ロギングを指定してINSERT…SELECTバッチ リクエストまたはMERGEバッチ リクエストを使用します。

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

結合インデックスを削除してMultiLoadまたはFastLoadバッチ ロードを使用できるようにする場合、そのインデックスにアクセスするすべてのリクエストが処理を完了するまでは、その操作を行なうことはできません。リクエストがアクセスする結合インデックスにREADロックが掛けられ、ロックされたインデックスに対するDROP JOIN INDEXリクエストの処理は、データベースにより、READロックがすべて解除されるまで先送りされます。

行パーティション結合インデックスの保守は、非パーティションという点を除いてまったく同一の非パーティション結合インデックスよりも効率性が落ちる場合があります。例えば、行パーティション結合インデックスのプライマリ インデックスにパーティション列セットが含まれておらず、DELETEまたはUPDATE文がプライマリ インデックスに制約を指定している場合、行パーティションPI結合インデックスの保守は、非パーティション結合インデックスの高速プライマリ インデックスアクセスとは対照的に、AMP内のすべてのパーティションを調査する必要があります。