 | レベル: 初級 Serge Rielau (srielau@ca.ibm.com)IBM Toronto Lab
2002年 9月 17日 DB2 Universal Databaseバージョン8の更新可能なUNION ALLビュー
はじめに
UNION ALLビューを使った区分化は一般的に、データ保守を容易にして単一表のスケーラビリティーの限界を克服するために使用されます。この記事では、DB2® Universal DatabaseTM V8 for Linux, UNIX® and Windows®が、UNION ALLを用いて定義されたビューを通して、どのように挿入、更新、削除を可能にしているかについて解説します。挿入処理に使用される特許取得済みアルゴリズムを詳細に説明します。
UNION ALLビューの多くの用途
UNION ALLに基づくビューのサポートは、DB2ではバージョン2から提供されています。それ以来、このようなビューはかなり一般的なものになってきました。UNION ALLビューの用途のほとんどは、次のいずれかのカテゴリーに分類されます。
異なるが意味的には関連する表の合併
この種のビュー作成は、オブジェクト指向型階層のリレーショナル・マッピングとして見られることが多く、データの水平方向の区分化 (すなわち、特定の「クラス」のそれぞれが1つの表によって表されること) を表します。
典型的な例として、person、employee、managerというクラスから構成される階層で、person、employee、およびmanagerがそれぞれ別個のリレーショナル表であるケースが挙げられます。UNION ALLビューを使って表をまとめると、すべてのpersonの入ったビューを作成できます。
類似表の合併
全体的なデータ・セットのレンジ区分化を隠ぺいするために、類似表に対してUNION ALLビューを作成して用いることがよくあります。各区画は別々の表なので、保守が容易になり、サイズ制限が緩和され、場合によってはデータ全体に対する照会パフォーマンスが向上します。
1年分の売上データを四半期ごとに分けて4つの表に区分化するケースなどは、典型的な例です。
連合データ・ソースの統合
時には、定義すべきデータがローカル・データベースとリモート・データベースに分散していることもあります。このような場合、データをローカルに複製する以外の方法で、全データの透過的なビューを得るための唯一の選択肢が、UNION ALLの使用です。
DB2では、リモート・オブジェクトをニックネームとして表し、ビューを作成してローカル表や他のニックネームとそれらを関連付けることができます。たとえば、『Building Federated Systems with Relational Connect and Database Views（リレーショナル・コネクトとデータベースのビューを使用した連合データベースの構築）』(PDF形式) を参照してください。
実践的な例
実際の例として、前述のような表をまとめてみましょう。
- まず、表の1つを作成します。
CREATE TABLE Q1(product_no INT, sales INT, date DATE);
|
- 今作成した表から、他の四半期別の表を派生させます。
CREATE TABLE Q2 LIKE Q1;
CREATE TABLE Q3 LIKE Q1;
CREATE TABLE Q4 LIKE Q1;
|
- 各表には、該当する四半期のデータだけが入るようにする必要があります。表Q1は1～3月、表Q2は4～6月、…とします。適切な制約を追加してみましょう。
ALTER TABLE Q1 ADD CONSTRAINT Q1_CHK_DATE
CHECK (MONTH(date) IN (1, 2, 3));
ALTER TABLE Q2 ADD CONSTRAINT Q2_CHK_DATE
CHECK (MONTH(date) IN (4, 5, 6));
ALTER TABLE Q3 ADD CONSTRAINT Q3_CHK_DATE
CHECK (MONTH(date) IN (7, 8, 9));
ALTER TABLE Q4 ADD CONSTRAINT Q4_CHK_DATE
CHECK (MONTH(date) IN (10, 11, 12));
|
- これで「売上データ」を表に入れる準備が整いました。
INSERT INTO Q1 VALUES (5, 6, '2001-01-02'),
(8, 100, '2001-02-28');
INSERT INTO Q2 VALUES (3, 10, '2001-04-11'),
(5, 15, '2001-05-19');
INSERT INTO Q3 VALUES (1, 12, '2001-08-27');
INSERT INTO Q4 VALUES (3, 14, '2001-12-29'),
(2, 21, '2001-12-12');
|
- 最後に、全四半期をまとめて1年分のデータのビューを作成します。
CREATE VIEW FY AS
SELECT product_no, sales, date FROM Q1
UNION ALL
SELECT product_no, sales, date FROM Q2
UNION ALL
SELECT product_no, sales, date FROM Q3
UNION ALL
SELECT product_no, sales, date FROM Q4;
|
ビューの内容は、次のように見えます。
SELECT * FROM FY ORDER BY date, product_no;
PRODUCT_NO SALES DATE
----------- ----------- ----------
5 6 01/02/2001
8 100 02/28/2001
3 10 04/11/2001
5 15 05/19/2001
1 12 08/27/2001
2 21 12/12/2001
3 14 12/29/2001
7 record(s) selected.
|
UNION ALLビューは非常によく使われるので、DB2にはパフォーマンスを改善するためにUNION ALLに関する照会の最適化を目的とした特定のルールがあります。詳しくは、『DB2におけるUNION ALLビューを使用した区分化』(PDF形式) を参照してください。
たとえば、次のステートメントでは、DB2のセオリー検査機構が、表に対する検査制約を用いてQ3およびQ4が影響を受けないことを証明できるため、表Q1およびQ2だけが選択されます。
SELECT sum(sales) AS total FROM FY
WHERE MONTH(date) BETWEEN 3 AND 5;
TOTAL
-----------
25
1 record(s) selected.
|
DB2は更新または削除対象の行がどの表にあるかを認識しており、変更も容易なので、FYビューの行の更新または削除は簡単です。
次のステートメントはQ3の行を更新します。
SELECT sum(sales) AS total FROM FY
WHERE MONTH(date) BETWEEN 3 AND 5;
TOTAL
-----------
25
1 record(s) selected.
|
同じ行をQ3から削除することもできます。
DELETE FROM FY
WHERE product_no = 1 AND date = '2001-08-27';
|
次のステートメントは、削除された行を再挿入します。
INSERT INTO FY VALUES (1, 20, '2001-08-27');
|
簡潔な挿入
ちょっと待ってください。この例の始めで、挿入は基礎表に対して実施しました。なぜでしょうか? DB2バージョン8より前は、UNION ALLビューを介した挿入はできませんでした。したがって、アプリケーションはビューの構成を認識したうえで、適切な基礎表に挿入する必要がありました。
DB2 V8では、新しい非常に整然としたロジックにより、UNION ALLビューに対する挿入を受け入れることができます。
INSERT INTO FY VALUES (1, 20, '2001-06-03'),
(2, 30, '2001-03-21'),
(2, 25, '2001-08-30');
|
DB2 V8では、新しい非常に整然としたロジックにより、UNION ALLビューに対する挿入を受け入れることができます。
INSERT INTO FY VALUES (1, 20, '2001-06-03'),
(2, 30, '2001-03-21'),
(2, 25, '2001-08-30');
|
意味的には、UNION ALLビューの基になる表のそれぞれに対して、DB2は指定の行の挿入を試みます。そして行を受け入れた表の数を数えます。
- 行を受け入れるのが1つの表だけの場合、挿入は受け入れられます。
- 行を受け入れる表がない場合、「ターゲットがない」旨のエラーが発生します。
- 行を受け入れる表が複数ある場合、「ターゲットが曖昧」の旨のエラーが発生します。
上記ビューでは、各四半期表に関する制約を全部合わせると、有効な月 (1〜12) のすべてを重複なく網羅するため、常に1つの表だけが該当するはずです。
では、Q1基礎表に1月ではなく4月のデータを許容してしまうようなタイプミスが制約Q1_CHK_DATEにあった場合は、どうなるでしょうか。
ALTER TABLE Q1 DROP CONSTRAINT Q1_CHK_DATE;
ALTER TABLE Q1 ADD CONSTRAINT Q1_CHK_DATE
CHECK (MONTH(date) IN (4, 2, 3));
|
1月のデータは受け入れることのできる表がないため、挿入できません。
INSERT INTO FY VALUES (5, 35, '2001-01-14');
SQL20154N The requested insert operation into view "SRIELAU.FY"
is not allowed because no target table can be determined for a row.
Reason code = "1". SQLSTATE=23513
|
一方、4月のデータはQ1とQ2の両方とも受け入れることができるため、矛盾が発生します。
INSERT INTO FY VALUES (3, 30, '2001-04-21');
SQL20154N The requested insert operation into view "SRIELAU.FY"
is not allowed because no target table can be determined for a row.
Reason code = "2". SQLSTATE=23513
|
定義された制約にはNullデータの明示的処理が含まれていなかったことに注意してください。制約はfalseを却下するだけなので、すべての表がNull日付を持つ行を許容してしまい、「ターゲットが曖昧」を示すエラーが生成されます。
上の例では、問題は明らかに制約のまずさ、すなわち不適切な区分化にありますが、すべてのUNION ALLビューが区分化するわけではないので、DB2が制約の構成についてうるさく関知しないことが重要です。重要なのは、基礎表のデータの区分化ではなく、基礎表の制約に則した入力データの区分化です。
では、UNION ALLビューへの挿入は、実際にはどのように機能するのでしょうか? 各表への挿入はコスト高ではないでしょうか? 複数のデータベース区画を持つシステムの場合はどうでしょう? この機能にスケーラビリティーはありますか?
内部的な仕組み
UNION ALL経由の挿入の仕組みを内部的に詳しく検討してみましょう。解りやすくするために、各ステップをSQLを通して説明します。ただし、DB2の内部的な表現力はSQLで表現できることを包含するスーパーセットなので、SQLでは正確に近い説明ができるにすぎません。
開始に先立ち、Q1は製品番号9を6として認識すると宣言することにより、いくつかの興味深いロジックを追加しましょう。これは単純なBEFOREトリガーで実施されます。
CREATE TRIGGER I_Q1_PNO NO CASCADE BEFORE INSERT ON Q1
REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL
SET n.product_no =
CASE WHEN n.product_no = 9 THEN 6
ELSE n.product_no END;
|
さらに、Null日付が現在の日付を意味すると解釈されると定義します。ここでもBEFOREトリガーを使用します。
CREATE TRIGGER I_Q1_NULL NO CASCADE BEFORE INSERT ON Q1
REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL
SET n.date
= COALESCE(n.date, CURRENT DATE);
CREATE TRIGGER I_Q2_NULL NO CASCADE BEFORE INSERT ON Q2
REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL
SET n.date
= COALESCE(n.date, CURRENT DATE);
CREATE TRIGGER I_Q3_NULL NO CASCADE BEFORE INSERT ON Q3
REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL
SET n.date
= COALESCE(n.date, CURRENT DATE);
CREATE TRIGGER I_Q4_NULL NO CASCADE BEFORE INSERT ON Q4
REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL
SET n.date
= COALESCE(n.date, CURRENT DATE);
|
ここで、FYに次のように3行を挿入したいとします。
INSERT INTO FY VALUES (7, 20, NULL),
(9, 30, '2001-03-21'),
(9, 25, '2001-04-30');
Input:
PRODUCT_NO SALES DATE
---------- ----- ----------
7 20 -
9 30 2001-03-21
9 25 2001-04-30
|
ではDB2がどうするかを見てみましょう。
- まず、アルゴリズム (図1参照) は各行を一意に識別します。
TABLE(SELECT ROWNUMBER() OVER() AS RN,
product_no, sales, date
FROM Input) AS Step1
Step1:
RN PRODUCT_NO SALES DATE
-- ---------- ----- ----------
1 7 20 -
2 9 30 2001-03-21
3 9 25 2001-04-30
|
- DB2コンパイラーはインメモリー表を作成します。この表には、すべての考え得るターゲット表の識別子に加えて、標本行 (あるいは、常に通常の表の識別子の後につけるため「caboose」(本来は貨物列車の後部に連結される車掌車の意味) と親しみを込めて呼ばれます) が含まれます。
TABLE(VALUES ('Q1')
('Q2')
('Q3')
('Q4')
('ZZ') /* Caboose */
) AS Step2(table_id)
Step2:
TABLE_ID
--------
Q1
Q2
Q3
Q4
ZZ
|
Cabooseは常に存在することに注意してください。
図1. UNION ALLビューを介した挿入のDB2のアルゴリズム
- 次に、ネストされたループ結合を使用して、入力のクロス積を作成します。このクロス積は、表の順序を保持し、見やすくグループ化します。
TABLE(SELECT rn, table_id, product_no, sales, date
FROM Step1 INNER JOIN Step2) AS Step3
Step3:
RN TABLE_ID PRODUCT_NO SALES DATE
-- -------- ---------- ----- ----------
1 Q1 7 20 -
1 Q2 7 20 -
1 Q3 7 20 -
1 Q4 7 20 -
1 ZZ 7 20 -
2 Q1 9 30 2001-03-21
2 Q2 9 30 2001-03-21
2 Q3 9 30 2001-03-21
2 Q4 9 30 2001-03-21
2 ZZ 9 30 2001-03-21
3 Q1 9 25 2001-04-30
3 Q2 9 25 2001-04-30
3 Q3 9 25 2001-04-30
3 Q4 9 25 2001-04-30
3 ZZ 9 25 2001-04-30
|
なぜ行を複製したのでしょうか? それは簡単に言えば、検査制約が実行される前にBEFOREトリガーの起動をSQLが要求するからです。異なる表のBEFOREトリガーは、新しい遷移変数で異なる変更を実施できるため、ターゲットとなり得る表には専用コピーが作成される必要があります。
- この例では、トリガーI_Q1_PNO、およびI_Q1_NULLからI_Q4_NULLが起動された後、中間結果は、次のようになります (CURRENT DATEがと仮定)。
Step4:
RN TABLE_ID PRODUCT_NO SALES DATE
-- -------- ---------- ----- ----------
1 Q1 7 20 2002-01-17
1 Q2 7 20 2002-01-17
1 Q3 7 20 2002-01-17
1 Q4 7 20 2002-01-17
1 ZZ 7 20 -
2 Q1 6 30 2001-03-21
2 Q2 9 30 2001-03-21
2 Q3 9 30 2001-03-21
2 Q4 9 30 2001-03-21
2 ZZ 9 30 2001-03-21
3 Q1 9 25 2001-04-30
3 Q2 9 25 2001-04-30
3 Q3 9 25 2001-04-30
3 Q4 9 25 2001-04-30
3 ZZ 9 25 2001-04-30
|
注：特定の行が最終的にトリガーのサブジェクト表に配置されないとしても、セマンティクスの定義方法によっては、各表に対するbeforeトリガーを起動する必要があります。電子メールを送ったりNEXTVALシーケンス式を実行したりといった副次作用のあるbeforeトリガーは、トリガーの定義者の予想を超えて頻繁に起動されます。
- beforeトリガーが考慮されたので、違反するすべての行をフィルターで除外するために、検査制約が使われます。さらに、依然として、(1, 2, 3) の代わりに (4, 2, 3) という月を使用する誤った検査制約Q1_CHK_DATEを使用しているとします。
TABLE(SELECT rn, table_id, product_no, ...
FROM Step4
WHERE (table_id = 'Q1'
AND month(date) IN (4, 2, 3))
OR (table_id = 'Q2'
AND month(date) IN (4, 5, 6))
OR (table_id = 'Q3'
AND month(date) IN (7, 8, 9))
OR (table_id = 'Q4'
AND month(date) IN (10, 11, 12))
OR (table_id = 'ZZ'))
AS Step5
Step5:
RN TABLE_ID PRODUCT_NO SALES DATE
-- -------- ---------- ----- ----------
1 ZZ 7 20 -
2 Q1 6 30 2001-03-21
2 ZZ 9 30 2001-03-21
3 Q1 9 25 2001-04-30
3 Q2 9 25 2001-04-30
3 ZZ 9 25 2001-04-30
|
Cabooseには検査制約がないため、フィルターされることはありません。したがって、次のような結果になります。
- 最初の行にはcabooseのみ (1月がない)
- (Q1の) 通常の行が1行 (および3月の行として残ったcaboose)
- Q1とQ2に残った4月の行、そしてもちろんcaboose
- この後すべきことは、各グループに番号を付けることです。
TABLE(SELECT rn, table_id,
rownumber() over(partition by rn) as rank,
product_no, ...
FROM Step5) AS Step6
Step6:
RN TABLE_ID RANK PRODUCT_NO SALES DATE
-- -------- ---- ---------- ----- ----------
1 ZZ 1 7 20 2001-01-03
2 Q1 1 9 30 2001-03-21
2 ZZ 2 9 30 2001-03-21
3 Q1 1 9 25 2001-04-30
3 Q2 2 9 25 2001-04-30
3 ZZ 3 9 25 2001-04-30
|
-
cabooseのランクを簡単にテストすると、何をすべきかわかります。
- 'ZZ'と1は、すべての表の制約によって行がフィルターされたことを意味します。これは「ターゲットなし」のエラーです。
- 'ZZ'と2は、1つの表の制約が行を受け入れたことを意味します。これは成功です。
- 'ZZ'と2より大きな数値は、複数の表の制約が行を受け入れたことを意味します。これは「ターゲットが曖昧」を示すエラーです。
TABLE(SELECT rn, ....
FROM Step6
WHERE 1 = CASE WHEN table_id <> 'ZZ' THEN 1
WHEN rank = 2 THEN 1
WHEN rank = 1
THEN raise_error('no target')
ELSE raise_error('ambiguous target')
END
AS Step7
|
- アトミシティ (原子性) に求められるとおり、2つのエラーのいずれかに直面するとステートメント全体をロールバックするので、すべての行が却下されます。
このテストに続いて、caboose行をフィルターして追加列 (ターゲット表の識別子を除く) を予測できます。次に、表の識別子を使用して、それぞれの表に挿入が発生します。
これは何を意味するのでしょうか。この特許取得済みのアルゴリズムの何がそれほど優れているのでしょうか?
- ターゲット表のインメモリー表は、ステートメントが準備されるときにSQLコンパイラーによってソートされ、各処理ノード上に複製できます。
- 入力の番号付けは一時表がなくても実施でき、処理ノードにローカルでも構いません。
- ネストされたループ結合の結果によって、ソートや一時表を使わなくても、入力データ (外部) はグループ (各グループの終わりはcaboose) として保持されます。ネストされたループは、実行するノードにローカルなデータのみを処理する必要があります。
- beforeトリガーがサポートされます。
- 検査制約は、挿入が実際に発生する前に処理されます。区分化を静的分析する必要は一切ありません。
- グループの最終的な番号付け (ランキング) にはソートは不要で、各処理ノード上でローカルに実施できます。
要するに、この記事の例で、アルゴリズムに渡した行がデータベースの別区画からのものである場合、各区画は孤立して完全なアルゴリズムを実行でき、挿入対象の行をターゲット表へ送る前に別ノードと通信する必要はありません。この最後の通信は、ハッシュ指定で実施できます。したがって、アルゴリズムによってスケーラビリティーに何らボトルネックが生じることはありません。
完全に並列化されパイプライン化されたBEFOREトリガー処理もDB2 V8が共通トリガー用に導入したとすると、このアルゴリズムは単純にロックします。
潜在的な改善点
「区分化キー」として機能する指定日付のタイプミスを挿入後に修正しようとすると、どうなるでしょうか? DB2は表内の行を更新できるだけなので、制約違反エラーが発生します。
UPDATE FY
SET date = '2001-07-11'
WHERE product_no = 3 AND date = '2001-04-11';
SQL0545N The requested operation is not allowed because a row does
not satisfy the check constraint "SRIELAU.Q2.Q2_CHK_DATE".
SQLSTATE=23513
|
代わりに、行は削除され再挿入される必要があります。
DELETE FROM FY
WHERE product_no = 3 AND date = '2001-04-11';
INSERT INTO FY VALUES (3, 10, '2001-07-11');
|
DB2バージョン8はUNION ALLビューの更新機能に大きな一歩を踏み出したかのようですが、さらなる向上も構想できます。
-
DB2 V7で複数データベース区画グループ内の表の区分化キーに対する更新機能が導入されたように、いつの日か、UNION ALLビューでは、1つの基礎表から別の基礎表へ行をマイグレーションする更新機能をサポートするようになると予想されます。
たとえば、次のステートメントにより、行はQ2表からQ3表へ移動されます。これは、行がQ2から削除されて、この記事で解説した挿入アルゴリズムを使用して、更新された行がFYに挿入されることを意味します。
UPDATE FY
SET date = '2001-07-11'
WHERE product_no = 3 AND date = '2001-04-11';
|
- さらに、ビューの本体の述部を活用して、検査制約に加えて、あるいは検査制約の代わりに、行をディスパッチするには、UNION ALLを介して挿入を一般化することが望まれます。このビューは次のようになります。
CREATE VIEW FY AS
SELECT product_no, sales, date FROM Q1
WHERE month(date) IN (1, 2, 3)
UNION ALL
SELECT product_no, sales, date FROM Q2
WHERE month(date) IN (4, 5, 6)
UNION ALL
SELECT product_no, sales, date FROM Q3
WHERE month(date) IN (7, 8, 9)
UNION ALL
SELECT product_no, sales, date FROM Q4
WHERE month(date) IN (10, 11, 12)
WITH CASCADED CHECK OPTIONS;
|
上記のcheck options文節は、ビューに挿入される各行は同じビューを使用して選択できることを保証します。したがって、この種のビューは「対称型」と呼ばれます。この記事で説明したとおり、UNION ALLビューを介して挿入のセマンティクスを1つが受け入れると、次のような改善が考えられます。
行がUNION ALLビューの1つの分岐だけで受け入れられた場合、1行をUNION ALLビューに挿入できます。
結論
本書では、UNION ALLビューが有益であるケースを説明し、複数の基礎表を基にするビューを更新することが何を意味するかを解説しました。UNION ALLビューへの挿入に対処するスケーラブルなアルゴリズムが提示されました。挿入可能なUNION ALLビューは、DB2 UDB V8 for Linux, UNIX, and Windowsでサポートされ、全SQLデータ操作ステートメントの基になるデータを完全にカプセル化して保持するのに役立ちます。
免責事項
この記事にはサンプル・コードが含まれます。IBMは読者のみなさま (「ライセンス所有者」)に対して、このサンプル・コードを使用するための使用料無料の非独占的な使用権を許諾します。サンプル・コードは現存するままで提供され、保証およびプログラム・サービスは提供されません。IBMはこのサンプル・コードについて、商品性の保証、特定目的適合性の保証および法律上の瑕疵担保責任を含むすべての明示もしくは黙示の保証責任を負わないものとします。また、IBMは逸失利益を含むいかなる直接または間接の損害についても責任を負わないものとします。
参考文献
著者について | | | Serge RielauのDB2のキャリアは6年前、アルマデン研究所における1年間のインターンシップで始まりました。インターンシップでは、オンラインSQL PLと構造化タイプに従事しました。次の5年間は、タイプ付きビューや生成列など、さまざまなSQL機能拡張を担当しました。
現在はSQL照会コンパイラーのテクニカル・マネージャーとして、DB2およびSQL標準に対する言語関連の変更に従事しています。連絡先: comp.databases.ibm-db2 またはsrielau@ca.ibm.com |
記事の評価
| |
