記事一覧へ戻る

掲載元 2013年11/12月

テクノロジー：Ask Tom

Oracle Database 12cについて：パート2

オラクルの技術者が、行パターン・マッチングと一時UNDOについて説明します。

Ask Tomの各コラムでは通常、過去2か月の間にユーザーが投稿した質問を3つか4つ取り上げて、それらの質問と回答を紹介しています。しかし、前回と以後3回のコラムでは、Oracle Database 12cの重要な機能のいくつかについて説明します。これらの重要な機能はすべて、2012年にサンフランシスコで開催されたOracle OpenWorldで筆者が行ったプレゼンテーション"12 Things About Oracle Database 12c"で取り上げたものです（このプレゼンテーションのスライドは、asktom.oracle.comの「Files」タブで公開しています）。前回のコラムでは、Oracle Database 12cの最初の3つの機能（改善されたデフォルト値、大きなデータ型、上位Nの問合せ）について説明しました。今回は、新しい行パターン・マッチングについて説明し、一時表のUNDOがOracle Database 12cでどのように変わったかを紹介します。

行パターン・マッチング

当初、SQLでは結果セットを左右に見ることができました。つまり、1行のデータ（多数の表を結合した結果の場合もあります）を確認し、その行に条件を適用できました。その行に含まれる任意の列とその行に含まれる別の任意の列を比較できたのです。しかしながら、1つの結果セットの中を上下に見ることはできませんでした。上下に見る機能は、分析ウィンドウ関数という形でOracle8i Databaseに含まれていました。この分析関数を使用すると、結果セットを左右に見るだけでなく、LAG、LEAD、FIRST_VALUE、LAST_VALUE、NTH_VALUEなどのウィンドウ関数を使用して上下に見ることもできました。これらのウィンドウ関数は極めて強力であるため、SQLを使用したデータ分析のまったく新しい方法となりました。しかし、強力であると同時に、いくつかの制限がありました。

データの処理時には、順列したデータの中にパターンを見つけることが必要になる場合がよくあります。たとえば、ユーザー名、発生したイベント、そのイベントの発生時刻を表すタイムスタンプを表す列で構成される監査証跡データがあるとします。この監査証跡から、"アプリケーションX"、"アプリケーションY"、"アプリケーションZ"を順番に使用し、最後に"アプリケーションX"の特定の部分に戻ったすべてのユーザーを検索する必要があったとします。これらのイベントの前後および中間には他のイベントが無数に存在する可能性があります。検索対象のアクションの間にイベントが1つもないユーザーもいるかもしれませんが、数百または数千のイベントが存在するユーザーもいるでしょう。そうしたパターンを見つけ出すには、分析ウィンドウ関数では不十分です。結果セットの中でどれほどの行を前方向あるいは後方向に確認する必要があるのか分からないため、LAGやLEAD分析は本当の意味では役に立ちません。少なくとも、データを何件も読み飛ばし、複数の自己結合を使用するか、スカラー副問合せを実行することが必要になります。記述すべき問合せは非常に複雑になり、その実行にかかるコストは極端に高くなるでしょう。

そこで、行パターン・マッチングの登場です。実装にはOracle Database 12cのMATCH_RECOGNIZE句を使用します。この句を使用すると、1つのデータセット（結果セット—この例では監査証跡）を対象にして、データの重複がない複数のデータセットに（監査証跡内のユーザー名で）分割し、それぞれのセットを（監査証跡内のタイムスタンプで）ソートし、この分割してソートしたセット内の多数の行にまたがるパターンを検索できます。この監査証跡の例では、アプリケーションX、Y、およびZのレコードをすべて問い合わせ、レコードをユーザー名で分割し、ユーザー名ごとにタイムスタンプでソートします。その後、1件以上のX、1件以上のY、1件以上のZの順に検索し、最後にアプリケーションXの関心のある特定部分を検索します。データを1回パススルーするだけで、このすべての処理を実行でき、自己結合もデカルト結合もスカラー副問合せも不要です。この問合せは比較的簡単に記述できるうえ（MATCH_RECOGNIZE句はとてもコンパクトです）、MATCH_RECOGNIZE句を使用せずに記述した問合せよりはるかに高いパフォーマンスを示します。

