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

キーワード・論点整理

• 統計情報の定義
  「各テーブルの列値の個数は, 統計更新処理時点の各列に存在する異なる値の個数である。」（問題文〔RDBMS のアクセス経路に関する主な仕様〕3項）
• カテゴリテーブルの構成
  「カテゴリ1は, 筆記具, 用紙などの大分類(100 種類)を表す。」「カテゴリ1が筆記具の場合, カテゴリ2は鉛筆, ボールペンなどである。」（問題文〔販売商品の概要〕）
• 他テーブルの統計情報との関係
  「商品分類」テーブルの統計情報では，C1番号の列値の個数=100，C2番号の列値の個数=500（表2）

解答の導出

1. 「カテゴリ番号」の列値の個数＝600（＝a）
   • PK であるカテゴリ番号は全行で異なる（表2 の「列値の個数＝行数」）。
   • カテゴリ1の行数＝100、カテゴリ2の行数＝500 なので，全体の行数＝100＋500＝600。
   • よって a＝600。
2. 「親カテゴリ番号」の列値の個数＝100（＝b）
   • 「親カテゴリ番号」はカテゴリ2 の各行にのみ値が設定され，カテゴリ1 の行は NULL。
   • かつ，各カテゴリ2 の親は必ずいずれかのカテゴリ1（一意の100種）なので，異なる値の個数はカテゴリ1 の種別数＝100。
   • よって b＝100。

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

• カテゴリ1 とカテゴリ2 の区別不足
  カテゴリテーブルの行数を「100」と誤答しやすいですが，「カテゴリ」テーブルには両階層の合計が登録されており，行数は600です。
• NULL 値の扱い
  「親カテゴリ番号」の列値の個数に NULL は含まれず，カテゴリ2 のみを対象とする点を押さえておく必要があります。
• 他テーブルの値との混同
  「商品分類」テーブルの C2番号（500）と混同し，「親カテゴリ番号＝500」と誤解しないよう注意してください。

試験対策におけるポイントまとめ

• 統計情報の「列値の個数」は NULL を除く異なる値の数 であることを正確に理解する。
• 階層構造を持つテーブルでは，各階層の行数や NULL 設定行を意識して合計・区分けを行う。
• 表2 の他のテーブルや「商品分類」テーブルの統計情報を横断的に参照し，値の関係性を読み解く練習をする。

キーワード・論点整理

• 統計情報：表2に記録されている「行数」「列値の個数」を利用
• オプティマイザの仮定（問題文より引用）

  (2) 列値当たりの行数は均等である。

• 平均結果行数の算出方法
  • 単一テーブル検索（＝述語が「=」の場合）
    平均結果行数 ＝ （テーブル全体の行数） ÷ （対象列の列値の個数）
  • 結合処理
    ＝ 片方の検索結果行数 × もう片方の平均付随行数

平均結果行数の計算過程

(c) SQL2 の計算

SELECT * FROM 注文
 WHERE 顧客番号 = :h
 ORDER BY 注文年月日 DESC

• 「顧客番号 = :h」という等価検索なので、
  平均結果行数 ＝ 2,700,000 ÷ 300,000
  ※ 統計情報（表2）とオプティマイザの仮定(2)より
• 計算すると
  2,700,000 ÷ 300,000 = 9
• よって c＝9

(d) SQL3 の計算

SELECT * FROM 注文明細
 WHERE 注文番号 = :h

• 「注文番号 = :h」という等価検索なので、
  平均結果行数 ＝ 27,000,000 ÷ 2,700,000
• 計算すると
  27,000,000 ÷ 2,700,000 = 10
• よって d＝10

(e) SQL4 の計算

SELECT * FROM 注文 X, 注文明細 Y
 WHERE X.注文年月日 = :h
   AND X.注文番号 = Y.注文番号

1. 注文テーブル側の絞り込み行数
   • 「注文年月日 = :h」の等価検索
     → 平均行数 ＝ 2,700,000 ÷ 90 = 30,000
