DBデータベーススペシャリスト試験午後Ⅰ

平成23年度特別データベーススペシャリスト午後Ⅰ 問3

事例を読んで設問の答えを考えてから、解説のポイントとIPA公式の解答例・採点講評で確認しましょう。

問題

問3　関係データベースの性能に関する次の記述を読んで、設問1～3に答えよ。クレジットカード会社のC社では、カード決済の検査機能を強化することになった。 C社で使用しているテーブルは大規模であり、情報システム部のFさんが関係データベース管理システム（RDBMS）のSQL文の性能測定を実施することになった。【RDBMSの主な仕様】 RDBMSの物理構造とアクセス経路に関する主な仕様は、次のとおりである。 1. テーブルの行は、データページに格納される。異なるテーブルの行が、同じデータページに格納されることはない。また、1行が異なるデータページに格納されることはない。 2. 索引には、ユニーク索引と非ユニーク索引がある。ユニーク索引のキーは1行だけを指すのに対し、非ユニーク索引のキーは1行以上の行を指す。 3. 索引には、クラスタ索引と非クラスタ索引がある。キー値の順番と、キーが指す行の物理的な並び順が一致している場合をクラスタ索引と呼び、ランダムな場合を非クラスタ索引と呼ぶ。 4. アクセス経路は、RDBMSによって表探索又は索引探索に決められる。表探索では索引を使わずに先頭データページから全行を探索し、索引探索ではWHERE句中の述語に適した索引によって絞り込んでからデータページ中の行を読み込む。SQL文の実行によって得られる行を、結果行と呼ぶ。 5. 索引探索に使える述語は、次のとおりである。ここで、比較演算子は＝、＜、＞、＜＝、＞＝のいずれかである。 (1) 列名　比較演算子　比較値 (2) 列名 BETWEEN 比較値 AND 比較値 RDBMSのデータ入出力処理とログ出力処理に関する仕様は、次のとおりである。 1. データ入出力処理とログ出力処理は、4、096バイトのページ単位に行われる。 2. データ入出力処理において、磁気ディスク装置（以下、ディスクという）に対してデータページをランダムに入出力する場合、SQL処理中のCPU処理とデータ入出力処理は並行して行われない。この場合のデータ入出力処理を同期データ入出力処理と呼び、SQL処理時間は次の式で近似できる。 SQL処理時間＝ CPU処理時間＋同期データ入出力処理時間 3. データ入出力処理において、ディスクに対してデータページを順次に入出力する場合、SQL処理中のCPU処理とデータ入出力処理は並行して行われる。この場合のデータ入出力処理を非同期データ入出力処理と呼び、SQL処理時間は次の式で近似できる。ここで、関数MAXは、引数のうち、大きい方の値を返す。 SQL処理時間＝ MAX（CPU処理時間、非同期データ入出力処理時間） 4. テーブルごとに確保されたデータバッファが、変更データページによって一杯になった場合、データバッファの内容がディスクに出力される。 5. UPDATE文によって行を変更した場合、変更前後の行全体がシステムによる回復用ログとしてログページに書き込まれ、システムに1個存在するログバッファに蓄えられる。次の事象A～Cのいずれかが発生したとき、ログ先行出力機能によって、ログページはデータページよりも先にディスクに出力される。事象A　トランザクションが、コミットかロールバックを行った。事象B　ログバッファが、ログページによって一杯になった。事象C　データバッファが、変更データページによって一杯になった。 6. 同じディスクに対して、同時に入出力を行うことはない。また、データページが格納されているディスクとログページが格納されているディスクの間で競合は起きない。 7. データバッファとログバッファの大きさは、ともに2、000ページである。【測定用テーブルとその索引の設計】測定用テーブルとして、行長が異なる2個のテーブル、“TA”と“TB”を用意した。測定用テーブルについて、その大きさを表1に、列値の範囲と分布を表2に、索引の設計を表3に示す。表1 測定用テーブルの大きさ表2 測定用テーブルの列値の範囲と分布表3 測定用テーブルの索引の設計【測定用SQL文の仕様と構文】測定用SQL文について、その仕様を表4に、構文を表5に示す。表4 測定用SQL文の仕様注記　xがAの場合は“TA”テーブルを、Bの場合は“TB”テーブルをアクセスする。表5 測定用SQL文の構文（未完成）注記1　ISOLATIONレベルは、READ COMMITTEDであり、排他制御は行単位に行われる。注記2　SQL1B～SQL7Bの各構文は、対応するSQL1A～SQL7AのTAをTBに替えたものである。【SELECT文の処理時間の測定】 SELECT文の処理時間の一部を表6に示す。測定では、索引ページはすべてバッファヒットさせ、SELECT文の結果行は外部ファイルに出力していない。表6 SELECT文の処理時間の一部（秒）注記　繰返し測定し、値はその平均値で求めた。0.1秒未満は四捨五入した。 Fさんは、表6のSELECT文の処理時間について次のように分析した。分析A SQL1AとSQL2Aのデータ入出力処理は、【f】データ入出力処理である。データ入出力処理時間は、【g】秒である。全データページを順次にデータバッファに入力するのに必要なCPU処理時間は10秒であるから、全行をデータバッファからプログラム内に移動するのに必要なCPU処理時間は、【h】秒である。SQL1Aに述語C4＜:hvを指定するWHERE句を追加して結果行を減らせば、SQL処理時間は短くなるが、【i】行以下に減らしても【j】秒より短くはならない。表探索によって“TA”テーブルからN個の結果行を得るのに必要なSQL処理時間T1は、次の実験式で表せる。 T1 ＝ MAX（10秒＋0.1ミリ秒／行×N行、1ミリ秒／ページ×【k】ページ）分析B SQL3Aのデータ入出力処理は、【l】データ入出力処理である。最大で【m】ページをランダムに入力したときのデータ入出力処理時間は、10秒である。非クラスタ索引探索によって、“TA”テーブルからN個の結果行を得るのに必要なSQL処理時間T2は、次の実験式で表せる。ただし、Nは1、000以下とし、また、データページはバッファヒットしないものとする。 T2 ＝ 0.1ミリ秒／行×N行＋10ミリ秒／ページ×【n】ページ【UPDATE文の処理時間の測定】 Fさんの上司であるG氏から、UPDATE文の処理時間の測定に対して次のような要望があった。要望①　UPDATE文を含むトランザクションを並行して実行するとデッドロックが起きる場合がある。デッドロックを意図的に起こさせる試験を追加してほしい。要望②　テーブルの全行更新処理が予想される。テーブルを区分に物理分割した場合を検討し、ログ出力処理時間を考慮したSQL処理時間を推定してほしい。 Fさんは、まずSQL6xを測定し、クラスタ索引探索によってN行を更新する場合のSQL処理時間T3を推定する実験式を考えた。ここで、データページの1ページ中の全行を更新するごとにログページが2ページ出力されるものとする。 T3 ＝ MAX（0.2ミリ秒／行×N行、1ミリ秒／ページ×入出力データページ数）＋ 1ミリ秒／ページ×出力ログページ数次に、“TA”テーブルと“TB”テーブルをそれぞれ次のように区分に物理分割し、処理時間を短縮するために区分別のUPDATE文を並行して実行させることを考えた。 1. C3列を区分キーとして、テーブルを四つの区分に物理分割する。 2. データ入出力処理が競合しないように、各区分を異なるディスクに配置する。 3. 索引のTAX3とTBX3がクラスタ索引になるように、各テーブルの行を並べ替える。 4. ホスト変数に区分キーのいずれかの値を指定した四つのSQL7xを区分別に作成する。SQL7xは、それぞれ該当区分の全行を更新する。 5. UPDATE文は、カーソルを使用し、1、000行を更新するごとにコミットする。そして、“TA”テーブルと“TB”テーブルを物理分割せずに全行更新する場合と、物理分割した上で、1区分だけを全行更新する場合と、全区分を並行して全行更新する場合の各UPDATE文の処理時間を、実験式を用いて推定し、表7に示した。表7 UPDATE文の処理時間（秒）（未完成）注記1　【o】は表示していない。注記2　並行数は、並行して実行するUPDATE文の数を示す。注記3　1秒未満は四捨五入した。設問1　【測定用SQL文の仕様と構文】におけるSQL文について、表5のSQL文中のa～eに入れる適切な字句を答えよ。設問2　【SELECT文の処理時間の測定】におけるFさんの分析Aと分析Bについて、本文中のf～nに入れる適切な字句を答えよ。設問3　【UPDATE文の処理時間の測定】におけるG氏の要望について、(1)～(3)に答えよ。 (1) 要望①に対応するために、FさんはSQL5Aとデッドロックが起きる可能性があるUPDATE文の例を次のように作成した。このSQL文中の【ア】に入れる適切な字句を答えよ。また、このときにデッドロックが起きる理由を、35字以内で述べよ。ここで、WHERE句の述語1個だけで1、000行を更新し、かつ、アクセス経路は索引探索に限るものとする。 UPDATE TA SET C5 ＝ C5 ＋ 1 WHERE 【ア】 (2) 要望②に対応するために推定した表7中の【o】～【q】に入れる適切な字句を答えよ。 (3) SQL6Aを実行したとき、ログ先行出力機能の動作条件である事象A～Cのうち、発生しない事象がある。発生しない事象を記号で答え、その理由を本文中の用語を用いて、25字以内で述べよ。ここで、ほかに実行中の更新処理はないものとする。

