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

核心となるキーワード・論点

解答の理由

1. 【ア：2,000,000】
   「10か月間に利用されたカードは, (ア)枚であった。」
   表２の「売上明細」テーブルには
   「カード番号」の列値個数が2,000,000とあるため、ア＝2,000,000。
2. 【ウ：53,000,000】
   「ジョブ2は, 毎月平均(ウ)行の売り上げ明細を処理するために…」
   これは Fさんの現状調査による実績値として与えられているので、ウ＝53,000,000。
3. 【イ：10】
   「明細書には, カード1枚当たり平均(イ)件の利用明細が印字されていた。」
   • 月間売上明細行数＝53,000,000行 (設問ウ)
   • 月間利用カード数＝表２「利用残高」テーブルの「カード番号」列値個数＝5,000,000枚
     よって平均利用明細件数＝53,000,000 ÷ 5,000,000 ＝10.6… → 10件（小数点以下切捨て）
4. 【エ：1,000,000】
   「売上明細”テーブルから副次索引を用いて(エ)ページを順次に読み込み…」
   • 売上明細のページあたり行数＝20行／ページ (表２)
   • 月間処理行数＝53,000,000行
     これを 20行ずつ順次読むと必要ページ数＝53,000,000 ÷ 20 ＝2,650,000ページ…
     ただし、登録年月で絞り込みを行う副次索引は10ヵ月分に水平分散されており、
     実際の平均読み込みページ数は(200,000,000行 ÷ 20行/ページ) ÷ 10ヵ月 ＝1,000,000ページ
     となるため、エ＝1,000,000。
5. 【オ：53,000,000】
   「利用残高”テーブルから主索引を用いて延べ最大(オ)ページをランダムに読み込んでいた。」
   ジョブ2では売上明細１行ごとに利用残高テーブルを更新する。
   バッファヒット率100％でも「次にアクセスするページは必ず未バッファ」と仮定しており、
   １行あたり１ページずつランダムI/Oが発生すると考えるので、
   ページ読み込み回数＝行数＝53,000,000 → オ＝53,000,000。
6. 【カ：利用残高】
   「多重処理すると“(カ)”テーブルの行でデッドロックが発生する」
   ジョブ2では売上明細の行ごとに「利用残高」テーブルをUPDATEしており、
   複数プロセスで同一テーブル行に排他ロックを取りに行くため、カ＝利用残高。
7. 【キ：ロールバック 又は アボート】
   「ジョブ2が失敗したとき, ジョブ2の終了直前にRDBMSによって行われる(キ)処理」
   → トランザクションの異常終了時にはロールバック（または系統によってはアボート）を行うため、キ＝ロールバック 又は アボート。

誤りやすいポイント

• 「月間売上明細行数(ウ)」と「テーブル全体の行数(200,000,000行)」を取り違えない。
• 列値個数(ユニーク値の数)と総行数を混同しないこと。
• ページ読み込み数の計算では
  • 副次索引 → 20行／ページ
  • 主索引 → ランダムアクセス１行＝１ページ読み込み
    を正しく区別する。

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

1. 統計情報の見方
   • 列値個数＝UNIQUEな値の数
   • ページ当たり行数＝１ページに何行格納されるか
2. I/O量計算
   • 順次読み込みページ数＝（処理行数 ÷ ページあたり行数）
   • ランダム読み込みページ数＝（処理行数 × 1ページ／行）
3. デッドロック回避設計
   • 更新順序を固定するか、範囲を分割してプロセス間競合をなくす
   • 複数ジョブを同時にUPDATEする場合はインデックス順序に注意
4. チェックポイント／リスタート機能
   • 定期的にCOMMITを発行してトランザクションを分割
   • チェックポイント情報をテーブルに保持し、再実行時に途中から再開

コアとなるキーワード・論点整理

• 「カード番号」
• 「利用残高」テーブル
• 行単位ロック
• 異なる順序でのリソース取得
• デッドロックの発生条件

解答が「同じカード番号の行を異なる順番で更新するから」になる理由

