TERADATA: Aggregate Join Indexでさらに検索を早くする

Join Indexはクエリーリライト用にあらかじめ結合、集計しておく仕組み

TERADTAに、Join Indexという機能があります。

これはOracleのMaterialized Viewをクエリーリライトに特化したような機能で、あらかじめ結合や集計をした結果を保持しておく仕組みです。

もちろん、元のテーブルが更新されれば即座にJoin Indexも更新されます。（V4ぐらいでは、元のテーブルが更新されてもJoin Indexは更新されなかったような記憶がありますが、最近のバージョンでは問題なく更新されます）

BI/DWHでは、BIツールとTERADATAでシステムを構築した場合、なるべく加工(集計)をしないでデータを持っているほうが、利用者は使いやすいシステムとなります。

が、加工(集計)しないと性能に問題が生じる場合が多く、その時にJoin Indexを利用すると性能が改善できます。

例えば、次のようなデータモデルで、

売上データは集計していなくて件数が2億件あるとした場合、顧客別の分析をしない次のような人には、売上データに顧客コードを除いて集計したJoin Indexが有効です。

・店舗別で過去数年に渡ってデータを見たい

・商品別で過去数年に渡ったデータを見たい

・店舗/商品別で当年のデータを見たい

Join Indexのサンプル

--1.売上テーブルの定義

CREATE SET TABLE Sample1
(
     Datestring     CHAR(8)     CHARACTER SET LATIN CASESPECIFIC
    ,ProductCode    CHAR(10)    CHARACTER SET LATIN CASESPECIFIC
    ,StoreCode      CHAR(10)    CHARACTER SET LATIN CASESPECIFIC
    ,AccoundCode    CHAR(10)    CHARACTER SET LATIN CASESPECIFIC
    ,Amount         DECIMAL(6,0) COMPRESS (0)
)
PRIMARY INDEX
(
     Datestring
    ,ProductCode
    ,StoreCode
)
PARTITION BY (ZEROIFNULL(( CAST((Datestring) AS INTEGER))) MOD 65535 ) + 1
;

--2.顧客コードを除いたJoin Indexを指定する。

CREATE JOIN INDEX JX_Sample1
AS
SELECT
     Datestring
    ,ProductCode
    ,StoreCode
    ,SUM(Amount) AS Amount
FROM
    Sample1
GROUP BY
     Datestring
    ,ProductCode
    ,StoreCode
PRIMARY INDEX (
     Datestring
    ,ProductCode
    ,StoreCode
)
PARTITION BY
(CAST(Datestring AS INTEGER) MOD 65535) + 1
;

Join Indexが有効となるのは、そのJoin Indexを作成した場合、どのくらいデータサイズが小さくなるかがポイントとなります。

Join Indexを作成した結果、さほどデータサイズが小さくならないならそのJoin Indexはあまり効果がありません。

個人的な考え

Join Indexは"あらかじめ結合する"、"あらかじめ集計する"、"あらかじめ結合して集計する"を行うことができます。BIツールを使う場合、常にJoin Indexで指定した結合キーをつかって結合するとは限らないので、"あらかじめ集計する"のが一番使いやすいと思っています。

また、下記のように年月日が決まると確実に年月が定まるような項目はファクトに持たせておき、年月に対応するマスタを用意しておくと、年月別で集計できてJoin Indexのサイズをさらに小さくできます。

Join Indexのサンプル
    

--1.売上テーブルの定義

CREATE SET TABLE Sample2
(
     Datestring     CHAR(8)     CHARACTER SET LATIN CASESPECIFIC
    ,YearMonth      CHAR(6)     CHARACTER SET LATIN CASESPECIFIC
    ,ProductCode    CHAR(10)    CHARACTER SET LATIN CASESPECIFIC
    ,StoreCode      CHAR(10)    CHARACTER SET LATIN CASESPECIFIC
    ,AccoundCode    CHAR(10)    CHARACTER SET LATIN CASESPECIFIC
    ,Amount         DECIMAL(6,0) COMPRESS (0)
)
PRIMARY INDEX
(
     Datestring
    ,ProductCode
    ,StoreCode
)
PARTITION BY (ZEROIFNULL(( CAST((Datestring) AS INTEGER))) MOD 65535 ) + 1
;



--2.顧客コードを除き、さらに年月別別でJoin Indexを指定する。

CREATE JOIN INDEX JX_Sample2
AS
SELECT
     YearMonth
    ,ProductCode
    ,StoreCode
    ,SUM(Amount) AS Amount
FROM
    Sample2
GROUP BY
     YearMonth
    ,ProductCode
    ,StoreCode
PRIMARY INDEX (
     YearMonth
    ,ProductCode
    ,StoreCode
)
PARTITION BY
(CAST(YearMonth AS INTEGER) MOD 65535) + 1
;

 テーブル定義では、年月日にパーティション指定されているけど、Join Indexでは、年月にパーティション指定ができるのでパーティションも使えます。

ただ、普通に集計軸の考えだと、年月日のカレンダの上位に年月のカレンダ(年月)をつなぐのが普通なので、年月のカレンダ(年月)を単独で結合するケースはあまりないと思います。

が、BIツールでショートカット結合が用意されていれば、Join Indexをうまく利用できると思います。（Business Objectsではショートカット結合があります）

Business Objectsユニバースでのショートカット結合

ショートカット結合で、カレンダ(年月)の年月と売上データの計上年月を結合する。

ユニバースでショートカット結合を指定すると、例えば、カレンダ(年月)の年月項目と売上データの売上金額を選択すると、発行されるSQLはテーブルは、カレンダ(年月)と売上データとなる。

そして、結合条件は「カレンダ(年月)．年月＝売上データ．計上年月」となる。

そのため売上データに対して、計上年月で集計したJoin Indexを利用可能となる。

他のBIツールにもショートカット結合と同様の機能があるかはわかりませんが、Business Objectsなら使えるので、お試しください！