表1 測定用テーブルの大きさ。TAとTBについて、行長、行数、全データページ数、1ページ当たりの行数を示す表。

表2 測定用テーブルの列値の範囲と分布。列C1からC6までの値の範囲、重複の有無、分布、初期値、文字列長を示す表。

表3 測定用テーブルの索引の設計。TAとTBの各索引について、索引名、列名、並び順、ユニーク性、クラスタ性を示す表。

表4 測定用SQL文の仕様。SQL1xからSQL7xまでのアクセス方法、結果行、更新処理などの仕様と、TA・TBのアクセス対象に関する注記を示す表。

表5 測定用SQL文の構文（未完成）。SQL1AからSQL7Aまでの未完成のSELECT文・UPDATE文の構文と、分離レベルおよびTB構文に関する注記を示す表。

表6 SELECT文の処理時間の一部（秒）。TAとTBについて、SQL文ごとのSQL処理時間とCPU処理時間を示す表。

表7 UPDATE文の処理時間（秒）（未完成）。TAとTBについて、並行数別のSQL処理、CPU処理、データ入出力処理、ログ出力処理を示す未完成表と注記。

解き方のヒントを見る

前提条件と根拠を対応付けて読む

判断の根拠は、問題中に示された前提条件の中に求めるのが基本です。一般論や経験で補わず、どの記述がどの判断を支えるのかを一つずつ対応付け、書かれていない条件を持ち込まずに検討する姿勢を大切にしましょう。

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

