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

処理の入出力を一つずつ追う

設問が問う処理について、何を読み取り何を書き出すのかを一つずつ言葉にして確認しましょう。記述されている条件や前提を自分の理解と照らし合わせ、根拠を問題の記述の中に求める姿勢で検討を進めることが大切です。

この問題で問われていること

確認すべき観点は一つ、各処理がどのデータを入力として受け取り、どのデータを結果として残すかという対応関係です。この対応を記述に即して整理できれば、どの箇所で何が求められているかを落ち着いて判断できます。

視点

データを扱う処理を検討するときは、処理の前後でデータがどう変わるかを追うのが基本です。入力、途中の結果、最終的な出力を区別し、それぞれを自分の言葉で説明できるかを確かめると、理解があいまいな部分に気付きやすくなります。

確認ポイント

• 各処理が受け取るデータと残すデータを区別して書き出せるか
• 処理の前後でデータの状態がどう変わるかを記述から追えるか
• 判断の根拠が問題に書かれた条件や前提に基づいているか

解説

E社の生産部品表を題材に、構成テーブル（親品番、子品番、構成数）の自己結合による正展開・逆展開、所要量の計算、逆展開に必要な索引の設計、品目区分とLLCを使った処理の効率化と矛盾の検知、並行して行う所要量計算処理で起きるデッドロックとその回避を問う問題です。公式解答例では、逆展開用の索引を（子品番，親品番）とし、デッドロックの回避策を「SQL4を部品の品番順に実行する」としています。

問題の全体像

E社はRDBMS上に生産部品表を構築しており、FさんがDB管理者向けの研修資料を作る、という設定です。品目テーブル（品番、品目区分、LLC）、構成テーブル（主キーは親品番，子品番）、在庫テーブル（品番、引当可能数）があり、各テーブルには主索引だけが定義されています。前半では、図1の構成図と表2の構成テーブルの対応、正展開のSQL1と逆展開のSQL2、索引の追加を扱います。後半では、表4の所要量計算処理プログラムに対するK部長の3つの指示（品目区分でSQL3の発行回数を減らす、LLCの述語で不具合を防ぐ、並行処理で起きるデッドロックを回避する）を扱います。出題趣旨では、部品表の基本的なテーブル設計と索引設計、正展開処理・逆展開処理・所要量計算処理の概要、それを操作するSQL構文の特徴と考慮点を理解しているかを問うとしています。採点講評では、全体として正答率は高く、設問1、2及び設問3(1)の正答率は高かったと述べています。設問3(2)については、主索引と同じ順番に列名を並べた誤った解答が散見されたと述べています。設問4(1)、(2)は設問1、2に比べて正答率が低く、設問4(3)と設問4(4)はそれぞれ正答率が低かったとしています。

設問1(1)(ア)

図1の製品AYは、P5の下に【ア】と【イ】があり、【イ】の下に【ウ】とP9があります。表2で親品番がP5の行を探すと、子品番はP3とP6の2行です。このうち、さらに子品目を持つのはP6（P6→P8、P6→P9）で、P3を親品番とする行はありません。したがって、子品目を持たない側の【ア】がP3です。公式解答例もP3です。

設問1(1)(イ)

【イ】はP5の子品目で、その下に【ウ】とP9がぶら下がっています。表2で子品番にP9を持つ親品番のうち、P5の子品目でもあるのはP6です（P5→P6、P6→P9）。したがって【イ】はP6で、公式解答例もP6です。

設問1(1)(ウ)

【ウ】は【イ】（P6）の子品目で、P9ではない方です。表2の親品番P6の行は子品番P8とP9なので、【ウ】はP8です。公式解答例もP8です。図1のLLCの例（P9のLLCは3）とも矛盾しません。AYではP5→P6→P9でP9がレベル3に現れるからです。

設問1(2)(エ)

表2で親品番がAZの行は、P3、【エ】（構成数2）、P9の3行です。図1のAZの直下はP3、P7、P9なので、残る【エ】はP7です。公式解答例もP7です。

設問1(2)(オ)

表2には、図1で親子関係にあるのに記載されていない組があるはずです。図1のAZではP7の下にP2とP4があり、表2の最終行にはP7→P4だけがあります。したがって、P7→P2の行が欠けています。表2は親品番順に並んでおり、P6の行の後、P7→P4の直前に位置する【オ】は親品番P7です。公式解答例もP7です。

設問1(2)(カ)

前の空欄と同じく、欠けている行はP7→P2です。子品番の【カ】はP2で、公式解答例もP2です。子品番順でもP2はP4より前に来るので、行の並びとも合っています。なお、P2→P3の行は表2に既にあるため、AZの下でP2の下にP3が続く構成も表されています。

設問2(1)(a)

SQL1のUNION ALLの後半は、レベル2の部品を求める部分です。L1は親品番='AZ'の行、つまりAZの直下（レベル1）の部品を表します。その部品を親とする行をL2として結合するので、結合条件はL1の子品番とL2の親品番を等しくすることになります。したがって【a】はL1側の子品番で、公式解答例も子品番です。所要量は、L2.構成数とL1.構成数の積で求めています。

設問2(1)(b)

前の空欄と同じ理由で、L2側は親品番です。レベル1の部品（L1.子品番）がL2の親品番に一致する行を探すことで、1階層下をたどれます。公式解答例も親品番です。

設問2(2)

表2の構成数を使ってAZ1個分を計算します。AZ直下はP3×1、P7×2、P9×2です。P7はP2×1、P4×1を使うので、P2は2×1＝2個、P4は2×1＝2個です。P2はP3×2を使うので、レベル3のP3は2×2＝4個です。これにAZ直下のP3の1個を加えると、P3は5個になります。公式解答例も、P2が2、P3が5、P4が2です。P3は複数のレベルに現れるので、レベルをまたいで合計するのがポイントです。

