自動照会再書き込み - 複雑な例
これらの例は、Db2® がどのように自動照会再書き込みを適用して、 大きなファクト表に対してコストの高い集計と結合を回避するかを理解するために役立ちます。

データウェアハウスは、クレジット・カードから成るトランザクションを記録します。 各トランザクションには、一緒に購入された一組の品目が含まれています。 データウェアハウスのセンターには、2 つの大きいファクト表があります。 TRANS には、クレジット・カードによる購入トランザクションのセットが記録されます。 TRANSITEM には、購入された品目に関する情報が記録されます。 この 2 つのファクト表が、スター・スキーマのハブになっています。 このスター・スキーマは、これら 2 つのファクト表を含んでいるため、マルチファクト・スター・スキーマです。 ファクト表は、新たなクレジット・カード・トランザクションが発生するたびに更新されます。
このスキーマには、2 つのファクト表に加えて、トランザクションを記述する 4 つのディメンション (製品、ローカル、アカウント、および時間) も含まれています。
- 製品ディメンションは、製品グループと製品ラインを表す 2 つの正規化表 (PGROUP および PLINE) から成っています。
- ロケーション・ディメンションは、市、州、および国を表す 1 つの非正規化表 (LOC) から成っています。
- アカウント・ディメンションは、アカウントと顧客を表す 2 つの正規化表 (ACCT および CUST) から成っています。
- 時間ディメンションは、日、月、年を含む TRANS 表から成っています。
このようなクレジット・カード・アプリケーションの分析者は、多くの場合販売データの集計に関心を持ちます。 分析者が行う照会では、一般に、1 つ以上のディメンション表とファクト表の結合が行われます。 ファクト表にはディメンション表よりはるかに多くの行が含まれており、大きいファクト表を含む複雑な照会は非常にコストが高くなることがあります。 多くの場合、マテリアライズ照会表を使用して、ファクト表からの情報を要約して保管することができます。 マテリアライズ照会表は、大規模なファクト表を使用する場合のコストのかかる集計と結合を回避するために役立ちます。

例 1

UserQ1
------
SELECT T.ACCTID, L.STATE, T.YEAR, COUNT(*) AS CNT
FROM TRANS T, LOC L
WHERE T.LOCID = L.ID AND
L.COUNTRY = 'USA'
GROUP BY T.ACCTID, L.STATE, T.YEAR;
以下の CREATE TABLE ステートメントにより、TRANSCNT という名前のマテリアライズ照会表を作成したとします。
CREATE TABLE TRANSCNT AS
(SELECT ACCTID, LOCID, YEAR, COUNT(*) AS CNT
FROM TRANS
GROUP BY ACCTID, LOCID, YEAR )
DATA INITIALLY DEFERRED
REFRESH DEFERRED;
自動照会再書き込みを使用可能にしてあれば、Db2 は UserQ1 を NewQ1 として再書き込みすることができます。NewQ1 は、TRANS ファクト表の代わりに TRANSCNT マテリアライズ照会表にアクセスします。
NewQ1
-----
SELECT A.ACCTID, L.STATE, A.YEAR, SUM(A.CNT) AS CNT
FROM TRANSCNT A, LOC L
WHERE A.LOCID = L.ID AND
L.COUNTRY = 'USA'
GROUP BY A.ACCTID, L.STATE, A.YEAR;
- TRANS 表は UserQ1 と TRANSCNT の両方に共通している。
- Db2 は、TRANSCNT から照会結果の列を引き出すことができる。
- 照会の中の GROUP BY は、TRANSCNT の定義の中のレベルより高いレベルでグループ化されたデータを要求している。

例 2

