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

問1　データベースの設計，実装に関する次の記述を読んで，設問1～3に答えよ。 B銀行では，支店の窓口において，預金の入出金，税金・公共料金の収納，振込などの金融サービス業務（以下，窓口業務という）を行っている。現在，行員が使用する窓口業務専用の端末（以下，窓口端末という）で記録されたログを，障害調査，監査などに利用している。今後は，蓄積したログを更に活用して，事務手続，アプリケーションソフトウェア（以下，APという）の改善に役立てるために，ログ分析システムを構築することにした。 ［システムの現状］ 1. 現行システムの構成 （1）支店は60支店あり，店番で識別している。各支店には，複数の窓口端末がある。窓口端末は，PC，モニタ，スキャナ，プリンタ，現金処理機などを組み合わせた端末で，支店ごとに一意な機番で識別している。 （2）データセンタが1拠点ある。データセンタには，窓口端末サーバ，ホストコンピュータがある。窓口端末サーバでは，全支店の窓口端末の監視，ログ収集，APの配布などを行う運用管理システムが稼働しており，ホストコンピュータでは，業務処理を行う勘定系システムが稼働している。 （3）支店内，データセンタ内の機器はLANで接続されており，LAN間はWANで接続されている。 （4）窓口端末のPCでは，窓口端末用のAP（以下，端末APという）が稼働している。端末APは，業務ロジックをもたず，勘定系システムと連動しながら，画面の入出力及び接続機器の制御を行っている。 （5）端末APでは，預金入金，預金出金，振替などの取引ごとに取引メニューが用意されており，取引は，取引番号で識別している。取引メニューに対応する手続用の画面が複数ある。画面は，全体で一意な画面番号で識別している。 2. ログ収集・利用状況 （1）窓口端末には，ログを自動的に記録する機能が備わっている。窓口端末のPCは，ログをログファイルに保存し，約15分ごとに窓口端末サーバに転送している。 （2）ログには，店番，機番，タイムスタンプ（以下，TSという），及びログテキストを記録する。ログテキストは，取引番号，画面番号，画面ごとに固有な項目とその値を“［項目ID：項目名＝値：値の説明］”（値の説明の表示は任意）の形式で編集した文字列を連結したものである。表1にログの例を示す。 表1　ログの例（一部省略） （3）窓口端末サーバでは，RDBMSが稼働していて，窓口端末のPCから収集したログファイルのデータを，約1時間ごとに“ログ収集”テーブルに格納している。 （4）利用者である行員（以下，利用者という）は，店番，機番，及びTSの範囲を指定してテーブルの行を選択し，TS及びログテキストを射影する問合せを行って，障害調査，監査の際に利用している。 ［新たなログ分析システムの構築］ 1. ログ分析システムの主な機能 （1）蓄積した60か月分のログを対象に，障害調査，監査，事務手続改善，AP改善に用いるデータを抽出するログ分析処理の機能を提供する。表2に，ログ分析処理の例を示す。 （2）ログ分析システムの利用時間帯は，平日の8:00～22:00である。毎月の最終営業日の22:00から，翌営業日の8:00までの間に，過去データの削除，再編成などの処理（以下，月末処理という）を行う。 表2　ログ分析処理の例 2. ログ分析システムの構成 （1）窓口端末サーバで稼働中のRDBMSをログ分析システムに利用する。 （2）分析用データの作成・参照・削除に用いるAP（以下，分析APという）を新たに開発し，窓口端末及び窓口端末サーバ上で稼働させる。 3. ログ分析システムのテーブル 次の方針でデータベースにデータを格納することにし，図1のログ分析システムのテーブル構造，表3の主なテーブルのデータ量見積りを作成した。 （1）支店から収集したログは，現状どおり“ログ収集”テーブルに格納する。格納処理は，ログファイルごとに，並行して実行する。新たに発生したログを追加するだけで，更新は行わない。 （2）“ログ基本”テーブルには，“ログ収集”テーブルの各行に対応する行を格納し，一意な文字列であるログIDで識別する。検索，集計のキーとなる項目を列として定義し，ログテキストから取り出した値を設定する。年月には，TSの年月を設定する。 （3）ログテキスト内に記録されている複数の操作を，操作ごとに分割して“ログ明細”テーブルに格納する。 （4）取引の開始から終了までの手続は，複数の操作に分岐していくので，ログは木構造をもつ。分析APによってログを解析し，取引開始ログをルートノードに，その他のログをノードとする木構造の関連を“ログ関連”テーブルに格納する。 （5）マスタ情報（支店，端末種別，窓口端末，取引種別，取引，画面，行員，行員所属）を各テーブルに格納する。マスタ情報は，データ量が少なく，更新は月1回なので，月末処理で変更を反映する。 （6）“ログ収集”，“ログ基本”，“ログ明細”，“ログ関連”テーブルは，月末処理において，TSが翌月の60か月前の月以前の行を削除して，59か月分のログが保存された状態にする。月が変わった後のログを合わせて60か月分を保有する。 図1　ログ分析システムのテーブル構造（一部省略） 表3　主なテーブルのデータ量見積り ［RDBMSの主な仕様］ ログ分析システムに利用するRDBMSの主な仕様は次のとおりである。 1. ページ RDBMSとストレージ間の入出力単位をページという。同じページに異なるテーブルの行が格納されることはない。 2. オプティマイザの仕様 （1）LIKE述語の検索パターンが‘ABC%’のように前方一致の場合は索引探索を選択し，‘%ABC%’，‘%ABC’のように部分一致，後方一致の場合は表探索を選択する。 （2）WHERE句の述語が関数を含む場合，表探索を選択する。 3. 再帰的な問合せの構文のサポート WITH句にRECURSIVEを指定した再帰的な問合せの構文をサポートする。再帰的な問合せの構文を用いると，例えば，階層構造の組織について，ある組織を起点として上位又は下位の組織を，階層に沿って連続的に検索することができる。 4. ウインドウ関数のサポート ウインドウ関数をサポートする。例えば，選択する行ごとに，その直前の行の列値を参照したい場合には，LAG関数が使用できる。 5. テーブルの物理分割 （1）テーブルごとに一つ又は複数の列を区分キーとし，区分キーの値に基づいて物理的な格納領域を分ける。これを物理分割という。 （2）区分方法には，ハッシュとレンジの二つがある。ハッシュは，区分キー値を基にRDBMS内部で生成するハッシュ値によって，一定数の区分に行を分配する方法である。レンジは，区分キー値によって決められる区分に行を分配する方法で，分配する条件を，値の範囲又は値のリストで指定する。 （3）物理分割されたテーブルには，区分キーの値に基づいて分割された索引（以下，ローカル索引という）を定義できる。ローカル索引のキー列には，区分キーを構成する列（以下，区分キー列という）が全て含まれていなければならない。 （4）テーブルを検索するSQL文のWHERE句の述語に区分キー列を指定すると，区分キー列で特定した区分だけを探索する。また，WHERE句の述語に，ローカル索引の先頭列を指定すると，ローカル索引によって区分内を探索することができる。 （5）問合せの実行時に，一つのテーブルの複数の区分を並行して同時に探索する。同一サーバ上では，問合せごとの同時並行探索数の上限は20である。 （6）指定した区分を削除するコマンドがある。区分内の格納行数が多い場合，コマンドによる区分の削除は，DELETE文よりも高速である。 6. クラスタ構成のサポート （1）シェアードナッシング方式のクラスタ構成をサポートする。クラスタは複数のノードで構成され，各ノードには，当該ノードだけがアクセス可能なディスク装置をもつ。 （2）各ノードへのデータの配置方法には，次に示す分散と複製があり，テーブルごとにいずれかを指定する。 ・分散による配置方法は，一つ又は複数の列を分散キーとして指定し，分散キーの値に基づいてRDBMS内部で生成するハッシュ値によって各ノードにデータを分散する。分散キーに指定する列は，主キーを構成する全て又は一部の列である必要がある。 ・複製による配置方法は，全ノードにテーブルの複製を保持する。 （3）データベースへの要求は，いずれか一つのノードで受け付ける。要求を受け付けたノードは，要求を解析し，自ノードに配置されているデータへの処理は自ノードで処理を行う。自ノードに配置されていないデータへの処理は，当該データが配置されている他ノードに処理を依頼し，結果を受け取る。特に，テーブル間の結合では，他ノードに処理を依頼するので，自ノード内で処理する場合と比べて，ノード間通信のオーバーヘッドが発生する。 ［問合せの検討］ 1. 問合せの傾向分析・索引設計 表2の処理1～6の参照テーブルを表4にまとめて，問合せの傾向を分析した。また，図1中のマスタ情報を格納するテーブルについて，処理1～6で結合に用いられる主キー以外の列に索引を定義することにし，その対象テーブル名・列名を表5にまとめた。 表4　処理1～6の参照テーブル（未完成） 表5　主キー以外の索引定義対象テーブル名・列名（未完成） 2. 処理1の問合せ検討 表2の処理1の問合せに用いる図2のSQL文を作成した。 図2　処理1の問合せに用いるSQL文（未完成） 3. 処理2の問合せ検討 表2の処理2の問合せに用いるSQL文の例を，図3～5に作成した。ここで，GET_LAPSE(TS1、 TS2)は，TS1からTS2までの経過時間を，ミリ秒単位の整数で返却するユーザ定義関数である。また，図5中の【ア】は，図3，4のSQL文と同じ結果を返すように，“ログ基本”テーブルに追加した列の列名である。 なお，図3～5の結果行には，分析対象でない前画面番号と後画面番号の組が含まれるが，これらは，分析AP上で別途除外されるものとする。 図3　処理2の問合せに用いるSQL文の例1（未完成） 図4　処理2の問合せに用いるSQL文の例2（未完成） 図5　処理2の問合せに用いるSQL文の例3（未完成） ［“ログ関連”テーブルの検討］ 1. テーブル構造の案 木構造をもつデータを取り扱う場合，テーブル構造によって，行の追加・削除の効率，問合せに用いるSQLの構文，問合せの性能，データの制限が異なる。そこで，表6の“ログ関連”テーブルのテーブル構造案1～3を検討することにした。 検討に当たって，木構造をもつログの例を用いて案を比較することにし，各案におけるテーブルの行を表7～9に，案3の左端番号・右端番号付与の例を図6にまとめた。 表6　“ログ関連”テーブルのテーブル構造案 表7　案1の行 表8　案2の行 表9　案3の行 図6　案3の左端番号・右端番号付与の例 2. テーブル構造案の検討 表6の案ごとに，次の問合せ［A］，［B］に用いるSQL文の案を，表10にまとめた。 ・問合せ［A］　ログID＝‘101’及びその下位の全てのログIDを抽出する。 ・問合せ［B］　ログID＝‘105’及びその上位の全てのログIDを抽出する。 表10　問合せに用いるSQL文の案（未完成） 3. テーブル構造案の評価 行の追加・削除の効率の観点では，例えば，ログID＝‘199’の新たなログを‘101’の下位，‘103’の上位に追加し，‘103’の階層の深さが一つ下がる場合，‘199’の行追加以外の更新行数は，案1では1行，案2では【i】行，案3では【j】行である。案【k】の負荷が最も高いが，本業務では，【l】ので，変更時の負荷は問題にならない。 問合せの難易度の観点では，処理3の問合せにSQL文を用いる場合，案1では，【m】の構文を用いるが，案2，3では，選択，射影，【n】の関係演算を行う構文を用いればよい。案2と案3の問合せの性能を比較すると，RDBMSのオプティマイザの仕様から，表10の問合せ［A］ではほぼ同等であるが，問合せ［B］では案2が劣る。 これらの評価から，案3による実装が最適と判断されるが，左端番号，右端番号の列を整数型で定義する場合，整数型の上限を超えないように留意する必要がある。 ［テーブルの物理分割・クラスタ構成の検討］ ログデータを格納するテーブルは，格納行数が多いので，データの追加，参照，削除の性能が懸念される。そこで，テーブルの物理分割を行うことにした。 1. “ログ収集”テーブルの物理分割 表11の“ログ収集”テーブルの物理分割案A～Cを作成し，性能評価を行った。各案の性能を相対的に評価し，性能が低い案から順に0，1，2の点数を付ける。評価が同じ案は，同じ点数とする。 表11　“ログ収集”テーブルの物理分割案・性能評価 2. “ログ基本”テーブルの物理分割 （1）物理分割案とその評価 “ログ収集”テーブルと同様に，表12の“ログ基本”テーブルの物理分割案D～Fを作成し，性能評価を行った。追加，削除の性能評価は“ログ収集”テーブルと同様であったが，参照の性能評価は処理によって異なるので，更に処理ごとに性能評価を行うことにした。 表12　“ログ基本”テーブルの物理分割案・性能評価 （2）探索対象ページ数試算による性能評価 表2の処理4～6における物理分割案D～Fについて，次の①～④を前提条件として，探索対象の最大区分数（以下，探索区分数という），テーブルからの読込みページ数（以下，探索ページ数という）を試算し，表13を作成した。 ① “ログ基本”テーブルへのアクセスだけを対象とする。どの処理でも，検索条件に合致する行は，30、000行あるものとする。 ② 月末営業日の処理を想定し，60か月分のログが全て蓄積されていて，ログは各支店，各年月に均一に分布しているものとする。 ③ どの区分方法でも，ページ総数は同じで，どのページにも最大行数のデータが格納され，ページは各区分に均等に配置されているものとする。また，テーブルの行が複数ページに分割されて格納されることはないものとする。 ④ バッファヒット率は，索引のページでは100％，テーブルのページでは0％とする。 表13　表2の処理4～6における探索区分数・探索ページ数試算（未完成） 3. クラスタ構成の検討 テーブルの物理分割だけでは，将来的なデータ量の増加に対応できないので，RDBMSがサポートしているクラスタ構成の検討を行った。クラスタ構成への変更に当たり，各テーブルのデータの配置方法に分散を指定し，主キー列を分散キーとして設定することにした。 クラスタ構成への変更後，表2の処理2，4の性能を試算したところ，処理2は同時並行探索数の上限がなくなることで性能が改善されたが，処理4は性能が低下することが判明した。そこで，処理4の性能低下への対策として，“取引種別”テーブル，“取引”テーブル及び“ログ明細”テーブルのデータの配置方法を見直すことにした。 設問1　［問合せの検討］について，（1）～（4）に答えよ。 （1）表4中の太枠内に“○”印を記入し，表を完成させよ。 （2）表5中の空欄を埋め，表を完成させよ。 （3）図2中の【a】～【c】に入れる適切な字句を答えよ。また，図3～5中の【d】に入れる適切な字句を答えよ。 （4）図5中の【ア】の列に事前に設定すべき値の内容を，20字以内で具体的に述べよ。 設問2　［“ログ関連”テーブルの検討］について，（1）～（3）に答えよ。 （1）表10中の【e】～【h】に入れる適切な字句を答えよ。 （2）“3. テーブル構造案の評価”の本文中の【i】～【k】に入れる適切な数字を答えよ。また，【l】～【n】に入れる適切な字句を，本文中の用語を用いて答えよ。 （3）“3. テーブル構造案の評価”の本文中の下線部について，問合せ［B］では，案2の性能が案3よりも劣る理由を，30字以内で具体的に述べよ。 設問3　［テーブルの物理分割・クラスタ構成の検討］について，（1）～（3）に答えよ。 （1）表11中の性能評価について，次の①，②をそれぞれ30字以内で具体的に述べよ。 ① 追加の性能評価において，案Cが低い理由 ② 参照の性能評価において，どの案も同じ理由 （2）表13中の【o】～【r】に入れる適切な数値を答えよ。また，案Eについて，処理4，5の探索ページ数の試算値が最小となるローカル索引を構成する列名を全て答えよ。 （3）“3. クラスタ構成の検討”について，①～③に答えよ。 ① 処理4の性能が低下した理由を，50字以内で具体的に述べよ。 ② “取引種別”テーブル及び“取引”テーブルについて行うべき対応を，20字以内で具体的に述べよ。 ③ “ログ明細”テーブルの分散キーを見直すことにした。どのように見直せばよいか，20字以内で具体的に述べよ。