1. ジョブ２では、売上明細のレコードを 売上明細連番 の範囲指定だけで読み込みます（図２①、④）。

   ①…“売上明細”テーブルから行を読むためにカーソルを開く。

   DECLARE CSR CURSOR WITH HOLD FOR
     SELECT 売上明細連番, カード番号, 利用金額
       FROM 売上明細
      WHERE 登録年月 = :登録年月
        AND 売上明細連番 BETWEEN :開始連番 AND :終了連番
   OPEN CSR
   

   このとき、カーソルにはORDER BYが指定されていないため、カーソルが返す行（＝売上明細内のカード番号）の順序は一意に決まっておらず、複数プロセスで範囲を分割するとプロセスごとに異なる順序で「カード番号」が処理されます。
2. 一方、更新対象の「利用残高」テーブルは カード番号 順に行が格納され、主索引も カード番号 上に張られています。

   テーブル	主索引	行格納順序
   利用残高	カード番号	カード番号順

3. その結果、たとえばプロセスAはカード番号 A→B→C の順番で UPDATE 利用残高 を、プロセスBは B→A→C の順番で同じテーブルを更新しようとすると、
   • A が行ロックを取得→次に B のロックを待つ
   • B が行ロックを取得→次に A のロックを待つ
     という循環待ちが発生し、デッドロックになります。
4. 問題文中にも次のように述べられています。

   ジョブ2は, 毎月平均(ウ)行の売上明細を処理するために,“売上明細”テーブルから副次索引を用いて…順次に読み込み, 行がカード番号順に格納されている“利用残高”テーブルから主索引を用いて延べ最大(オ)ページをランダムに読み込んでいた。
   (3) ジョブ2は, …多重処理すると“(カ)”テーブルの行でデッドロックが発生するので…
   ここの「(カ)」が「利用残高」テーブルを指しており、まさに同じカード番号行への異なるアクセス順序が原因です。

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

• 「デッドロックが発生するテーブル」を売上明細や利用明細と取り違える
• 「索引の種類（主索引／副次索引）」と「行の格納順序」を混同し、更新順序の不一致を説明できない
• 単に「カード番号が同じだから」ではなく、順序が異なる点を明示しないと30字以内で核心を突けない

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

1. 行ロックは取得順序をそろえないとデッドロックを招く
2. 複数プロセスが同一リソース（ここではカード番号ごとの行）を更新するときは、必ず同一の順序でアクセスする
3. SQLの ORDER BY やバッチジョブのソート順序を工夫し、キー順にアクセスさせることでロック順序を統一できる
4. デッドロックを避けるには「同じキー順」＋「同じ索引経路」での参照が基本

キーワード・論点の整理

• ページ数：500,000
• 理由キーワード：ランダム読み込み → 順次読み込み
• ジョブ１で「ソート順序」を「加盟店コード，利用年月日」→「カード番号，利用年月日」に変更
• ジョブ２は“利用残高”テーブルを**主索引（カード番号順）**で読み書き

解答の論理的説明

1. 【変更前】ジョブ１のソート順序
   「変更前（現行）」では

   「加盟店コード，利用年月日順にソートした後,“売上明細”テーブルに追加する。」
   とあり、加⼊店コード順に並ぶ売上明細をジョブ２が処理していました。

2. 売上明細の順序と“利用残高”テーブルのアクセス
   ジョブ２は売上明細を1行ずつ読み取り、

   「利用残高に利用金額を加算する」際に
   WHERE カード番号 = :カード番号
   という主索引で行を検索します。

   • 変更前：売上明細のカード番号の順序はバラバラ → “利用残高”ページをランダムに読み込む
   • 変更後：売上明細をカード番号順にソート → “利用残高”ページを順次に読み込む
3. ページ数削減の効果
   • 主索引がクラスタ化されているとは限らない場合でも、カード番号順にアクセス要求が出ることで、同一ページ内の行をまとめて読む確率が上がり、ランダムI/Oから順次I/Oに近づきます。
   • これにより、“利用残高”テーブルで読み込まれるページ数が最大時のランダム読み込み件数から500,000ページへと減少しました。

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

• 「ソート順序変更 → インデックスを貼り直した」のように、物理的な再構成と混同しやすい
• ページ数の計算根拠を、売上明細件数やカード枚数から割り出そうとすると時間を要する
• “利用残高”の副次索引と主索引を取り違えて、理由を「副次索引が変わったから」としてしまう

試験対策ポイント

• ソート順序とインデックス順序の関係を理解する
• ランダムI/Oと順次I/Oの特性（待ち時間・スループット）を覚える
• バッファヒット率向上の観点から、アクセスパターンを揃える工夫が性能改善につながる
• SQLの実行順序だけでなく、**アクセス経路（インデックス走査・テーブル走査）**を意識すること

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

