11g リリース1（11.1）
E05768-02
 目次 |
 索引 |
| Oracle Databaseユーティリティ 11g リリース1（11.1） E05768-02 |
|
外部表機能は、既存のSQL*Loader機能を補足する機能です。この機能によって、データベースに表がある場合と同様に、外部ソースのデータにアクセスできます。
Oracle Database 10g より前のリリースでは、外部表は読取り専用でした。Oracle Database 10g からは、外部表は書込みも可能となりました。データ・ロード時にステージング表の追加の索引付けが必要な場合、SQL*Loaderを使用する方が有効です。SQL*Loaderと外部表との処理内容の違いについては、「SQL*Loaderと外部表との処理内容の違い」を参照してください。
ORACLE_LOADERアクセス・ドライバを使用し、データ・ファイルがテキスト形式である場合に、外部表機能を使用するには、ご使用のプラットフォーム上のデータ・ファイルのファイル形式およびレコード形式の知識が必要です。また、外部表を作成し、その外部表に問合せを実行するためのSQLの知識も必要です。
この章の内容は、次のとおりです。
外部表は、SQLのCREATE TABLE...ORGANIZATION EXTERNAL文を使用して作成されます。外部表の作成時に、次の属性を指定します。
TYPE: 外部表の型を指定します。ORACLE_LOADER型およびORACLE_DATAPUMP型の2種類の型が選択可能です。外部表の各型は、固有のアクセス・ドライバによってサポートされます。
ORACLE_LOADERドライバは、デフォルトです。このドライバでは、データのロードのみを実行できます。また、データはテキスト・データ・ファイルからロードする必要があります。外部表から内部表へのロードは、外部表にあるテキストのみで構成されるデータ・ファイルから読み取ることによって実行されます。
ORACLE_DATAPUMPアクセス・ドライバでは、ロードおよびアンロードを実行できます。データは、バイナリのダンプ・ファイルからロードする必要があります。内部表から外部表へのロードは、バイナリのダンプ・ファイルからフェッチすることによって実行されます。内部表から外部表へのアンロードは、外部表のバイナリのダンプ・ファイルを移入することによって実行されます。
DEFAULT DIRECTORY: 外部表によって読取りまたは書込みが行われるファイルのデフォルトの位置を指定します。位置はディレクトリ・パスではなく、ディレクトリ・オブジェクトで指定されます。詳細は、「データ・ファイルおよび出力ファイルの位置」を参照してください。
ACCESS PARAMETERS: 外部データ・ソースを記述し、指定された外部表の型を実装します。各外部表の型には、その外部表の型に固有のアクセス・パラメータを提供する専用のアクセス・ドライバがあります。詳細は、「アクセス・パラメータ」を参照してください。
LOCATION: 外部表の位置を指定します。位置はディレクトリ・オブジェクトおよびファイル名のリストとして指定されます。ディレクトリ・オブジェクトが指定されない場合は、ファイルの位置として、デフォルトのディレクトリ・オブジェクトが使用されます。
次に、各属性の使用例を示します。
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とは異なります。この違いは、次のことを意味しています。
ORACLE_DATAPUMPアクセス・ドライバによって作成されたダンプ・ファイルの作成と書込みを行う必要があります。
アクセス・ドライバでは、ファイルに完全な詳細を指定できません。これは、ユーザーがアクセスできないファイルに、サーバーがアクセスする場合があり、ユーザーがデータを読み込むことができると、セキュリティに影響するためです。同様に、通常、ユーザーが削除権限を持たないファイルを上書きする可能性もあります。
かわりに、ファイルの読取り元および書込み元の位置として、ディレクトリ・オブジェクトを指定する必要があります。ディレクトリ・オブジェクトは、ファイル・システムのディレクトリ名に名前をマップします。たとえば、次の文は/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文の次の位置に示すことができます。
DEFAULT DIRECTORY句。この句は、明示的にディレクトリ・オブジェクトに名前を付けないすべての入出力ファイルに対して、使用するデフォルトのディレクトリを指定します。
LOCATION句。ファイル名は、directory:fileという書式です。directoryの部分はオプションです。この部分を指定しないと、デフォルトのディレクトリがファイルのディレクトリとして使用されます。
ACCESS PARAMETERS句。ファイル名は、directory:fileという書式です。directoryの部分はオプションです。この部分を指定しないと、デフォルトのディレクトリがファイルのディレクトリとして使用されます。アクセス・パラメータの構文で、特定の出力ファイルを作成しないように指定できます。この構文が有効なのは、出力ファイルを必要としない場合、またはいずれのディレクトリ・オブジェクトにも書込みアクセスをしない場合です。
SYSユーザーのみがディレクトリ・オブジェクトを所有できます。ただし、SYSユーザーは、ディレクトリ・オブジェクトを作成する権限を他のユーザーに付与できます。ディレクトリ・オブジェクトへのREAD 権限またはWRITE権限は、Oracle Databaseによるファイルの読取りまたは書込みのみを意味します。適切なオペレーティング・システム権限がないかぎり、Oracle Databaseの外部にあるファイルには直接アクセスできません。同様に、Oracle Databaseには、ディレクトリのファイルに対して読取りおよび書込みを行うオペレーティング・システム権限が必要です。
この項の手順では、ORACLE_LOADERアクセス・ドライバを使用した外部表の作成およびロードの例を示します。empという従来の表とemp_loadという外部表が定義されます。外部データは次に内部表にロードされます。
.dat ファイルが次のとおりであるとします。
56november, 15, 1980 baker mary alice 09/01/2004 87december, 20, 1970 roper lisa marie 01/01/1999
CREATE DIRECTORY ext_tab_dir AS '/usr/apps/datafiles'; GRANT READ ON DIRECTORY ext_tab_dir TO SCOTT;
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);
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.
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.
.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.
この例に関する注意事項
employee_numberフィールドは、外部表のemployee_numberフィールドの文字列に変換されます。
employee_dobフィールドが含まれています。
employee_middle_name列で使用されているsubstr関数によって、emp表のmiddle_initialの値が生成されます。
info.dat内のemployee_hire_dateの文字列は、外部表の定義で指定されている書式マスクを使用して、外部表へのアクセス時にDATEデータ型に自動的に変換されます。
employee_hire_dateとは異なり、employee_dobに対するDATEデータ型への変換は、SELECT時に行われます。この変換は、外部表の定義には含まれていません。外部表のコンテキストではデータのロードは、外部表からデータを読み込み、データベース内の表にロードすることを意味します。データのアンロードは、データベース内のデータを読み込み、外部表に挿入することを意味します。
データがロードされた場合、データ・ストリームはLOCATION句およびDEFAULT DIRECTORY句で指定されたファイルから読み込まれます。INSERT文では、外部データ・ソースからデータが処理されるOracle SQLエンジンへのデータの流れが発生します。外部ソースからのデータはアクセス・ドライバにより解析され、外部表インタフェースに提供される際に、外部表現からOracle内部データ型に変換されます。
データをアンロードするには、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'));
表rosterをroster_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アクセス・ドライバでは、大きいデータ・ファイルを、個別に処理できるチャンクに分割します。
次のファイル、レコードおよびデータ特性によって、ファイルのパラレル処理が禁止されます。
この制限事項は、1レコード当たりのバイト数が固定のデータ・ファイルには適用されません。
VAR形式のレコード
PARALLEL句の指定は、大量のデータを扱う場合にのみ有効です。
データのアンロードに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アクセス・ドライバに固有のパフォーマンスに関する情報について説明します。
パフォーマンスは、日付キャッシュ機能を使用して向上させることができる場合があります。日付キャッシュを使用して、ロード中に予測される一意の日付の数を指定する、入力データ内に多数の重複する日付またはタイムスタンプ値が存在する場合と、日付変換が実行される回数を減らすことができます。外部表で提供される日付キャッシュ機能は、SQL*Loaderで提供されるものと同じです。詳細は、「DATE_CACHE」を参照してください。
パフォーマンスを向上させるには、並列度の変更および日付キャッシュの使用に加えて、次のことを考慮してください。
WHEN、NULLIFおよびDEFAULTIFを含む）を使用すると、処理速度が遅くなる。
この項では、外部表機能で行われない処理および外部表処理上の制限事項について説明します。
ALTER TABLEコマンドを使用すると、この列処理動作を変更できます。
LONG列にデータをロードできません。
ORACLE_LOADERアクセス・ドライバのアクセス・パラメータにSQL文字列を指定できません。解決策として、外部表を読み取る文のSELECT句にDECODE句を使用できます。また、DECODE句を使用する外部表のビューを作成して、外部表ではなくそのビューから選択することもできます。
ORACLE_DATAPUMPアクセス・ドライバには、前述の制限の他に次の制限があります。
この項では、外部表を使用したデータのロード方法（ORACLE_LOADERアクセス・ドライバを使用）と、SQL*Loaderの従来型パス・ロードおよびダイレクト・パス・ロードを使用したデータのロード方法の重要な違いについて説明します。ここで示す情報は、ORACLE_DATAPUMPアクセス・ドライバには適用されません。
SQL*Loaderのロードを使用したプライマリ入力データ・ファイルが複数存在する場合は、入力データ・ファイルごとに不良ファイルおよび廃棄ファイルが作成されます。外部表ロードでは、すべての入力データ・ファイルに対する不良ファイルおよび廃棄ファイルは、1つずつのみです。外部表ロードでパラレル・アクセス・ドライバが使用される場合は、各アクセス・ドライバに不良ファイルおよび廃棄ファイルが含まれます。
次の操作は、外部表ロードではサポートされていません。
CONTINUEIFまたはCONCATENATEを使用した、1つの論理レコードへの複数の物理レコードの結合
GRAPHIC、GRAPHIC EXTERNALおよびVARGRAPHIC）のロード
LONG、ネストした表、VARRAY、REF、主キー、REFおよびSID）の使用
SQL*Loaderでは、プライマリ・データ・ファイルにUnicodeキャラクタ・セット（UTF8またはUTF16）が使用され、バイト順序マーク（BOM）が含まれている場合、バイト順序マークは対応する不良ファイルおよび廃棄ファイルの先頭に書き込まれます。外部表ロードでは、バイト順序マークは不良ファイルおよび廃棄ファイルの先頭に書き込まれません。
データ・ファイルのフィールドでは、クライアントのNLS環境変数によって、デフォルトのキャラクタ・セット、日付マスクおよび小数点区切りが決定されます。外部表のフィールドでは、NLSパラメータのデータベース設定によって、デフォルトのキャラクタ・セット、日付マスクおよび小数点区切りが決定されます。
SQL*Loaderでは、次のようにバックスラッシュ（\）エスケープ文字を使用して、一重引用符を一重引用符として使用できます。
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\''
外部表では、文字列内でバックスラッシュ・エスケープ文字を使用すると、エラーが発生します。解決策としては、次のように分離文字列に二重引用符を使用します。
TERMINATED BY ',' ENCLOSED BY "'"
|
 Copyright © 2007 Oracle Corporation. All Rights Reserved. |
|