2. 注文明細との結合後行数
   • 1件の注文あたり平均10行の明細が付随
     （SQL3 と同じ計算結果）
   • よって結合後の平均行数 ＝ 30,000 × 10 = 300,000

• よって e＝300,000

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

• 「列値当たりの行数は均等」の仮定に基づく計算を忘れる
• 結合時の平均付随行数（ここではSQL3の10）を掛け合わせるのを見落とす
• SQL4を「単一テーブルの検索」として誤って解く

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

1. オプティマイザの仮定項目を正確に把握する
   • 特に「均等分布」を使った行数推定
2. 等価検索(=)の平均結果行数は「行数 ÷ 列値の個数」
3. 結合時は「片方の絞り込み行数 × もう片方の平均付随行数」
4. 統計情報の「行数」「列値の個数」はシステムカタログから取得
5. 同様の問題が出たら、必ず表2の数値を表に抜き出して整理することでミスを防ぐ

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

• 列値の個数：
  「各テーブルの列値の個数は, 統計更新処理時点の各列に存在する異なる値の個数である。」
• 注文明細テーブルの「注文明細番号」：
  注文明細テーブルでは，ある注文内の明細を識別する番号として「注文明細番号」が持たれており，1件の注文ごとに1から付与される。
• 最大明細行数：
  ある注文（同一の注文番号）に対して付与された「注文明細番号」の種類数（異なる値の個数）が，１注文あたりに発生しうる最大の明細行数を示す。

2. 解答が「48行」になる理由

1. 統計情報の定義（問題文より引用）

   「各テーブルの列値の個数は, 統計更新処理時点の各列に存在する異なる値の個数である。」

2. 注文明細テーブルの統計情報（表２より該当部分を抜粋）

   テーブル名	列名	列値の個数
   注文明細	注文明細番号	48

3. 考え方
   • 「注文明細番号」は，１つの注文（同一の注文番号）内で，明細行を1から連番で識別する番号。
   • 異なる注文明細番号の数が，その注文に対して実際に登録され得る最大行数を示す。
   • 統計情報上の「列値の個数＝48」は，システムに存在する全注文を横断したうえで
     ある注文で最大48種類の注文明細番号が付与されていることを意味する。
   • したがって，１つの注文で発生する明細行数の最大は「48行」となる。

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

• 注文番号の列値の個数（2,700,000）を誤って使う
  注文番号の異なる値の個数は「2,700,000」ですが，これは注文の件数そのものを表しており，１注文あたりの明細数ではありません。
• 商品番号の列値の個数（60,000）を誤って使う
  注文明細テーブルの「商品番号」は，注文した商品品目の種類数を表していますが，これも１注文あたりの明細上限ではありません。
• 「明細番号」は注文全体で通し番号と勘違いする
  本設計では「注文明細番号」はあくまで同一注文内で1から付与されるため，「最大連番の数＝行数」と読み取る必要があります。

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

• 統計情報の「列値の個数」は異なる値の数
  → 比較演算やグループ化の結果行数、１グループあたりの最大件数などを推定する際に用いる。
• 明細番号は“グループ内連番”であることを確認
  → １つのキー（注文番号）に対して連番で付与される列は，その列値の最大種類数が「１グループあたりの最大行数」となる。
• 表から必要な統計情報を正しく読み取る習慣
  → 試験では大量の統計情報表が示されるため，表の見出し（テーブル名，列名，列値の個数）を確認し，設問に対応する列を取り出す力が重要です。

キーワード・論点整理

• ページング（10行ずつ画面表示）
• 前回取得した最終行の商品番号を使い、次回は「商品番号 > '00051'」で開始
• 結果の並び順は ORDER BY C1番号, C2番号, 商品番号
• 次ページ取得のSQL（表3のSQL5）では、C1番号で絞り込み、商品番号で開始位置を指定
• 表4はSQL1の取得結果（C1番号='P1' の全800行中の一部を抜粋）