この新しい機能を説明するために、株価データセットを分析することにします。株式アナリストは通常、株価データ中に見られるV字やW字のパターンに関心があります。つまり、株価が高値を付けた時点、その後下落を続け上昇に転じた時点を知りたいのです。アナリストは、V字を形成するパターンがいつ始まり、いつ最安値を付け、いつまた最高値を付けたのかを知りたいのです。説明を始めるために、簡単な株価表を定義します。 

SQL> create table stocks
  2  ( symbol   varchar2(10),
  3    tstamp   date,
  4    price    number,
  5    primary key (symbol,tstamp)
  6  )
  7  organization index
  8  /
Table created. 

次に、分析に必要な少しのデータを作成します。 

SQL> declare
  2    l_data sys.odciNumberList :=
  3           sys.odciNumberList
  4           ( 35, 34, 33, 34, 35,
  5           36, 37, 36, 35, 34, 35, 
                              36, 37 );
  6    l_cnt  number := l_data.count;
  7  begin
  8    for i in 1 .. l_cnt
  9    loop
 10        insert into stocks
 11        ( symbol, tstamp, price )
 12        values
 13        ('XYZ', sysdate-l_cnt+i, 
                           l_data(i) );
 14    end loop;
 15    commit;
 16  end;
 17  /
PL/SQL procedure successfully completed. 

このデータセットはとても小さいため、リスト1に示すとおり、SQL*PlusでASCIIアートを使用して簡単に分析できます。

コード・リスト1：ASCIIアート・パターンの表示

SQL> select symbol, tstamp, price,
  2         rpad('*',price,'*') hist
  3    from stocks
  4   order by symbol, tstamp;

SYMBOL     TSTAMP         PRICE HIST
—————————— —————————————— ————— ——————————————————————————————————————
XYZ        01-SEP-12         35 ***********************************
XYZ        02-SEP-12         34 **********************************
XYZ        03-SEP-12         33 *********************************
XYZ        04-SEP-12         34 **********************************
XYZ        05-SEP-12         35 ***********************************
XYZ        06-SEP-12         36 ************************************
XYZ        07-SEP-12         37 *************************************
XYZ        08-SEP-12         36 ************************************
XYZ        09-SEP-12         35 ***********************************
XYZ        10-SEP-12         34 **********************************
XYZ        11-SEP-12         35 ***********************************
XYZ        12-SEP-12         36 ************************************
XYZ        13-SEP-12         37 ************************************* 

このデータセットは13行しかないため、総当たり方式でも十分に分析でき、求めているパターンをすぐに見つけることができます。このデータでは2つのV字型をはっきり確認できます。9月1日にV字の起点があり、9月3日に底を打ち、9月7日に再度頂点に達しています。2つ目のV字も簡単に見つかります。こちらは7日に始まり（2つ目のパターンの起点は1つ目の終点です。基本的なSQLではどのようにすればこれをレポートできるか考えてみてください）、10日に底を打ち、13日に再度頂点に達しています。では、どうすれば、対応するこれら3つの日付をそれぞれに含む2つのデータ行を取得できるでしょうか。Oracle Database 11g Release 2ではどのようなSQL文を記述できるか考えてみてください。その前に、求めている情報が含まれる2つの行をどのようにして出力できるか考えてみてください（9月7日の繰り返し部分が難しいところです）。SQLで記述することはできますが、かなり複雑な文になります。そして、ひいき目に見てもパフォーマンスには問題があるでしょう。

それが、MATCH_RECOGNIZE句を使用すれば、かなり簡単な問合せになります。たとえば、次のようになります。 

SQL> SELECT *
  2  FROM stocks MATCH_RECOGNIZE
  3  ( PARTITION BY symbol
  4    ORDER BY tstamp
  5    MEASURES
  6       STRT.tstamp AS start_tstamp,
  7       LAST(DOWN.tstamp) AS 
          bottom_tstamp,
  8       LAST(UP.tstamp) AS end_tstamp
  9    ONE ROW PER MATCH
 10    AFTER MATCH SKIP TO LAST UP
 11    PATTERN (STRT DOWN+ UP+)
 12    DEFINE
 13      DOWN AS 
         DOWN.price < PREV(DOWN.price),
 14      UP AS UP.price > PREV(UP.price)
 15  ) MR
 16   ORDER BY MR.symbol, 
               MR.start_tstamp;

