ヘッダーをスキップ

Oracle Database ユーティリティ
10g リリース2(10.2)

B19211-01
目次
目次
索引
索引

戻る 次へ

12 外部表の概要

外部表機能は、既存のSQL*Loader機能を補足する機能です。この機能によって、データベースに表がある場合と同様に、外部ソースのデータにアクセスできます。

Oracle Database 10g より前のリリースでは、外部表は読取り専用でした。Oracle Database 10g からは、外部表は書込みも可能となりました。データ・ロード時にステージング表の追加の索引付けが必要な場合、SQL*Loaderを使用する方が有効です。SQL*Loaderと外部表との処理内容の違いについては、「SQL*Loaderと外部表との処理内容の違い」を参照してください。

ORACLE_LOADERアクセス・ドライバを使用し、データ・ファイルがテキスト形式である場合に、外部表機能を使用するには、ご使用のプラットフォーム上のデータ・ファイルのファイル形式およびレコード形式の知識が必要です。また、外部表を作成し、その外部表に問合せを実行するためのSQLの知識も必要です。

この章の内容は、次のとおりです。

外部表の作成方法

外部表は、SQLのCREATE TABLE...ORGANIZATION EXTERNAL文を使用して作成されます。外部表の作成時に、次の属性を指定します。

次に、各属性の使用例を示します。

SQL> CREATE TABLE emp_load
  2    (employee_number      CHAR(5),
  3     employee_dob         CHAR(20),
  4     employee_last_name   CHAR(20),
  5     employee_first_name  CHAR(15),
  6     employee_middle_name CHAR(15),
  7     employee_hire_date   DATE)
  8  ORGANIZATION EXTERNAL
  9    (TYPE ORACLE_LOADER
 10     DEFAULT DIRECTORY def_dir1
 11     ACCESS PARAMETERS
 12       (RECORDS DELIMITED BY NEWLINE
 13        FIELDS (employee_number      CHAR(2),
 14                employee_dob         CHAR(20),
 15                employee_last_name   CHAR(18),
 16                employee_first_name  CHAR(11),
 17                employee_middle_name CHAR(11),
 18                employee_hire_date   CHAR(10) date_format DATE mask "mm/dd/yyyy"
 19               )
 20       )
 21     LOCATION ('info.dat')
 22    );
 
Table created.

アクセス・ドライバで指定する情報により、データ・ソースのデータが、外部表の定義と一致するように処理されます。CREATE TABLE emp_loadの後にリストされるフィールドが、実際にinfo.datソース・ファイル内のデータのメタデータを定義します。アクセス・パラメータはオプションです。

アクセス・パラメータ

特定の型の外部表を作成する場合、アクセス・パラメータを指定して、アクセス・ドライバのデフォルトの動作を変更できます。各アクセス・ドライバには、アクセス・パラメータ用の固有の構文があります。

参照:

 

データ・ファイルおよび出力ファイルの位置

アクセス・ドライバは、データベース・サーバー内で実行されます。これは、SQL*Loaderが、ロードするデータをサーバーに送信するクライアント・プログラムであるという点で、SQL*Loaderとは異なります。この違いは、次のことを意味しています。

アクセス・ドライバでは、ファイルに完全な詳細を指定できません。これは、ユーザーがアクセスできないファイルに、サーバーがアクセスする場合があり、ユーザーがデータを読み込むことができると、セキュリティに影響するためです。同様に、通常、ユーザーが削除権限を持たないファイルを上書きする可能性もあります。

かわりに、ファイルの読取り元および書込み元の位置として、ディレクトリ・オブジェクトを指定する必要があります。ディレクトリ・オブジェクトは、ファイル・システムのディレクトリ名に名前をマップします。たとえば、次の文は/usr/apps/datafilesにあるディレクトリにマップされる、ext_tab_dirという名前のディレクトリ・オブジェクトを作成します。

CREATE DIRECTORY ext_tab_dir AS '/usr/apps/datafiles';

ディレクトリ・オブジェクトは、DBAまたはCREATE ANY DIRECTORY権限を持つすべてのユーザーが作成できます。

ディレクトリの作成後、ディレクトリ・オブジェクトを作成するユーザーは、そのディレクトリのREAD権限およびWRITE権限を他のユーザーに付与する必要があります。これらの権限は、ロールを使用して割り当てるのではなく、明示的に付与する必要があります。たとえば、ext_tab_dirで指定されたディレクトリのユーザーscottのかわりに、サーバーがファイルを読み込むことができるようにするには、ディレクトリ・オブジェクトを作成したユーザーが、次のコマンドを実行する必要があります。

GRANT READ ON DIRECTORY ext_tab_dir TO scott;

ディレクトリ・オブジェクトの名前は、CREATE TABLE...ORGANIZATION EXTERNAL文の次の位置に示すことができます。