解答の論理的説明

1. 【問題文】より引用

   「Fさんが表4の取得順で示した11行目以降を取得するために表3のSQL5を実行したところ、最初の3行の取得順は次のようになった。」
   「ORDER BY C1番号, C2番号, 商品番号」

2. 表4から、C1番号='P1' かつ 商品番号 > '00051' を満たす行を並び順どおりに見ると、次のようになります。

   取得順	C1番号	C2番号	商品番号
   11	P1	AA1	00082
   12	P1	AA2	00017
   13	P1	AA2	00055
   14	P1	AA2	00072
   …	…	…	…

3. ここで 商品番号>'00051' の条件から、取得可能なのは
   • 00082 (11行目)
   • 00017 (12行目) は除外（'00017' < '00051'）
   • 続いて 00055 (13行目)、00072 (14行目)…
4. よって、SQL5の取得結果の最初の３行は
   1. 00082
   2. 00055 → (ア)
   3. 00072 → (イ)
5. 従って、(ア)=00055, (イ)=00072 となります。

誤りやすいポイント

• 「文字列比較」であることを忘れ、数値の 17 を51以上と誤解しないこと
• ORDER BYのキー順序（C1 → C2 → 商品番号）を正しく理解すること
• 「次ページ」のSQLで前回の最終レコード以降を取りたい場合、開始位置の商品番号のみをWHEREに指定し、並び順と合わせる点

試験対策として覚えておくべきこと

• ページング機能実装時は、前回最終行のソートキーすべて（本問ではC1番号, C2番号, 商品番号）のうち、開始したい位置以下の行を除外する条件を満たすようにWHERE句を組む
• 文字列型の列に対する大小比較は、文字列辞書順で判断される
• ORDER BYのキーを決めたら、それに一致する索引を設計すると性能向上につながることを抑えておくこと

キーワードと論点の整理

• 主索引のキー順序
  表示順序と同じ ORDER BY C1番号, C2番号, 商品番号 に対応する主索引が存在する（表２の「索引１」）。
• キーセット方式（keyset pagination）
  前ページの末尾のキーを基準に、次ページ分の行を効率よく取得する方法。
• タプル（複数列）のレキシコグラフィカル比較
  (C1, C2, 商品番号) > (P1, AA1, 00051) を列単位の比較条件に分解する。
• WHERE句の組み立て
  等価比較と順序比較を使い、かつ AND／OR の優先度を正しく扱う。

解答が導かれる論理的な説明

1. 条件の前提
   問題文より、SQL5 の ORDER BY は

   ORDER BY C1番号, C2番号, 商品番号
   

   であり、かつ WHERE で C1番号='P1' が固定されているため、
   次のページ先頭の行も C1番号='P1' を持つ。
2. 前ページ末尾のキー値
   表4で１ページ目の最後の行が

   (C1番号='P1', C2番号='AA1', 商品番号='00051')
   

   である。
3. タプル比較を列分解する
   タプル (C2, 商品番号) > ('AA1', '00051') を次のように展開する。
   – C2 = 'AA1' かつ 商品番号 > '00051'
   – または C2 > 'AA1'
   これに C1番号='P1' を組み合わせれば、ページ切り替え用のWHERE句となる。
4. 最終的な WHERE句

   WHERE C1番号 = 'P1'
     AND (
       (C2番号 = 'AA1' AND 商品番号 > '00051')
       OR C2番号 > 'AA1'
     )
   

   これなら、同じカテゴリ２（AA1）の続きも、さらに後のカテゴリ２（AA2 以降）も漏れなく取得できる。

模範解答の再掲

SELECT * 
FROM 商品 
WHERE C1番号='P1'
  AND (
    (C2番号='AA1' AND 商品番号>'00051')
    OR C2番号>'AA1'
  )
ORDER BY C1番号, C2番号, 商品番号;

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

