12 ユーザー定義集計関数の使用
ユーザー定義集計関数は、単独でもパラレルでも使用できます。ここでは、大規模な集約コンテキストおよびマテリアライズド・ビューについて考えます。
関連項目:
ODCIAggregate
インタフェースの詳細は、「ユーザー定義集計関数インタフェース」を参照してください。
12.1 ユーザー定義集計関数の概要
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つのコンテキストを入力として受け取り、それらを結合して、単一の集計コンテキストを戻します。
-
終了は、Oracleにより集計の最終ステップとしてODCIAggregateTerminate()ルーチンが起動されるときに発生します。このルーチンは入力として集計コンテキストを取り、結果の集計値を戻します。
このプロセスについては「ユーザー定義集計関数の使用」の項で説明します。
12.2 ユーザー定義集計の作成
ユーザー定義集計関数の作成プロセスは、例12-1および例12-2に示す2つのステップで構成されます。どちらの例でも、Oracle Spatialで定義されたSpatialUnion()
集計関数を使用しています。この関数は、一連の入力ジオメトリのバインド・ジオメトリを計算します。
例12-1 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;
例12-2 ユーザー定義集計関数の定義
この関数定義では、関数のシグネチャとODCIAggregate
インタフェースを実装するオブジェクト型を指定してSpatialUnion()
集計関数が作成されます。
CREATE FUNCTION SpatialUnion(x Geometry) RETURN Geometry AGGREGATE USING SpatialUnionRoutines;
12.3 ユーザー定義集計の使用
ユーザー定義集計は、SQL DML文および問合せ文の組込み集計関数と同様に使用できます。これらの関数は、SELECT
構文のリストやORDER BY
句に使用したり、HAVING
句の条件の一部として使用できます。次の例12-3、例12-4および例12-5でいくつかのオプションを示します。
関連項目:
ROLLUP
、CUBE
およびグルーピング・セットなどのGROUP BY
拡張の詳細は、『Oracleデータ・ウェアハウス・ガイド』を参照してください。
12.3.1 SELECT文とユーザー定義集計関数の使用
例12-3 SELECT文とユーザー定義集計関数の使用
次の問合せを使用すると、同じ州に属しているすべての郡のジオメトリを集計して州の境界を計算できます。
SELECT SpatialUnion(geometry) FROM counties GROUP BY state
12.3.2 HAVING句とユーザー定義集計関数の使用
例12-4 HAVING句とユーザー定義集計関数の使用
ユーザー定義集計をHAVING
句に使用すると、集計関数の結果に基づいて出力からグループを除外できます。この例では、MyUDAG()
はユーザー定義集計です。
SELECT groupcol, MyUDAG(col) FROM tab GROUP BY groupcol HAVING MyUDAG(col) > 100 ORDER BY MyUDAG(col);
12.3.3 問合せオプションとユーザー定義集計関数の使用
例12-5 他の問合せオプションとユーザー定義集計関数の使用
ユーザー定義集計は、入力パラメータでDISTINCT
またはALL
(デフォルト)オプションを取ることができます。DISTINCT
を指定すると、集計の計算時に重複値が無視されます。ユーザー定義集計を含むSELECT
文には、ROLLUP
、CUBE
およびグルーピング・セットなどのGROUP BY
拡張も含めることができます。
SELECT ..., MyUDAG(col) FROM tab GROUP BY ROLLUP(gcol1, gcol2);
この種のロールアップ操作で超集計値を計算するために、ODCIAggregateMerge()インタフェースが起動されます。
12.4 ユーザー定義集計のパラレル評価
組込み集計関数と同様に、ユーザー定義集計もパラレルで評価できます。
パラレル・スレーブ内の行のサブセットを集計することで生成される集計コンテキストは、次のパラレル・ステップ(問合せコーディネータまたは次のスレーブ・セット)に送られます。次に集計コンテキストがマージされ、終了ルーチンが起動されて集計値が取得されます。この動作を図12-1に示します。
集計関数は、例12-6のようにパラレル対応として宣言する必要があることに注意してください。
例12-6 ユーザー定義集計関数のパラレル対応化
CREATE FUNCTION MyUDAG(...) RETURN ... PARALLEL_ENABLE AGGREGATE USING MyAggrRoutines;
12.5 大規模な集計コンテキストの処理
実装タイプのメソッドがC++やJavaのような外部言語で実装される場合は、実装タイプのメソッドがコールされるたびに、Oracleサーバー・プロセスと外部関数の言語環境の間で集計コンテキストがやりとりされる必要があります。これにより集計コンテキストのサイズが大きくなるため、パフォーマンスが低下する可能性があります。
パフォーマンスを強化するために、集計コンテキストを外部関数の実行環境で割り当てられた外部メモリーに格納できます。これにより、Oracleサーバーと外部関数の間で参照またはキーをやりとりできます。キー自体は、実装タイプのインスタンスself
に格納する必要があります。この方法では、実装タイプのインスタンスが小型に保たれるため、迅速に転送できます。この方法のもう1つのメリットは、集計コンテキストの保持に使用されるメモリーが、Oracleサーバーではなく関数の実行環境(extproc
など)で割り当てられることです。
通常、集計コンテキストを保持するメモリーを割り当てて、その参照を実装タイプのインスタンスに格納するには、ODCIAggregateInitialize()
を使用する必要があります。以降のコールでは、外部メモリーとそこに格納された集計コンテキストに参照を使用してアクセスできます。通常、外部メモリーはODCIAggregateTerminate()
で解放する必要があります。ODCIAggregateMerge()
では、マージの終了後にマージ済コンテキスト(ODCIAggregateMerge()
の第2引数)の格納に使用された外部メモリーを解放します。
12.5.1 外部コンテキストとパラレル集計
ユーザー定義集計を使用した問合せのパラレル実行では、スレーブ・プロセスにより計算されたすべての部分集計で構成される集計コンテキスト全体を、別のスレーブまたはマスター・プロセスに転送する処理が必要になる場合があります。オプションのルーチンODCIAggregateWrapContext()
を実装すると、すべての部分集計を収集できます。ユーザー定義集計がパラレルで評価される場合に、ODCIAggregateWrapContext()
が定義されていれば、Oracleはルーチンを起動して外部コンテキスト参照をすべて実装タイプのインスタンスにコピーし、外部メモリーを解放します。ODCIAggregateWrapContext()
をサポートするには、集計コンテキストを保持する属性と、外部メモリーの識別キーを保持する別の属性を、実装タイプに含める必要があります。
集計コンテキストが外部に格納される場合、実装タイプのキー属性には外部メモリーを識別する参照を含み、また実装タイプの他の属性をNULL
に設定する必要があります。ODCIAggregateWrapContext()
コールが正常に実行された後、キー属性はNULL
になり、他の属性には実際の集計コンテキストが保持されます。
実装タイプの各メンバー・メソッドは、最初にコンテキストがインライン(実装タイプのインスタンスに含まれる)か、または外部メモリーにあるかをチェックする必要があります。他のパラレル・スレーブから送信された場合のように、コンテキストがインラインの場合は、参照渡しできるように外部メモリーにコピーします。
ODCIAggregateWrapContext()
ルーチンの実装はオプションです。これは、外部メモリーに集計コンテキストが保持され、ユーザー定義集計がパラレルで評価される場合にのみ必要です。ユーザー定義集計がパラレルで評価されない場合、ODCIAggregateWrapContext()
は不要です。ODCIAggregateWrapContext()
メソッドが定義されていなければ、集計コンテキストは外部に格納されないとみなされ、このメソッドはコールされません。
12.5.1.1 外部メモリーを使用した集約コンテキストの格納
この例は、外部メモリーへの参照を含む集計コンテキスト・タイプに、必要に応じてコンテキスト全体も格納する方法を示しています。
4
バイトkey
パラメータは、外部コンテキストの検索に使用されます。NULL
は、コンテキスト値全体がオブジェクト内の他の属性に保持されていることを示します。GeometrySet
など、他の属性は実際の集計コンテキストに対応します。key
値がNULL
でない場合は、これらの属性をNULL
値にする必要があります。ただし、ODCIAggregateWrapContext()のコール後のようにコンテキスト・オブジェクトが自己完結型の場合、これらの属性では現行のコンテキスト値が保持されます。
CREATE TYPE MyAggrRoutines AS OBJECT ( key RAW(4), ctxval GeometrySet, ctxval2 ... );
12.5.2 ユーザー定義集計と分析関数
分析関数を使用すると、ウィンドウと呼ばれる行セットに対して各種の累積集計、移動集計およびセンター集計を計算できます。分析関数では、表の行ごとに特定の行のウィンドウに含まれる他の行で計算された値が戻されます。これらの関数を使用すると、自己結合なしで表の複数の行にアクセスできます。ユーザー定義集計は分析関数として使用できます。
12.5.3 分析関数への集計コンテキストの再利用
ユーザー定義集計を分析関数として使用すると、集計は各行の対応するウィンドウについて計算されます。通常、連続する各ウィンドウには、新規集計コンテキスト、新規ウィンドウなど、古い集計コンテキストや前のウィンドウとは数行が異なるのみの、ほぼ同じ行セットが含まれます。集計コンテキストを再利用するには、古いコンテキストになかった新規の行を反復して追加し、新規コンテキストに属さない行を古いコンテキストから削除する必要があります。集計コンテキストを再利用できない場合は、そこに含まれる行をすべて再度反復して再作成する必要があります。
オプションのルーチンODCIAggregateDelete()
を実装すると、Oracleで集計コンテキストを効率的に再利用できます。ODCIAggregateDelete()
により、新規(現行)のウィンドウにない集計コンテキスト行が前のコンテキストから削除されます。このルーチンは、削除する必要のある行ごとにコールされます。追加する必要のある行ごとに、ODCIAggregateIterate()
がコールされます。
新規集計コンテキストが古い集計コンテキストのスーパーセットである場合は、古いコンテキストの行がすべて含まれており、行を削除する必要はありません。この場合は、ODCIAggregateDelete()
が実装されていなくても古いコンテキストが再利用されます。
関連項目:
-
分析関数の詳細は、『Oracleデータ・ウェアハウス・ガイド』を参照してください。
12.5.4 外部コンテキストとユーザー定義分析関数
ユーザー定義集計を分析関数として使用すると、あるウィンドウの集計コンテキストを次のウィンドウに再利用できます。このような場合、ODCIAggregateTerminate()
ファンクションのフラグ引数のODCI_AGGREGATE_REUSE_CTX
ビットは、集計コンテキストを保持する外部メモリーを解放しないことを示すように設定されます。また、ODCIAggregateInitialize()
メソッドには、メモリーが再び割り当てられるかわりに前のウィンドウの実装タイプ・インスタンスが渡されるため、前に割り当てた外部メモリーにアクセスして再び初期化できます。ユーザー定義分析関数について外部コンテキストをサポートする手順は、次のとおりです。
-
ODCIAggregateInitialize()
- 渡される実装タイプ・インスタンスがNULL
でない場合は、新規の外部メモリーを割り当てるかわりに割当済の外部メモリーを使用し、集計コンテキストを再び初期化します。 -
ODCIAggregateTerminate()
- フラグ引数のビットODCI_AGGREGATE_REUSE_CTX
が設定されていない場合にのみ、外部メモリーを解放します。 -
ODCIAggregateMerge()
- マージ済集計コンテキストに関連付けられていた外部メモリーを解放します。 -
ODCIAggregateTerminate()
- 外部メモリーから実装タイプ・インスタンスに集計コンテキストをコピーして、外部メモリーを解放します。 -
すべてのメンバー・メソッド - 最初に、コンテキストが外部に格納されるかインラインであるかを判別します。コンテキストがインラインの場合は、外部メモリーを割り当てて、そこにコンテキストをコピーします。
12.6 マテリアライズド・ビューとユーザー定義集計の使用
マテリアライズド・ビュー定義に、例12-7で示すようにユーザー定義集計と組込み集計演算子を含めることができます。
マテリアライズド・ビューでクエリー・リライトを使用可能にするには、例12-8に示すようにマテリアライズド・ビュー内のユーザー定義集計をDETERMINISTIC
として宣言する必要があります。
ユーザー定義集計が削除または再作成されると、その依存マテリアライズド・ビューすべてに無効マークが付きます。
例12-7 マテリアライズド・ビューの作成
CREATE MATERIALIZED VIEW MyMV AS SELECT gcols, MyUDAG(c1) FROM tab GROUP BY (gcols);
例12-8 マテリアライズド・ビューでのクエリー・リライトの使用可能化
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 Databaseデータ・ウェアハウス・ガイド』を参照してください。
12.7 ユーザー定義集計関数の作成と使用
例12-9に単純なユーザー定義集計関数SecondMax()
を作成して使用する方法を示します。
例12-9 ユーザー定義集計関数の作成と使用
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 MyEmployees GROUP BY department_id HAVING SecondMax(salary) > 9000;