この節では、次のETL機能を紹介します。実際の演習は含んでいません。

• Change Data Captureによるデータ・ウェアハウス
• 外部表によるデータ・ウェアハウス
• 表ファンクションによるデータ・ウェアハウスのロード

New Media Technologies社では、Sales History（SH）データ・ウェアハウスを保守して定期的にリフレッシュする必要があります。各四半期ごとに、新規販売データをSales Historyデータ・ウェアハウスにロードします。さらに、受注に関するオンライン・トランザクション処理（OLTP）システムは、データ・ウェアハウスに存在するデータを変更します。また、同社は社内のOLTPシステムに統合されていない個別ベンダーとも取引しています。データに個別にアクセスして、データの変更、What-If分析や影響分析を行うために受注部門からの要求も受けています。

Oracle9i では、典型的な「ETL（抽出、変換、ロード）」処理の要件に対応する頑健なサーバー機能セットが提供されます。結果としてスケーラブルなETLインフラストラクチャが得られ、大容量データのデータ・ウェアハウスへの処理ができるようになります。OracleではETL機能のツールキットを提供して、ETL処理のすべての問題に対応しています。

Oracle9i の新規ETL機能は、相互併用の点で非常にパワフルです。たとえば、1つのSQL操作で外部表機能を使用してフラット・ファイルからデータを選択したり、表ファンクションを使用して複雑な変換を追加適用したり、複数のターゲット表に結果を挿入したりすることが可能です。この節では、このようなETL機能の一部を紹介します。
　　

New Media Technologies社では、増分データ・ロード・プロセスを使用して、Sales Historyデータ・ウェアハウスに四半期の販売データをロードします。ただし、この四半期の処理が完了した後で、販売OLTPシステムがしばしばデータを変更します（戻り品や改訂など）。幸い、Oracle9i はこの変更データをOracleデータ・ソースからキャプチャすることができます。データ・キャプチャの方法は、「同期」（Oracleのレプリケーション・フレームワークを介して）または「非同期」（OracleのLogMiner技術でアーカイブREDOログを処理）のいずれかです。アーカイブ・ログはオフラインで処理できるため、非同期の変更データ・キャプチャがソース・システムに何らかの形で影響を及ぼすことはありません。Change Data Capture（CDC）はデータをキャプチャして公開することができ、アプリケーションは制御された方式で変更データをサブスクライブすることが可能です。

Change Data Captureの使用
Change Data Captureはデータベースのさまざまな表におけるトランザクションの影響を追跡するのに便利です。オペレーティング・システムへのスキーマ変更は必要とされず、次のような目的でデータ・ウェアハウス環境で便利に使用できます。

• オンライン分析処理（OLAP） ― OLAPを促進するため、CDCを同期モードで実行してユーザー表を更新したのと同じトランザクションでデータをキャプチャできます。この場合、CDCシステムを操作オンライン・トランザクション処理（Online Transaction Processing：OLTP）が稼動しているのと同じシステムでローカルに実行する必要があります。
  　
• 抽出、トランスポート、変換（ETT） ― CDCは、中間ステージング・ファイルまたはデータをデータ・ウェアハウスにトランスポートする前にデータ変換または消去を実行するシステムで直接データ・ウェアハウス上で実行できます。データがデータ・ウェアハウスまたはデータ・マートに直接ロードされる環境では、CDCはステージング・システムで実行できます。
  　
• 軽量レプリケーション ― できるだけ迅速にデータをキャプチャする必要があるビジネスには、同期データ・キャプチャ・モードが提供されています。同期環境では、変更表の移入はリソース表更新中にトリガーによって行われます。この場合、CDCシステムは稼動データベースの実行システムと同じシステム上でローカルに実行する必要があります。
  　

変更データの公開
「パブリッシャ」（つまりデータベース管理者）は、ローカルとリモートどちらのシステムからのデータなのか、またデータが非同期または同期のどちらの方法でのキャプチャされるのかといった、データのソースに関心があるでしょう。パブリッシャはDBMS_LOGMNR_CDC_PUBLISHパッケージのプロシージャを使用して、次のステップを実行して変更システム構成要素の構造体を記述するスキーマ・オブジェクト、つまり変更ソース、変更セット、変更表を作成します。

