DB勉強会レポート_2019年01月22日（火）実施分

こんにちは。
今回で午後Ⅰは最終回です（意外と長かった）。
もう一息、頑張りましょう！

解説範囲

平成30年度 春期 データベーススペシャリスト試験 午後Ⅰ 問3 設問1 (5) ~ 設問2 (3)

• 設問1 (5)
• 設問2

⚓ 設問1 (5)

作業W6（DML性能予測）について、表6中のク〜コに入れる適切な字句を、表5、6中の下線部分の用語を用いて答えよ。

---

設問に関する情報を問題文中から探し整理していきましょう。

表2 物理DB設計及び実装の作業工程表（抜粋）

作業順	作業ID	作業名	作業内容
6	W6	DML性能予測	DMLの結果行数および読込みページ数を机上で予測する。

表5 可変長列があるテーブルのストレージ所要量を見積もる計算の手順（完成版）

手順	手順の内容
1	テーブルの見積行数, 平均行長（バイト）及び最大行長（バイト）を得る。
2	RDBMSで指定できるページ長の中から, 最大行長の行を1行以上格納できるページ長を決め, ページ長からヘッダ部を除いた有効ページ長（バイト）を得る。
3	テーブルに行をロードした後, ページあたり1行以上を挿入したい場合, 次の不等式を満たす適切な空き領域率（%）を決める。   平均行長 ≦ 有効ページ長 × 空き容量 ÷ 100
4	次の計算式によって, テーブルのストレージ所要量（バイト）を求める。   ページ当たりの平均行数 = 有効ページ長 ÷ 平均行長の小数点以下を切捨て   必要ページ数 = 見積行数 ÷ ページ当たりの平均行数の小数点以下を切上げ   テーブルのストレージ所要量 = ページ長 × 必要ページ数

表6 テーブルからの読込みページ数を予測する計算の手順（未完成）

手順	手順の内容
1	DMLのアクセスパスを想定, そのアクセスパスを用いてページから読み込む探索行数を予測する。結果行数 = 探索行数とは限らない。
2	・アクセスパスが表探索の場合：読込みページ数 = ク ・アクセスパスが索引探索の場合, かつ,     索引が低クラスタな索引の場合：最大読込みページ数 = ケ … A     索引が高クラスタな索引の場合：         最小読込みページ数 = ケ ÷ コの小数点以下を切上げ … B ・アクセスパスが索引探索の場合、読込みページ数はAとBの間の値になる。

下線部分の用語について確認しておきましょう。

用語	意味	単位
探索行数	DMLを基に探す行の総数	行
結果行数	DMLを基に探し、返す行の総数	行

解説

まずは手順1から。
直接問題には関係ありませんが、「結果行数 = 探索行数とは限らない」とあります。
どういうことでしょうか？

例えば、以下のようなつテーブルが存在するとします。

単語	起源
minister	ラテン語
country	フランス語
tea	中国語
clerk	ラテン語
they	スカンディナヴィア語
orange	アラビア語
fruit	フランス語
yacht	オランダ語
priest	ラテン語
want	スカンディナヴィア語
melon	ギリシャ語
cigarette	スペイン語
hara-kiri	日本語

上記は私の個人ブログから一部を引っ張ってきたもので、英語の語彙の起源を示したものです（完全な個人的趣味ですみません）。
全部で13行あり、テーブル名を「借用語」とします。
SELECT 起源 FROM 借用語のSQLを実行すると、実行結果は以下の通りです。

起源
ラテン語
フランス語
中国語
ラテン語
スカンディナヴィア語
アラビア語
フランス語
オランダ語
ラテン語
スカンディナヴィア語
ギリシャ語
スペイン語
日本語

上記の場合は、結果行数（13行） = 探索行数（13行）が成り立っています。
しかし、SELECT DISTINCT 起源 FROM 借用語を実行すると、DISTINCT句によって重複する行が一つにまとまり、以下の通りの結果になります。

起源
ラテン語
フランス語
中国語
スカンディナヴィア語
アラビア語
オランダ語
ギリシャ語
スペイン語
日本語

この場合、結果行数（9行） ≠ 探索行数（13行）となります。
このように、DISTINCTやJOIN句を使うと、ユーザーに見える返される行数と実際に裏で探索している行数が異なることがあります。

---

それでは、空欄を含む手順2を見ていきましょう。

クは、アクセスが表探索、つまりテーブルの全ての行を探索する場合の読込みページ数と同じになるものは何かを聞いています。
全ての行を探索するということは必然的に全てのページを見ることになります。