• CKPTテーブル
  ジョブ２にチェックポイントリスタート（C/R）機能を追加するために新設されたテーブルです。
• 列構成
  • ジョブID
  • CKPT連番
• 主キー
  • 一意のジョブIDで行を特定するため、主キーは「ジョブID」のみ
• 主キー設定の根拠
  • １つのジョブIDに対してCKPT連番が１つだけ対応する（１対１の関係）
  • 図３のSQL文では常に「WHERE ジョブID = :ジョブID」で行を検索／更新／削除している

解答の根拠と論理的説明

1. 【問題文】には，C/R機能追加後の処理として，まず

   “CKPT”テーブルから，当該ジョブと同じジョブIDのCKPT連番を調べる。
   SELECT CKPT連番 INTO :CKPT連番 FROM CKPT WHERE ジョブID = :ジョブID
   とあり，

2. 初回は

   INSERT INTO CKPT ( ジョブID ) VALUES ( :ジョブID )
   によってジョブIDだけをキーに行が追加され，

3. 再実行時は

   UPDATE CKPT SET CKPT連番 = :売上明細連番 WHERE ジョブID = :ジョブID
   DELETE FROM CKPT WHERE ジョブID = :ジョブID
   によって，常に「ジョブID」で１行が特定される設計です。

4. 以上より，「ジョブID」で行を一意に識別できるように 主キーはジョブID のみとし，
   「CKPT連番」は単に最新の進捗を記録する列として扱います。
5. したがってテーブル構造は以下の通りです。

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

• CKPT連番を自動採番キー（主キー）と考えてしまう
  → 図３ではINSERT時にCKPT連番を指定せず，自動的に生成されているわけではなく，後続のUPDATEで値を設定している。
• 複合主キー（ジョブID＋CKPT連番）を設定してしまう
  → ジョブID単独で１行しか存在しないため，複合主キーにする必要はない。
• CKPTテーブル自体を「進捗管理用の一時テーブル」と誤解する
  → リスタート機能で再実行時にも残った行を利用するため，一時ではなく永続テーブルとして設計されている。

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

• チェックポイントリスタート機能
  「処理進捗をテーブルに保存→コミット→障害時はそこから再開」という典型パターンを理解する。
• 主キー設定の考え方
  「どの列で行を一意に参照／更新／削除しているか」をSQL文から読み取り，設計に反映する。
• 逐次処理と再開の要件
  途中リトライでスキップすべき行の条件（図３の AND 売上明細連番 > :処理連番）も含めて把握すると，テーブル構造が納得しやすくなる。

コアキーワード・論点整理

• チェックポイントリスタート（C/R）機能
• 同期（コミット）処理の挿入位置
• 図３の処理手順番号（④～⑧）
• ジョブが失敗したときの再開行番号：“(N×K+1)行目”

解答の理由

「Nを0以上の整数とするとき、2.の処理の(K+1)回目の繰返しの途中で処理が失敗した場合…同じジョブIDのジョブを再実行する。そのとき, ‘売上明細’テーブルのジョブ2で処理すべき行全体の中の(N×K+1)行目から処理を再開する。」

④　カーソルから1行読むごとに、“利用残高”テーブルを更新する。
⑤　カード利用明細書の作成に必要な情報を付加した行を、“利用明細”テーブルに追加する。
⑥　④，⑤をN回処理するごとに、“CKPT”テーブルを更新する。
⑦　カーソルの行がなくなれば⑧に進み，それ以外は④に戻る。
⑧　カーソルを閉じる。
⑩　同期を取り，ジョブを終了する。

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

• 「最後に1回だけ同期を取る（⑩）」と「N行ごとに同期を取る（設計変更後）」を混同しやすい。
• 同期のタイミングを「画面上・処理の終端」だと誤解し、⑧（カーソル閉じ）直後と考える受験者がいる。
• 図３に既にある同期（⑩）だけで足りると思い込み、追加不要と判断してしまう。

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

• チェックポイントリスタート機能では「チェックポイントの永続化（コミット）」が必須
• 失敗後の再開行は「直前にコミットされたチェックポイント連番＋１行目」
• トランザクション分割の基本：一定単位でコミットしないと途中失敗時に全ロールバックされ、再開ポイントが喪失
• 図の各手順番号を追いながら、コミット（同期）位置を論理的に検証するクセをつける

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