SYMBOL  START_TST BOTTOM_TS END_TSTAM
——————  ————————— ————————— —————————
XYZ     01-SEP-12 03-SEP-12 07-SEP-12
XYZ     07-SEP-12 10-SEP-12 13-SEP-12 

では、この問合せを1行ずつ解説していきます。最初の2行は、結果セットを定義する問合せ定義部です。この結果セットを後から分割し、ソートしてパターンを適用します。この問合せは、結合、集計を使用するものなど、どのような問合せにすることもできます。MATCH_RECOGNIZE句は2行目の終わりから始まり、3～15行目に含まれています。

3行目と4行目ではパーティションを設定し、このデータ・ストリームを整理します。パーティションの設定には銘柄記号を使用し、各銘柄内のデータは古いものから新しいものの順にタイムスタンプでソートします。この結果セットはここでは確定性を持っている点に注意してください。主キーはSYMBOL, TSTAMPであるため、この問合せを実行するたびに一連の行は確定されていきます。この事実はパターン・マッチングにおいては通常とても重要です。つまり、パターンを形成する行が"安定した"状態（確定された状態）で観察されているかどうかが分かっている必要があります。そうでなければ、同じ問合せを2回実行した場合に異なる答えが戻されてしまいます。正しい結果を得るにはこの確定的な動作が望ましく、また通常は必須である理由について、詳しい説明および例はbit.ly/Z6nxLLを参照してください。

では、数行飛ばして問合せのパターン部分を見てみましょう。 

 11    PATTERN (STRT DOWN+ UP+)
 12    DEFINE
 13      DOWN AS 
         DOWN.price < PREV(DOWN.price),
 14      UP AS UP.price > PREV(UP.price) 

関心があるデータ・パターンは、任意のレコードの後に株価が下落しているレコードが1つ以上続き、株価が上昇しているレコードが1つ以上続くというものです。11～14行目に表現されているパターンがそれです。検索するのは開始レコードです。これは、"DOWN"レコードが1つ以上続き、"UP"レコードが1つ以上続いている任意のレコードです。"任意のレコード"を特定するために、startを短縮したSTRTという相関名を使用しています（この名前はかなり恣意的なものです。有効な識別子を使用することもできました）。STRTはDEFINE（定義部）に含まれていないため、どのレコードも適合する可能性があります。つまり、適合してSTRTレコードになる可能性は、結果セット内のすべてのレコードにあります。ただし、STRTレコードになるには、後にDOWNレコードが1つ、できればそれ以上（DOWNの後ろにある+はそういう意味です）続いている必要があります。さて、この例ではDOWNに定義があります。あるレコードをDOWNレコードと見なせるのは、そのレコードの株価が結果セット内の前のレコードの株価より低い場合のみです（結果セットは確定的にソートされたデータ・ストリームであることを思い出してください）。この定義に適合するレコードがある限り、DOWNレコードの照合を続けます。最終的に、DOWNレコードになるための条件を満たさないレコードに到達したら、それがUPレコードであることを期待してパターン・マッチングを続行します。UPレコードは、前のレコードの株価より高い株価を持つレコードと定義されています。

