データベーススペシャリスト試験 2011年 午後1 問03

模範解答の核心となるキーワードや論点

解答になる理由を問題文の記述を引用しながら論理的に説明

1. (a) SQL2A の「WHERE C4 < a」
   問題文「SQL2x … 結果行はない。」という要件を満たすには，C4 の最小値未満を指定します。
   表2 によると「C4 列は 1〜1,000 のいずれかの整数。各列値に同数の行が存在する。」ため，C4 < 1 とすれば該当行はゼロ件になります。
   → a：1
2. (b) SQL3A の「WHERE b」
   問題文「SQL3x … 非クラスタ索引探索によって、1,000行の結果行を得る。述語は1個。」
   表3 によると，TA テーブルの非クラスタ索引には TAX2（列名 C2，ユニーク） と TAX3（列名 C3，非ユニーク） があります。
   • TAX3 は非ユニークだが，C3 = 定数 では行数が 1/4 にあたる ≒ 5,000,000 行取得されてしまう。
   • TAX2 はユニーク索引ながら，範囲検索 BETWEEN を使えば複数行を得られます。C2 は 1～20,000,000 の一意な整数。
     ここで C2 BETWEEN 1 AND 1000 とすればちょうど 1,000 行を取得できます。
     → b：C2 BETWEEN 1 AND 1000
3. (c)/(d)/(e) SQL4A の「SELECT c FROM TA GROUP BY d HAVING e」
   問題文「SQL4x … C3列とC4列を組み合わせたグループごとにC5列の合計値を求める。ただし、対象となるのは、グループの行数が10以上のグループだけである。」
   • SELECT 句には集約対象の列 C5 の合計と，グループ化のキー C3,C4 を含める必要があります。
   • GROUP BY 句にはグループ化キーを列挙します。
   • HAVING 句で「グループの行数が10以上」の条件は COUNT(*)>=10 で表します。
     → c：C3,C4,SUM(C5)
     → d：C3,C4
     → e：COUNT(*)>=10

受験者が誤りやすいポイント・ひっかけ

• 【SQL2A の a】
  • 「C4 列に索引はない」ため，どんな値でも表探索になることを忘れ，索引探索できると思い込みがちです。
  • C4<0 としても正解ですが，最小値「1」を意識せずに「0」や「–1」を書くと，問題文に記載のない値を持ち出すことになり不適切です。
• 【SQL3A の b】
  • 範囲検索をすべての列に当てはめてしまい，C3 や C1 に BETWEEN を使おうとする誤り。C3 列は非ユニークで = 検索のみ意味があり，1,000行を得られません。
  • 索引のクラスタ／非クラスタの違いを混同し，「クラスタ索引だから高速」とだけ考え，TAX1（C1 のクラスタ索引） を使おうとしても，=定数 では 1 行しか得られません。
• 【SQL4A の c/d/e】
  • HAVING 句と WHERE 句を混同し，集約後のフィルタを WHERE COUNT(*)>=10 と書いてしまう。
  • COUNT(C5)>=10 と間違えると，NULL 扱いの影響で意図と異なる結果になります。本問では C5 に NULL はない前提ですが，試験では COUNT(*) が最適です。

試験対策として覚えておくべきポイント

1. 索引の種類と探索方法
   • 非クラスタ索引は，キー順とデータの物理順が異なる。範囲検索（BETWEEN／<,>,<=,>=）での利用を想定する。
   • クラスタ索引は物理順と一致するが，1:1 またはユニークな検索に向く。
2. SQL 文法
   • WHERE はグループ化前の行フィルタ，用いるのは非集約列に対する条件。
   • GROUP BY で指定した列＋集約関数のみ SELECT できる。
   • HAVING はグループ化後の行フィルタ，用いるのは集約結果に対する条件。
3. BETWEEN の仕様
   • A BETWEEN x AND y は A>=x AND A<=y と同義。
4. 問題文の要件を正確に読む
   • 「結果行はない」「1,000行の結果行を得る」など，行数要件から逆算して定数や範囲を決める。
   • 列値の分布や索引設計を表から正確に読み取り，利用可能な索引を把握する。

1. キーワードと論点の整理

2. f～n の解答と解説

分析 A の空欄

分析 B の空欄

3. 受験者が誤りやすいポイント

• 「同期」「非同期」の区別：
  順次読み込みで並行可能なのが「非同期」，ランダム読み込みで並行不可なのが「同期」です。
• 単位の扱い：
  ミリ秒と秒の変換（1ミリ秒 = 0.001秒）を忘れると，g, i の計算で誤りやすいです。
• ページ数と行数を混同しない：
  表探索の I/O はページ数ベース，一行あたりの CPU 処理は行数ベースで時間を計算します。

4. 試験対策まとめ

• I/O モードの理解：
  順次／ランダムアクセスで「非同期／同期データ入出力処理」と名称も性能式も変わる。
• 性能式の構造把握：
  SELECT 文は CPU 時間と I/O 時間のうち大きい方を取る（非同期時），もしくは両者の和（同期時）。