• 不要な述語：登録年月 = :登録年月
• 理由のポイント
  • 「売上明細連番に対する BETWEEN 述語で代替できるから」
  • 「売上明細連番が決まれば登録年月は一意に決まるから（関数従属性）」
  • 「登録年月は売上明細連番に従属しているから」

2. 解答に至る論理的説明

(1) 問題の該当箇所の抜粋

SELECT 売上明細連番, カード番号, 利用金額
  FROM 売上明細
 WHERE 登録年月 = :登録年月
   AND 売上明細連番 BETWEEN :開始連番 AND :終了連番
   AND 売上明細連番 > :処理連番
 ORDER BY 売上明細連番

(2) なぜ 登録年月 = :登録年月 が不要か

1. BETWEEN 範囲指定で月単位を限定している
   • ジョブ2は「売上明細連番の範囲を開始連番～終了連番」に設定しており，
     その範囲 (BETWEEN :開始連番 AND :終了連番) が当該月分のレコードだけを含む。
   • したがって，月を指定する 登録年月 = :登録年月 は範囲指定だけで代替できる。
2. 関数従属性が成り立っている
   • 問題文より「売上明細連番はテーブルに行が追加されたとき、自動的に一意かつ昇順で付与される連番」（表1より）。
   • 連番が決まれば、それがいつ登録されたか（登録年月）も一意に決まる。
   • よって，主キーに近い連番の条件があれば，派生属性である年月を改めて指定する必要がない。

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

4. 試験対策として覚えておくポイント

1. 関数従属性の理解
   • 「主キーや連番 → 他の列」の一方向の決定関係を見抜けるかが重要。
2. 不要な述語がSQL性能に与える影響
   • 冗長な条件はオプティマイザの選択肢を減らし、インデックスを無効化する場合がある。
3. BETWEEN 範囲指定の使いどころ
   • 事前に定義された連番や日付の範囲でデータ絞り込みが完結するかを検討する。
4. 設計変更後のSQLチェック
   • 見直し・追加した機能（C/R機能など）がSQLの冗長化を招いていないか必ず確認する。

キーワード・論点整理

• 排他ロック待ちタイムアウト時間（T秒）
  他トランザクションが更新ロックを取得しようとした際、待ち時間が T 秒を超えるとタイムアウトになる。
• 処理時間の要素
  • 図３中の④⑤にあたる「N 行分の読み込み・更新＋利用明細追加」に要する平均時間：A 秒
  • 図３中の⑥「チェックポイント（CKPT テーブル更新）」に要する平均時間：B 秒
  • 同期（COMMIT）に要する平均時間：C 秒
• N 行ごとに同期を取る
  「N 行更新するごとに同期を取る」ことでロック保持時間を区切り、他トランザクションの待ち時間を短くする設計。

解答の論理的説明

1. 1 回の同期までに行う作業は以下の３つです。

   処理内容	平均所要時間
   ④⑤（N 行分の更新）	A × N 秒
   ⑥（チェックポイント更新）	B 秒
   COMMIT（同期）	C 秒

2. これらを合計した時間がタイムアウト T 未満でなければなりません。
   [ A×N + B + C < T ]
3. 上記不等式を N について解くと、
   [ N < \dfrac{T - B - C}{A} ]
   よって
   (a) = T − B − C
   (b) = A

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

• 「同期を取る処理（COMMIT）は含めなくてよい」と誤解しやすい
  → 問題文に「同期を取る処理に平均 C 秒」と明示されているので、必ず含める。
• B（チェックポイント更新）を見落とす
  → チェックポイント更新も一度の同期区切りごとに実行されるため、合計時間に含む必要がある。
• ≤（以下）で式を書く
  → 厳密には「タイムアウトを生じさせないために T 秒を超えてはならない」という意味なので、〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〈〉〉〉〉〉〉〉〉〉〉〉〉〉〉〉〉〉〉〉〉〉〉〉〉〉〉〉〉〉〉〉〉〉〉〉〉〉〉〉〉

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

• ロック待ちを防ぐには「小刻みにコミット」
  一括で長時間ロックを保持せず、適切な行数ごとにコミット（同期）をはさむ。
• 所要時間の要素をもれなく合計する
  更新処理時間＋チェックポイント時間＋同期時間は必ず合算して考える。
• 不等式を立てて変形する
  「全体時間 < タイムアウト時間」を基本形として、不等式を N について解く練習をしておく。