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

1. キーワード・論点整理

• ネストループ結合（入れ子ループ法）：
  ‒ 外側表（オーソリ履歴）を表探索（全表順次読込み）
  ‒ 内側表（加盟店）を索引探索（ランダム読込み）
• データ入出力処理のモデル式：
  ‒ 同期データ入出力処理時間　＝ 各ページI/O時間×ページ数
  ‒ 非同期データ入出力処理時間　＝ 各ページI/O時間×ページ数
  ‒ SQL処理時間（外側順次読み込み）
    ＝ MAX(CPU時間, 非同期データ入出力処理時間)
• バッファヒット率：
  ‒ 索引バッファヒット率100％ → 索引ページはディスクI/O不要
  ‒ データバッファヒット率0％ → データページは全てディスクI/O
• 単位の変換：ページ数⇔バイト数⇔秒数

2. (a)～(h) の計算過程と解答

3. なぜこの解答になるのか

1. (a),(b) 非同期読み込みの計算
   問文より

   “オーソリ履歴” テーブルを外側…, アクセス経路は表探索だったので, (a)ページを非同期に読み込む。
   表1から「オーソリ履歴」の行数480億行・1ページ20行を使ってページ数を求め、ディスク転送速度100Mバイト/秒で時間を算出します。

2. (c) 結果行数
   問文より

   カード数を 1,000万枚, カード・月当たり平均オーソリ回数を80回…, 結果行数は (c)行
   単純に掛け算です。

3. (d),(e),(f) 内側表“加盟店”のI/O
   問文より

   外側の結果行ごとに “加盟店” テーブルの主キー索引を索引探索し, “加盟店” テーブルを1行, ランダムに合計(d)回読み込む。
   索引はバッファヒット率100%, テーブルはバッファヒット率0%と仮定すれば, “加盟店” テーブルを合計で (e)ページを同期的に読み込む。
   索引は全てキャッシュ内にあるためI/O不要。データ行は全てディスクからの読み込みで、1行≒1ページとして8億ページ。
   さらに「同期読込みにページ当たり1ミリ秒掛かる」とあるので時間を求めます。

4. (g) 内側CPU時間
   問文より

   内側の表の索引探索と結合に掛かる CPU 時間は,1 結果行当たり 0.01 ミリ秒…
   8億行×0.01ms。

5. (h) 全体SQL処理時間
   問文より

   外側の表の CPU 時間は96,000秒, 内側の表の CPU 時間は(g)秒, 内側の表の同期データ入出力処理時間は(f)秒なので, SQL文の処理時間を(h)秒と見積もった。
   外側表の非同期I/Oと外側CPUは並列化されるため重畳せず、MAX(96,000,96,000)=96,000秒。その後に内側のCPU／I/Oが直列加算されます。

4. 受験上の注意・ひっかけポイント

• 表探索（全表順次読込み）は非同期I/O、 索引探索（ランダムI/O）は同期I/Oと区別する点
• 単位変換ミス（ページ数⇔バイト数⇔秒）に注意
• 非同期I/Oでは「CPUとI/Oの重畳」を必ず意識し、MAX式を適用すること
• 内側と外側でI/Oモデルが異なる点に注意
• バッファヒット率の仮定を間違えると、I/O回数が大きく変わる

5. 試験対策ポイント

• ネストループ結合のコストモデル（外側スキャン＋内側ランダムアクセス）をおさえる
• 同期／非同期データ入出力処理時間の式を正確に覚える
• ページ単位、バイト単位、秒単位の換算に強くなる
• バッファヒット率の効果を理解し、I/O回数を正確に算出する
• 問文中の前提条件（ディスク速度、バッファヒット率、CPU時間モデル）を見落とさない

模範解答の論点整理

• コアキーワード：索引探索＝ランダムアクセス、表探索＝順次アクセス、同期／非同期I/O、バッファヒット率

解答が導かれる理由の詳細説明

1. 「加盟店」テーブル
   • 問題文：

     「外側の表の結果行ごとに “加盟店” テーブルの主キー索引を索引探索し、…ランダムに合計(d)回読み込む。」

   • 索引探索はページをランダムに読み込むため、同期データ入出力処理となり、バッファヒット率を上げることでディスクアクセス回数を減らし、待ち時間を短縮できる。
2. 「オーソリ履歴」テーブル
   • 問題文：

     「“オーソリ履歴” テーブルのアクセス経路は表探索だったので、(a)ページを非同期に読み込む。」

   • 表探索は先頭ページから順次に読み込む非同期データ入出力で、I/OとCPU処理を重ねて並行実行するため、バッファヒット率向上による性能改善効果がほとんどない。

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