• 具体数値演算：
  問題文中の「1ミリ秒/ページ」「10ミリ秒/ページ」「0.1ミリ秒/行」などを用いた時間計算に慣れる。
• テーブル定義の把握：
  ページ当たり行数，総ページ数，インデックスのクラスタ／非クラスタ性が処理時間に影響する。

キーワードと論点の整理

• デッドロック
  複数トランザクションが互いに相手が保持するロックを待ち合う状態
• 索引探索とクラスタ／非クラスタ索引
  「表探索ではなく，WHERE句の索引によって絞り込む」（問題文より）
  • TAX1：C1列に対するユニーク・クラスタ索引
  • TAX2：C2列に対するユニーク・非クラスタ索引
• ロック取得順序の不一致
  • クラスタ索引探索 → C1の昇順で行ロックを取得
  • 非クラスタ索引探索 → C2の昇順で行ロックを取得
    → 順序が異なるため，循環待ち（デッドロック）を招く

解答の根拠と論理展開

1. 【問題文】より
   「アクセス経路は索引探索に限るものとする」「WHERE句の述語１個だけで1,000行を更新」
   → BETWEEN 1 AND 1000 のような範囲指定にすると，ユニーク索引を用いた索引探索でちょうど1,000行を更新できる
2. 索引設計（表3）より

   テーブル名	索引名	列名	ユニーク性	クラスタ性
   TA	TAX1	C1	ユニーク	クラスタ索引
   TA	TAX2	C2	ユニーク	非クラスタ索引

   • WHERE C1 BETWEEN 1 AND 1000 → TAX1を使い，C1昇順でロック
   • WHERE C2 BETWEEN 1 AND 1000 → TAX2を使い，C2昇順でロック
3. トランザクションを並行実行すると，
   • TX1：UPDATE … WHERE C1 BETWEEN 1 AND 1000
     → C1 の昇順に行ロックを取得
   • TX2：UPDATE … WHERE C2 BETWEEN 1 AND 1000
     → C2 の昇順に行ロックを取得
   両者が同じ行群を更新対象とすると，ロック取得の順序が逆転して「TX1がある行のロックを待ち，TX2が別の行のロックを待つ」という循環待ちが発生し，デッドロックとなる。

模範解答

ア：C2 BETWEEN 1 AND 1000
理由：索引 TAX1 と TAX2 とでは行を更新する順番が異なるから

受験者が誤りやすいポイント

• 「クラスタ索引」と「非クラスタ索引」でロック順序が同じならデッドロックは起きない
• = 演算子で 1 行だけを更新するとデッドロックを起こしにくい
• 範囲条件は BETWEEN でなければ索引が利用されない場合がある

試験対策ポイント

1. デッドロックの本質：ロック取得順序の不一致による循環待ち
2. クラスタ索引 vs 非クラスタ索引
   • クラスタ索引：物理順序とキー順序が一致
   • 非クラスタ索引：物理順序はランダム
3. 索引探索の条件
   • 比較演算子（=,<,>,<=,>=）または BETWEEN を用い，列を明示
   • ユニーク索引／非ユニーク索引の特性を把握
4. 並行更新試験
   • 異なるアクセス経路を使う更新 SQL を複数用意し，意図的にデッドロックを検証する
5. ロック粒度
   • 本試験では「行単位ロック」「READ COMMITTED」と明記されているので，行レベルでの競合を想定すること

模範解答のキーワード・論点整理

• 更新対象行数（N行）の算出
  ・TA：20 000 000行÷4区分＝5 000 000行／区分
  ・TB： 2 000 000行÷4区分＝ 500 000行／区分
• 行数→データページ数の変換（表 1 より「1ページ当たりの行数」を引用）

  テーブル	行数／区分	1ページ当たりの行数	ページ数／区分
  TA	5 000 000	20	250 000
  TB	500 000	2	250 000

• データ入出力処理時間の算出
  RDBMS仕様：「データ入出力処理は,4 096バイトのページ単位」「順次入出力時は CPU 処理と並行」「1 ミリ秒/ページ」
  更新では「読み込み＋書き込み」で 1 ページ当たり 2 ミリ秒 ⇒
  250 000ページ×2 ミリ秒＝500 000ミリ秒＝500秒
• ログ出力処理時間の算出
  問題文：「データページの1ページ中の全行を更新するごとにログページが2ページ出力されるものとする。」
  1区分あたりのログページ数：250 000ページ×2＝500 000ページ
  1ミリ秒/ページ×500 000ページ＝500秒
• 並行数 4 のときのログ出力の直列化
  RDBMS仕様：「同じディスクに対して,同時に入出力を行うことはない。」
  ログは1つのディスクに格納され,4ジョブが同時発生すると排他で直列化 ⇒
  合計ログページ数＝500 000ページ×4＝2 000 000ページ
  合計ログ出力時間＝2 000 000ミリ秒＝2 000秒

• o（TA のログ出力処理時間）：2 000
• p（TB のデータ入出力処理時間）： 500
• q（TB のログ出力処理時間）：2 000