1. 商品番号だけで範囲検索を行う
   商品番号>'00051' のみだと、同一カテゴリ２（AA1）の後続は取れるが、AA2 以降が抜ける。
2. カテゴリ２だけで比較する
   C2番号>'AA1' のみだと、AA2 以降は取れるが、AA1 の中の商品（商品番号 00052 以降）が抜ける。
3. AND／OR のかっこの抜け
   AND と OR の優先順位を意識せずに書くと、想定外の行が返るか、最適化時に索引が使えなくなる恐れがある。

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

• 主索引のキー順に合わせた範囲検索
  ORDER BY と同じキーカラム順で WHERE 範囲を組み立てれば、索引範囲検索（range scan）が可能。
• キーセット方式（offset を使わないページング）
  前ページの最終行キーを次ページの開始条件に使い、全行スキャンを避ける。
• 複数列タプル比較の分解ルール
  (A, B) > (a, b) → (A = a AND B > b) OR (A > a)
• AND/OR の優先順位
  必要に応じて明示的にかっこを書く。
• 統計情報とインデックスを意識した SQL 設計
  期待するアクセス経路（表探索／索引探索）にマッチする書き方を心がける。

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

• SQL6
    SELECT * FROM 仮注文 WHERE 仮注文番号 = :h ORDER BY 仮注文番号, 仮注文明細番号
• アクセス経路の切り替わり条件
  「行数がゼロの場合、表探索が最適である」（仕様7-(3)）
• 保守処理の順序
  毎朝の削除処理 → テーブル再編成 → 統計更新処理
• 模範解答
  SQL：SQL6
  理由：
  ・“仮注文”テーブルの全行が削除された直後に統計更新処理を行ったから
  ・“仮注文”テーブルの統計情報の行数がゼロになるから

2. なぜ SQL6 か――論理的説明

1. オプティマイザの仮定（仕様7-(3)）
   「行数がゼロの場合、表探索が最適である」

   仕様7-(3)「行数がゼロの場合,表探索が最適である。」

2. Fさんの見直し後の毎朝処理
   1. “仮注文”テーブルの不要な行を DELETE
   2. テーブル再編成 → 統計更新処理
      → 更新後に「行数＝0」がカタログに記録される
3. SQL6 の対象テーブル

   SQL	対象テーブル	統計情報（更新後）
   SQL6	仮注文	行数 = 0

4. その結果
   • オプティマイザは「行数 = 0 → 表探索が最適」と判断
   • 従来の索引探索から表探索に変わる

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

• 「DELETE → 統計更新」は全テーブルに行われるが，行数0になるのは仮注文テーブルのみ
• 注文／注文明細テーブルは削除後も行数が数百万単位で残るため，索引探索が維持される
• 商品照会(SQL1, SQL5)や更新(SQL7)の対象は「商品」「在庫」で，ここは行数0にならない

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

1. オプティマイザの仮定
   • 統計情報は最新とみなし，行数や列値数は均等分布と仮定
   • 行数=0 → 表探索が最適という特例
2. 保守処理と統計更新のタイミング
   • DELETE → REORG（再編成）→ 統計更新 の順序で行うと統計に反映される
   • 統計更新後の行数を正しく把握する
3. テーブルごとの行数を押さえる
   • 行数ゼロのテーブル → 表探索
   • 一部行のみ削除 → 行数は大きく減少しても0ではない → 索引探索継続
4. 問題文中の「仕様番号」「設問番号」を参照しながら条件を確認
   • 特に仕様7-(3)の「行数がゼロ」の文言を正確に覚えること

キーワードと論点整理

• 表探索（フルテーブルスキャン）
  「表探索は，索引を使わずに先頭のデータページから全行を探索する」（問題文※5）。
• 索引探索（インデックススキャン）
  「WHERE 句中の述語に適した索引によって行を絞り込んでから，データページ中の行を探索する」（問題文※5）。
• 読み込む行数の増加
  “仮注文”テーブルの行数は「9,000,000」と多く，「蓄積されるので表探索によって読み込む行数が増える」（模範解答）。