SYSユーザーのみがディレクトリ・オブジェクトを所有できます。ただし、SYSユーザーは、ディレクトリ・オブジェクトを作成する権限を他のユーザーに付与できます。ディレクトリ・オブジェクトへのREAD 権限またはWRITE権限は、Oracleデータベースによるファイルの読取りまたは書込みのみを意味します。適切なオペレーティング・システム権限がないかぎり、Oracleデータベースの外部にあるファイルには直接アクセスできません。同様に、Oracleデータベースには、ディレクトリのファイルに対して読取りおよび書込みを行うオペレーティング・システム権限が必要です。

例: ORACLE_LOADERを使用した外部表の作成およびロード

この項の手順では、ORACLE_LOADERアクセス・ドライバを使用した外部表の作成およびロードの例を示します。empという従来の表とemp_loadという外部表が定義されます。外部データは次に内部表にロードされます。

  1. .dat ファイルが次のとおりであるとします。

    56november, 15, 1980  baker             mary       alice     09/01/2004
    87december, 20, 1970  roper             lisa       marie     01/01/1999
    
    
  2. データ・ソースを含むデフォルトのディレクトリを設定して、それに対するアクセス権限を付与するには、次のSQL文を実行します。

    CREATE DIRECTORY ext_tab_dir AS '/usr/apps/datafiles';
    GRANT READ ON DIRECTORY ext_tab_dir TO SCOTT;
    
    
  3. empという名前の従来の表を作成します。

    CREATE TABLE emp (emp_no CHAR(6), last_name CHAR(25), first_name CHAR(20), middle_
    initial CHAR(1), hire_date DATE, dob DATE);
    
    
  4. emp_loadという名前の外部表を作成します。

    SQL> CREATE TABLE emp_load
      2    (employee_number      CHAR(5),
      3     employee_dob         CHAR(20),
      4     employee_last_name   CHAR(20),
      5     employee_first_name  CHAR(15),
      6     employee_middle_name CHAR(15),
      7     employee_hire_date   DATE)
      8  ORGANIZATION EXTERNAL
      9    (TYPE ORACLE_LOADER
     10     DEFAULT DIRECTORY def_dir1
     11     ACCESS PARAMETERS
     12       (RECORDS DELIMITED BY NEWLINE
     13        FIELDS (employee_number      CHAR(2),
     14                employee_dob         CHAR(20),
     15                employee_last_name   CHAR(18),
     16                employee_first_name  CHAR(11),
     17                employee_middle_name CHAR(11),
     18                employee_hire_date   CHAR(10) date_format DATE mask "mm/dd/yyyy"
     19               )
     20       )
     21     LOCATION ('info.dat')
     22    );
     
    Table created.
    
    
  5. emp_load外部表からemp表へデータをロードします。

    SQL> INSERT INTO emp (emp_no,
      2                   first_name,
      3                   middle_initial,
      4                   last_name,
      5                   hire_date,
      6                   dob)
      7  (SELECT employee_number,
      8          employee_first_name,
      9          substr(employee_middle_name, 1, 1),
     10          employee_last_name,
     11          employee_hire_date,
     12          to_date(employee_dob,'month, dd, yyyy')
     13  FROM emp_load);
     
    2 rows created.
    
    
  6. .dat ファイル内の情報がemp表にロードされたことを確認するために、次の選択操作を行います。

    SQL> SELECT * FROM emp;
     
    EMP_NO LAST_NAME                 FIRST_NAME           M HIRE_DATE DOB
    ------ ------------------------- -------------------- - --------- ---------
    56     baker                     mary                 a 01-SEP-04 15-NOV-80
    87     roper                     lisa                 m 01-JAN-99 20-DEC-70
     
    2 rows selected.
    
    

この例に関する注意事項

外部表を使用したデータのロードおよびアンロード

外部表のコンテキストではデータのロードは、外部表からデータを読み込み、データベース内の表にロードすることを意味します。データのアンロードは、データベース内のデータを読み込み、外部表に挿入することを意味します。


注意:

データは、ORACLE_DATAPUMPアクセス・ドライバを使用してのみアンロードできます。 


データのロード

データがロードされた場合、データ・ストリームはLOCATION句およびDEFAULT DIRECTORY句で指定されたファイルから読み込まれます。INSERT文では、外部データ・ソースからデータが処理されるOracle SQLエンジンへのデータの流れが発生します。外部ソースからのデータはアクセス・ドライバにより解析され、外部表インタフェースに提供される際に、外部表現からOracle内部データ型に変換されます。

ORACLE_DATAPUMPアクセス・ドライバを使用したデータのアンロード

データをアンロードするには、ORACLE_DATAPUMPアクセス・ドライバを使用します。アンロードされるデータ・ストリームには独自の形式が使用され、アンロードされるすべての行に対するすべての列データが含まれます。

