ビューも含めた SQL のオプティマイザーによる書き換え (DB2 for IBM i)

SQL というのは、データの集合の記述であり、データを取得してくる手続きを記述したものではありません。いろんな書き方ができます。

通常、どんなデータベース・エンジンでも書き方次第でパフォーマンスが(場合によっては大きく)異なってくるので、そのことにアタマを悩ますすが、DB2 for IBM i は、きます。

DB2 for IBM i は、少なくともアプリケーションを最優先で生産性よく作ってほしい、というアーキテクチャのもと、それほど複雑でないSQL であればそれぞれの一番効率のよい形に書き換えてくれるようになっています。
(複雑な SQL でもそれは行ってくれますが、そういう"複雑なSQL"を書けるくらいのスキルがあればやはり最初から"効率のいいSQL"を書けるようでないとまずいようには思います。。)


背景

SQL というのは手続きを記述する言語ではなく、最終的に取得したいデータの集合の条件を記述したものです。

要は「集合」の記述の仕方なので、同じ集合に対していくつかの記述/定義の仕方があるように、同じ結果を返すいくつかの記述の仕方が違う SQL が存在することになります。

たとえば↓のような SQL を見てみましょう。
EMPLOYEE テーブルから、WORKDEPT カラムについて DEPARTMENT テーブルから DEPTNO カラムを一致させて部署名を持ってきた上で、部署毎の人数を集計し、さらに今回は特に DEPTNO (WORKDEPT) が 'D11' である部署の人数を確認したい、という要求を記述しています。

SELECT D.DEPTNO, COUNT(E.EMPNO)
FROM SAMPLE.DEPARTMENT D, SAMPLE.EMPLOYEE E
WHERE E.WORKDEPT = D.DEPTNO
GROUP BY D.DEPTNO
HAVING D.DEPTNO = 'D11' ;

実は↑の SQL は↓のように書きかえることができます。こちらの方が 'D11' の部署に所属する EMPLOYEE のみに最初に絞ってしまうので、↑のSQL よりパフォーマンスはよくなります。
それでも、データの集合要素はまったく同じものになります。ヴェン図を書いてみれば確認できます。

はじめからこちらを書く人もいるでしょうね。どう発想したかということで、実際どちらがいいか悪いかという問題ではありません。

SELECT D.DEPTNO, COUNT(E.EMPNO)
FROM SAMPLE.DEPARTMENT D, SAMPLE.EMPLOYEE E
WHERE E.WORKDEPT = D.DEPTNO AND D.DEPTNO = 'D11'
GROUP BY D.DEPTNO ;

もともと部署名と各部署の人数を取得するビューがあった場合、そのビューにたいして部署の選択を行ったとしても最終的に得られる結果は同じになりますね。

CREATE VIEW SAMPLE.EMPCOUNT_BY_DEPT
AS
SELECT D.DEPTNO, COUNT(E.EMPNO) AS EMPCOUNT
FROM SAMPLE.DEPARTMENT D, SAMPLE.EMPLOYEE E
WHERE E.WORKDEPT = D.DEPTNO
GROUP BY D.DEPTNO ;
SELECT * FROM SAMPLE.EMPCOUNT_BY_DEPT
WHERE DEPTNO = 'D11'

こうしたSQL はまずデータベースのオプティマイザという機能によって「アクセス・プラン」という、インデックスを使うか使わないか/どういう順番で結合や選択を処理するか(先に選択を行ってから結合するか、またはその逆か)などといった、結果を得るための"戦略"に変換されます。
そうして生成された「アクセス・プラン」をデータベースエンジンは"実行"するわけです。

最終的に得られるデータの集合が同じ、ということは、これらのSQL はそれを書いた人間の発想の違いを示してはいるものの、数学的にはまったく同じ"式"である、ということを示しています。
("数学的"という言葉が意味しているリレーショナル代数/演算などについては「初歩のデータベース―「表のサイエンス」入門」を読んでみてください。たいへんわかりやすくコンパクトにまとまっていておすすめです)