解答導出の論理的ステップ

1. 区分あたりの更新行数
   問題文より

   「テーブルを四つの区分に物理分割する。」
   「C3列を区分キーとして,テーブルを四つの区分に物理分割する。」
   TA 全行 20,000,000行 ÷4 = 5,000,000行／区分
   TB 全行 2,000,000行 ÷4 = 500,000行／区分

2. ページ数の計算
   表 1「1ページ当たりの行数」を引用して
   • TA：行長200バイト ⇒ 1ページ当たり 20行 ⇒ 5,000,000÷20＝250,000ページ
   • TB：行長2,000バイト ⇒ 1ページ当たり 2行 ⇒ 500,000÷2 ＝250,000ページ
3. データ入出力処理時間
   RDBMS 仕様より

   「データ入出力処理において,ディスクに対してデータページを順次に入出力する場合…1ミリ秒/ページ」
   更新では「読み込み＋書き込み」なので 1ページあたり2ミリ秒
   250,000ページ×2ミリ秒＝500,000ミリ秒＝500秒
   → p = 500

4. ログ出力処理時間（単体ジョブ）
   問題文より

   「データページの1ページ中の全行を更新するごとにログページが2ページ出力されるものとする。」
   250,000データページ×2＝500,000ログページ
   1ミリ秒/ページ×500,000ページ＝500秒

5. 並行4ジョブ時のログ直列化
   RDBMS 仕様より

   「同じディスクに対して,同時に入出力を行うことはない。」
   4ジョブ分のログページ合計＝500,000×4＝2,000,000ページ
   ログ出力時間＝2,000,000ミリ秒＝2,000秒
   → o = 2,000, q = 2,000

受験者が陥りやすいポイント

• 「1ページあたりの読み込み時間」にのみ注目して読み込み500秒、書き込みも500秒で合計1,000秒と誤算する。
  → 更新では「読み込み＋書き込み」で 1ページあたり 2ミリ秒 になることを忘れずに。
• 並行実行時の ログ出力は直列化 される点を見落とし、単体ジョブの 500秒のままとしてしまう。
• TB は「1ページ当たりの行数が 2 行」と TA と異なる点を混同する。

試験対策ポイント

• 行数→ページ数変換には，「行長」や「1ページ当たりの行数」 を必ず確認する。
• データ入出力：読み込み・書き込み両方 の時間を足す場合がある。
• ログ出力：1ページの更新で何ページのログ出力が発生するか 問題文を正確に読み取る。
• 並行処理の制約：同じディスク上での入出力は直列化，別ディスク配置の有無もチェックする。
• 実験式（T3）における MAX や加算の意味を正しく理解し，各要素の単位が揃っているかも確認する。

1. キーワード整理

• ログ先行出力の事象A～C
  • A：トランザクションが，コミットかロールバックを行った。
  • B：ログバッファが，ログページによって一杯になった。
  • C：データバッファが，変更データページによって一杯になった。
• SQL6A の仕様

  UPDATE TA SET C5 = C5 + 1 WHERE C1 BETWEEN 1 AND :hv
  （SQL5xと同様に，更新後にCOMMITを実行）

• バッファ容量

  バッファ種別	ページ数
  ログバッファ	2,000ページ
  データバッファ（テーブル単位）	2,000ページ

• TAテーブルの構成

  行長(バイト)	行数	データページ数	1ページ当たりの行数
  200	20,000,000	1,000,000	20

• ログ出力量

  データページの1ページ中の全行を更新するごとにログページが2ページ出力される。

2. 解答の論拠

1. 更新行数 N ≤ 1,000
2. データページ数 ≒ ⌈N÷20⌉ ≤ ⌈1,000÷20⌉ = 50 ページ
3. ログページ数 = 更新データページ数 × 2 ≤ 50 × 2 = 100 ページ

• 事象B（ログバッファが一杯）
  → ログページは100ページしか出力せず，ログバッファ(2,000ページ)を超えない。
• 事象C（データバッファが一杯）
  → 変更データページは50ページしかなく，データバッファ(2,000ページ)を超えない。

3. 注意点・ひっかけ

• コミット頻度の確認
  SQL6Aは更新後に必ずCOMMITしているため，事象Aは必ず発生します。
• バッファの単位
  「ページ数」で管理される点を押さえ，「バイト」換算しないよう注意。
• クラスタ索引による連続更新
  TAはC1にクラスタ索引を持つため，更新対象行は物理的に連続したページに存在し，
  各ページで全行更新→ログ出力量の計算が容易になります。

4. 試験対策ポイント

• ログ先行出力の発生条件A～Cを確実に暗記する。
• バッファサイズと1トランザクションでの更新量を比較し，
  「バッファがあふれるかどうか」を判断できるようにする。
• UPDATE～COMMITの流れと，**コミット時の処理（事象A）**を押さえる。
• **索引の種類（クラスタ／非クラスタ）**が性能にどう影響するか，演習問題で慣れておく。