1. 変更データを提供するソース・システムとなるOracleを決定します。パブリッシャはサブスクライバから要件を収集して、関連するソース表を含むソース・システムを決定する必要があります。
   　
2. データのキャプチャは、同期または非同期モードのどちらで行われるか決定します。パブリッシャは、ソース表（ローカル）と同じシステムの表または異なる（リモート）ステージング表を変更するデータのキャプチャを決定する必要があります。
   　
3. 各変更ソース・システムを識別して、変更ソースを作成します（非同期のみ）。
   DBMS_LOGMNR_CDC_PUBLISH.CREATE_CHANGE_SOURCEプロシージャを使用して、変更ソースを作成します。
   　
4. 「非同期」モードでは、REDOログをステージング・システムに移動します。「同期」モードでは、パブリッシャはSYNC_SOURCEというシステム生成の変更ソースを使用します。
   　
5. 変更セットを作成して、拡張の頻度を指定します（非同期のみ）。DBMS_LOGMNR_CDC_PUBLISH.CREATE_CHANGE_SETプロシージャを使用します。
   　
6. 個々のソース表への変更を含む変更表を作成します。DBMS_LOGMNR_CDC_PUBLISH.CREATE_CHANGE_TABLEプロシージャを使用します。
   　
7. 変更表を移入して、変更表が拡張される頻度を指定します。DBMS_LOGMNR_CDC_PUBLISH.ADVANCEプロシージャを使用します。
   　
8. サブスクライバにアクセス権を付与します。管理者はユーザーとロールに対して、変更表へのSELECT権限の付与と取消しによって変更データへのサブスクライバのアクセスを制御します。

変更データのサブスクライブ
通常はアプリケーションであるサブスクライバは、1つ以上のソース表に対象を登録してこれらの表へのサブスクリプションを取得します。十分なアクセス権限を前提とした場合、サブスクライバはパブリッシャが公開した任意のソース表をサブスクライブできます。サブスクライバの主な役割は、変更データにアクセスして使用することです。これを行うため、サブスクライバは最初に対象となるソース表を決定する必要があり、その後DBMS_LOGMNR_CDC_SUBSCRIBEパッケージのプロシージャをコールして次のようにアクセスします。

1. サブスクライバがアクセス権を持つソース表を検索します。ALL_PUBLICATIONSビューを問い合わせて、サブスクライバがアクセス権を持つ公開済みのすべてのソース表を表示します。
   　
2. サブスクリプション・ハンドルを取得します。DBMS_LOGMNR_CDC_SUBSCRIBE.GET_SUBSCRIPTION_HANDLEプロシージャをコールして、対象ソース表に関連付けられた変更セットの対象を登録します。
   　
3. ソース表とソース表の列をサブスクライブします。DBMS_LOGMNR_CDC_SUBSCRIBE.SUBSCRIBEプロシージャを使用して、サブスクライバがキャプチャするソース表の列を指定します。
   　
4. サブスクリプションをアクティブにします。DBMS_LOGMNR_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTIONプロシージャを使用して、サブスクリプションをアクティブにします。
   　
5. 新規データを表示して旧データを削除するため、境界を設定します。DBMS_LOGMNR_CDC_SUBSCRIBE.ECTEND_WINDOWプロシージャをコールして、サブスクリプションの境界（「最高水位標」と呼ばれます）を設定します。
   　
6. 変更ビューを作成します。DBMS_LOGMNR_CDC_SUBSCRIBE.PREPARE_SUBSCRIBER_VIEWプロシージャを使用して、変更ビューを作成します。
   　
7. 変更表の内容を読み込んで問い合わせます。変更ビューのSQL SELECT文を使用して、変更表の内容を読み込んで問い合わせます。
   　
8. 変更ビューを削除します。DBMS_CDC_SUBSCRIBE.DROP_SUBSCRIBER_VIEWプロシージャを使用して、変更ビューを削除します。
   　
9. サブスクリプション・ウィンドウの古いデータを空にします。DBMS_LOGMNR_CDC_SUBSCRIBE.PURGE_WINDOWプロシージャを使用して、サブスクライバが現在のサブスクリプション・ウィンドウのデータを必要としていないことをCDCソフトウェアに指示します。
   　
10. サブスクリプションを終了します。DBMS_LOGMNR_CDC_SUBSCRIBE.DROP_SUBSCRIPTIONプロシージャを使用して、サブスクリプションを終了します。
    
    次に示すのは、関連パラメータのあるDBMS_LOGMNR_CDC_PUBLISHパッケージのCREATE_CHANGE_TABLEプロシージャを使用した例です。
    
    sys.dbms_cdc_publish.create_change_table(
    owner = 'SH',
    change_table_name = 'DORTMUND_CT',
    change_set_name = 'SYNC_SET',
    source_schema = 'SH',
    source_table = 'CUSTOMERS',
    column_type_list =
    'CUST_LAST_NAME VARCHAR2(30),
    CUST_CITY VARCHAR2(30),
    CUST_CREDIT_LIMIT NUMBER',
    capture_values = 'both',
    rs_id = 'n',
    row_id = 'n',
    user_id = 'n',
    timestamp = 'y',
    object_id = 'n',
    source_colmap = 'n',
    target_colmap = 'n',
    options_string = NULL);

