DB2 for i のアクセス・プランは特殊ではない (サブクエリのウィンドウ関数による改善)

DB2 for i はとてもまじめに標準 SQL を実装したデータベース製品です。

OS にはいろんな最先端のアイディアが実装されていて特殊なイメージがあるかもしれませんが、データベースの動き自体は基本に忠実なものです。
当然、生成されるアクセス・プランもなんら特殊なものではありません。

データベースのパフォーマンスには確かにさまざまな要因が影響しますが、製品によって異なるものではありません。
テーブルやインデックスなどの統計情報だったり、システムの構成やパフォーマンス情報などと、SQL の解析結果を勘案して、リレーショナル代数による代数演算をします。

煎じ詰めれば計算なので、誰がやってもほぼ同じ結果が出るわけです。
その結果に対して、IO の構成だったり、ディスクの分散度合いだったり、CPU の速さだったり、メモリの大きさだったりで個性が出る、ということと、統計情報の正確さでやはり誤差がでる、といった違いが出てくる、ということなんです。

ときどき「システムが勝手にプランを作るので、どんなものが出てくるかわからない」とか、おかしなプランが出てきても「システムが計算したものなので結果は正しいはず」とか滅茶苦茶なことをいう技術者の人がいたりするので、ちゃんと言っておきたいのですが、基本的に「SQL からアクセス・プランは理解可能」です。
DB2 for i の場合は、ほとんどの統計情報は常に最新で、ディスクなども分散されているので、逆に、純粋に SQL とインデックスとシステム構成から考えられるとおりかそのさらに上を行くプランが出てきます。変な選択をしていたらそれは問題として問い合わせるようにしてください。


今回は、「SQL緊急救命室 第1回 サブクエリ・パラノイア〜副問い合わせ乱用による性能劣化を治療せよ!」を見ながら、DB2 for i が生成するプランも理論的に想定されるとおりのプランを基本的には作成することを確認したいと思います。
当り前のことですけど、そうでないと通常の SQL チューニングができないことになりますからねぇ。。

悪い例その1

上記リンクの記事で、もともと一番最初に「悪い例」としてあげられている SQL です。

SELECT R1.cust_id, R1.seq, R1.price
  FROM Receipts R1
         INNER JOIN
          (SELECT cust_id, MIN(seq) AS min_seq
             FROM Receipts
            GROUP BY cust_id) R2
    ON R1.cust_id = R2.cust_id
   AND R1.seq = R2.min_seq;

V5R4 で実行してみた結果の Visual Explain です。

記事には「問題は,Receiptsテーブルに対するテーブルアクセスが2回発生していることです」とあり、PostgreSQL と Oracle の実行計画で確認されていますが、↑でも同様にテーブルへのアクセスが 2回(テーブル・スキャンとテーブル・プローブ)発生していることが確認できます。

悪い例その1 の改善例その1

次に記事の中で「これじゃ解決になっとらんぞ」と言われている↑の SQL の”改善例”です。

SELECT cust_id, seq, price
  FROM Receipts R1
 WHERE seq = (SELECT MIN(seq)
                FROM Receipts R2
               WHERE R1.cust_id = R2.cust_id);

同様に V5R4 で実行した結果の Visual Expalin 出力です。

「Oracle,PostgreSQL ともに,やはり Receipts テーブルへのアクセスが2度発生していることがわかります」とありますが、これもそのとおりテーブルへのアクセスが 2回発生していることがわかりますね。(テーブル・スキャンとテーブル・プローブと)

悪い例その1 の改善例その2

悪い例その1 について、決定版としてのウィンドウ関数を使った解決例です。

SELECT cust_id, seq, price
  FROM (SELECT cust_id, seq, price,
               ROW_NUMBER() OVER (PARTITION BY cust_id
                                      ORDER BY seq) AS row_seq
          FROM Receipts ) WORK
 WHERE WORK.row_seq = 1;

V5R4 での Visual Explain 結果です。

Visual Explain の図が見るからにシンプルになっていますね。

合計時間が改善を追うごとに短くなっていっていることを確認してみてください。

あと、これは必ずしも通常そうであるわけではありませんが、最適化時間も短くなっていっています。
ただし、最適化時間は基本的には最初の一回だけなので、これが長くなってしまったとしても、実行時間の短いアクセス・プランを作ってくれる方が助かります。
最適化されていったんプラン・キャッシュに入ってしまえば、その後に実行される SQL の実行時間に最適化時間は含まれません。実行回数が多ければ多いほど、FORCE_JOIN_ORDER などを設定して最適化時間を端折って遅い実行時間のアクセス・プランを生成するとまさしくメタボのように効いてきます。
最適化時間にはあまり注目し過ぎないようにしてください。