また、アンロード処理では、データ・ストリームの内容を記述するメタデータ・ストリームが作成されます。メタデータ・ストリーム内の情報はデータ・ストリームのロードに必要です。そのため、メタデータ・ストリームはデータ・ファイルに書き込まれ、データ・ストリームの前に置かれます。

列オブジェクトの処理

SQL文を介して外部表にアクセスすると、外部表のフィールドは、通常の表の他のフィールドと同様に使用できます。特に、SQLの組込み関数、PL/SQLファンクションまたはJavaファンクションの引数として使用できます。これによって、外部ソースのデータを操作できます。

外部表に列オブジェクトは含まれませんが、コンストラクタ・ファンクションを使用して外部表の属性から列オブジェクトを作成できます。たとえば、データベースの表が次のように定義されているとします。

CREATE TYPE student_type AS object (
student_no CHAR(5),
name CHAR(20))
/

CREATE TABLE roster (
  student student_type,
  grade CHAR(2));

また、次のように定義された外部表があるとします。

CREATE TABLE roster_data (
  student_no CHAR(5),
  name CHAR(20),
  grade CHAR(2))
  ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
                         ACCESS PARAMETERS (FIELDS TERMINATED BY ',')
                         LOCATION ('info.dat'));

rosterroster_dataからロードするには、次のとおり指定します。

INSERT INTO roster (student, grade)
  (SELECT student_type(student_no, name), grade FROM roster_data);

外部表の使用時のデータ型の変換

データを外部表から、または外部表に移動すると、次の3つの場所にある同一の列が、異なるデータ型を持つ可能性があります。

データ・ファイルと外部表の間で変換エラーが発生すると、エラーの発生した行は無視されます。変換エラーおよび制約違反を含む、外部表とデータベース内の列でエラーが発生すると、操作全体が正常に完了されないまま終了します。

データが外部表にアンロードされる際に、ソース表内の列のデータ型が外部表の列のデータ型と一致していない場合、データが変換されます。変換エラーが発生した場合、その時点までに処理されたすべての行がデータ・ファイルに含まれず、データ・ファイルの読取りができなくなる場合があります。変換エラーによる操作の異常終了を回避するには、外部表の列のデータ型とデータベースの列のデータ型を一致させます。ただし、外部表はすべてのデータ型をサポートするわけではないため、必ず成功するとはかぎりません。そのような場合、ソース表でサポートされていないデータ型を、外部表でサポートするデータ型に変換する必要があります。たとえば、ソース表にLONG列がある場合、外部表の対応する列はCLOBである必要があり、外部表を移入するために使用したSELECT副問合せは、列をロードするためにTO_LOB演算子を使用する必要があります。次に例を示します。

CREATE TABLE LONG_TAB_XT (LONG_COL CLOB) ORGANIZATION EXTERNAL...SELECT TO_LOB(LONG_
COL) FROM LONG_TAB;

外部表へのパラレル・アクセス

データ・ファイルでのパラレル処理を外部表でサポートするには、外部表の作成時にPARALLEL句を使用します。アクセス・ドライバによって、パラレル・アクセス方法がわずかに異なります。

ORACLE_LOADERを使用したパラレル・アクセス

ORACLE_LOADERアクセス・ドライバでは、大きいデータ・ファイルを、個別に処理できるチャンクに分割します。

次のファイル、レコードおよびデータ特性によって、ファイルのパラレル処理が禁止されます。

PARALLEL句の指定は、大量のデータを扱う場合にのみ有効です。

ORACLE_DATAPUMPを使用したパラレル・アクセス

データのアンロードにORACLE_DATAPUMPアクセス・ドライバを使用する場合、PARALLEL句またはPARALLELヒントが指定され、外部表に複数の位置が指定されていると、データはパラレルでアンロードされます。

パラレル処理では、それぞれ固有のファイルに書込みを行います。したがって、LOCATION句には、並列度と同じファイル数を指定する必要があります。指定した並列度よりファイル数が少ない場合、並列度は指定したファイル数に制限されます。指定した並列度よりファイル数が多い場合、超過したファイルは使用されません。

データのアンロードに加え、ORACLE_DATAPUMPアクセス・ドライバでは、データのロードも実行できます。パラレル処理では、複数のダンプ・ファイルや同じダンプ・ファイルのチャンクも同時に読み取ることができます。したがって、ファイルが複数のファイル・オフセットを含むことができる大きさであれば、ダンプ・ファイルが1つであっても、データをパラレルでロードできます。これは、ORACLE_DATAPUMPアクセス・ドライバがデータをアンロードする場合、新しいデータ・チャンクの最初のダンプ・ファイルにオフセットを定期的に記録し、アンロードが完了した際に、ファイルにその情報を書き込むためです。非パラレル・ロードでは、一度にファイルにアクセスできる処理は1つのみのため、ファイルのオフセットは無視されます。パラレル・ロードでは、パラレル処理間でファイル・オフセットが分散され、ファイルまたはファイル・セットで同時に複数の処理が行われます。