• 「順次アクセス＝バッファ不要」と覚えた後に、
  →「非同期I/Oでもバッファ増強が効く」と誤解しやすい。
• 「索引探索なら必ず高速」と考え、バッファの有無を無視すると、ランダムI/O時の待機時間を見落とす。

試験対策として押さえるべきポイント

1. アクセス経路とI/O特性の対応
   • 索引探索＝ランダムI/O（同期的にCPU待ち）
   • 表探索＝順次I/O（非同期でCPUと重畳）
2. バッファヒット率の効果範囲
   • ランダムI/O時：ヒット率向上が処理時間短縮に直結
   • 順次I/O時：多少のヒットはあってもI/OとCPUの重畳により改善効果は限定的
3. 設問文中のキーワード探し
   • 「ランダムに読み込む」「同期データ入出力」「非同期データ入出力」を確実にキャッチすること
4. 字数制限の対策
   • 25字以内で「原因」と「効果」を簡潔に述べる練習をしておくこと。

キーワード整理

• 「副次索引（利用日列をキーとする索引）」
• 挿入時の索引更新コスト
• RDBMSの索引メンテナンス

解答の論拠

「INSERT 文で行を挿入するとき, RDBMS は...挿入行の主キー値に近い行が格納されているページに空き領域があれば...」
「行を挿入,更新、削除した場合, 変更内容がログとして RDBMS に一つ存在するログバッファに書き込まれる。」

1. 該当索引のツリー構造やハッシュ構造 に対してもノード（葉ページ）挿入や分割が発生し、
2. ログ出力 や ディスクI/O が増大し、
3. オーソリ処理（INSERT多重実行）の同期データ入出力処理時間やCPU時間 が増加します。

受験上の注意ポイント

• 索引を増やせばSELECTは速くなるが、INSERT/UPDATE/DELETEは遅くなる というトレードオフを必ず意識する。
• 問題文で「INSERT時の挙動」や「ログ出力のタイミング・条件」を読み落とすと、「副次索引追加＝一律改善」と誤解しやすい。
• RDBMSの仕様では、行挿入ごとに関連する全索引を更新し、変更ログを出力するため、索引追加は必ず負荷増の要因になる。

試験対策まとめ

• 索引のメリット／デメリット（検索高速化 vs. 更新遅延）を整理しておく。
• RDBMSのログ出力条件やデータ入出力方式（同期／非同期）の概念を押さえておく。
• 問題文で「INSERT」「UPDATE」「DELETE」の処理経路と、それに伴うI/O／CPU負荷の発生源を常に確認する。

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

• 「区分化」：テーブルを物理的な表領域に分割し，区分キーの値に基づいて行を分散配置する機能
• 案A/Bの区分キー：「カード番号」
• 案Cの区分キー：「利用日（1か月を1区分）」
• 課題1：オーソリ処理のINSERT文性能の低下改善
• 性能改善のポイント：挿入処理が集中する区分の分散化

2. 解答理由の論理展開

1. 問題文より，区分化の効果として「テーブルを検索する SQL 文の WHERE 句の述語に区分キー列を指定すると，区分キー列で特定した区分だけを探索する」ことが示されています。

   「レンジは, 区分キー値の範囲によって区分に行を分配する方法である。」
   「ハッシュは, 区分キー値を基に RDBMS が生成するハッシュ値によって一定数の区分に行を分配する方法である。」

2. 案A/Bは「カード番号」を区分キーとしています。カード単位では乱数的に散らばるため，複数の区分にINSERT行が分散します。
3. 一方，案Cは「利用日」で1か月を1区分としているため，オーソリ処理時の当月分INSERTは必ず同一区分に集中し，その区分の入出力やロックがボトルネックになります。
4. よって，「挿入される行が複数の区分に分散するから」が適切な理由です。

提案案の比較表

3. 受験上の注意・よくある誤答

• 「レンジだから分散しない」とだけ書くのは×
  → 案Cの特性は「利用日で1か月を1区分」にしている点が重要です。
• パーティション数やページ数の計算に引きずられて長文になると，字数制限（25字以内）を超えてしまいます。
• 「分散しているから」とだけの記載も不十分
  → 「挿入される行が複数の区分に分散する」と具体的に書くことがポイントです。

4. 試験対策ポイント