UserQ2
------
SELECT T.ID, TI.QUANTITY * TI.PRICE * (1 - TI.DISCOUNT) AS AMT
FROM TRANSITEM TI, TRANS T, PGROUP PG
WHERE TI.TRANSID = T.ID AND
TI.PGID = PG.ID AND
TI.PRICE > 100 AND
TI.DISCOUNT > 0.1 AND
PG.NAME = 'TV';
以下のマテリアライズ照会表 TRANSIAB を定義してあれば、Db2 は UserQ2 を NewQ2 として再書き込みすることができます。
TRANSIAB
--------
CREATE TABLE TRANSIAB AS
(SELECT TI.TRANSID, TI.PRICE, TI.DISCOUNT, TI.PGID,
L.COUNTRY, TI.PRICE * TI.QUANTITY as VALUE
FROM TRANSITEM TI, TRANS T, LOC L
WHERE TI.TRANSID = T.ID AND
T.LOCID = L.ID AND
TI.PRICE > 1 AND
TI.DISCOUNT > 0.1)
DATA INITIALLY DEFERRED
REFRESH DEFERRED;
NewQ2
-----
SELECT A.TRANSID, A.VALUE * (1 - A.DISCOUNT) as AM
FROM TRANSIAB A, PGROUP PG
WHERE A.PGID = PG.ID AND
A.PRICE > 100 AND
PG.NAME = 'TV';
Db2 は、以下の理由により、UserQ2 の再書き込みを行って、マテリアライズ照会表 TRANSIAB を使用する新しい照会を作成できます。
- 述部 T.LOCID = L.ID はマテリアライズ照会表にしか含まれていないが、これが原因で Db2 が行を破棄することにはならない。TRANS.LOCID 列と LOC.ID 列の間に参照制約があるため、マテリアライズ照会表定義の中の TRANS と LOC の間の結合は無損失となります。 この結合が無損失となるのは、制約の中の外部キーが NOT NULL である場合のみです。
- 述部 TI.TRANSID = T.ID および TI.DISCOUNT > 0.1 は、ユーザー照会と TRANSIAB の全選択の両方に含まれている。
- TRANSIAB の中の全選択述部 TI.PRICE >1 は、UserQ2 の中のユーザー照会述部 TI.PRICE > 100 を包含している。 全選択の述部はユーザー照会の述部より包含範囲が広いので、Db2 は、TRANSIAB からユーザー照会の述部を計算することができます。
- ユーザー照会の述部 PG.NAME = 'TV' は、TRANSIAB の全選択の中にはない表を参照している。 しかし、Db2 は、PGROUP 表からこの述部を計算することができます。 PG.NAME = 'TV' のような述部により、照会内の他の述部の適格性が奪われて 自動照会再書き込みに使用できなくなることはありません。 このケースでは、PGROUP は比較的小さいディメンション表なので、この表を参照する述部のコストはさほど高くはありません。
- Db2 は、マテリアライズ照会表定義から照会結果を導き出すことができる (この導出は必ずしも明瞭ではないこともあります)。
- Db2 は、TRANSIAB 全選択の中の T.TRANSID から、照会の中の T.ID を導き出します。 この 2 つの列は異なる表から発生しているものですが、T.TRANSID = T.ID という述部があるため、この 2 つは等価です。Db2 は、結合述部を使用してこのような列の等価性を認識します。 したがって、Db2 は T.TRANSID から T.ID を導き出し、その結果、この照会は自動照会再書き込みに適正に使用できると見なされます。
- Db2 は、TRANSIAB 全選択の中の DISCOUNT および VALUE から、照会 UserQ2 の中の AMT を導き出します。

例 3

UserQ3
------
SELECT YEAR, AVG(QUANTITY * PRICE) AS AVGVAL
FROM TRANSITEM TI, TRANS T
WHERE TI.TRANSID = T.ID
GROUP BY YEAR;
以下のマテリアライズ照会表 TRANSAVG を定義してあれば、Db2 は UserQ3 を NewQ3 として再書き込みすることができます。
TRANSAVG
--------
CREATE TABLE TRANSAVG AS
(SELECT T.YEAR, T.MONTH, SUM(QUANTITY * PRICE) AS TOTVAL, COUNT(*) AS CNT
FROM TRANSITEM TI, TRANS T
WHERE TI.TRANSID = T.ID
GROUP BY T.YEAR, T.MONTH )
DATA INITIALLY DEFERRED
REFRESH DEFERRED;
NewQ3
-----
SELECT YEAR, CASE WHEN SUM(CNT) = 0 THEN NULL
ELSE SUM(TOTVAL)/SUM(CNT)
END AS AVGVAL
FROM TRANSAVG
GROUP BY YEAR;
- Db2 は、ユーザー照会の中の YEAR と、マテリアライズ照会表の全選択の中の YEAR が、正確に一致するものと見なす。
- Db2 は、マテリアライズ照会表の全選択の中の SUM 関数と COUNT 関数から、ユーザー照会の中の AVG 関数を導き出すことができる。
- 照会 NewQ3 の中の GROUP BY 文節は、TRANSAVG の定義の中のレベルより高いレベルのデータを要求している。
- Db2 は、TRANSAVG の中のトランザクション項目の月合計とカウントを使用して、ユーザー照会の中の年平均を計算できる。Db2 は、CASE 式を使用して、マテリアライズ照会表の CNT および TOTVAL 列から年平均を導き出します。
