System iを愛するすべての人のためのコミュニティサイト

前回の「SQLの互換性」では、各データベース製品によってSQL には意外と違いがある、というお話と、
そんな中でも DB2 for IBM i は実は"標準SQL" に近いSQL が使えるので、学習を始めるのにはけっこう向いている、というお話をしました。

今回は、そういった細かな部分の違いではなく、そもそも SQL という言語をきちんと使うには一種の発想の転換が必要で、そうしないと、結果としてとんでもなく非効率な、システム資源を浪費してしまうSQL を書いてしまう危険性がある、ということに触れてみたいと思います。

SQL という言語は C やJava 、COBOL や RPGなどといった言語とは大きく異なった特徴を持っています。

それは、SQL というものは基本的に「取得したい結果（行/カラム）の集合」を記述するものであり、「取得するための手続き」を記述するものではない、ということです。

C やJava 、COBOL や RPGなどといった"手続き型"言語と言われるプログラミング言語では、どういう手順でデータを取ってくるか、を記述しますね。

SQL の場合、取得したいデータの集合の条件を別の条件のデータの集合と共通部分（または非共通部分）を取ったり、さらにその全体に条件をつけたり、とあくまで結果として得られるデータ集合の条件を記述することに主眼が置かれています。
SQL の場合、いかにどうやってデータをとってくるか、に見えたとしても、結果として得られるデータ領域の記述に過ぎず（学校で習った「ベン図」を想像してみるとイメージしやすいのではないでしょうか）、それをどういう"手順"で取ってくるか（それを「アクセス・プラン」といいます）はデータベース・エンジンのお仕事なわけです。

具体的に考えてみましょうか。

たとえば、マスター・テーブルを読み、ある条件に一致するかどうかを確認し、さらにそのレコードに一致する子テーブルを読み出し、その子テーブルの中の条件をチェックする、といった処理があったとしましょう。

↑のような処理を行う RPG プログラムを想像するのはそんなに難しくはありませんね?!

SQL 処理の場合は、JOIN でそれぞれのテーブルの結合条件を指定し WHERE でそれぞれのテーブルの条件を指定する、といった一文で書くのが一般的でしょう。

実際にアプリケーションが使用している状態ではデータは入った状態ですね。
マスター・テーブルは結局巨大なものになってしまい、マスター・テーブル側の条件にあう子テーブルの行はとても少なくなってしまいました、しかもさらに子テーブル側で条件にあって選択される行がとても少なくなっていた、としてみましょう。

これ、いわゆるベン図で考えて見れば一目瞭然なのですが、
正直にマスター・テーブルを全件読んで、一致する行をチェックして、とやっているととても無駄にシステム資源を浪費してしまうことがわかると思います。
子テーブル側で最初に選択済みの集合を作って、その行の集合と一致するマスター・テーブルを選択すれば圧倒的に速く処理は終わってしまうんですね。
（コストベースのオプティマイザーであれば、割合一般的に選択されるアクセス・プランです）

データの量が幾百万件や数億件といった規模になってしまっていれば、
もともとの"手続き"のように「全件読んでチェック」などをしていたら大変な時間がかかってしまいます。

もちろん、マスター・テーブルも子テーブルもほとんど全件読んでしまうような選択結果だった場合は、もともとの"手続き"でもそれほど効率が悪いわけではありません。

つまり、データの内容（量、分布など）によって、最適な"手続き"は変わってきてしまうわけです。
だから、オプティマイザーにとって、それを知るための統計や（情報の元となる）インデックスが大事になってくるわけです。

昔は、（データ量も少なく、内容が予想とはずれていても影響が少なかったから?!） データの構造（DDS）だけを見て、手続き（PGM）を決めておけばよかったわけですが、
今は、データは大量になり、その内容の変化も速く激しくなってきているような世の中になり、その変化に対応して最適な手続きは大幅に異なってしまうような状況になってしまっています。
（その変化に対応して常に最適な手続きを選択できるように、データベース製品ではオプティマイザーの技術が発達してきているわけです）

事前に決めておいた"手続き"ですべて杓子定規に行うだけでは、駄目な時代になってきてしまったわけです。
（まぁだいたい世の中全体がそうなってきているわけですが。。）

ここから、まず 2つの大きな"基本原則"がでてきます。

• システムのオプティマイザーにとって妨げになることをなるべくしないこと。
• システムにとってわかりやすいSQLを書くこと。

上の「システムのオプティマイザーにとって妨げになることをなるべくしないこと」というのは、たとえば
データベースにはいろんな指定ができる（DB2 for IBM i でいうとQAQQINI というデータベースの設定ファイルで可能）のですが、よっぽどよんどころない場合以外はなるべくデフォルトのままにしておいた方がいいでしょう、ということですね。少なくとも予想外の被害は少ないように思います。

具体的にいうと、↑に挙げた例ではオプティマイザーは SQL に書かれているテーブルの結合順序をひっくり返してしまうわけですが、「テーブルの結合順序を何が何でも守れ」という設定がされていた場合はそれができません。
みすみす実行パフォーマンスの悪いアクセス・プランを使わなくてはならない羽目になってしまう、ということが起こり得ます。

次の「システムにとってわかりやすいSQLを書くこと」というのは、よりSQL として"自然"なSQL を書くことによって、結果的にシステムがよりよいアクセス・プランを作成し、選択することを助けることができる、ということです。
"手続き型"の発想に囚われたままでSQL を書くと、余計な自己結合や副照会、複雑すぎる条件指定などを含んだ、必要以上に複雑な SQL になってしまうことがよくあります。ベン図に描いてみると結果は実はもっとシンプルなものだったということもよくあります。当然、最適化の時間もかかってしまうし、アクセス・プランも効率の悪いものができてしまいがちです。

まずは、より自然なSQL を書くように心がけてみることを始めてみるのはどうでしょう??

インターネットにはいろんな参考になる記事もいっぱいあります。
たとえば「SQL アタマ養成講座」はいかがでしょうか? （「現在のところ実装依存」と書かれているOLAP 関数を使用した部分は動作しませんが、それ以外の例はDB2 for IBM i でもちゃんと動作します）
とても参考になると思いますよ!!

書籍では、「SQL書き方ドリル」が DB2 での例が載っており、そのまま動くのでかなり参考にできると思います。（相関名などに日本語が使えないのでそこは英数字に直す必要があります。IBM i のようなサーバーの場合はあまり問題にはならないとは思いますが）

すっきりきれいなSQL が書ける、ということは、どんなデータベース製品を使っていたとしても重要なことですし、
間違いなくスキルアップ、生産性の向上につながりますし、ひいてはシステム資源の効率的な利用にもつながることです。
ぜひ、すぐにでも始めてみましょう!!