解答の論理的根拠

1. アクセス経路の違い
   問題文※5より：
   「表探索は…先頭のデータページから全行を探索する」
   「索引探索は…述語に適した索引によって行を絞り込む」
   → 表探索では全行を読みに行くため，絞り込み効率が非常に低い。
2. “仮注文”テーブルの大規模性
   表2にある統計情報より：“仮注文”テーブルの行数は9,000,000行。
   索引探索なら「WHERE 仮注文番号 = :h」で目的の行（仮注文明細数：平均24行）だけを読み込めるが，
   表探索になると9,000,000行すべてを読み込んでから条件に合う行を探すことになる。
3. 処理時間への影響
   SQL6（「SELECT * FROM 仮注文 WHERE 仮注文番号 = :h…」）は，
   仮注文に対して1画面分の処理ごとに何度も実行される。
   表探索で毎回9,000,000行を読み込むため，実行するたびに読み込み行数が膨大となり，
   処理時間が長くなる。

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

• 「表探索だからインデックスのアクセス順が変わる」とだけ答えてしまう。
  → 真に問題なのは「読み込む行数が増え，I/O時間が増大する」ことである。
• “仮注文”テーブルの「仮注文明細番号＝24行」だけを読むと誤解し，
  本来は全行スキャンが行われる点を見落としやすい。

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

• 表探索 vs 索引探索の特徴とコスト差を押さえる。
• 統計情報（行数・列値の個数）に基づくオプティマイザの判断を理解する。
• 大規模テーブルでのフルスキャンはI/O負荷が著しく高いため，
  適切な索引利用やSQLの見直し（ページング取得など）が必要。

キーワード・論点整理

• アクセス経路の決定は「統計情報」に基づき、オプティマイザは「統計情報はテーブルの最新状態を反映したものである」と仮定している（仕様4、仕様7(1)）。
• 統計更新処理（C）を行うとシステムカタログに最新の行数や列値の個数が記録される。
• DELETE処理（A）やテーブル再編成（B）を行った後に統計更新を行うと、削除済み行や再編成後のページ削減を反映した統計が記録され、場合によってはアクセス経路（索引探索／表探索）の選択が変わる可能性がある。
• 切り替え前の高い行数を保持した統計のまま運用することで「索引探索」を維持し、パフォーマンス劣化を防ぐ。

解答の論理的説明

1. A（不要な行の削除）→
2. B（再編成）→
3. C（統計更新処理）
   の順序で実施すると、C では A・B の結果（行数減少／空きページ削除）を反映した統計がカタログに上書きされます。
   その結果、行数が極端に少ない状態とみなされ、オプティマイザが「表探索（フルスキャン）」を選択する可能性が高まり、注文確定時の SQL 実行時間が長くなってしまいます。

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

• 「統計更新処理は常に最新の状態を反映すべき」と直感的に考えがちですが、最新の状態を反映してしまうと逆にアクセス経路が変わりパフォーマンスが落ちる場合があります。
• DELETE→再編成→統計更新をすれば“最新の正しい統計”が得られるという発想と、アクセス経路の一貫性を保つために「敢えて古い統計を維持する」という対処の整合性を混同しやすいです。
• 「再編成（B）は統計に影響しない」と思い込みがちですが、再編成後のデータページ状態を統計更新で把握すると、物理的なページ数やクラスタリング率などからオプティマイザがアクセス経路を変更する要因になります。

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

1. 統計情報はオプティマイザが「最新状態を反映したもの」と仮定してアクセス経路を決定する。
2. 統計情報の更新タイミングをどこに置くかで、索引探索／表探索の選択に大きく影響する。
3. パフォーマンス維持のために、敢えて「古い統計」を保持し続ける運用パターン（統計更新を先行させる、あるいは省略する）がある。
4. 日次バッチ運用では「C→A→B」または「A→B」によって削除後も索引探索を継続させるのが定石である。