設問3(1)(c)

SQL2は逆展開で、L2を子品番='P9'の行とし、そこから1階層上をたどります。L2の親品番（P9の親）を子品番に持つ行がL1になるので、結合条件はL2.親品番 = L1.子品番です。したがって【c】は親品番で、公式解答例も親品番です。

設問3(1)(d)

前の空欄と同じ理由で、L1側は子品番です。L1の親品番を品目テーブルと結合し、LLC=0（製品）に絞り込むことで、レベル2にP9を使う製品が得られます。公式解答例も子品番です。

設問3(1)(製品の品番)

P9を子品番とする行は、AX→P9、AY→P9、AZ→P9、P1→P9、P6→P9です。さらに1階層上の親が製品（LLC=0）かどうかを調べます。P1の親はAXで製品です。P6の親はP5で、製品ではありません。AX、AY、AZは製品なので、それらの上に親はありません。したがって、レベル2でP9を使う製品はAXだけで、公式解答例もAXです。AYはP5→P6→P9でP9をレベル3に使っていますが、SQL2は2階層分しか結合しないので対象外です。

設問3(2)

RDBMSの仕様では、等値比較の述語の列が索引キーの先頭から連続していないと索引探索は選ばれません。SQL2では、L2の検索条件が子品番='P9'です。L1の結合条件もL1.子品番 = L2.親品番で、子品番に対する等値比較です。構成テーブルの主索引は（親品番，子品番）の順なので、子品番だけでは索引探索になりません。品目テーブルは品番（主索引）で結合するので問題ありません。そこで、子品番を先頭にした（子品番，親品番）のユニーク索引を追加します。親品番と子品番の組は主キーなので一意であり、ユニーク索引にできます。公式解答例も「子品番，親品番」です。採点講評では、主索引と同じ順番に列名を並べた誤った解答が散見されたと述べ、等値比較のローカル述語が指定された列を索引キーの先頭列にするのが索引設計の基本だと指摘しています。

設問4(1)

表4の手順③では、前の手順で引き当てた全部品についてSQL3を発行し、その1階層下の部品を調べます。品目区分が単体部品の品目は他の部品から組み立てられないので、子部品は存在しません。単体部品に対してSQL3を発行しても結果は0件です。手順①・③で品目区分を取得しておけば、単体部品に対するSQL3の発行を省略できます。公式解答例は「単体部品は子部品がないのでSQL3の発行は不要だから」です。30字以内で答えるには、①単体部品には子部品がないこと、②そのためSQL3の発行が不要になること、の2点を入れます。採点講評では、品目区分を利用すれば性能を効率化できることを理解してほしいとしています。

設問4(2)

指示2の例では、AXの構成でP2の子品目P3をP11に置き換えるべきところ、誤ってP1を登録しています。すると、P1→P2→P1→P2…という循環した親子関係ができます。SQL3は、HLLCに当該品目の一つ下のレベルを設定し、LLC>=:HLLCで絞り込みます。P1のLLCは1なので、レベル3以降に現れたP1は除外され、展開が止まります。この述語がないと、手順③〜⑤が同じ部品を繰り返し展開し続け、手順⑥に到達しません。公式解答例は「処理が無限ループして終わらない。」です。20字以内で答えるには、処理が循環して終了しないことを示します。採点講評では、ローレベルコードは部品の親子関係の矛盾を検知する目的で導入されると述べています。

設問4(3)

表4では、在庫の引当可能数をレベルごとに品番順に更新し、COMMITは手順⑥で行うため、更新した在庫行のロックは製品の処理が終わるまで保持されます。AXの更新順は、レベル1がP1、P4、P9、レベル2がP2（P9は既にロック済み）、レベル3がP3です。AZの更新順は、レベル1がP3、P7、P9、レベル2がP2、P4、レベル3がP3（ロック済み）です。ケース1は、AXのレベル1のP9とレベル3のP3、AZのレベル1のP3とP9の組で、更新順序が逆になる場合です。同様に見ると、AXはP4→P9の順（どちらもレベル1）、AZはP9（レベル1）→P4（レベル2）の順で更新します。AXがP4を、AZがP9をロックした状態で互いに相手の行を待つと、デッドロックになります。公式解答例の図では、AXのP4とレベル1のP9、AZのレベル1のP9とレベル2のP4に丸印を付けています。採点講評でも、在庫テーブルを更新する行の品番の順番に注目すれば、ケース1以外ではP4とレベル1のP9の組合せになると述べています。

設問4(4)

デッドロックの原因は、製品ごとにレベル単位で在庫行を更新するため、トランザクション間で更新する行の品番の順序がそろわないことです。Fさんの改良では、製品ごと部品ごとに集計した所要量で、手順⑥の前にSQL4をまとめて実行します。このとき、どの製品の処理でも同じ順序で行をロックするよう、SQL4を部品の品番順に実行すればデッドロックを避けられます。公式解答例は「SQL4を部品の品番順に実行する。」です。20字以内で答えるには、実行順序を品番順にそろえることを明示します。採点講評では、ケース1から、更新する行の品番の順番をそろえる方法を考えることで正答を導いてほしかったと述べています。

学習のポイント

自己結合で親子関係をたどる結合条件の向き、等値述語の列を索引の先頭に置く索引設計、階層ごとに更新するとロックの順序が製品ごとに異なってデッドロックが起きる仕組みと、品番順にそろえる回避策を重点的に復習してください。