変更ロード・プロセスの管理
Change Data Captureでは、パブリッシャ（データベース管理者）によるデータ・ウェアハウスへの変更ロード・プロセスに役立つ多数のデータ・ディクショナリ・ビューが提供されます。

• SYS.CHANGE_TABLESビュー ― 変更表を記述します。
  　
• SYS.CHANGE_SETSビュー ― 変更セットとは、常に互いにトランザクションが一貫している関連変更表のグループです。
  一般的に、アプリケーションで使用されるすべての変更表は同じ変更セットにあります。
  　
• SYS.CHANGE_SOURCESビュー ― 変更セットとは、同じ獲得方法つまり非同期（REDOログ・ベース）または同期（トリガー・ベース）獲得を共有する変更ソースのグループです。
  　
• SYS.CHANGE_LOGSビュー ― 現在のステージング領域にあるすべてのREDOログを記述します。
  　
• SYS.CHANGE_DICTIONARIESビュー ― 現在のステージング領域にあるすべてのディクショナリを記述します。
  　
• SYS.CHANGE_SUBSCRIPTIONSビュー ― 変更キャプチャ・システムへのすべてのサブスクリプションを記述します。

New Media Technologies社は、同社のOLTP受注システムに直接統合されていない小規模な独立店舗とも多数取り引きをしています。これらの企業は、データ・フィードを外部ファイル形式で送信することが頻繁にあります。同社では、受注システムとデータ・ウェアハウスにこのデータをロードするため、フラット・ファイル型のような外部データ（カード型データなど）を通常のデータベース表と同じようにデータベース内で公開できるOracle9i の外部表機能を使用しています。外部表にはSQLを介してアクセスできるため、SQL、PL/SQL、Javaを使用して外部ファイルを直接パラレルに問い合わせることができます。外部表をETLプロセスで使用して、（SQLを通じた）データ変換とデータ・ロードを単独ステップに組み合わせることもあります。外部表は、ETLで使用できる多数のアプリケーションとフラット・ファイル型のデータが処理される他のデータベース環境において、非常にパワフルな機能です。

外部表の作成
外部表は、単独の「CREATE TABLE」DDLコマンドで作成できます。このコマンドにより、データベース内部からシームレスに外部データにアクセスするために必要なメタ情報が作成されます。次の情報を提供する必要があります。

• データベースでアクセスするための列とデータ型
• 外部データの検索場所
• SQL*Loaderに類似した外部データのフォーマット
• 並列処理の程度

以下に、外部表の作成構文例を示します。