次に、11～15行目に記述されているパターンについて、実際のデータセットを使用して説明します。まず、最初のレコード（9月1日のもの）を処理します。STRTレコードを定義する条件はないため、このレコードはSTRTレコードになる条件を満たしています。最初のレコードがパターン・マッチングのSTRTレコードになることができるのは、後続のレコードがDOWNレコードの場合のみです。そのため、結果セットの処理を進め、2番目のレコードを取得し、DOWNの定義をこのレコードに適用します。このレコードの34という株価は、前のレコードの株価（35）より低いでしょうか。株価は低いため、パターン・マッチングを続行します。3番目のレコードに進み、再度、定義を適用します。これもDOWNレコードです。続いて、4番目のレコードを取得します。これはDOWNレコードではありません。そのため、パターン・マッチングのSTRT DOWN+の部分は終了しました。次は、4番目のレコードがUPの定義に適合しているかどうかを検証する必要があります。案の定、このレコードは適合しています。パターンが見つかりました。つまり、DOWNレコードが後ろに1つ以上続き、その後ろにUPレコードが1つ以上続くレコードがあると分かったわけです。しかし、パターンはここで終わりません。正規表現を使用する場合と同様で、もっとも大きい適合パターンを見つけるためにマッチングを続行します。5、6、7番目のレコードを確認すると、これらはすべてUPレコードであることが分かります。そのため、これらはパターンの一部になります。続いて、8番目のレコードを取得します。このレコードの株価は前のレコードの株価より高くないため、このレコードはUPの定義に適合しなくなっています。最初のパターンが終了しました。このパターンに適合するのは、9月1日～7日の行セットです。これで、この情報を出力できる状態になりました。

MATCH_RECOGNIZE句に戻ると、5行目からMEASURES句が始まっています。この句は問合せの出力を識別します。この例では、パターンに含まれる行のうち、STRT.tstamp（開始レコードと関連付けられているタイムスタンプ）を使用してパターンの始まりを示すタグを付けるべき行を求めています。また、最後のDOWNのタイムスタンプ、LAST(DOWN.tstamp)と、最後のUPのタイムスタンプ、LAST(UP.tstamp)も求めています。これら3つの値は、V字パターンの始まり、底、終わりを表します。ONE ROW PER MATCH（問合せの9行目）を使用することで、MATCH_RECOGNIZE句の動作は集計関数とよく似たものになります。7行分のデータに及ぶこの1つの大きなパターンについて、1行で3つの基準日を出力する必要があります。こうすると、数百万行が数十行か数百行、あるいは数千行という把握できる程度の分量になるため、データの分析が楽になります。

これで1つ目のパターンが見つかったため、2つ目の検索を始められます。今度は、"次のパターンを探し始める場所"が問題になります。次のパターンの検索を2行目から開始することもできますが、このパターンの場合は意味がありません。なぜなら、V字型の中に繰り返しV字型が見つかることになり、それはあまり興味深いことではないためです。次のパターンの検索を開始するのが望ましい場所は、最初のパターンの最後の行です。前回の検索を終了した場所を取得する必要がありますが、これは問合せの10行目（AFTER MATCH SKIP TO LAST UP）で実行しています。この例では、最初のパターンの最後の行が次のパターンの最初の行になる可能性が認められるわけです。そして、出力を見れば分かるとおり、実際、9月7日が次のパターンの最初の行になります。9月7日は事実上、この結果セットに2回出力されます（通常のSQLで単一行を結果セットに2回出力させるには何をする必要があったか考えてみてください）。

これで完了です。適合するパターンをすべて見つけ、銘柄記号と寄付き値でソートし、表示します。この例では、13行を取得し、それぞれ3つの点のデータを表す2行に変換しました。これで、パターンを確認してその解釈を始めるのがとても簡単になりました。このデータをさらに分析したり、集計したりすれば、"特定の銘柄記号について、V字の開始から終了までの平均日数は何日か"、"最大日数は何日か"、"通常、1年間に何回V字が現れるか"、"何年かに1度、または何か月かに1度株価が底値を打つ、という共通する時期はあるか"といった質問に回答できることが分かります。

MATCH_RECOGNIZEの構文について詳しくは、bit.ly/15x5p0oを参照してください。特に、Oracle Databaseデータ・ウェアハウス・ガイド12cリリース1 (12.1)の"パターン一致用SQL"の章は、初めての方に最適なリソースです。

一時UNDO

次は、Oracle Database 12cでグローバル一時表のUNDOを処理する新しい方法について説明します。