• 区分化（パーティショニング）の種類と特徴
  • ハッシュ：キー値に応じて均等分散
  • レンジ：値の範囲で分割
• INSERT性能を改善するには，ホットスポット（集中書き込み）が起きないよう区分キーを選ぶ
• 表探索／索引探索，同期／非同期入出力の特徴
• 設問字数制限に注意し，キーワードを漏らさず簡潔にまとめる

模範解答の論点整理

• 区分化方式
  • 案Cは「レンジ」による区分化で、区分キーは「利用日（1か月を1区分）」
• WHERE句による区分限定（パーティション・プルーニング）
  • 図3のWHERE句中に「A.利用日 BETWEEN :hv1 AND :hv2」があるため、該当する１区分だけを探索
• ページ数の計算
  • 「オーソリ履歴」テーブル全体のページ数は「24億」（表1より）
  • 60区分に均等に分配 ⇒ 1区分あたりのページ数 = 24億 ÷ 60 = 0.4億 = 40,000,000ページ

解答の根拠

1. 区分化の前提

   「いずれの案も 60 区分に行を均等に分配する前提であり、…」
   （問題文より引用）

2. 案Cの区分化

   区分方法	区分キー
   レンジ	利用日（1か月を1区分）

3. WHERE句による区分限定

   図3
   WHERE A.審査結果 = 'Y' AND A.利用日 BETWEEN :hv1 AND :hv2
   AND A.カード番号 BETWEEN :hv3 AND :hv4 AND A.加盟店番号 = B.加盟店番号
   （図3のWHERE句より引用）

   上記のように「利用日」によるBETWEEN句があるため、レンジ区分化された利用日列に対してパーティション・プルーニングが効き、該当月に対応する1区分のみを探索する。
4. 1区分あたりのページ数計算

   テーブル名	全体ページ数	区分数	1区分あたりページ数
   オーソリ履歴	24億	60	24億 ÷ 60 = 0.4億 = 40,000,000

• （イ）：1
• （ロ）：40,000,000

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

1. 区分キーとWHERE句の整合性
   • 案B（レンジ、区分キー：カード番号）は、WHERE句が「カード番号 BETWEEN …」だけなら区分限定が効くが、問題の図3ではWHERE句に「利用日 BETWEEN …」も含む。
   • 区分キーに含まれない列で絞り込んでもパーティション・プルーニングは起きず、全60区分を探索してしまう。
2. ハッシュ区分ではレンジ指定による限定ができない
   • 案A（ハッシュ、区分キー：カード番号）は、レンジ指定に対応しないため常に60区分全件走査になる。
3. ページ数の読み違え
   • 「表1」にある「ページ数」は24億。これを見落として「480億」や「9,600万」と混同しないよう注意。

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

• パーティション（区分化）方式
  • ハッシュ：特定の値による絞り込み（=）のみパーティション限定
  • レンジ：範囲指定（BETWEEN／<,>）による絞り込みで限定
• パーティション・プルーニングの条件
  • WHERE句に区分キー列の絞り込み条件が含まれていること
• 大量データのページ計算
  • ページ数 = 全行数 ÷ 1ページ当たり行数
  • 区分後の1区分あたりページ数 = 全体ページ数 ÷ 区分数
• 問題文の数値は正確に引用
  • 「24億」「60区分」「利用日 BETWEEN :hv1 AND :hv2」など、原文に忠実に扱うこと。

キーワードと論点

• ハッシュ区分化
• 探索区分の限定
• RANGE（レンジ） vs HASH（ハッシュ）
• パーティションプルーニング（区分プルーニング）

解答の理由

「ハッシュは, 区分キー値を基に RDBMS が生成するハッシュ値によって一定数の区分に行を分配する方法である。」
とあり、さらに
「SQL 文の WHERE 句の述語に区分キー列を指定すると, 区分キー列で特定した区分だけを探索する。」
とあります。

注意ポイント

• ハッシュ区分化は「等価条件（=）のみ」でプルーニング可能。
• **範囲検索（BETWEEN など）**で区分プルーニングできるのはレンジ区分化のみ。
• 「区分キー列に述語を指定すれば必ず区分のみ探索する」と誤解しない。

試験対策としてのまとめ

• ハッシュ区分化は等価検索向き、範囲検索ではプルーニングされず全区分を走査。
• レンジ区分化は範囲検索との相性がよく、BETWEEN によって不要区分を除外可能。
• パーティション（区分）化を設計する際は、想定する検索条件（等価なのか範囲なのか）をまず整理すること。

キーワード・論点整理