　　CREATE TABLE sales_external
　　(prod_id NUMBER, cust_id NUMBER, ..., amount NUMBER(6,2), cost NUMBER(6,2))
　　ORGANIZATION EXTERNAL 　--　 表を外部表として宣言します
　　( TYPE oracle_loader 　--　 データがSQL*Loaderへの入力ソースと同じ要件であることを宣言します
　　DEFAULT DIRECTORY stage_dir 　--　 外部ファイルの検索先である論理ディレクトリ
　　ACCESS PARAMETERS 　--　 SQL*Loaderと同じ
　　( RECORDS DELIMITED BY NEWLINE
　　BADFILE 'bad_saless_ext'
　　LOGFILE 'log_sales_ext'
　　FIELDS TERMINATED BY ','
　　MISSING FIELDS ARE NULL )
　　LOCATION ('new_sales1.txt','new_sales2.txt') ) 　-- 　外部ファイル
　　PARALLEL 5　 -- 　ファイル数に依存しない
　　REJECT LIMIT UNLIMITED;


外部表のデータ・ディクショナリ・ビュー
外部表の情報は、次のビューで表示できます。

• DBA_OBJECTS
• DBA_EXTERNAL_TABLES
• DBA_EXTERNAL_LOCATIONS

外部表の操作
外部表は、仮想の読み取り専用の表としてデータベース内部でアクセス可能です。無制限の複雑なアクセスと必要に応じた変換が許可されます。外部表はデータベースにもアクセス可能な外部データ・ソースを統合して、標準的なSQLを使用してロードすることができます。この特徴により通常のSQL操作のルック&フィールが提供されるので、大容量データのロードと変換が高速になります。

以下に、外部表のロード構文例を示します。

　　INSERT INTO sh.sales
　　AS SELECT * FROM sales_external
　　WHERE time_id > (SYSDATE - 7);

Oracle9i の表ファンクションは、PL/SQL、Java、C、C++（Oracle9i がサポートする任意の言語）で実装される、パイプライン変換と変換のパラレル実行をサポートします。

表ファンクションの理解
表ファンクションは、結果セットを提供するFROM句の仮想PL/SQL表として使用できます。これらの結果セットをPL/SQL表のフィードにさらに使用することで、複雑なプロシージャ変換中の不要な中間ステージングを回避できます。次のような利点があります。

• 出力/入力として行セット（表）を使用
• SQLのSELECT文で通常の表のように使用
• 結果セットはステージングなしで増分パイプライン可能
• 並列処理の透過的使用
• ETLプロセス・フローのさらに柔軟なアーキテクチャ
• パフォーマンス全体の向上

表ファンクションの使用
表ファンクションを使用するには、コードを（PL/SQL、Java、Cなどで）記述して次の項目を決定します。

• 入出力レコード/レコード・タイプの定義
• パイプラインするかどうか? ― 結果セットの増分リターンを制御
• 並列処理を有効にするかどうか?
• 並列処理の方法 ― 表ファンクションに並列処理中に認識される必要のある依存コンポーネントがあるか?

次に、表ファンクションの作成構文例を示します。

　　CREATE OR REPLACE FUNCTION func1(cur refCursorInput) 　--　 レコード・タイプ
　　return tabOfRecordOutput 　--　 レコードの収集
　　parallel_enable (partition cur by any) 　--　 透過的な並列処理
　　IS
　　region varchar2(10) ;
　　sales number:=0;
　　objset tabOfRefCursorOutput := tabOfRecordOutput();
　　i number := 0;
　　begin LOOP FETCH cur INTO region,sales;
　　EXIT WHEN cur%NOTFOUND;
　　i:=i+1;
　　objset.extend;
　　objset(i):=tabOfRecordOutput(region,sales); 　--　 収集に対する
　　END LOOP;
　　CLOSE cur;
　　return objset; 　--　 レコードのリターン収集
　　END;

次のSQL問い合わせ例では、表ファンクションを使用してグループ関数をシミュレートし、特定リージョンのレコードをフィルタリングしています。

　　SELECT * FROM TABLE(func1(cursor(SELECT region, sales FROM oltptable))
　　AS tabOfRecordOutput);

表ファンクションを使用せずに同等のSQL文で表すと、次のようになります。

　　SELECT region, SUM(sales) FROM oltptable
　　WHERE region <> 'ACCOUNTING'
　　GROUP BY region; 　

この節では、次の項目を紹介しました。

• Change Data Captureによるデータ・ウェアハウス
• 外部表によるデータ・ウェアハウス
• 表ファンクションによるデータ・ウェアハウス