Database
技術記事
Ask Tom
テクノロジー： Ask Tom反学習とMODEL句についてTom Kyteオラクルの技術者が、Oracle Exadata と MODEL 句について学び直します。 AskTom（asktom.oracle.com）では、Oracle Exadata Database Machineに関する質問をよく受けます。 そのような質問の多くは、学び直すこと、さらに言えば忘れること（私が以前に説明した概念である“反学習”）に関するものです。 データウェアハウスでOracle Exadata Database Machineを使用する場合に忘れる必要のある概念のいくつかを、ここで簡単に説明します。 まず、従来うまくいっていた技法がOracle Exadata環境で不適切になる理由を認識するために、Oracle Exadataの基本設計を理解する必要があります。 Oracle Exadata Database Machineは巨大であり、一般的な構成では8台のサーバー・ノードが14のストレージ・アレイと組み合わされます。 その各コンポーネントには12のCPUコアが搭載され、データベース・サーバー・レベルでは96コア、ストレージ・レベルでは168コアをデータ処理に利用できます。 これは非常に高いCPU処理能力です。 さらにすべてのOracle Exadataストレージ、I/O帯域幅、キャッシュ、CPUがデータベース専用で動作します。 このような構成は、現在の多くの環境とは異なります。多くの環境では、すべてではないとしても多くのリソースが他の多くの機能と共有されています。 一貫性のある保証されたパフォーマンスを実現するための秘訣は、他の機能とリソースを共有しないようにすることです。 このように、巨大なサーバー・マシンが大容量の専用ストレージと組み合わされており、このストレージには、ストレージ層でデータを処理するためのCPUとメモリが搭載されています。 このことだけでもすばらしいのですが、Oracle Exadataはさらに2段階先に進んでいます。 まず、このストレージのすべてが、一連の40ギガビット/秒InfiniBandスイッチを介してデータベースに接続されています。この専用プライベート・ネットワークの唯一の目的は、ディスクからデータベース・サーバーのCPUに、最大75GB/秒の速度でデータを転送することです（この75GB/秒というパフォーマンスについては、bit.ly/tkTYqDを参照してください）。 これを通常の企業ネットワークと比較しましょう。典型的な企業ネットワークは、共有ネットワーク上で100MB/秒もしくは1GB/秒の能力を持つ1ギガビット/秒あるいは10ギガビット/秒のネットワークを使用しているでしょう （そして、リソースが共有されているため、実際に100MB/秒あるいは1GB/秒の転送レートを実現することはできないでしょう）。 第2段階として、ディスクの情報をストレージ・レベルでキャッシュするための専用Exadata Smart Flash Cacheフラッシュ・メモリが最大5TB追加されています。 Oracle Exadataでは、ストレージ・レベルの5TBのフラッシュ・メモリに加えて、約1TBのデータベース・サーバー・メモリを情報のキャッシュに使用できます。 Oracle Exadataのサーバー、ストレージ・アレイ、ネットワーク・スイッチ、メモリはすばらしいのですが、テレビの深夜番組でよく耳にするように、“これだけではありません”。 ストレージ・レベルのCPUとメモリを使用して、ほかのソフトウェアを実行することができます。 Oracle Exadataには従来型のデータベース・サーバーとして8つのノードがありますが、ストレージ・レベルのソフトウェアは、ディスクに保存されているデータがOracleデータベースのブロックであることを理解しています。 ストレージ・アレイが、I/Oの実行方法を知っているだけではなく、実行しているI/OがOracleデータベースのブロックを取得するためのものであることを理解するようになったのです。 そのため、ストレージでデータベース・ブロックを読み取るだけではなく、処理することも可能です。 仮に、従来のSANサーバーやNFSサーバーを使用して1TBのデータの全体スキャンを実行する場合、1TBのデータを読み取るだけではなく、その同じ1TBのデータをネットワーク経由で送信し、データベース・サーバー・レベルで処理する必要があります。 しかし、Oracle Exadataのストレージ・セルでは、これらの処理は必要ありません。 ストレージ・セルが1TBの情報を読み取り、（ネットワーク経由でデータを送信する前に）処理して、必要なデータのみをデータベース・サーバーに送信します。 たとえば、“SELECT A, B, C FROM some_table WHERE D > 42”のような問合せを実行すると、ストレージ・セルが全表スキャンをパラレル実行します（14のストレージ・セルのすべてが表のそれぞれの部分を処理します）。データの読み取りでは、列A、B、C、Dのみがデータベース・サーバーに送信するために必要なデータだと見なされます。 さらに、Dが42よりも大きい行のみがデータベース・サーバーに送信されます （ほかに数百の列があるかもしれませんが、ストレージ・セルはそれらの列をデータベース・サーバーに送信しません）。 要するに、必要な行と列のみがデータベース・サーバーに送信されます。 1TBのデータをスキャンした場合でも、ネットワーク経由で送信されるデータは、たとえば200MBとなります。これは大きな違いです。 まとめると、大容量のディスク・ストレージを使用して、優れた読み取りパフォーマンスを実現し、必要な行と列のみを取得でき、必要に応じて大量の情報を転送でき（75GB/秒の転送レート）、さらに結果のデータを8台のデータベース・サーバー・ノードと96のCPUコアを使用して処理できます。 つまり、Oracle Exadataを使用することで、突如としてI/Oが問題ではなくなります。 これまで、データウェアハウスにおける主要な制約要因はI/Oでした。そして、多くのシステムが“不十分な構成”であるため、パフォーマンスが大幅に低下していました。 この方程式からI/Oを取り除けば、状況は一変します。 I/Oに制約されるシステム向けに構築したプロセスは、I/Oの制約がないシステムでは効率的に動作しません。 もっとも、そのようなプロセスは、I/Oに制約されるシステムでも効率的ではありません。 そのようなプロセスが最適解でないとしても、I/Oパフォーマンスが貧弱なため、そのことは問題になりませんでした。 以上のことは何を意味するでしょうか。 Oracle Exadataのためには何を忘れる必要があるでしょうか。 まず、ロードと問合せのプロセスについて検討する必要があります。 AskTomでは、ロードを高速化する方法についてよく尋ねられます。そういう人たちは、数時間の処理時間で5,000,000件の新規レコードをロードすることができないと訴えます。 数百万行のロードに数時間かかる場合は、どこかに重大な問題があります。なぜならば、私はラップトップを使って数百万行を数分でロードできるからです。 重要なのは、ロードの方法です。 多くの人が採用しているのは、私がスローバイスローと呼ぶ方法です。 つまり、1行ずつロードして検証するという処理を数百万回繰り返しています。 必要なことは、数百万行のロードと検証を一度に行うことです。 この2つの方法はスケーラビリティがまったく異なるため、大きな違いが生じます。 数字で考えてみましょう。 5,000,000行をロードする必要があり、スローバイスロー・プロセスでは検証/変換フェーズで各行の処理に1ミリ秒（1/1000秒）かかるとすると、5,000秒、つまり約1.5時間かかる見込みになります。 しかし、このプロセスには、それぞれの行のネットワーク・ラウンドトリップ、データベースCPU、クライアントCPU、ロードする入力ファイルの読み取りなどのさまざまな手順が含まれるため、1ミリ秒というのはおそらく非常に控え目でしょう。 1行ずつの方法で5,000,000行のロードを行うと、行数が進むにつれてすぐにボトルネックになることが簡単にわかるでしょう。 大きな数値（数百万や数億）を小さな数値（1行あたりの処理時間）と掛け合わせても、その結果はやはり大きな数値です。 一方、ダイレクト・パス処理とパラレル問合せを使用してデータをバルク・ロードし検証と変換を行う場合は、他の方法では数時間かかる処理を数秒ないし数分で実行できます。 信じられない場合は、YouTubeに公開されている次の一連のビデオを参照してください。 これらのビデオは、StatspackおよびAutomatic Workload Repositoryの生みの親であるGraham WoodとオラクルReal World Performanceグループのシニア・ディレクターであるAndrew Holdsworthを中心とするチームによって制作され、Holdsworthによって配信されています。 これらのビデオは、1TBのデータ（80億行）をOracle Exadata Database Machineにロードし、統計情報を収集し、すべてのデータを検証する処理を約20分で行う方法について示しています。そう、わずか20分です。 これは、パラレル・ダイレクト・パス処理を使用しなければ実現できません。 次のビデオはそれぞれ、Migrate a 1 TB Datawarehouse in 20 Minutesのパート1、パート2、パート3です。 大規模なバルク・ダイレクト・パス・ロードを使用することを決定した後は、ツールの選択も重要になります。 これまで、大規模なロードを実行するためのツールとして、多くの人がSQLLDRを使用してきました。 SQLLDRは1900年代には十分な機能を果たしましたが、21世紀においては適切なツールではありません。 新世紀には新しいツールが求められます。ロードの問題がはるかに大きくなったためです。 SQLLDRのしくみについて確認しましょう。 SQLLDRを使用してパラレル・ダイレクト・パス・ロードを実行する場合、n個のSQLLDRプロセスを開始するスクリプトを作成する必要があります（ここで、nは並列度です）。 それぞれのSQLLDRプロセスに1つずつ、n個の入力ファイルが必要になります。 さらに、ロード先のセグメントに関するメタデータをキャッシュするため、それぞれのSQLLDRプロセスに大量のメモリを割り当てる必要があります。このメモリは相当な容量になります。 現在では、表に数万（たとえば40,000）のパーティションが含まれ、それぞれのパーティションに数百のエクステントが含まれるような状況も珍しくはありません。 その場合、表構造に数百万件の情報が含まれるかもしれません。それぞれのSQLLDRプロセスで、このメタデータを何度もキャッシュする必要があります。 その結果、SQLLDRだけのために1TBものメモリが必要になります。 このジレンマの解決策として、 Oracle9i Databaseで導入された外部表を使用できます。 外部表を使用すれば、ファイルまたはプログラムの出力を表ソースとして、SQLを直接使用して読み取ることができます。 つまり、“SELECT * FROM some_file”を呼び出して、データベースの外部にあるファイルから直接データを読み取ることができます （外部表の強力な機能を示す記事については、bit.ly/vYSoDoを参照してください）。 SQLは、パラレルで実行でき、ダイレクト・パス・ロードを実行できるため、表のパラレル・ダイレクト・パス・ロードが単純な“INSERT /*+ append */ INTO some_table SELECT * FROM some_file”という文になります。 データベースの外部でスクリプトを実行する必要はありません。 CPUが増えた（または減った）場合に並列度を変更することも、非常に簡単です（cpu_count初期化パラメータを異なる値に設定したデータベースで同じSQL文を実行するだけです）。一方、SQLLDRで並列度を変更するには、スクリプトの再プログラミングが必要です。 さらに、ダイレクト・パス・ロードでは、メモリを考慮する必要もなくなります。 SQLではシステム・グローバル領域（SGA）の共有プールを使用するため、表のメタデータのキャッシュは1つになります。SQLLDRプロセスあたり1つにはなりません。 最後に、外部表を使用すれば、SQLLDRを使用する場合よりも柔軟性が大幅に高くなります。 Oracle Exadataのために次に忘れる必要があるのは、問合せのチューニング方法です。 問合せのチューニング技法として誰もが思いつく方法は、索引の使用です。 索引は、数十、数百、あるいは数千の行を処理するシステムでは十分に機能します。しかし、数百万あるいは数億の行を対象とする場合、索引では対応できません。 これを理解するために、もう一度算数をしてみましょう。 表の1%を取得して処理するとします。 ほとんどの人の頭に浮かぶ言葉は索引でしょう。行の1%のみを取得するのですから、索引が適切でしょう。 では、対象の表が100,000,000行だとしたら、どうでしょうか。 それでも索引が適切でしょうか。 計算して確認しましょう。 まず、必要な表ブロックはバッファ・キャッシュ内に存在しない可能性が高いと仮定することができます。そのため、表ブロックの大部分を読み取るために物理I/Oを実行する必要があるでしょう。 また、このデータベースに他のオブジェクトが数多く含まれているため、結果セットをバッファ・キャッシュに保持することができない可能性が高いと仮定します（データベースのブロック・サイズが8Kだとすると、キャッシュする必要のある表ブロックは合計で7.6GBにもなります）。 （つまり、問合せの実行を繰り返しているうちに、最初の実行時と同じ回数のI/Oを実行する必要が生じるようになります）。 さらに、計算を容易にするために、索引はすべてキャッシュされ、“自由に”アクセスできると仮定します。 索引からブロックを取得するためにかかる時間が0.0秒になるため、表ブロックのみを検討すればよくなります。 ここで、適切なI/Oシステムはランダム・シーク、読み取り、転送を約5ミリ秒で実行すると考えれば、1,000,000件のランダムI/Oの実行には5,000秒かかることになります。つまり、この問合せには、I/Oだけで1.5時間もかかることになります。 しかし、索引スキャンを全表スキャンに変更すると、これらのI/Oの実行時間は数秒もしくは数分になります （この点に関する有名な例については、bit.ly/srjb0cを参照してください）。 Oracle Exadataを使用する場合、オンライン・トランザクション処理（OLTP）のために学習した経験則のほぼすべてを忘れる必要があります。 たとえば、“表のごく一部を取得する場合、索引を使用する”と学習したでしょう。 このルールを“表から数行を取得する場合、索引を使用する”とする必要があります。 Oracle Exadataを使用する場合、大量のデータの処理には大規模なバルク処理を採用する必要があります。また、少量のデータの処理にはCPUとランダムI/Oを活用する処理を実行する必要があります。 要するに、考え方を変える必要があります。 Oracle Exadataのために変える必要のある次の考え方は、リソース管理についてのものです。 かつては、I/Oがボトルネックであり、I/Oが事実上のリソース・マネージャとなっていました。 データウェアハウスを忙しく稼働させるように、つまりCPUを働かせるようにするためには、可能な限り多くの問合せを同時に処理させようとするでしょう。 I/Oリソースの不足によって大部分の問合せの処理が遅れるため、どの時点でもCPUリソースが過剰に要求されることはありません。一度に数件のみの問合せを実行すると、データベースがアイドル状態であるように見えるでしょう。 Oracle Exadataを使用してI/Oの問題を“修正”すると、I/Oのボトルネックが取り除かれ、I/Oリソースの不足によって問合せが遅れることがなくなります。 代わりに、すべての問合せがお互いにCPUリソースを奪い合うことになります。 このような状況で、さらにCPUに高い負荷のかかることを同時に実行しようとすれば、重大な問題が発生し、マシンのプラグを引き抜く必要が生じるでしょう。 ここで必要なものは、リソース管理とキューイングです。 目標は、マシンを十分に使用し、かつ過剰に使用しないようにすることです。 問合せによって“マシンをフル稼働”させますが、フル稼働になったら、リソース（CPUやメモリ）が利用できるようになるまで新しい問合せを待機させます（つまり、キューイングします）。 マシンが過負荷になることを許してしまうと、適切な時間に応答を得ることができなくなります。 一方、ちょうどできるだけの量の作業を実行し（それ以上は1つも実行しないで）、現在のワークロードの一部の処理が完了するまで新しい処理要求をキューイングすれば、一定期間待機しなければならない人を含めて、すべての人がより早く応答を得ることができます。 ここでも、私の話によって納得していただけない場合は、実際の例をご自分で見ることができます。 YouTubeに公開されているHoldsworthのMigrate a 1 TB Datawarehouse in 20 Minutesパート4（bit.ly/sYSLih）を紹介します。 この例では、Holdsworthが、同時に実行しようとする多数の問合せを管理するためのさまざまな方法を示しています。その結果、全体的な実行時間、つまり、平均実行時間、最小実行時間、最大実行時間の観点から、キューイングがもっとも優れていることが分かります。 以上をまとめると、Oracle Exadataのために忘れる必要のあるおもな概念は次のとおりです。
リストのグループ化連続したデータをグループ化するSQL文を記述する必要があります。 次にデータを示します。 study_site tot_rec それぞれが65,000を超えない小計を計算する必要があり、次のように、それぞれの開始サイト番号、終了サイト番号、レコードの小計を取得する必要があります。 start_site end_site running_total この出力から分かるように、サイト1001、1002、1003が第1グループを形成し、サイト1004が第2グループの開始サイトとなっています （サイト1004を第1グループに残すと、小計が110,500となり65,000を上回ります。そのため、第1グループは一杯であり、第2グループを開始しています。 第2グループは1004のみです。これは、1005をこのグループに含めると、小計が65,000を上回るからです）。 これを単純なSQLのみで効率的に解決することは困難です。 ここでは、3つの方法を提案します。 第1の方法は私が最初に思い付いたものであり、PL/SQLのパイプライン・ファンクションを使用しています。 第2の方法と第3の方法は、ユーザーがAskTomに投稿したものです。 第2の方法では、Oracle Database 11g Release 2の新機能である、再帰的副問合せのファクタリングを使用しています。また、第3の方法では、Oracle Database 10gの新機能であるMODEL句を使用しています （この質問と回答の展開や、SQLに基づく2つの方法の洗練化については、bit.ly/vJiMVwで参照できます）。この回答より、この問題への取組み方は1つだけではないことが分かります。 私は特にMODEL句を使用する方法を気に入りました。そのため、新年の誓いとして、2012年中にMODEL句を完全に習得しようと思いました。 今後はより多くの質問に対する回答でMODEL句を使用することになるでしょう。 この質問には、ビンパッキング問題も関連しています。ビンパッキング問題の目標は、行をグループ化し、何らかのしきい値に達したら新しいグループを開始することです。 これを“純粋なSQL”で実現するのは極めて難しいため、私の解決策では少々ずるをして、PL/SQLを少し使用しています。 PL/SQLパイプライン・ファンクションを実装するには、最初にいくつかのオブジェクト型を作成する必要があります。 リスト1に示すこれらのオブジェクト型は、ファンクションが返す“表”を表現します。 コード・リスト1：PL/SQLパイプライン・ファンクション向けのオブジェクト型の作成 SQL> create or replace type myScalarType as object 2 ( study_site number, cnt number, the_group number ) 3 / Type created. SQL> create or replace type myTableType as table of myScalarType 2 / Type created. 次に、必要な結果を返すプロシージャ・コードが必要です。 基本的には、このファンクションに対して、SYS_REFCURSOR型を使用して結果セットを渡します。 この結果セットは、必要な行を選択し並べ替えたものです。STUDY_SITEを基準として並べ替えているため、データを昇順で処理できます。これは、この質問で要求されていることです。 さらに、65,000という値をPL/SQL内にハードコーディングするのではなく、65,000という“しきい値”を渡します。これにより、柔軟性が高まり、このしきい値がパラメータになります。 実装したコードをリスト2に示します。 コード・リスト2：PL/SQLパイプライン・ファンクションによる解決策
SQL> create or replace function foo( p_cursor in sys_refcursor,
p_threshold in number )
return myTableType
2 pipelined
3 as
4 type array is table of t%rowtype index by binary_integer;
5
6 l_data array;
7 l_running_total number := 0;
8 l_group number := 1;
9 n number := 100;
10 begin
11 loop
12 fetch p_cursor bulk collect into l_data limit N;
13 for i in 1 .. l_data.count
14 loop
15 l_running_total := l_running_total + l_data(i).cnt;
16 if ( l_running_total > p_threshold )
17 then
18 l_group := l_group + 1;
19 l_running_total := l_data(i).cnt;
20 end if;
21 pipe row( myScalarType( l_data(i).study_site,
l_data(i).cnt, l_group ));
22 end loop;
23 exit when p_cursor%notfound;
24 end loop;
25 close p_cursor;
26 return;
27 end;
28 /
お分かりのように、ここではSYS_REFCURSORから行を読み取り、渡されたしきい値を小計が超えた場合に、カウンタをリセットしてグループ番号に1を加えています。 PIPE ROW()コールが、作成されたデータを返します （パイプライン・ファンクションは、リレーショナル問合せの中に小さなプロシージャ処理を実装する場合に適しています）。 このファンクションを使用して最終的な出力を確認するためには、リスト3の問合せを実行します。 コード・リスト3：PL/SQLパイプライン・ファンクションによる解決策を実行するための問合せ
SQL> select min(study_site), max(study_site), sum(cnt)
2 from (
3 select *
4 from TABLE( foo( cursor(select study_site, cnt from t
order by study_site), 65000 ) )
5 )
6 group by the_group
7 order by the_group
8 /
MIN(STUDY_SITE) MAX(STUDY_SITE) SUM(CNT)
--------------- --------------- --------
1001 1022 48081
1023 1044 62203
1045 1045 3360
このPL/SQLパイプライン・ファンクションで問題を解決できますが、前述のとおり、SQLによる解決策も存在します。 最初に使用するのは、新機能の再帰的副問合せのファクタリングです。 これはOracle Database 11g Release 2の新機能であり、私はこれが登場したときにbit.ly/tYWtBGで検討しました （この機能のしくみや動作の説明については、「Recursive Subquery Factoring」を参照してください）。 リスト4には、この再帰的副問合せによる方法が含まれています。 コード・リスト4：副問合せのファクタリングによる解決策
with data1 as
(
select row_number()over(order by study_site) as rno,
study_site Site_no, cnt tot_rec from t
)
,
rec (rno, Site_no, tot_rec, Total, flg) as
(
select rno, Site_no, tot_rec, tot_rec, 0
from data1 where rno=1
union all
select d.rno, d.Site_no, d.tot_rec,
case when r.total + d.tot_rec > 65000
Then d.tot_rec
Else r.total + d.tot_rec END,
case when r.total + d.tot_rec > 65000
Then r.flg+1
Else r.flg END
from data1 d, rec r
where d.rno=r.rno+1
)
select min(site_no), max(site_no), sum(tot_rec)
from rec
group by flg
order by flg
これを順番に説明します。 最初に、副問合せのファクタリング（Oracle9i Database以降で利用可能）が使用されています。この問合せの“WITH DATA1 AS”の部分です。 この部分で、実表のデータ・セットを受け取り、STUDY_SITEを基準としてソートした後、それぞれの行に1からNまでの行番号を割り当てています。 次に、第2の副問合せのファクタリングであるRECがあります。これは、定義の中で自分自身を参照しているため、再帰的副問合せのファクタリングです。 UNION ALLの後半に注目してください。自分自身に対して問合せを実行しています。 再帰的副問合せのファクタリングであるRECは、以下のように動作します。 この副問合せがUNION ALLの前半を実行すると、DATA1から1行目のレコードが取り出され、そのレコードが結果セットに出力されます。 次に、取り出された1行が問合せの後半に渡されます。つまり、1行目のレコードが2行目のレコードと結合されます。 R.TOTALの値（1行目の小計）と2行目の小計であるD.TOT_RECを足した値がしきい値を上回る場合は、小計がリセットされてD.TOT_RECになります。 R.TOTAL+D.TOT_RECがしきい値以下の場合は、小計はリセットされず、R.TOTAL+D.TOT_RECのままです。 FLG列はグループ化のための列です。R.TOTAL+D.TOT_RECがしきい値を上回る場合に1足し込まれ、しきい値以下の場合は同じ値のままになります。 その後、この副問合せで2つ目の組立て済みのレコードを取得し、そのレコードをDATA1の3行目のレコードと結合して、同じ処理を実行します。 さらに、3つ目のレコードを4行目のレコードに結合します。これを、処理するレコードがなくなるまで続けます。 この問合せの実行が完了すると、上述のパイプライン・ファンクションと同じ結果が返されます。 ただし、この方法に喜びすぎないようにしてください。必要となる処理について考えてみましょう。 結果セット全体を取得し、ソートして行番号を割り当てる必要があります（すべての方法に多かれ少なかれソートが含まれるため、これ自体は問題ありません）。 次に、このサブ結果に問合せを実行して1行目を取り出し、もう1度サブ結果をスキャンして2行目を取り出し、もう1度サブ結果をスキャンして3行目を取り出す、というようになります。 つまり、この問合せは重い処理を何度も繰り返します。そのため、この方法はもっとも効率的な方法ではありません。 数字で確認したい場合は、この問合せの処理量を示したTKPROFレポートをbit.ly/vJiMVwで参照してください。 最後の方法は、SQLのMODEL句を使用するもので、中国は天津のJichao Liが投稿してくれました。 Liが投稿した問合せをリスト5に示します。 コード・リスト5：SQLのMODEL句を使用した解決策
SELECT s, MAX(e), MAX(sm) FROM (
SELECT s, e, cnt, sm FROM t
MODEL DIMENSION BY(row_number()
over(order by study_site) rn)
MEASURES(study_site s, study_site e, cnt, cnt sm)
RULES(sm[rn > 1] =
CASE WHEN (sm[cv() - 1] + cnt[cv()]) > 20000
OR cnt[cv()] > 20000
THEN cnt[cv()]
ELSE sm[cv() - 1] + cnt[cv()]
END,
s[rn > 1] =
CASE WHEN(sm[cv() - 1] + cnt[cv()]) > 20000
OR cnt[cv()] > 20000
THEN s[cv()]
ELSE s[cv() - 1]
END))
GROUP BY s ORDER BY s;
Liは、異なるグループ化を示すために、しきい値として20,000を使用していますが、全体的な結果は同じです （この問合せで20,000と記述されている箇所を別の値に置き換えるかバインドすることで、値を変更できます）。 私はPL/SQLのプロシージャ・コードを使用して質問に回答しましたが、Liは、ある意味でプロシージャ・コードをSQL内で直接使用して、この質問に回答しています （SQLのMODEL句のドキュメントについては、bit.ly/rZtatbを参照してください）。 このSQL問合せは、前述の解決策と基本的には同じ方法をとっていますが、異なる構文を使用しています。 まず、STUDY_SITEを基準としてデータを並べ替え、それぞれの行に行番号を割り当てています。次に、その行番号を結果セットのインデックスとして使用し、特定の行と列を取得しています（これは、スプレッドシート内に関数を記述するようなものです。この場合、スプレッドシートは結果セットです）。 その後、PL/SQLによる解決策や再帰的副問合せのファクタリングによる解決策と同様の処理を実行しています。 まとめれば、 SQLのMODEL句が、この問題に対する解決策の中でパフォーマンスが飛びぬけて優れた方法でしょう。 次がPL/SQLパイプライン・ファンクション、3番目が再帰的副問合せのファクタリングとなるでしょう。 では、MODEL句は常にPL/SQLや再帰的方法よりも優れているのでしょうか。 もちろん、違います。 これら3つの方法のどれもが、それぞれの状況で“最適な”方法となるでしょう。 多くの方法を使用できるようにしておき、ベンチマーキングを実行して、特定の問題の解決にもっとも適した方法を見つけるようにしてください。
Tom KyteはオラクルのServer Technologies部門に籍を置くデータベース・エバンジェリストで、1993年からオラクルに勤務しています。 |
Printer View