悪い例その2

記事にあげられているもう一つの悪い例です。

SELECT TMP_MIN.cust_id, TMP_MIN.price - TMP_MAX.price
  FROM (SELECT R1.cust_id, R1.seq, R1.price
          FROM Receipts R1
                 INNER JOIN
                   (SELECT cust_id, MIN(seq) AS min_seq
                      FROM Receipts
                     GROUP BY cust_id) R2
            ON R1.cust_id = R2.cust_id
           AND R1.seq = R2.min_seq) TMP_MIN
       INNER JOIN
       (SELECT R3.cust_id, R3.seq, R3.price
          FROM Receipts R3
                 INNER JOIN
                   (SELECT cust_id, MAX(seq) AS min_seq
                      FROM Receipts
                     GROUP BY cust_id) R4
            ON R3.cust_id = R4.cust_id
           AND R3.seq = R4.min_seq) TMP_MAX
    ON TMP_MIN.cust_id = TMP_MAX.cust_id;

V5R4 での Visual Explain 出力です。

見るからに複雑なのがわかりますね。
実際に読み込むテーブルは 1つなのに 4回もアクセスに行っていることがわかります。

悪い例その2 の解決例

二番目の悪い例のウィンドウ関数を使った解決例です。

SELECT cust_id,
       SUM(CASE WHEN min_seq = 1 THEN price ELSE 0 END)
           - SUM(CASE WHEN max_seq = 1 THEN price ELSE 0 END) AS diff
  FROM (SELECT cust_id, price,
               ROW_NUMBER() OVER (PARTITION BY cust_id
                                      ORDER BY seq) AS min_seq,
               ROW_NUMBER() OVER (PARTITION BY cust_id
                                      ORDER BY seq DESC) AS max_seq
          FROM Receipts ) WORK
 WHERE WORK.min_seq = 1
    OR WORK.max_seq = 1
 GROUP BY cust_id;

V5R4 での Visual Explain 出力です。

やはりシンプルなプランになっていることがわかりますね。
合計時間も短くなっています。


テスト用テーブル・データ作成用スクリプト

テスト用のテーブルの作成とデータを入力するためのスクリプトです。

CREATE TABLE Receipts(
  cust_id  CHAR NOT NULL,
  seq INT,
  price INT
);

INSERT INTO Receipts VALUES('A',1,500);
INSERT INTO Receipts VALUES('A',2,1000);
INSERT INTO Receipts VALUES('A',3,700);
INSERT INTO Receipts VALUES('B',5,100);
INSERT INTO Receipts VALUES('B',6,5000);
INSERT INTO Receipts VALUES('B',7,300);
INSERT INTO Receipts VALUES('B',9,200);
INSERT INTO Receipts VALUES('B',12,1000);
INSERT INTO Receipts VALUES('C',10,600);
INSERT INTO Receipts VALUES('C',20,100);
INSERT INTO Receipts VALUES('C',45,200);
INSERT INTO Receipts VALUES('C',70,50);
INSERT INTO Receipts VALUES('D',3,2000);

ちょっとしたまとめ

SQL をきちんと書けば、セオリーに従って実行時間は短くなる、ということが垣間見れたのではないでしょうか。
記事の中では PostgresSQL と Oracle が例にあげられていましたが、DB2 for i でも原則として同様な動きをするということが確認できたと思います。

SQL をうまく書けばアクセス・プランの最適化時間も実行時間も短くできる、ということは言えるでしょう。

セカンドベストは、多少最適化時間が長くても実行時間の短い SQL です。

とにかくきちんと SQL を書くこと。
これがどんなデータベースでも基本です。
きちんとリレーショナルデータベースのセオリーに逆らわない SQL を書くようにしましょう。

インデックスを作成するのはいいのですが、更新系の SQL は必ずそれによって負の影響を受けます。
DB2 for i の場合、更新が得てして RPG による夜間バッチなどで行われていることが多くありますよね。
そんな場合、RPG にはインデックスを作る、とかいうことはできないので、必然的にプログラムの修正・変更になってしまいますが、これはオンラインの SQL を修正することより大変なことであることがほとんどです。

わけわからずインデックスアドバイザーに従ってインデックスを作って様子を見る、というのはあまりよい手段とは言えません。
あくまでアドバイスなので、アプリケーション開発者の観点できちんと検討するための材料、と考えるべきだと思います。

[Top Pageに戻る]

Ads by TOK2