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

模範解答

解説

解答の論理構成

1. 表示された統計情報を確認
   表2より
   • “商品分類” の行数は 500
   • “C1番号” の列値の個数は 100
   • “C2番号” の列値の個数は 500
2. カテゴリ数の読み替え
   〔販売商品の概要〕には「カテゴリ1は … (100 種類)」と明示され、表2の “C1番号 100” と一致する。
   よって
   • カテゴリ１　＝　１００種類
   • カテゴリ２　＝　５００種類
3. “カテゴリ” テーブルの構造
   問題文より “カテゴリ” テーブルにはカテゴリ１とカテゴリ２の両方が格納される（カテゴリ１行では親カテゴリ番号がNULL）。したがって
   行数（＝カテゴリ番号の列値の個数）＝カテゴリ１＋カテゴリ２
   $100+500=600$
   → (a) ＝ 600
4. 親カテゴリ番号の列値個数
   親カテゴリ番号が入るのはカテゴリ２行のみ。親はカテゴリ１の番号なので distinct 値はカテゴリ１の種類数と同じ
   → (b) ＝ 100

誤りやすいポイント

• “商品分類” の行数 500 をそのまま (a) と誤記する
• 親カテゴリ番号に NULL が含まれるかどうかを見落とし、(b) を 101 などにしてしまう
• “C2番号 500” を (b) に入れてしまう（親と子を取り違え）

FAQ

模範解答

解説

解答の論理構成

1. オプティマイザの仮定
   問題文に「(2)列値当たりの行数は均等である。」とあるため、平均値は
   総行数 ÷ 列値個数 で求められる。
2. (c) の算出
   SQL2 は 「SELECT * FROM 注文 WHERE 顧客番号 = :h」。
   注文テーブル行数は 2,700,000、顧客番号の列値個数は 300,000。
   よって 2,700,000 ÷ 300,000 ＝ 9。
3. (d) の算出
   SQL3 は 「SELECT * FROM 注文明細 WHERE 注文番号 = :h」。
   注文明細テーブル行数は 27,000,000、注文番号の列値個数は 2,700,000。
   よって 27,000,000 ÷ 2,700,000 ＝ 10。
4. (e) の算出
   SQL4 は 「SELECT * FROM 注文 X, 注文明細 Y WHERE X.注文年月日 = :h AND X.注文番号 = Y.注文番号」。
   ① 注文テーブルにおける 注文年月日 列値個数は 90。
   2,700,000 ÷ 90 ＝ 30,000 … 指定日付の注文行数
   ② 1件の注文に対する注文明細は平均 10 行（上記 (d)）。
   ③ 結合後の行数は 30,000 × 10 ＝ 300,000。

誤りやすいポイント

• 注文年月日の列値個数 90 の読み落としにより、(e) を 30,000 と誤答しがちです。
• (d) の分母を列値個数ではなくテーブル行数で割って 0.1 としてしまう計算ミス。
• オプティマイザの仮定「均等分布」を忘れ、実際の偏りを想像して平均でない数字を書くケース。

FAQ

模範解答

解説

解答の論理構成

1. 表2から事実を抜き出す。
   • “注文明細”–“注文明細番号” の「列値の個数」は “48” である。
2. 表1で列の性質を確認する。
   • “注文明細番号” は「一つの注文の中で、商品ごとの注文を一意に識別する番号。1から付与する。」
3. 以上より “注文明細番号” は 1 注文につき 1〜N の連番を取り，「列値の個数」48 はその連番の取り得る最大値＝1 注文当たりの最大行数を示す。
4. 従って求める最大明細行数は 48 行 となる。

誤りやすいポイント

• 「列値の個数」がテーブル全体の distinct 値だと早合点し，注文単位の上限と結び付けられない。
• 行数 27,000,000 と混同し，平均や最大を誤って算出する。
• “注文明細番号” ではなく “注文番号” の列値個数 2,700,000 を見てしまう。

FAQ

模範解答

解説

解答の論理構成

1. 「表４」の１０行目は P1, AA1, 00051 である。
2. 「表３ SQL5」は

   WHERE C1番号 > 'P1'
     AND C2番号 > 'AA1'
     AND 商品番号 > '00051'
   ORDER BY C1番号, C2番号, 商品番号

   を実行するため、F さんが取得した結果も同じ並び順（C1→C2→商品番号の昇順）で返る。
3. １０行目より後の並びを「表４」から列挙すると
   • １１行目 P1, AA1, 00082
   • １２行目 P1, AA2, 00017
   • １３行目 P1, AA2, 00055
   • １４行目 P1, AA2, 00072
4. このうち最初の３件が設問の図に示された取得順
   • １行目　P1, AA1, 00082（すでに図に記載）
   • ２行目　P1, AA2, 00055
   • ３行目　P1, AA2, 00072
5. よって (ア)＝00055、(イ)＝00072 となる。

誤りやすいポイント

• ORDER BY の先頭列が同じときは２列目、さらに同じなら３列目で比較されることを見落とす。
• 「前ページの最後のキー３列よりすべて大きい」という条件を AND で書くと、実際には１列目が同値の行が除外されるため正しくページングできない。
• 文字列比較で 'P1' > 'P1' は偽になる点を忘れ、結果が合わないと混乱する。

FAQ

模範解答

解説

解答の論理構成

1. 【問題文】表４の 10 行目は
   C1番号 P1 / C2番号 AA1 / 商品番号 00051。
2. ORDER BY は【問題文】「ORDER BY C1番号, C2番号, 商品番号」である。したがって辞書順で
   (P1, AA1, 00051) より後ろの行を次画面とする。