確認すべき観点は一つ、各前提条件の適用範囲です。ある条件が全体に共通なのか、特定の場面や対象だけに限られるのかを読み分け、判断ごとにどの条件が効いているのかを自分の言葉で説明できるか確かめてください。

視点

データベースを扱う問題では、同じ用語でも前提によって扱いが変わることがあります。そのため、知識から結論を先に決めるのは避けましょう。まず前提条件を書き出し、それぞれがどこまで適用されるのかを整理します。そのうえで各判断に照らし合わせると、見落としや思い込みを減らせます。

確認ポイント

各前提条件が問題全体に共通するのか、特定の場面だけに限られるのかを区別できているか
判断や結論のそれぞれについて、根拠とした前提条件の記述を指し示せるか
問題中に書かれていない条件を、一般論や経験から補って使っていないか

正解と解説を見る

解説

RDBMSの性能測定を題材に、測定目的に合ったSQL文の作成、表探索（非同期入出力）と非クラスタ索引探索（同期入出力）の処理時間分析、アクセス経路の違いによるデッドロック、大量更新でのログ出力のボトルネックを問う問題です。公式解答例の数値は、どれも表1・表6・表7の値を本文の近似式や実験式に当てはめると導けます。

問題の全体像

C社のFさんは、1、000、000データページを持つ2つのテーブルTA（20、000、000行、1ページ20行）とTB（2、000、000行、1ページ2行）を使い、SQL文の性能を測定します。本文では、ランダム入出力は同期処理で「SQL処理時間＝CPU処理時間＋入出力時間」、順次入出力は非同期処理で「MAX（CPU処理時間、入出力時間）」と定義されています。さらに、ログ先行出力の条件（事象A～C）、データバッファとログバッファの大きさ（各2、000ページ）も示されています。出題趣旨では、アクセス経路による性能の違いを見極める力と測定結果を分析する力を問い、特に大量更新ではログ出力がボトルネックになり得ることを理解しているかを問う、とされています。設問1は測定目的に合ったSQLの穴埋め、設問2は表6の分析、設問3はデッドロック、区分の並行更新、ログ先行出力を扱います。