よって、クには「テーブルの行を格納するために必要なページ数」という意味の「必要ページ数」が入ります。

---

ケは、低クラスタな索引検索の場合の「最大ページ読込みページ数」を聞いています。
低クラスタなので、行が規則正しく固まって同じページに存在する可能性が低い場合を想定しています。
最小の読込みページ数は、探索する行が全て同じページに存在している場合で、その場合は1回ですみます。

しかし、5行探索するとして、最悪のケースは全ての行が別々のページに存在する場合になります。
その場合、探索する行数と読み込むページ数が一致することになります。

よって、ケには「探索行数」が入ります。

---

最後にコですが、高クラスタな探索の場合の「最小読込みページ数」は、ケの「探索行数」を何で除算すれば求められるかの問題です。
高クラスタな探索の場合、探索対象の行が全て同じページに存在しているケースが最小読込みページ数になります。

具体的な数字で考えてみましょう。
例えば、ページAが30行、ページBが40行、ページCが50行のデータを格納しているとし、600行を探索するとします。
もし、ページAからページCの全てのページに探索対象の600行が格納されている場合、読込みページ数はいくつになるでしょうか？

「600 ÷ { (30 + 40 + 50) ÷ 3 }」より、5ページとなります。

上記の計算式より、「探索行数 ÷ ページ当たりの平均行数」で高クラスタな探索の場合の「最小読込みページ数」を求めることが出来ます。

よって、コには「ページ当たりの平均行数」が入ります。

⚓ 設問2

[G部長の指摘]について, (1) ~ (3)に答えよ

---

[G部長の指摘]の序文は以下の通りですので、表2を併せて以下に示しておきます。

表2の作業工程表について、Fさんは、表に示した作業順で逐次、各作業を行うつもりであったが、G部長から次のような指摘があった。

---

作業順	作業ID	作業内容
1	W1	テーブル名、列名、列データ型、主キー制約及びNOT NULL制約を決めて、CREATE TABLE文を設計する。
2	W2	UNIQUE制約、検査制約及び参照制約を決めて、これらの制約を追加するALTER TABLE文を設計する。
3	W3	ユーザーごとまたはロールごとにテーブルのアクセス権限を決めて、GRANT文を設計する。
4	W4	DMLのアクセスパスを想定し、性能向上のために追加索引を設計する。
5	W5	テーブル及び索引のストレージ所要量を見積もり、表領域をストレージに割り当てる設計を行う。
6	W6	DMLの結果行数及び読込みページ数を机上で予測する。
7	W7	表領域をストレージに割り当て、CREATE TABLE文、ALTER TABLE文及びGRANT文を実行する。
8	W8	テーブル及び索引に関する統計情報を取得し、DMLのアクセスパスが想定どおりかどうかを確認する。
9	W9	性能測定用データを生成するための設計書を作成し、必要なプログラムの開発後に性能測定用データを生成する。 注1)
10	W10	テーブルに性能測定用データをロードする。
11	W11	DMLの性能を測定し、目標を達成するかどうか確認する。

注1) ”店舗利用”テーブルについては、1日分の性能測定用データを生成する。

(1)

指摘1について、Fさんは、他の三つ以上の作業と並行して進められる作業としてW3とW9の二つを選んだ。作業順を変えた後の、直前の作業及び直後の作業の作業IDを、それぞれ答えよ。

---

指摘1の内容を確認しておきましょう。

作業を並行して進めることで、作業工程期間を短縮できる。

---

表2の作業は以下のようにざっくりと分類出来ます。

• W1 - W6：設計 => テーブルはまだ存在しない
• W7 - W11：実装 => テーブルが存在する

W3は設計段階の、W9は実装段階の作業です。

「並行して進められる」作業というのは、「その作業が完了していないと次の作業に移れない」ものではないということです。
逆に言えば、並行して進められない、「その作業が完了していないと次の作業に移れない」作業を決定すれば、それを基準に並行可能な作業とその順序を決めることが出来ます。

---

まずはW3の解答を得るために、設計段階の作業から見ていきましょう。

W1はテーブル設計ですが、この作業は「その作業が完了していないと次の作業に移れない」作業でしょうか？
答えは「正」ですね。
この作業がないと、続く追加制約設計、アクセス権限設計、追加索引設計、表領域設計やDML性能予測を行うことが出来ません。
換言すれば、W3の作業はW1以外の「W2 - W6」の作業と並行して行うことが出来ます。

W7はどうでしょうか？
この作業は実際に表領域をストレージ容量に割り当ててテーブルを作成するので、設計段階の作業が完了していなければ行うことが出来ませんので、並行することは出来ません。

