ver 1.00 2001/04/01
ジャン：ゆうちゃん，こんにちは。
ゆう：こんにちは。先生，いい天気やね。
ジャン：さっき，長良公園に行って来たら，桜，少し咲いていましたね。
ゆう：もう春ですね。試験も近いし。
ジャン：勉強しましょうか。
ゆう：はい。
ジャン：では，ゆうちゃん，線を引いてもらえますか？
問２ 問合せの最適化に関する次の記述を読んで，設問１〜３に答えよ。
書店のＫ社では，関係データベースを使用して売上管理を行っている。本部スタッフは SQL文を用いて各種分析資料の出力及び照会を行う。
Ｋ社の売上管理システムのテーブル構造は図１のとおりである。
Ｋ社では，新たに月次の売上管理資料として，図２のような書籍分類による資料を作成するために，図３のような SQL文で問合せを行うことにし，業務運用上問題がないかどうかのテストを行った。
| SELCET 書籍.書籍コード, 書名, 著者名,
出版社, SUM(冊数) FROM 売上明細, 書籍 WHERE 売上明細.書籍コード = 書籍.書籍コード AND 書籍.分類 = 'ミステリ' AND 売上明細.販売年月日 = '199803' GROUP BY 書籍.書籍コード, 書名, 著者名, 出版社 |
なお，Ｋ社が使用している DBMS は次に示すような仕様となっている。
〔インデックス使用に関する規則〕
次の規則に従い，インデックス使用に関するアクセスパスの最適化が行われる。
（1）WHERE 句での指定によるインデックス使用の優先順位
優先順位1："インデックスの設定された列名＝定数" のユニークインデックス
優先順位2："インデックスの設定された列名＝定数" の非ユニークインデックス
優先順位3："テーブル結合処理" で使用される列のインデックス
（2）(1)での優先順位が同じインデックスが複数存在する場合
１．異なるテーブルに属しているインデックスがある頃合は，FROMに近い方に書いたテーブルのインデックスを優先する。
２．すべてのインデックスが同じテーブルに属している場合は，WHEREに近い方に書いたインデックスを優先する。
〔テーブル結合処理〕
テーブル結合処理は次に示すような入れ子ループ法を使用して行われる（図4参照）。
（1）I/Oバッファのブロック数は外側用，内側用それぞれ指定するものとする。
（2）FROM に近い方に書いたテーブルを外側ループ用とする。
（3）外側用，内側用それぞれのバッファに読み込めるだけブロックを読み込む。
（4）I/Oバッファ内の各ブロックに含まれるすべてのデータに対してマッチングを行う。
（5）外側用バッファ内容を固定し，内側用バッファに次のデータを読み込み，（4）のようにマッチングを行い，以下，順次内側用テーブルを全件マッチングする。
（6）外側用テーブルの次のデータを外側用バッファに読み込み，再度内側用テーブルを順次全件マッチングする。以下，外側用テーブルのデータ全件分繰り返す。
設問１
図３の問合せを実行したとき，優先順位が一番に使用されるインデックスはどれか。
設問２
図３の問合せでは選択が先に行われ，その結果を基にテーブル間の結合が行われるものとする。同題文中に示すような仕様に基づき，テーブル結合処理が行われるとした場合，次の条件でテーブル結合処理にかかる物理入出力時間（選択，及びグループ化に要する時間は含めない）は何秒か求めよ。答えは小数第1位を四捨五入して整数で求めよ。
・データ量は平均値を使う。
なお，書籍テーブルで分類が "ミステリ" のデータは 900 件である。
・I/Oバッファは外側用 1 プロツク，内側用 50 ブロックを指定する。
・1ブロックに格納される件数は売上明細が 40 件，書籍が 20 件である。
・1ブロックの物理入出力時間は 25ミリ 秒である。
設問3
次に，図３の SQL 文の "書籍.分類＝'ミステリ' "を "書籍.分類＝'エッセイ' "（書籍テーブルで該当のデータは1,100件）に変更して問合せを行ったところ，処理が長時間となり，このままでは実務上使用に耐えられないことが判明した。
原因調査の結果，物理入出力回数が非常に増加していることが分かった。
（1）物理入出力回数が非常に増加した理由を，80 字以内で述べよ。
（2）上記を改善するために，SQL文の変更以外での改善策を，50 字以内で述べよ。ただし，システムリソースの制約からI/Oバッファの総量は変更できないものとする。
ジャン：設問１から，順に解いていきましょう。
ゆう：はい。
|
設問１ 図３の問合せを実行したとき，優先順位が一番に使用されるインデックスはどれか。 |
ジャン：図３の問い合わせと，優先順位の説明を確認しましょう。
ゆう：まず，図３です。
| SELCET 書籍.書籍コード, 書名, 著者名,
出版社, SUM(冊数) FROM 売上明細, 書籍 WHERE 売上明細.書籍コード = 書籍.書籍コード AND 書籍.分類 = 'ミステリ' AND 売上明細.販売年月日 = '199803' GROUP BY 書籍.書籍コード, 書名, 著者名, 出版社 |
インデックスの優先順位は
|
〔インデックス使用に関する規則〕 次の規則に従い，インデックス使用に関するアクセスパスの最適化が行われる。 （1）WHERE 句での指定によるインデックス使用の優先順位 優先順位1："インデックスの設定された列名＝定数" のユニークインデックス 優先順位2："インデックスの設定された列名＝定数" の非ユニークインデックス 優先順位3："テーブル結合処理" で使用される列のインデックスゆう （2）(1)での優先順位が同じインデックスが複数存在する場合 １．異なるテーブルに属しているインデックスがある頃合は，FROMに近い方に書いたテーブルのインデックスを優先する。 ２．すべてのインデックスが同じテーブルに属している場合は，WHEREに近い方に書いたインデックスを優先する。 |
ジャン：アクセスパスとは，利用するために通り道ということですが，ファイルのデータがどういう順に読まれるかということですね。
ゆう：優先順位１は，インデックスにしてあるので，定数との比較ですね。売上明細（表），書籍（表）のインデックスにしてあるのは，それぞれ，売上番号，書籍コードですが，それと定数との比較はありません。
ジャン：優先順位２の「非ユニークインデックス」というのは，読み込むためだけのインデックスで，主キーでないということですね。だから，重複をゆるすということで，非ユニークインデックスといっているのでしょう。
ゆう：優先順位２の非ユニークインデックスですが，売上明細（表）の方は，販売年月日で，書籍（表）の方は，分類です。
ジャン：図３のSQLでは，両方とも登場してますね。
|
AND 書籍.分類 = 'ミステリ' AND 売上明細.販売年月日 = '199803' |
ゆう：こういうときは，
|
（2）(1)での優先順位が同じインデックスが複数存在する場合 １．異なるテーブルに属しているインデックスがある頃合は，FROMに近い方に書いたテーブルのインデックスを優先する。 |
とありますので，「売上明細.販売年月日 = '199803'」が優先ですね。
ジャン：そうですね。では，模範解答例としますね。
データベーススペシャリスト平成１０年午後１問２設問１ 売上明細.販売年月日 |
ゆう：アクセスパスとか，非ユニークインデックスとか，意味を知っていれば，簡単な設問でした。
ジャン：そうですね。では，設問２にいきましょう。
ゆう：はい。
|
設問２ 図３の問合せでは選択が先に行われ，その結果を基にテーブル間の結合が行われるものとする。同題文中に示すような仕様に基づき，テーブル結合処理が行われるとした場合，次の条件でテーブル結合処理にかかる物理入出力時間（選択，及びグループ化に要する時間は含めない）は何秒か求めよ。答えは小数第1位を四捨五入して整数で求めよ ・データ量は平均値を使う。 ・I/Oバッファは外側用 1 プロツク，内側用 50 ブロックを指定する。 ・1ブロックに格納される件数は売上明細が 40 件，書籍が 20 件である。 ・1ブロックの物理入出力時間は 25ミリ 秒である。 |
ジャン：テーブル結合処理は，
|
〔テーブル結合処理〕 テーブル結合処理は次に示すような入れ子ループ法を使用して行われる（図4参照）。 （1）I/Oバッファのブロック数は外側用，内側用それぞれ指定するものとする。 （2）FROM に近い方に書いたテーブルを外側ループ用とする。 （3）外側用，内側用それぞれのバッファに読み込めるだけブロックを読み込む。 （4）I/Oバッファ内の各ブロックに含まれるすべてのデータに対してマッチングを行う。 （5）外側用バッファ内容を固定し，内側用バッファに次のデータを読み込み，（4）のようにマッチングを行い，以下，順次内側用テーブルを全件マッチングする。 （6）外側用テーブルの次のデータを外側用バッファに読み込み，再度内側用テーブルを順次全件マッチングする。以下，外側用テーブルのデータ全件分繰り返す。 |
ゆう：なんか面倒ですけど，問題文に従ってやれば解けそうですね。がんばって，やってみるね。
ジャン：では，やってみましょう。ここでいうブロックというのは，ディスクの入出力単位ですね。ブロックの大きさで主記憶に持ってくるということです。そのときに入る主記憶の場所が，バッファです。バッファに可能な限り，ブロックを一度に読み込むと問題文にあります。
| （3）外側用，内側用それぞれのバッファに読み込めるだけブロックを読み込む。 |
ゆう：先生，
| 次の条件でテーブル結合処理にかかる物理入出力時間（選択，及びグループ化に要する時間は含めない）は何秒か求めよ。 |
とあります。ということは，このブロックの入出力時間だけを計算すればいいのですね。
ジャン：そうです。
ゆう：外側のループのテーブルは，売上明細（表），内側のループのテーブルは，書籍（表）ですね。ここから，分かります。
| （2）FROM に近い方に書いたテーブルを外側ループ用とする。 |
ジャン：そうですね。ゆうちゃん，簡単に動作を説明してもらえますか。
ゆう：まず，売上明細（表）の1998年の３月分を外側ループに入れて，内側ループで，書籍（表）の分類がミステリの分だけ入れて，結合します。内側ループですべて終わったら，外側ループを入れ替えて，また，内側ループで繰り返します。
ジャン：外側ループで，２回入れて，内側ループで３回入れたら，全部で何回入れたことになりますか？
ゆう：外側ループは，２回，それに加えて，内側ループは，それぞれに３回ですので，３回×２＝６回ですね。
ジャン：そうですね。では，外側ループの入力回数から考えましょうか？
ゆう：まず，1998年３月分の売上明細は？
ジャン：設問には，書籍テーブルでミステリの分の件数が載ってますが，売上明細の方はないですね。
ゆう：先生，本文の方に，図１のところにデータ量が2,400,000件/年とあります。
ジャン：設問２のところに，平均量をつかうとありますので，単純に１２ヶ月でわりましょう。そうすると，
2,400,00 ÷ 12 = 200,000 (件）
ゆう：外側ブロックは，バッファは１ブロックで，ブロックに入る売上明細は４０件とありますので，外側ループは，
200,000 ÷ 40 = 5000 (回)
ジャン：そうですね。外側ループは，5000回ですね。外側ループの時間も計算しておきましょう。
ゆう：設問に，次のようにあります。
|
・1ブロックの物理入出力時間は 25ミリ 秒である。 |
25 × 5000 = 125,000 (ミリ秒) = 125 (秒)
先生，000が３つあって，ミリが簡単にとれました。
ジャン：先ほどの外側ループの回数，このミリ秒の秒への変換も簡単にいきましたよね。試験問題は，たいていこうのようにできています。実際の仕事だと，こうはいきませんね。
| 技：正しい解き方をすると，計算は，きれいになる。 |
ゆう：そうですね。先生，次，内側ループにいきますね。まず，データ量は，900件とあります。
ジャン：主記憶に用意したバッファは，５０ブロック分，その５０ブロックそれぞれにたいして，２０件の書籍テーブルのデータが入りますね。
ゆう：先生，なんか混乱します。
ジャン：ブロックの計算は，まず，全件が何ブロックか明確にしていくといいですよ。
| 技：ブロック計算は，ブロック数を明確にする。 |
ゆう：900 ÷ 20 = 45 (ブロック)です。
ジャン：バッファにそれだけ一度で入るよね。
ゆう：そうですね。内部ループは，45ブロックを１度読み込んだら終わりですね。
ジャン：その時間は？
ゆう：25 × 45 = 1125 (ミリ秒)
ジャン：先ほど，ゆうちゃんが，言った，外側の１回ループあたり，内側のループを繰り返すというのは，今回は，全部バッファに入ってしまったので，１度のみで終わりですね。比較，選択等は主記憶ないで処理が終わりますので，ディスク入出力は，発生しません。
ゆう：ということは，単純に，先ほどの，125 秒に 1125 ミリ秒を足せば終わりですね。
ジャン：そうです。
ゆう：秒にして，小数点以下は，四捨五入ですので，答えは，126秒ですね。
ジャン：外側ループのために磁気ディスクが要する時間が，このうち，125秒，内側ループのためには，1秒でした。
ゆう：効率よかったですね。
ジャン：そうですね。では，模範解答例です。
データベーススペシャリスト平成１０年午後１問２設問２ 126 秒 |
ゆう：設問３にいきますね。
|
設問3 次に，図３の SQL 文の "書籍.分類＝'ミステリ' "を "書籍.分類＝'エッセイ' "（書籍テーブルで該当のデータは1,100件）に変更して問合せを行ったところ，処理が長時間となり，このままでは実務上使用に耐えられないことが判明した。 原因調査の結果，物理入出力回数が非常に増加していることが分かった。 （1）物理入出力回数が非常に増加した理由を，80 字以内で述べよ。 （2）上記を改善するために，SQL文の変更以外での改善策を，50 字以内で述べよ。ただし，システムリソースの制約からI/Oバッファの総量は変更できないものとする。 |
ジャン：書籍分類を，エッセイにしたところ，時間がかかりすぎるそうですよ。
ゆう：内部ループに入れるデータが，バッファを超えて，ディスク入出力が数回になったのでしょうね。
ジャン：ゆうちゃん，それが，（１）の答えですよ。
ゆう：先生，でも８０字とありますので，具体的に計算しなくてはいけないですか？
ジャン：時間次第ですね。時間がないときは，具体的な計算は，いいでしょう。
ゆう：先生，午後は，いつも時間が足らなくなりますので，具体的な計算は，しなくていいということですね。
ジャン：まあ，そうですね。
| 技：午後問題では，必要以上の計算はしない。 |
ゆう：では，８０字足らなくてもいいですか？
ジャン：だいたい，７割から８割は埋めた方がいいですよ。
ゆう：私の解答を作りますね。
データベーススペシャリスト平成１０年午後１問２設問３（１） −−−−−−−−−−−−−−−−−−−− 内側ループの対象となる書籍テーブルのデー タがバッファに一度に収まらなくなり，外側 ループの回数分，内部ループのためのディス ク入出回数が必要となっため。(74) |
ジャン：それでいいですよね。では，（２）の解決法は？
| （2）上記を改善するために，SQL文の変更以外での改善策を，50 字以内で述べよ。ただし，システムリソースの制約からI/Oバッファの総量は変更できないものとする。 |
ゆう：これは，外側のバッファを減らし，内側のバッファを増やせばいいですよね。ミステリの900件から，エッセイで，1100件に増えただけですので，それほど，外側バッファを減らす必要もないし。なんといっても，内側ループのバッファに１度に納めることです。
ジャン：そうですね。ゆうちゃん，すぐに答えに気づいたけど，一応，技をあげておきますね。
| 技：「ただし」は，重大ヒント |
ゆう：そうですね。ただしに，「総量は変更できない」って，わざわざ，総量以外をさわりなさいって言ってますね。
ジャン：では，ゆうちゃん，具体的にどうしたらいい？
ゆう：内側用のバッファを増やして，外側用のバッファを減らせば？ 先生，だめだ。外側用のバッファって，もう，最低の１ブロック分ではないですか。
ジャン：そうだよね。
ゆう：では，逆は，どうですか？
ジャン：内側ループ用のバッファを１ブロック分にして，外側分を５０ブロックにするということ？
ゆう：そうです。
ジャン：これは，計算する必要がありますね。
ゆう：外側は，全部で？
ジャン：先ほど計算しましたが，まず，ブロック数ですよ。
ゆう：5000ブロックです。
ジャン：バッファに５０ブロック分割り当てると，
ゆう：5000 ÷50 = 100 回です。
ジャン：この100回というのは，50ブロック転送する回数が100回ということですね。外側ループのために磁気ディスク入出力時間は，50ブロック分が100回ですので，
25 × 50 × 100 = 125000(ミリ秒)=125(秒)
ゆう：先生，この 125 ミリ秒って，設問２の外側ループに要する時間と同じですよ。
ジャン：設問２では，１ブロックを5000回やって，今は，50 ブロックを100回です。結局，どちらにしても，5000ブロック読み込んでいますからね。同じ答えです。
ゆう：そうか。
ジャン：内側ループは，
ゆう：まず，ブロックに注目ですね。計算しなてくはいけませんね。
1100 ÷ 20 = 55 (ブロック)
ジャン：内側ループ用に，１ブロック分のバッファを用意するということは，外側ループ１回あたり，55回のブロック読み込みをするということです。外側ループのための中身が入れ替わるのは，100回です。
ゆう：25 × 55 × 100 = 137500 (ミリ秒) = 137.5秒
ジャン：外側ループのためにディスク入出力時間が，125秒でしたので，併せて，125 + 138 = 263 秒ですね。
?