外部表使用時のパフォーマンスのヒント

パフォーマンスを監視する場合、最も重要なことは、ロードの経過時間の測定です。また、CPU使用量、メモリー使用量およびI/O率の測定も重要です。

並列度を増減することによって、パフォーマンスを変更できます。並列度は、データ・ファイルの処理に起動できるアクセス・ドライバの数を示します。並列度によって、リソース使用率を低くした遅いロードと、すべてのリソースを使用した速いロードを選択できます。アクセス・ドライバは、アクセス・ドライバ専用に使用するリソース量を判断できないため、自動的にはチューニングされません。

アクセス・ドライバは、パフォーマンスを向上させるために大きなI/Oバッファを使用します。共有サーバーを使用するデータベースでは、アクセス・ドライバが使用するすべてのメモリーはSystem Global Area(SGA)から割り当てられます。そのため、共有サーバー上の外部表を使用する際には注意が必要です。ORACLE_LOADERアクセス・ドライバでは、バッファ・サイズの指定にアクセス・パラメータのREADSIZE句を使用できます。

ORACLE_LOADERアクセス・ドライバに固有のパフォーマンスのヒント

この項では、ORACLE_LOADERアクセス・ドライバに固有のパフォーマンスに関する情報について説明します。

パフォーマンスは、日付キャッシュ機能を使用して向上させることができる場合があります。日付キャッシュを使用して、ロード中に予測される一意の日付の数を指定する、入力データ内に多数の重複する日付またはタイムスタンプ値が存在する場合と、日付変換が実行される回数を減らすことができます。外部表で提供される日付キャッシュ機能は、SQL*Loaderで提供されるものと同じです。詳細は、「DATE_CACHE」を参照してください。

パフォーマンスを向上させるには、並列度の変更および日付キャッシュの使用に加えて、次のことを考慮してください。

外部表の制限事項

この項では、外部表機能で行われない処理および外部表処理上の制限事項について説明します。

ORACLE_DATAPUMPアクセス・ドライバに固有の制限

ORACLE_DATAPUMPアクセス・ドライバには、前述の制限の他に次の制限があります。

SQL*Loaderと外部表との処理内容の違い

この項では、外部表を使用したデータのロード方法(ORACLE_LOADERアクセス・ドライバを使用)と、SQL*Loaderの従来型パス・ロードおよびダイレクト・パス・ロードを使用したデータのロード方法の重要な違いについて説明します。ここで示す情報は、ORACLE_DATAPUMPアクセス・ドライバには適用されません。

複数のプライマリ入力データ・ファイル

SQL*Loaderのロードを使用したプライマリ入力データ・ファイルが複数存在する場合は、入力データ・ファイルごとに不良ファイルおよび廃棄ファイルが作成されます。外部表ロードでは、すべての入力データ・ファイルに対する不良ファイルおよび廃棄ファイルは、1つずつのみです。外部表ロードでパラレル・アクセス・ドライバが使用される場合は、各アクセス・ドライバに不良ファイルおよび廃棄ファイルが含まれます。

構文およびデータ型

次の操作は、外部表ロードではサポートされていません。

BOM

SQL*Loaderでは、プライマリ・データ・ファイルにUnicodeキャラクタ・セット(UTF8またはUTF16)が使用され、バイト順序マーク(BOM)が含まれている場合、バイト順序マークは対応する不良ファイルおよび廃棄ファイルの先頭に書き込まれます。外部表ロードでは、バイト順序マークは不良ファイルおよび廃棄ファイルの先頭に書き込まれません。

デフォルトのキャラクタ・セット、日付マスク、小数点区切り

データ・ファイルのフィールドでは、クライアントのNLS環境変数によって、デフォルトのキャラクタ・セット、日付マスクおよび小数点区切りが決定されます。外部表のフィールドでは、NLSパラメータのデータベース設定によって、デフォルトのキャラクタ・セット、日付マスクおよび小数点区切りが決定されます。

バックスラッシュ・エスケープ文字の使用

SQL*Loaderでは、次のようにバックスラッシュ(\)エスケープ文字を使用して、一重引用符を一重引用符として使用できます。

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\''

外部表では、文字列内でバックスラッシュ・エスケープ文字を使用すると、エラーが発生します。解決策としては、次のように分離文字列に二重引用符を使用します。

TERMINATED BY ',' ENCLOSED BY "'"


戻る 次へ
Oracle
Copyright © 2005 Oracle Corporation.

All Rights Reserved.
目次
目次
索引
索引