かつてDBAや開発者の多くは、グローバル一時表上の操作（INSERT、UPDATE、MERGE、およびDELETE）によってREDOが生成されると分かり驚きました。通常の永続表の場合に見られるREDOより少ないものの、それでもかなりの量だったためです。彼らは最初、「このREDOはどこから来るのだろうか」と疑問を持ちました。Oracle Databaseがグローバル一時表上に生成せざるを得ないUNDOから来る、というのが答えです。グローバル一時表のUNDOは必ず生成されます。アプリケーションがロールバックを発行した場合や、読取り一貫性のある結果を提供するために必要だからです。開発者がなんらかの情報をグローバル一時表に挿入し、その後、この表に対してSELECT文を発行し、さらにUPDATEまたはDELETEを発行する場合、読取り一貫性のルールでは、UPDATEまたはDELETEによる影響をSELECT文から見ることはできないことになっています。これを可能にするために、データベースにUNDOが必要なのです（Oracle Databaseの中心思想の1つである読取り一貫性について詳しくは、bit.ly/120NB0wを参照してください）。

つまり、グローバル一時表を変更する場合はUNDOを生成する必要があり、UNDO表領域はREDOで保護する必要があります。インスタンスがクラッシュした場合は、UNDO表領域をリカバリし、インスタンス障害が発生したときに処理中でまだコミットされていなかったトランザクションをすべてロールバックできるようにするために、REDO情報が必要です。さらに、Oracle Database 12cより前は、UNDO表領域に一時オブジェクト用UNDOと永続オブジェクト用UNDOの区別がありませんでした。

しかし、Oracle Database 12cからは、一時UNDOを一時表領域に格納でき、永続オブジェクト用UNDOをUNDO表領域に格納できます。これは実質的に、一時表上の操作によってREDOが生成されることがなくなることを意味します。グローバル一時表を使用する大規模なバッチ操作を行ったとすると、生成されるREDOの量が大幅に減少していることに気付くでしょう。さらに、UNDO表領域に生成されるUNDOも少なくなります。つまり、小さいUNDO表領域でサポートできるundo_retention時間が長くなるのです。

この新たな変更によるもう1つの嬉しい副次効果は、読取り専用Oracle Active Data Guardデータベースでグローバル一時表を利用できるようになったことです。そう、読取り専用データベースに、読取り/書込みが可能なグローバル一時表が用意されたのです。これまで、グローバル一時表の用途の1つは、レポート・システムでの使用（問合せの中間結果の格納）であったため、この変更によりOracle Active Data Guardレポート作成データベースの実用性がよりいっそう増しました。

Oracle Database 12cでグローバル一時表にUNDOを生成する方法は、新しいinit.oraパラメータ（temp_undo_enabled）で制御します。パラメータの設定には、TRUEとFALSEの2つがあります。デフォルトではFALSEに設定されており、UNDOはこれまでと同じ方法で生成されます。たとえば、次のようになります。

SQL> alter session 
set temp_undo_enabled = false;

Session altered.

SQL> insert into gtt
  2  select *
  3    from all_objects;
87310 rows created.

Statistics

———————————————————————————
…
     566304  redo size
…

SQ> update gtt
  2     set object_name = 
        lower(object_name);
87310 rows updated.

Statistics
————————————————————————————
… 
    8243680  redo size
… 

ご覧のとおり、INSERTでは約50MB（566,304バイト）のREDOが生成され、UPDATEでは8MB以上（8,243,680バイト）のREDOが生成されています。ところが、一時UNDOを有効にすると、次のようになります。

SQL> alter session 
set temp_undo_enabled = true;

Session altered.

SQL> insert into gtt
  2  select *
  3    from all_objects;
87310 rows created.

Statistics
———————————————————————————————
…
        280  redo size
…

SQL> update gtt
  2     set object_name = 
        lower(object_name);
87310 rows updated.
Statistics
———————————————————————————————
…
          0  redo size

… 

REDOはわずかしか、またはまったく存在しません。

読取り専用Oracle Active Data Guardデータベースでは、REDOがまったく存在しなくなることもあります。つまり、REDOの生成とREDOの適用を減らし、プライマリ・データベースのUNDO保存期間を長期化できる可能性があるということです。

▲ ページTOPに戻る

記事一覧へ戻る