3. 辞書順で後ろになるケースは３つ。
   ① C1番号 が同じで C2番号 も同じ、かつ 商品番号 が大きい。
   ② C1番号 が同じで C2番号 が大きい（商品番号は任意）。
   ③ C1番号 が大きい（以下の列は任意）。今回は WHERE 句の先頭で C1番号='P1' と固定したので③は不要。
4. ①を SQL にすると (C2番号='AA1' AND 商品番号>'00051')。
   ②を SQL にすると C2番号>'AA1'。
5. よって ((C2番号='AA1' AND 商品番号>'00051') OR C2番号>'AA1') が必要条件であり、
   (ウ) C2番号='AA1'、(エ) 商品番号>'00051'、(オ) C2番号>'AA1' となる。
6. (ウ) と (エ) の順序は AND で結合されるため入れ替えても意味は変わらない。
7. 以上より修正後 SQL は

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

誤りやすいポイント

• C1番号>'P1' を使うと C1番号='P1' の商品が全て除外され、正しくページングできない。
• (C2番号='AA1' OR C2番号>'AA1') と書くと 1 画面目の重複行（商品番号='00051' 以下）が再取得される。
• ORDER BY の列順と WHERE 句の条件順を混同し、末尾列の範囲指定だけで済むと誤解しやすい。

FAQ

模範解答

解説

解答の論理構成

1. 削除対象とタイミング
   • 〔テーブルの保守の見直し〕(2)
     「“仮注文”テーブルの不要な行(注文確定の列が ‘Y' の行) を削除する。」
   • 同(1) で「仕掛り中の注文処理がないことを確認」してから実施しているため，仮注文の残存行は実質 0 行。
2. 統計情報の更新
   • 同(4)
     「…テーブルを再編成し,次に統計更新処理を行う。」
   • 仕様 1,2 により統計更新処理の結果がシステムカタログへ登録される。
3. オプティマイザの判断基準
   • 仕様 7-(3)
     「行数がゼロの場合,表探索が最適である。」
4. 対象 SQL の特定
   • SQL6

     SELECT * FROM 仮注文 WHERE 仮注文番号 = :h
     ORDER BY 仮注文番号, 仮注文明細番号

   • インデックスは表2で “仮注文” の「仮注文番号」に 1A が定義されているが，行数 0 となった時点で表探索に切り替わる。
5. 以上より，「SQL6 が索引探索から表探索へ変わり，理由は統計更新後に行数 0 となるため」と結論付けられる。

誤りやすいポイント

• インデックスが残っているから索引探索と早合点し，統計情報を無視してしまう。
• 仕様 7-(3) の「行数がゼロの場合…」を見逃し，行数が極端に少ない場合の経路決定を誤解する。
• SQL1 など他の検索系 SQL に目を奪われ，更新系 SQL6 が対象であることに気付かない。

FAQ

模範解答

解説

解答の論理構成

1. 表探索の動作

   「表探索は,索引を使わずに先頭のデータページから全行を探索する。」
   この仕様により、条件に合致しない行も含めテーブル全体を走査します。

2. “仮注文”テーブルの規模
   表2より

   「仮注文　行数　9,000,000」「仮注文番号　列値の個数　900,000」
   1 つの「仮注文番号」で平均 10 行しか該当しません。

3. SQL6 の検索条件

   「SELECT * FROM 仮注文 WHERE 仮注文番号 = :h」
   一致比較なので索引探索なら10行程度で済みますが、表探索では 9,000,000 行を読むことになります。

4. 行数が増えるにつれ処理時間も増大
   保守後も未確定データは追加され続けるためテーブルは再び肥大化し、表探索による I/O 量も比例して増加します。

誤りやすいポイント

• 「表探索でも行数が少なければ速い」と思い込み、行数増加による I/O 量を軽視する。
• 「索引探索は常に最速」と誤認し、オプティマイザが表探索を選択するケース（統計情報の変化など）を想定しない。
• SQL6 の条件列と索引の先頭列が一致する点を見落とし、アクセス経路変更の影響を過小評価する。

FAQ

模範解答

解説

解答の論理構成

1. 性能低下の原因
   • G氏の指摘②「アクセス経路が索引探索から表探索に変わる SQL がある」
   • 表探索に変わるのは、統計情報に行数が「ゼロ又は極端に少ない」と記録されるため。仕様「7.(3) 行数がゼロの場合,表探索が最適である。」が適用される。
2. 現行フローの問題
   • 【問題文】〔テーブルの保守の見直し〕では「(4) …再編成し,次に統計更新処理を行う。」
   • つまり A→B→C の順。削除・再編成後に C を走らせるため行数が激減し、SQL6（仮注文番号検索）などが表探索化する。
3. 改善方針
   • 統計を「削除前」に採取するか、そもそも採取しない。
4. 処理順の決定
   • 方式① C→A→B
     統計を先に取得（行数多い状態）→削除→再編成。統計は古いままだが行数推定は大きく、索引探索を維持。
   • 方式② A→B
     統計更新を行わず、前日の統計（行数多い状態）を維持。
   • いずれも問題点②を解決するため、模範解答「C，A，B 又は A，B」に一致します。

誤りやすいポイント

• 再編成（B）を行えばアクセス経路が自動的に元へ戻ると誤解し、C を最後に置く。
• 統計情報は常に最新であることが正しいと考え、性能への影響を軽視する。
• A→C→B を選び、再編成後のページ構造変化で統計が不整合になる点に気付かない。

FAQ