平成25年度春期 データベーススペシャリスト 午後Ⅰ 問3

問3 SQLの設計及び性能に関する次の記述を読んで、設問1～3に答えよ。 全国の法人及び個人に事務用品をインターネット販売しているE社は、RDBMSを用いた注文システムを運用している。注文システムの運用は、情報システム部のFさんが担当している。 〔RDBMSのアクセス経路に関する主な仕様〕 1. SQL文の実行ごとに、アクセス経路が決められる。 2. 各テーブルの定義情報、及び統計更新処理が収集する統計情報、例えば、各テーブルの行数及び列値の個数は、システムカタログに記録される。 3. 各テーブルの列値の個数は、統計更新処理時点の各列に存在する異なる値の個数である。 4. アクセス経路は、各テーブルの統計情報及び索引定義情報に基づき、RDBMSのオプティマイザによって表探索又は索引探索のいずれかに決められる。 5. 表探索は、索引を使わずに先頭のデータページから全行を探索する。索引探索は、WHERE句中の述語に適した索引によって行を絞り込んでから、データページ中の行を探索する。SQL文の実行によって取得される行を、結果行と呼ぶ。 6. 索引探索に使われる索引は、1テーブル当たり1個である。索引探索に使える列は索引キーを構成する先頭の列として定義され、かつ、述語の比較演算子は＝、＜、＞、＜＝、＞＝のいずれかでなければならない。 7. オプティマイザは、アクセス経路を決めるとき、次のように仮定している。 (1) 統計情報は、テーブルの最新状態を反映したものである。 (2) 列値当たりの行数は均等である。 (3) 行数がゼロの場合、表探索が最適である。 〔販売商品の概要〕 販売する商品は、主にオフィス又は家庭で使用される事務用品である。販売する商品は、2階層のカテゴリによって分類される。カテゴリの例を次に示す。 (1) カテゴリ1は、筆記具、用紙などの大分類（100種類）を表す。 (2) カテゴリ1が筆記具の場合、カテゴリ2は鉛筆、ボールペンなどである。 〔テーブルの構造〕 注文処理に使用する主なテーブルの構造を図1に、主な列の意味を表1に示す。 図1 主なテーブルの構造 表1 主な列の意味 現在、「仮注文」テーブルのデータ保存期間を1か月間、また「注文」テーブル及び「注文明細」テーブルのデータ保存期間を3か月間として、毎月の末日に統計更新処理を行い、その後に不要な行を削除する削除処理を行っている。2013年3月末日に統計更新処理を行ったときにシステムカタログに記録された主なテーブル及び列の統計情報、並びに索引定義情報を、表2に示す。 表2 主なテーブル及び列の統計情報、並びに索引定義情報（一部未完成） 〔注文処理の概要〕 1. 注文システムによる処理 注文システムは、24時間オンライン稼働している。注文システムによる処理の概要は、次のとおりである。 なお、文中の（SQL1）、（SQL6）、（SQL7）は、表3のSQL1、SQL6、SQL7がそれぞれ実行されることを示す。 (1) 商品照会 ① 顧客は、インターネットから注文処理を呼び出し、メニューを表示させる。 ② 顧客は、メニューから商品照会を選んで、商品のカテゴリ1のカテゴリ名の一覧を表示させ、そのうちの一つを検索条件として選択する。 ③ 注文システムは、検索条件に合致する商品の情報（商品名、定価、販売価格、割引率、商品画像など）を「商品」テーブルから取得し（SQL1）、商品一覧画面に最大10商品を表示する。 ④ 顧客が次画面ボタンをクリックすると、次の10商品が表示される。 (2) 仮注文入力 ① 顧客は、商品一覧画面の1個又は複数個の商品に注文数を入力する。 ② 注文システムは、「在庫」テーブルを調べ、引当可能な商品ならば、「仮注文」テーブルに1行を追加する。注文システムは、1画面の処理ごとにCOMMIT文を発行する。 ③ 顧客は、仮注文入力を終えると、発送・支払に必要な顧客名、発送先住所などの情報を入力し、注文内容を注文確認画面で確認してから注文確定ボタンをクリックする。 ④ 仮注文入力が注文確定とならなかった場合、注文システムは注文処理を終了させて、「仮注文」テーブルから該当行を削除し、COMMIT文を発行する。 (3) 注文確定 ① 仮注文入力が注文確定となったとき、注文システムは、「注文」テーブルに1行を追加する。「仮注文」テーブルから主キー順に1行ずつ取得しながら（SQL6）、注文確定の列に'Y'を設定し、かつ、商品ごとに「在庫」テーブルの引当可能数の列を更新し（SQL7）、「注文明細」テーブルに1行を追加する。これを商品数だけ繰り返し、最後にCOMMIT文を発行する（在庫引当ができなかった場合の処理については、省略）。 ② その後、注文の支払が完了したとき、該当する「注文」テーブルの行の支払済の列に'Y'を設定する。 2. 注文処理に使用する主なSQL文 注文処理に使用する主なSQL文を、表3に示す。表3の平均結果行数は、表2の統計情報とオプティマイザの仮定に基づいて計算される推定行数である。また、表3のSQL1のホスト変数に「P1」を指定した場合のSQL1の結果行を、表4に示す。 表3 注文処理に使用する主なSQL文及び平均結果行数（一部未完成） 表4 SQL1の結果行（一部省略） 〔テーブルの保守の見直し〕 Fさんは、毎月の末日に行っていたテーブルの保守を、次のように日次処理として見直すことにした。 なお、行の削除にはDELETE文を用いる。 (1) 注文の少ない毎朝4時から4時30分までの間、仕掛り中の注文処理を終わらせ、仕掛り中の注文処理がないことを確認した後、注文処理を一時的に停止する。 (2) 「仮注文」テーブルの不要な行（注文確定の列が'Y'の行）を削除する。 (3) 「注文」テーブルの不要な行（保存期間を超過し、かつ、支払済の列が'Y'の行）、及び「注文明細」テーブルの不要な行を削除する。 (4) (2)、(3)の処理を行った後、テーブルを再編成し、次に統計更新処理を行う。 (5) 注文処理を再開する。 〔問題点の指摘〕 Fさんの上司であるG氏は、注文処理とテーブルの保守の見直しについて、次の問題点を指摘した。 ① 商品一覧画面の表示では、SQL1を実行したときの平均結果行数が多い。全結果行を取得してから表示するのではなく、1画面（10行）分を取得して、表示した方がよい。ただし、次の1画面分を取得するとき、最初から取得し直さないようにすべきである。 ② Fさんによる〔テーブルの保守の見直し〕では、アクセス経路が索引探索から表探索に変わるSQLがある。その結果、注文確定の際に、そのSQLを実行するたびに処理時間が長くなることが懸念される。 設問1 表2及び表3について、(1)～(3)に答えよ。 (1) 表2の「商品分類」テーブルの統計情報を基に、表2中の【a】、【b】に入れる適切な数値を答えよ。 (2) 表2の統計情報を基に、表3中の【c】～【e】に入れる適切な数値を答えよ。 (3) 表2の統計情報を基に、「注文明細」テーブルについて、一つの注文で発生した最大明細行数を答えよ。 設問2 〔問題点の指摘〕の①への対応について、(1)、(2)に答えよ。 (1) Fさんが表4の取得順で示した11行目以降を取得するために表3のSQL5を実行したところ、最初の3行の取得順は次のようになった。この取得順で示した2、3行目の【ア】、【イ】に入れる適切な字句を答えよ。 (2) (1)の結果はFさんの目的とは異なるので、SQL5を1画面目の情報を使って、次のように修正した。【ウ】～【オ】に入れる適切な字句を答えよ。なお、【ウ】～【オ】に入れる述語はそれぞれ一つとする。 SELECT * FROM 商品 WHERE C1番号='P1' AND （（【ウ】 AND 【エ】）OR（【オ】）） ORDER BY C1番号、C2番号、商品番号 設問3 〔問題点の指摘〕の②への対応について、(1)～(3)に答えよ。 (1) アクセス経路が索引探索から表探索に変わるSQLを、表3のSQL1～SQL7の中から一つ答え、アクセス経路が変わる理由を、40字以内で述べよ。 (2) (1)で答えたSQLのアクセス経路が表探索に変わった場合、そのSQLを実行するたびに処理時間が長くなる理由を、40字以内で述べよ。 (3) (1)で答えたSQLがアクセスするテーブルについて、②の問題点を改善するために表2に示した統計情報がシステムカタログに存在するという前提で、毎朝4時に行う次のA～Cの処理を正しい順番に並べよ。 なお、不要な処理は省いてよい。 A：不要な行の削除　B：再編成　C：統計更新処理