設問1(a)

表4のSQL2xは「表探索で全データページを読み込むが、結果行はない」SQL文です。表2でC4は1～1、000の整数なので、「C4 < 1」とすればどの行も条件を満たさず、結果行は0件になります。C4には索引がない（表3）ため、アクセス経路は表探索となり、全ページが読み込まれます。公式解答例では、aは「1」です。

設問1(b)

SQL3xは「非クラスタ索引探索で1、000行の結果行を得る。述語は1個」です。表3でTAの非クラスタ索引はTAX2（C2、ユニーク）とTAX3（C3、非ユニーク）です。C3は1～4の値しかないため、1値で5、000、000行が該当し、1、000行にはなりません。C2は1～2、000万の重複しない整数なので、BETWEENで1、000個の値を指定すればちょうど1、000行になります。索引探索に使える述語の形（本文の仕様5）にも合うため、公式解答例では「C2 BETWEEN 1 AND 1000」です。

設問1(c)

SQL4xは、C3とC4を組み合わせたグループごとにC5の合計を求めます。SELECT句にはグループを特定する列と集計値の両方が必要なので、公式解答例では「C3、C4、SUM(C5)」です。採点講評では、dを正しく答えていながら、cで列名を省いてSUM(C5)だけを書いた解答が散見されたと指摘されています。これでは、各結果行の合計値がどのグループのものか分かりません。

設問1(d)

グループ化の単位は、C3列とC4列の組合せです。公式解答例では、GROUP BY句のdは「C3、C4」です。

設問1(e)

「グループ内の行数が10以上のグループだけ」という条件は、グループ化した後に絞り込む条件です。そのためWHERE句ではなくHAVING句に書き、行数はCOUNT(*)で数えます。公式解答例では「COUNT(*)>=10」です。

設問2(f)

SQL1AとSQL2Aは、どちらも表探索で先頭ページから全データページを順に読み込みます。本文の仕様3では、順次入出力はCPU処理と並行して行われる非同期データ入出力処理とされています。公式解答例では「非同期」です。

設問2(g)

SQL2Aは、SQL処理が1、000秒、CPU処理が10秒です。非同期処理なのでSQL処理時間＝MAX（CPU、入出力）となり、1、000＝MAX（10、入出力）から入出力時間は1、000秒です。1、000、000ページ×1ミリ秒/ページとも一致します。採点講評でも、SQL1AとSQL2Aが同じ100万ページを読み込む点に注目するよう述べられています。公式解答例では「1、000」です。

設問2(h)

SQL1AのCPU処理は2、010秒です。このうち、全データページを順次にデータバッファへ入力するCPU処理は、結果行がないSQL2Aと同じ10秒です。残りの2、010−10＝2、000秒が、全20、000、000行をプログラム内に移動するCPU処理時間になります（1行当たり0.1ミリ秒）。公式解答例では「2、000」です。

設問2(i)

結果行をN行に減らすと、CPU処理時間は10秒＋0.1ミリ秒×Nです。一方、入出力時間は、全ページを読む限り1、000秒のまま変わりません。MAXの値が入出力の1、000秒で決まるのは、10＋0.0001×N≦1、000、つまりN≦9、900、000のときです。これ以下にしても処理時間はもう短くなりません。公式解答例では「9、900、000」です。

設問2(j)

結果行を減らして短くなるのはCPU処理時間だけです。全ページの順次入力時間1、000秒が下限として残ります。公式解答例では「1、000」です。

設問2(k)

T1の第2引数は非同期データ入出力時間です。表探索は結果行の数にかかわらず全データページを読むので、TAの全データページ数である1、000、000ページが入ります。公式解答例では「1、000、000」です。

設問2(l)

SQL3Aは非クラスタ索引探索です。索引キーの順番と行の物理的な並び順が一致しないため、データページをランダムに読むことになります。本文の仕様2から、これは同期データ入出力処理です。公式解答例では「同期」です。

