| Oracle Databaseデータ・カートリッジ開発者ガイド 11gリリース1（11.1） E05688-02 |
|
 戻る |
 次へ |
この章ではユーザー定義集計関数を紹介し、その作成方法と使用方法を、個別に動作する場合とパラレルに動作する場合の両方で示します。また、大規模な集計コンテキストおよびマテリアライズド・ビューでどのように動作するかを示します。
この章の内容は、次のとおりです。
Oracleには、一連の行に対する操作を実行できるように、MAX、MINおよびSUMなど、多数の事前定義済集計関数が用意されています。これらの事前定義済集計関数を使用できるのはスカラー・データの場合のみで、オブジェクト型、不透明型およびLOBを使用して格納されるマルチメディア・データのような複合データ型には使用できません。ただし、これらの関数のカスタム実装を複合データ型に対して定義することはできます。また、複合データで使用するために新規の集計関数を定義することも可能です。ユーザー定義集計関数は、Oracleの組込み集計と同様にSQL DML文に使用できます。関数をサーバーに登録すると、指定したユーザー定義集計ルーチンは単にネイティブ・ルーチンのかわりに起動されます。ユーザー定義集計は、科学アプリケーションに必要な複雑な統計データのようなスカラー・データにも使用できます。
ユーザー定義集計は拡張フレームワークの機能であり、ODCIAggregateインタフェース・ルーチンを使用して実装できます。
ユーザー定義集計関数を作成するには、総称的にODCIAggregateルーチンと呼ばれる一連のルーチンを実装します。これらのルーチンは、オブジェクト型のメソッドとして実装できるため、実装にはOracleでサポートされる言語（PL/SQL、C、C++またはJava）を使用できます。オブジェクト型を定義し、型本体にルーチンを実装した後、CREATE FUNCTION文を使用して集計関数を作成します。
各ユーザー定義集計関数では、最大4つのODCIAggregateルーチン（ステップ）を使用して、任意の集計関数で実行する初期化、反復、マージおよび終了などの内部操作を定義します。
初期化は、ODCIAggregateInitialize()ルーチンにより実行されます。このルーチンは、ユーザー定義集計の計算を初期化するときにOracleにより起動されます。初期化された集計コンテキストは、Oracleにオブジェクト型のインスタンスとして渡されます。
反復は、Oracleにより繰り返し起動されるODCIAggregateIterate()ルーチンを介して実行されます。起動されるたびに、新規の値または値セットと現行の集計コンテキストが渡されます。このルーチンは、新規の値を処理して更新後の集計コンテキストを戻します。このルーチンは、基礎となるグループ内のNULLでない値ごとに起動されます。NULL値は、集計時には無視され、ルーチンには渡されません。
マージは、ODCIAggregateMerge()ルーチンにより実行されます。これは、2つの集計コンテキストを結合するためにOracleにより起動されるルーチンです。このルーチンは入力として2つのコンテキストを取り、両者を結合して1つの集計コンテキストを戻します。
終了は、Oracleにより集計の最終ステップとしてODCIAggregateTerminate()ルーチンが起動されるときに発生します。このルーチンは入力として集計コンテキストを取り、結果の集計値を戻します。
例11-1にこのプロセスを示します。
例11-1 ユーザー定義集計関数の動作
次の文の集計関数AVG()を考えてみます。
SELECT AVG(T.Sales) FROM AnnualSales T GROUP BY T.State;
この計算を実行するために、集計関数AVG()は次のようなステップで実行されます。
初期化。集計コンテキスト（集計を実行する行）を初期化して計算を初期化します。
runningSum = 0; runningCount = 0;
反復。連続する入力値をそれぞれ反復的に処理してコンテキストを更新します。
runningSum += inputval; runningCount++;
[オプション] マージ。2つの集計コンテキストを結合して1つのコンテキストを戻します。この操作により、複数のサブセットにまたがって集計結果が結合され、セット全体の集計が取得されます。この追加ステップは、集計のシリアル評価時またはパラレル評価時に必要になる場合があります。このステップは、必要に応じてステップ4の前に実行されます。
runningSum = runningSum1 + runningSum2; runningCount = runningCount1 + runningCount2
このステップの詳細は、「ユーザー定義集計のパラレル評価」を参照してください。
終了。結果を計算し、コンテキストを使用して結果の集計値を戻します。
return (runningSum/runningCount);
AVG()がユーザー定義関数の場合、この関数を含むオブジェクト型では前述のステップごとに対応するODCIAggregateルーチンのメソッドが実装されます。変数runningSumおよびrunningCount（この例では集計の状態を判別）は、そのオブジェクト型の属性です。
ユーザー定義集計関数の作成プロセスは、例11-2および例11-3に示す2つのステップで構成されます。どちらの例でも、空間カートリッジで定義されたSpatialUnion()集計関数を使用しています。この関数は、一連の入力ジオメトリのバインド・ジオメトリを計算します。
例11-2 ODCIAggregateインタフェースの実装方法
ODCIAggregateルーチンは、オブジェクト型SpatialUnionRoutines内のメソッドとして実装されます。実際の実装には、PL/SQL、C、C++またはJavaなど、型のメソッドについてOracleでサポートしている任意の言語を使用できます。
CREATE TYPE SpatialUnionRoutines( STATIC FUNCTION ODCIAggregateInitialize( ... ) ..., MEMBER FUNCTION ODCIAggregateIterate(...) ... , MEMBER FUNCTION ODCIAggregateMerge(...) ..., MEMBER FUNCTION ODCIAggregateTerminate(...) ); CREATE TYPE BODY SpatialUnionRoutines IS ... END;
ユーザー定義集計は、SQL DML文および問合せ文の組込み集計関数と同様に使用できます。これらの関数は、SELECT構文のリストやORDER BY句に使用したり、HAVING句の条件の一部として使用できます。次の例11-4、例11-5および例11-6に、これらのオプションの一部を示します。
例11-4 SELECT文とユーザー定義集計関数の使用方法
次の問合せを使用すると、同じ州に属しているすべての郡のジオメトリを集計して州の境界を計算できます。
SELECT SpatialUnion(geometry) FROM counties GROUP BY state
例11-5 HAVING句とユーザー定義集計関数の使用方法
ユーザー定義集計をHAVING句に使用すると、集計関数の結果に基づいて出力からグループを除外できます。この例では、MyUDAG()はユーザー定義集計です。
SELECT groupcol, MyUDAG(col) FROM tab GROUP BY groupcol HAVING MyUDAG(col) > 100 ORDER BY MyUDAG(col);
例11-6 問合せオプションとユーザー定義集計関数の使用方法
ユーザー定義集計は、入力パラメータでDISTINCTまたはALL（デフォルト）オプションを取ることができます。DISTINCTを指定すると、集計の計算時に重複値が無視されます。ユーザー定義集計を含むSELECT文には、ROLLUP、CUBEおよびグルーピング・セットなどのGROUP BY拡張も含めることができます。
SELECT ..., MyUDAG(col) FROM tab GROUP BY ROLLUP(gcol1, gcol2);
この種のロールアップ操作で超集計値を計算するために、ODCIAggregateMerge()インタフェースが起動されます。
|
関連項目 ROLLUP、CUBEおよびグルーピング・セットなどのGROUP BY拡張の詳細は、『Oracleデータ・ウェアハウス・ガイド』を参照してください。 |
組込み集計関数と同様に、ユーザー定義集計もパラレルで評価できます。
パラレル・スレーブ内の行のサブセットを集計することで生成される集計コンテキストは、次のパラレル・ステップ（問合せコーディネータまたは次のスレーブ・セット）に送られます。次に集計コンテキストがマージされ、終了ルーチンが起動されて集計値が取得されます。この動作を図11-1に示します。
集計関数は、例11-7のようにパラレル対応として宣言する必要があることに注意してください。
実装タイプのメソッドがC++やJavaのような外部言語で実装される場合は、実装タイプのメソッドがコールされるたびに、Oracleサーバー・プロセスと外部関数の言語環境の間で集計コンテキストがやりとりされる必要があります。これにより集計コンテキストのサイズが大きくなるため、パフォーマンスが低下する可能性があります。
パフォーマンスを強化するために、集計コンテキストを外部関数の実行環境で割り当てられた外部メモリーに格納できます。これにより、Oracleサーバーと外部関数の間で参照またはキーをやりとりできます。キー自体は、実装タイプのインスタンスselfに格納する必要があります。この方法では、実装タイプのインスタンスが小型に保たれるため、迅速に転送できます。この方法のもう1つのメリットは、集計コンテキストの保持に使用されるメモリーが、Oracleサーバーではなく関数の実行環境（extprocなど）で割り当てられることです。
通常、集計コンテキストを保持するメモリーを割り当てて、その参照を実装タイプのインスタンスに格納するには、ODCIAggregateInitialize()を使用する必要があります。以降のコールでは、外部メモリーとそこに格納された集計コンテキストに参照を使用してアクセスできます。通常、外部メモリーはODCIAggregateTerminate()で解放する必要があります。ODCIAggregateMerge()では、マージの終了後にマージ済コンテキスト（ODCIAggregateMerge()の第2引数）の格納に使用された外部メモリーを解放します。
ユーザー定義集計を使用した問合せのパラレル実行では、スレーブ・プロセスにより計算されたすべての部分集計で構成される集計コンテキスト全体を、別のスレーブまたはマスター・プロセスに転送する処理が必要になる場合があります。オプションのルーチンODCIAggregateWrapContext()を実装すると、すべての部分集計を収集できます。ユーザー定義集計がパラレルで評価される場合に、ODCIAggregateWrapContext()が定義されていれば、Oracleはルーチンを起動して外部コンテキスト参照をすべて実装タイプのインスタンスにコピーし、外部メモリーを解放します。ODCIAggregateWrapContext()をサポートするには、集計コンテキストを保持する属性と、外部メモリーの識別キーを保持する別の属性を、実装タイプに含める必要があります。
集計コンテキストが外部に格納される場合、実装タイプのキー属性には外部メモリーを識別する参照を含み、また実装タイプの他の属性をNULLに設定する必要があります。ODCIAggregateWrapContext()コールが正常に実行された後、キー属性はNULLになり、他の属性には実際の集計コンテキストが保持されます。
例11-8 集計コンテキストの格納に外部メモリーを使用する方法
この例は、外部メモリーへの参照を含む集計コンテキスト・タイプに、必要に応じてコンテキスト全体も格納する方法を示しています。
4バイトkeyパラメータは、外部コンテキストの検索に使用されます。NULLは、コンテキスト値全体がオブジェクト内の他の属性に保持されていることを示します。GeometrySetなど、他の属性は実際の集計コンテキストに対応します。key値がNULLでない場合は、これらの属性をNULL値にする必要があります。ただし、ODCIAggregateWrapContext()のコール後のようにコンテキスト・オブジェクトが自己完結型の場合、これらの属性では現行のコンテキスト値が保持されます。
CREATE TYPE MyAggrRoutines AS OBJECT ( key RAW(4), ctxval GeometrySet, ctxval2 ... );
実装タイプの各メンバー・メソッドは、最初にコンテキストがインライン（実装タイプのインスタンスに含まれる）か、または外部メモリーにあるかをチェックする必要があります。他のパラレル・スレーブから送信された場合のように、コンテキストがインラインの場合は、参照渡しできるように外部メモリーにコピーします。
ODCIAggregateWrapContext()ルーチンの実装はオプションです。外部メモリーに集計コンテキストが保持され、ユーザー定義集計がパラレルで評価される場合にのみ使用してください。ユーザー定義集計がパラレルで評価されない場合、ODCIAggregateWrapContext()は不要です。ODCIAggregateWrapContext()メソッドが定義されていなければ、集計コンテキストは外部に格納されないとみなされ、このメソッドはコールされません。
分析関数を使用すると、ウィンドウと呼ばれる行セットに対して各種の累積集計、移動集計およびセンター集計を計算できます。分析関数では、表の行ごとに特定の行のウィンドウに含まれる他の行で計算された値が戻されます。これらの関数は、自己結合なしで表の複数行へのアクセスを提供します。ユーザー定義集計は分析関数として使用できます。
ユーザー定義集計を分析関数として使用すると、集計は各行の対応するウィンドウについて計算されます。通常、連続する各ウィンドウには、新規集計コンテキスト、新規ウィンドウなど、古い集計コンテキストや前のウィンドウとは数行が異なるのみの、ほぼ同じ行セットが含まれます。集計コンテキストを再利用するには、古いコンテキストになかった新規の行を反復して追加し、新規コンテキストに属さない行を古いコンテキストから削除する必要があります。集計コンテキストを再利用できない場合は、そこに含まれる行をすべて再度反復して再作成する必要があります。
オプションのルーチンODCIAggregateDelete()を実装すると、Oracleで集計コンテキストを効率的に再利用できます。ODCIAggregateDelete()により、新規（現行）のウィンドウにない集計コンテキスト行が前のコンテキストから削除されます。このルーチンは、削除する必要のある行ごとにコールされます。追加する必要のある行ごとに、ODCIAggregateIterate()がコールされます。
新規集計コンテキストが古い集計コンテキストのスーパーセットである場合は、古いコンテキストの行がすべて含まれており、行を削除する必要はありません。この場合は、ODCIAggregateDelete()が実装されていなくても古いコンテキストが再利用されます。
|
関連項目
|
ユーザー定義集計を分析関数として使用すると、あるウィンドウの集計コンテキストを次のウィンドウに再利用できます。このような場合、ODCIAggregateTerminate()ファンクションのフラグ引数のODCI_AGGREGATE_REUSE_CTXビットは、集計コンテキストを保持する外部メモリーを解放しないことを示すように設定されます。また、ODCIAggregateInitialize()メソッドには、メモリーが再び割り当てられるかわりに前のウィンドウの実装タイプ・インスタンスが渡されるため、前に割り当てた外部メモリーにアクセスして再び初期化できます。ユーザー定義分析関数について外部コンテキストをサポートする手順は、次のとおりです。
ODCIAggregateInitialize(): 渡される実装タイプ・インスタンスがNULLでない場合は、新規の外部メモリーを割り当てるかわりに割当済の外部メモリーを使用し、集計コンテキストを再び初期化します。
ODCIAggregateTerminate(): フラグ引数のビットODCI_AGGREGATE_REUSE_CTXが設定されていない場合にのみ、外部メモリーを解放します。
ODCIAggregateMerge(): マージ済集計コンテキストに関連付けられていた外部メモリーを解放します。
ODCIAggregateTerminate(): 外部メモリーから実装タイプ・インスタンスに集計コンテキストをコピーして、外部メモリーを解放します。
すべてのメンバー・メソッド: 最初に、コンテキストが外部に格納されるかインラインであるかを判別します。コンテキストがインラインの場合は、外部メモリーを割り当てて、そこにコンテキストをコピーします。
マテリアライズド・ビュー定義に、例11-10で示すようにユーザー定義集計と組込み集計演算子を含めることができます。
例11-10 マテリアライズド・ビューの作成方法
CREATE MATERIALIZED VIEW MyMV AS SELECT gcols, MyUDAG(c1) FROM tab GROUP BY (gcols);
マテリアライズド・ビューをクエリー・リライトに使用可能にするには、例11-11に示すようにマテリアライズド・ビュー内のユーザー定義集計をDETERMINISTICとして宣言する必要があります。
例11-11 マテリアライズド・ビューをクエリー・リライトに使用可能にする方法
CREATE FUNCTION MyUDAG(x NUMBER) RETURN NUMBER DETERMINISTIC AGGREGATE USING MyImplType; CREATE MATERIALIZED VIEW MyMV ENABLE QUERY REWRITE AS SELECT gcols, MyUDAG(c1) FROM tab GROUP BY (gcols);
ユーザー定義集計が削除または再作成されると、その依存マテリアライズド・ビューすべてに無効マークが付きます。
|
関連項目 マテリアライズド・ビューの詳細は、『Oracleデータ・ウェアハウス・ガイド』を参照してください。 |
例11-12に単純なユーザー定義集計関数SecondMax()を作成して使用する方法を示します。
例11-12 ユーザー定義集計関数を作成して使用する方法
SecondMax()は、一連の数値のうち2番目に大きい値を戻します。
ODCIAggregateルーチンを含むSecondMaxImpl型を実装します。
create type SecondMaxImpl as object
(
max NUMBER, -- highest value seen so far
secmax NUMBER, -- second highest value seen so far
static function ODCIAggregateInitialize(sctx IN OUT SecondMaxImpl)
return number,
member function ODCIAggregateIterate(self IN OUT SecondMaxImpl,
value IN number) return number,
member function ODCIAggregateTerminate(self IN SecondMaxImpl,
returnValue OUT number, flags IN number) return number,
member function ODCIAggregateMerge(self IN OUT SecondMaxImpl,
ctx2 IN SecondMaxImpl) return number
);
/
SecondMaxImplの型本体を実装します。
create or replace type body SecondMaxImpl is
static function ODCIAggregateInitialize(sctx IN OUT SecondMaxImpl)
return number is
begin
sctx := SecondMaxImpl(0, 0);
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT SecondMaxImpl, value IN number) return number is
begin
if value > self.max then
self.secmax := self.max;
self.max := value;
elsif value > self.secmax then
self.secmax := value;
end if;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN SecondMaxImpl,
returnValue OUT number, flags IN number) return number is
begin
returnValue := self.secmax;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT SecondMaxImpl, ctx2 IN SecondMaxImpl) return number is
begin
if ctx2.max > self.max then
if ctx2.secmax > self.secmax then
self.secmax := ctx2.secmax;
else
self.secmax := self.max;
end if;
self.max := ctx2.max;
elsif ctx2.max > self.secmax then
self.secmax := ctx2.max;
end if;
return ODCIConst.Success;
end;
end;
/
ユーザー定義集計を作成します。
CREATE FUNCTION SecondMax (input NUMBER) RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING SecondMaxImpl;
SecondMax()を使用します。
SELECT SecondMax(salary), department_id FROM employees GROUP BY department_id HAVING SecondMax(salary) > 9000;