• 「区分化」：テーブルを区分キーの値に基づいて物理的に分割
• 「再編成」：テーブル又は区分を再編成することで，行を主キー順に物理的に並び替える（【RDBMS の主な仕様】1.(4)）
• 案Cの区分方法と区分キー

  案	区分方法	区分キー
  A	ハッシュ	カード番号
  B	レンジ	カード番号
  C	レンジ	利用日（1か月を1区分）

• 模範解答キーワード：
  「1区分だけを再編成すれば良いから」

解答解説

1. 再編成の単位は「テーブル又は区分」（【RDBMS の主な仕様】1.(4)）
2. 案Cは「レンジ」による区分化で、**利用日をキーに「1か月を1区分」**とする
   • そのため，ある月の再編成では当該月を格納する１つの区分だけを対象にできる
3. 他案との比較
   • 案A・Bはカード番号で区分化しており，どの区分にも全利用日のデータが分散
   • したがって月初の再編成でも全区分を再編成しなければならず時間がかかる
4. よって，「1区分だけを再編成すれば良いから」という解答になります

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

• 「区分化＝全体を分割するだけ」と誤解し，再編成も全区分に同時適用されると思い込む
• ハッシュ／レンジの違いを押さえず，カード番号レンジでも月別に区分できると勘違い
• 再編成はあくまで区分単位で実行可能である点を忘れない

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

• パーティション（区分化）はレイアウト（ハッシュ／レンジ）によってデータ配置の偏在性が変わる
• レンジ区分は連続した範囲に属するデータをまとめるため，期間単位のメンテナンスに有利
• 再編成（オーガナイズ）はパーティション単位に実行でき，必要最小限の処理範囲で性能改善が図れる
• 問題文の仕様番号（例：1.(3)ハッシュ／レンジ，1.(4)再編成）を指示通りに引用し説明すると説得力が高まる

模範解答のキーワード整理

• コミット待ち：トランザクションのコミットは「ログ出力処理の完了まで待たされる」
• 逐次化（非並列化）：ログ出力は一つのログバッファ／一つのディスクに順次書き込まれる
• ログバッファ制約：ログバッファが一杯だと各更新処理が待機

解答の論理的説明

1. 「ログバッファが一杯の場合, トランザクショインの INSERT文 UPDATE 文, DELETE 文の処理は待たされる」
   → 更新処理がログバッファ空き待ちでブロックされる可能性があります。
2. 「トランザクションのコミットはログ出力処理の完了まで待たされる」
   → コミットを行った瞬間，ログのディスク出力が終わるまで他の処理は先に進めません。
3. ログ出力は単一のログバッファから一つのディスクへ，トリガー事象ごとに 逐次 行われ，並列化されない

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

• 「ディスクI/O」や「CPU処理」をボトルネックに考えがちですが，更新処理ではログ出力が先行して行われ，しかも並列化できない点が重要です。
• 「データバッファ競合」ではなく，「ログバッファ／ログ出力」が競合ポイントであることを押さえましょう。

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

• トランザクション処理では，WAL（Write Ahead Logging） の仕組みから，変更ページより先にログをディスクに書き出す
• コミット待ち＝ログ出力完了待ちになるため，ログI/O性能が全体性能を制限する
• ログ出力は逐次処理のため，並列化してもスループットは頭打ちになる
• ログバッファサイズ，ログデバイスのI/O性能，グループコミットなどの対策を理解しておくこと

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

• ログ出力処理
• コミット時の待ち時間
• 待ち時間の合計が長くなる

解答解説

「ログは, データより先にログバッファからディスクに出力される。これをログ出力処理と呼ぶ。…トランザクションのコミットはログ出力処理の完了まで待たされる。」
「ログ出力処理は…② トランザクションがコミット又はロールバックを行った。を契機に行われる。」

• 1回あたりのログ出力処理時間　×　コミット回数

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

• 「データ入出力」の待ち時間と混同しやすい
  → 本問はログ出力処理（ログバッファ→ディスク）の待ち時間が増える話です。
• 「コミット＝データバッファのフラッシュ」と考えがち
  → コミット時に必ず実行されるのはログ出力処理であり、データバッファの書き込み（再編成やチェックポイント）は別タイミングです。

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

• コミット時には必ず「ログ出力処理」が走り、その完了を待ってから次に進む（同期処理）。
• コミット頻度を上げるとログ出力回数が増え、待ち時間の合計が大きくなる。
• バッチ更新では、まとめコミット（バルクコミット）で性能向上を図る。