設問2(m)

SQL3AはSQL処理10.1秒、CPU処理0.1秒です。同期処理なので加算式が成り立ち、入出力時間は10.1−0.1＝10秒となります。非クラスタ索引なので、1、000個の結果行は最悪の場合すべて別のページにあり、1行につき1ページを読みます。10秒÷1、000ページ＝1ページ10ミリ秒です。採点講評でも、ページ当たり1行を読み込んだことを示す1000ページを答えられたはず、とされています。公式解答例では「1、000」です。

設問2(n)

T2は同期処理なので加算式です。非クラスタ索引探索では、データページがバッファヒットしない前提で、結果行1行ごとにデータページを1ページランダムに読みます。そのためページ数は結果行数Nと同じになります（N≦1、000の条件付き）。公式解答例では「N」です。

設問3(1)

SQL5Aは、クラスタ索引TAX1（C1）を使い、行を物理的な並び順どおりに更新してロックを取ります。デッドロックを起こすには、同じ行を別の順番でロックするUPDATE文を並行して実行すればよいのです。条件は、述語1個で1、000行を更新し、アクセス経路が索引探索であることです。ここで非クラスタ索引TAX2（C2）を使うと、行の物理的な位置がばらばらの順番で更新されます。公式解答例では、アは「C2 BETWEEN 1 AND 1000」、理由は「アクセス経路によって行を更新する順番が異なるから」または「索引TAX1と索引TAX2とでは行を更新する順番が異なるから」です。35字以内の答案では、「索引（アクセス経路）の違い」と「行を更新（ロック）する順番の違い」の2点を入れます。採点講評では、C1 BETWEEN 1000 AND 1のように逆順での更新を狙ったとみられる誤答が散見されたと指摘されています。更新の順番は述語の書き方ではなく、アクセス経路（索引）で決まる点に注意が必要です。

設問3(2)(o)

T3のログ出力時間は、1ミリ秒/ページ×出力ログページ数です。TAの全行更新では、1、000、000データページ×2＝2、000、000ログページで、2、000秒になります（表7のSQL6x）。区分に分割すると、データ入出力は区分ごとに別のディスクで並行して行われます。しかし、ログバッファはシステムに1個しかなく、4区分を並行して更新しても出力するログページの総数は変わりません（1区分500秒×4）。採点講評でも、ログ出力量が同じであれば、更新処理をいくら分割してもログ出力時間は変わらないことを式T3が示している、とされています。公式解答例では「2、000」です。

設問3(2)(p)

TBのデータ入出力処理は、1区分（250、000ページ分）では500秒です。4区分を並行で実行しても、各区分は異なるディスクに置かれて競合しないため、TAの並行数4の500秒と同じ考え方で500秒のままです。公式解答例では「500」です。

設問3(2)(q)

TBも全データページ数は1、000、000ページなので、全区分の更新で出力するログページは2、000、000ページになります。ログは単一のログバッファから出力されるため、並行して実行しても短縮されず2、000秒です。公式解答例では「2、000」です。

設問3(3)

UPDATE文は1、000行を更新するごとにコミットするので、1トランザクションの更新は高々1、000行です。TAでは1、000行は50データページ、ログは100ページです。どちらも2、000ページのバッファに比べてはるかに少ないため、バッファが一杯になる前にコミット（事象A）でログが出力されます。公式解答例では、記号は「B又はC」です。理由は、Bなら「高々1、000行を更新してコミットするから」または「ログバッファが一杯になる前にコミットするから」、Cなら「高々1、000行を更新してコミットするから」または「データバッファが一杯になる前にコミットするから」です。25字以内の答案では、コミットの単位（高々1、000行）と、バッファが一杯になる前にコミットされることを、本文中の用語を使って書きます。

学習のポイント

MAX式と加算式の使い分け、索引のクラスタ性とアクセスするページ数の関係、単一のログバッファが区分の並行実行でもボトルネックとして残ること、コミット単位とバッファサイズの関係を、自分で計算して確かめることが重要です。

出典：独立行政法人情報処理推進機構（IPA）平成23年度特別データベーススペシャリスト試験午後Ⅰ 問3（公式資料：問題冊子（PDF）／解答例／採点講評）

平成23年度特別 データベーススペシャリスト 午後Ⅰ 問3

問題