ところがたいていのデータベース製品はオプティマイザに入力を行う際の構文解析を行う機能がそれほど賢くはなく、SQL の書き方に引きずられてしまって、生成される「アクセス・プラン」が異なってきてしまう、ということが起こります。
つまり、SQL の書き方次第で実行結果を得るまでの時間が遅くなったり速くなったりしてしまう、ということですね。
それが「SQL のチューニング」と呼ばれています。

本来同じデータ集合を表す"式"なので本当はどう書いても同じアクセス・プランが出てこないとおかしいのですが、SQL 構文解析機能の不備のせいで人間が歩み寄ってデータベース製品の機嫌をお伺いすることを「SQL チューニング」と言ってしまっているわけです。

実際、上に挙げたような、GROUP BY 〜 HAVING を WHERE で書き換える、などというのは定番でそうした本やガイドにはよく載っている例です。

DB2 for IBM i では、こうした"人間が機械に合わせなければいけない"ようなことは極力少なくなっています。
上に挙げた 3つのケースとも、「本来」どおり同じアクセス・プランになるようになっています。

実際の例

実際に DB2 for IBM i で実行してみましょう。

まず、最初に HAVING で集計ずみの結合テーブルから 'D11' という部署のもののみを選択する、というSQL についてアクセス・プランがどのようになっているかを見てみましょう。

SELECT D.DEPTNO, COUNT(E.EMPNO)
FROM SAMPLE.DEPARTMENT D, SAMPLE.EMPLOYEE E
WHERE E.WORKDEPT = D.DEPTNO
GROUP BY D.DEPTNO
HAVING D.DEPTNO = 'D11' ;

集約すする前に EMPLOYEE テーブルで DEPTNO の選択を行っていることが確認できます。

次に、WHERE 句で 'D11' をあらかじめ選択して、集計するSQL のアクセス・プランを見てみましょう。

SELECT D.DEPTNO, COUNT(E.EMPNO)
FROM SAMPLE.DEPARTMENT D, SAMPLE.EMPLOYEE E
WHERE E.WORKDEPT = D.DEPTNO AND D.DEPTNO = 'D11'
GROUP BY D.DEPTNO ;

↑のSQL とまったく同じアクセス・プランになっているのがわかりますね。
やはり EMPLOYEE テーブルについて 'D11' を選択した上でネステッド・ループ結合を行っています。

次は、ビューについてのSQL を見てみましょう。

CREATE VIEW SAMPLE.EMPCOUNT_BY_DEPT
AS
SELECT D.DEPTNO, COUNT(E.EMPNO) AS EMPCOUNT
FROM SAMPLE.DEPARTMENT D, SAMPLE.EMPLOYEE E
WHERE E.WORKDEPT = D.DEPTNO
GROUP BY D.DEPTNO ;

繰り返しの説明になりますが、↑のようにビューを定義し、そのビューに対して部署番号で選択を行えば上記 2つのSQL と同じ結果が得られるはずです。

SELECT * FROM SAMPLE.EMPCOUNT_BY_DEPT
WHERE DEPTNO = 'D11'

DB2 for IBM i はオブジェクト指向のOS であり、オブジェクト指向のデータベース・エンジンです。

たとえば、ビューという"データベース・オブジェクト"に対しては、"ビュー"という"OS 上のオブジェクト"が存在するわけです。

もちろんそれが"ビュー"であることはわかっているので、そのプロパティとして持っている SQL をすんなり利用できるようになっています。

ビューという"オブジェクト"が存在し、その属性(プロパティ)に SQL があるから、ビューに対して発行された SQL とビュー内の SQL を両方あわせて最適化できる、という仕組みになっているんですね。

ビューに対する SQL のアクセス・プランを見てみましょう。

上記 2つのSQL とまったく同じアクセス・プランになっていることがわかりますね。

[Top Pageに戻る]

Ads by TOK2