よって、W3と並行可能な作業は「W2 - W6」となり、
並行して行うことが出来ない作業のうち、直前作業がW1、直後作業がW7になります。

---

続いてW9です。

こちらは「性能測定用データの生成」です。
W10を見ると、「性能測定用データをロードする」とありますので、W9が完了しないとこの作業は行うことが出来ません。
よって、「W9 -> W10」の作業は不即不離の関係にあるため、W9の直後作業はW10となります。

さて、直前作業ですが、W9は性能測定用データの「生成」なので、実際にテーブルがなくても作業を行うことは出来るので、W7より前でも構いません。
すでに確認したように、W1のテーブル設計以下の「W2 - W6」は並行作業が可能です。

よって、直前作業はW1となります。

(2)

指摘2について、アクセスパスが索引探索ではなく表探索に決められるDMLを、表3から全て答え、表探索に決められる理由を、W8の作業順の観点から40字以内で述べよ。

---

指摘2の内容を確認しておきましょう。

作業W8（統計情報及びアクセスパス確認）の作業順が8番目では、表3のDMLのアクセスパスが適切に決められない。作業W8を、作業あの後に行うべきである。

---

アクセスパスという用語がありますが、そもそもどういう意味でしょうか？
アシストの解説は以下の通りです。

「アクセス・パス」とはデータベースからデータを取り出す「経路」のことです。RDBMSでは、アプリケーションから発行されたSQLを「解析」して「実行」しており、「解析」では、SQLの構文チェック、参照されているオブジェクトの存在チェックが行われた後、データにアクセスするための経路であるアクセス・パスが決定されます。例えば、友人との待ち合わせ場所まで、どの経路を使って、どんな交通手段で移動するのが一番効率的かを考えるのと似ています。

アクセス・パスから見るRDBMSの違い_アシストより引用

---

「参照されているオブジェクトの存在チェックが行われた後、データにアクセスするための経路であるアクセス・パスが決定されます」は、逆に言えば「参照されているオブジェクトの存在チェックが出来なければ、データにアクセスするための経路であるアクセス・パスを適切に決定出来ない」と同義です。

つまり、テーブルが空の状態ではアクセス・パスを適切に決定出来ません。
W8の作業順8の段階では、テーブルは作成されていますが、まだ行は存在していません。

表3よりどのDMLが表探索に決められるかを決定します。
DML1は、問題文の[RDBMSの主な仕様]の(5)の以下の条件に一致するため、索引探索に決められます。

WHERE句のANDだけで結ばれた等価比較の対象列がユニーク索引のキー列に一致している場合：統計情報に関わらず、その索引の索引探索に決められる。

---

ユニーク索引のキー列に一致しているのが、WHERE句の施設IDと鍵番号です（鍵テーブルの複合主キー）。

DML2とDML3は索引探索か表探索のどちらかですが、同じく問題文の「[RDBMSの主な仕様]の(5)」に一致するため、表探索に決められます。

統計情報からテーブルが空（0行）と判断した場合：表探索に決められる。

---

よって、アクセスパスが表探索に決められるDMLは「DML2とDML3」、理由は「作業W7直後のテーブルは空と判断されて表探索に決められるから」となります。

(3)

あに入れる適切な作業IDを, 一つ答えよ。

テーブルに行が存在しなければ適切なアクセスパスを決定出来ないので、テーブルに性能測定用データがロードされるW10の後に作業W8を行うべきです。
よって、あにはW10が入ります。

今回の設問の解答

• 設問1 (5)
  • ク. 必要ページ数
  • ケ. 探索行数
  • コ. ページ当たりの平均行数
• 設問2
  • (1)
    • W3.【直前】W1【直後】W7
    • W9.【直前】W1【直後】W10
  • (2)
    • 【DML】DML2, DML3
    • 【理由】作業W7直後のテーブルは空と判断されて表探索に決められるから
  • (3) W10

参考ページ

• 平成30年度 春期 データベーススペシャリスト試験 - IPA情報処理推進機構 - 最終アクセス日: 2019年02月05日
• 重複した行をまとめる(DISTINCT) - Pursue SQL講座 - 最終アクセス日: 2019年02月04日
• アクセス・パスから見るRDBMSの違い - アシスト - 最終アクセス日: 2019年02月05日

次回解説予定範囲

平成30年度 春期 データベーススペシャリスト試験 午後Ⅱ 設問1

関連記事

DB勉強会レポート_2019年01月29日(火) & 02月05日(火)実施分