Oracle Databaseの場合の外部表の例

18.1 ORACLE_LOADERアクセス・ドライバを使用したパーティション化された外部表の作成

このトピックでは、ORACLE_LOADERアクセス・ドライバを使用してパーティション化された外部表を作成する方法について説明します。

例18-1 ORACLE_LOADERを使用したパーティション化された外部表の作成

この例では、次の内容の4つのデータ・ファイルがあると仮定しています。

p1a.dat:
1, AAAAA Plumbing,01372,
28, Sparkly Laundry,78907,
13, Andi's Doughnuts,54570,

p1b.dat:
51, DIY Supplies,61614,
87, Fast Frames,22201,
89, Friendly Pharmacy,89901,

p2.dat:
121, Pleasant Pets,33893,
130, Bailey the Bookmonger,99915,
105, Le Bistrot du Chat Noir,94114,

p3.dat:
210, The Electric Eel Diner,07101,
222, Everyt'ing General Store,80118,
231, Big Rocket Market,01754,

データ・ファイルには、CUSTOMER_NUMBER、CUSTOMER_NAMEおよびPOSTAL_CODEの3つのフィールドがあります。外部表ではCUSTOMER_NUMBERに対してレンジ・パーティション化を使用して3つのパーティションを作成します。

パーティション1、100未満のcustomer_number用
パーティション2、200未満のcustomer_number用
パーティション3、300未満のcustomer_number用

最初のパーティションのデータ・ファイルは2つで、他のパーティションでは1つのみであることに注意してください。次に、ファイルを作成する場合のSQLPlusからの出力を示します。

SQL> create table customer_list_xt
  2    (CUSTOMER_NUMBER number, CUSTOMER_NAME VARCHAR2(50), POSTAL_CODE CHAR(5))
  3  organization external
  4    (type oracle_loader default directory def_dir1)
  5  partition by range(CUSTOMER_NUMBER)
  6  (
  7    partition p1 values less than (100) location('p1a.dat', 'p1b.dat'),
  8    partition p2 values less than (200) location('p2.dat'),
  9    partition p3 values less than (300) location('p3.dat')
 10  );

Table created.
SQL>

次に、表全体に対するSELECT *からの出力を示します。

SQL> select customer_number, customer_name, postal_code
  2    from customer_list_xt
  3    order by customer_number;

CUSTOMER_NUMBER CUSTOMER_NAME                                      POSTA
--------------- -------------------------------------------------- -----
              1  AAAAA Plumbing                                    01372
             13  Andi's Doughnuts                                  54570
             28  Sparkly Laundry                                   78907
             51  DIY Supplies                                      61614
             87  Fast Frames                                       22201
             89  Friendly Pharmacy                                 89901
            105  Le Bistrot du Chat Noir                           94114
            121  Pleasant Pets                                     33893
            130  Bailey the Bookmonger                             99915
            210  The Electric Eel Diner                            07101
            222  Everyt'ing General Store                          80118
            231  Big Rocket Market                                 01754

12 rows selected.

SQL>

次の問合せでは、最初のパーティションのレコードのみが読み取られます。

SQL> select customer_number, customer_name, postal_code
  2    from customer_list_xt
  3    where customer_number < 20
  4    order by customer_number;

CUSTOMER_NUMBER CUSTOMER_NAME                                      POSTA
--------------- -------------------------------------------------- -----
              1  AAAAA Plumbing                                    01372
             13  Andi's Doughnuts                                  54570

2 rows selected.

SQL>

次の問合せでは、SELECT文の一部として読み取るパーティションを指定します。

SQL> select customer_number, customer_name, postal_code
  2    from customer_list_xt partition (p1)
  3    order by customer_number;

CUSTOMER_NUMBER CUSTOMER_NAME                                      POSTA
--------------- -------------------------------------------------- -----
              1  AAAAA Plumbing                                    01372
             13  Andi's Doughnuts                                  54570
             28  Sparkly Laundry                                   78907
             51  DIY Supplies                                      61614
             87  Fast Frames                                       22201
             89  Friendly Pharmacy                                 89901

6 rows selected.

SQL>

親トピック: Oracle Databaseの場合の外部表の例

18.2 ORACLE_LOADERアクセス・ドライバを使用したパーティション化されたハイブリッド表の作成

このトピックでは、ORACLE_LOADERアクセス・ドライバを使用してパーティション化されたハイブリッド表を作成する方法について説明します。

ハイブリッド・パーティション表は、データベース・セグメント内および外部のファイルまたはソース内に一部のパーティションが存在できるようにすることで、Oracle Partitioningを拡張する機能です。これにより、表の大部分が外部パーティションに存在できるようになり、Big Data SQLのパーティション化の機能が大幅に向上します。

例18-2 例

次に、パーティション化されたハイブリッド表を作成する文の例を示します。

CREATE TABLE hybrid_pt (time_id date, customer number)
  TABLESPACE TS1
  EXTERNAL PARTITION ATTRIBUTES (TYPE ORACLE_LOADER 
                                 DEFAULT DIRECTORY data_dir0
                                 ACCESS PARAMETERS(FIELDS TERMINATED BY ',')
                                 REJECT LIMIT UNLIMITED) 
PARTITION by range (time_id)
(
 PARTITION century_18 VALUES LESS THAN ('01-01-1800')
    EXTERNAL,                                                 <-- empty external partition
  PARTITION century_19 VALUES LESS THAN ('01-01-1900')
    EXTERNAL DEFAULT DIRECTORY data_dir1 LOCATION (‘century19_data.txt'),
  PARTITION century_20 VALUES LESS THAN ('01-01-2000') 
    EXTERNAL LOCATION (‘century20_data.txt'),
  PARTITION year_2000 VALUES LESS THAN ('01-01-2001') TABLESPACE TS2,
  PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

この例では、表に内部パーティションと外部パーティションの両方が含まれています。表の内部パーティションのデフォルト表領域は、TS1です。表内の外部パーティションに表レベルで適用されるパラメータを指定するために、EXTERNAL PARTITION ATTRIBUTES句が追加されます。ハイブリッド・パーティション表の場合、この句は必須です。この場合、外部パーティションはORACLE_LOADERアクセス・ドライバを介してアクセスされ、アクセス・ドライバで必要なパラメータは句で指定されます。パーティション・レベルで、EXTERNAL句は、パーティションに適用されるすべての外部パラメータとともに、外部パーティションごとに指定されます。

この例では、century_18、century_19およびcentury_20は外部パーティションです。century_18は、場所が含まれていないため空のパーティションです。パーティションcentury_19のデフォルト・ディレクトリはdata_dir1であり、表レベルのデフォルト・ディレクトリよりも優先されます。パーティションの場所はdata_dir1:century19_data.txtになります。パーティションにデフォルト・ディレクトリが指定されていない場合、表レベルのデフォルト・ディレクトリが場所に適用されるため、パーティションcentury_20の場所はdata_dir0:century20_data.txtになります。パーティションyear_2000およびpmaxは、内部パーティションです。パーティションyear_2000にはtablespace TS2が含まれます。パーティションにEXTERNAL句または外部パラメータが指定されていない場合、デフォルトで内部パーティションとみなされます。

親トピック: Oracle Databaseの場合の外部表の例

18.3 ORACLE_DATAPUMPアクセス・ドライバを使用したパーティション化された外部表の作成

サブパーティション化された外部表、および仮想列を含むパーティション表をORACLE_DATAPUMPアクセス・ドライバを使用して作成する方法について説明します。

ノート:

Oracle Database 23ai以降、ORACLE_DATAPUMPアクセス・ドライバでは、ハイブリッド・パーティション表(HyPT)のサポートのために、間隔、自動リストおよびコンポジット・パーティション化のオプションが提供されます。詳細は、Oracle Database VLDBおよびパーティショニング・ガイドを参照してください。

例18-3 ORACLE_DATAPUMPアクセス・ドライバを使用したパーティション化された外部表の作成

この例で使用するダンプ・ファイルは、前の例でORACLE_LOADERアクセス・ドライバを使用して作成されたものと同じです。ただし、この例では、customer_numberを使用してデータをパーティション化するだけでなく、postal_codeを使用してデータをサブパーティション化します。すべてのパーティションに、postal_codeが50000未満のサブパーティションと、postal_codeが他のすべての値の別のサブパーティションがあります。それぞれ2つのサブパーティションを含む3つのパーティションでは、合計で6つのファイルが必要となります。ファイルを作成するために、SQL CREATE TABLE AS SELECTを使用してパーティションの正しい行を選択し、それらの行をORACLE_DATAPUMPドライバのファイルに書き込みます。

次の文では、パーティションp1 (customer_numberが100未満)の最初のサブパーティション(postal_codeが50000未満)のデータを含むファイルが作成されます。

SQL> create table customer_list_dp_p1_sp1_xt
  2  organization external
  3    (type oracle_datapump default directory def_dir1 location('p1_sp1.dmp'))
  4  as
  5    select customer_number, customer_name, postal_code
  6      from customer_list_xt partition (p1)
  7      where to_number(postal_code) < 50000;

Table created.

SQL>

この文では、パーティションp1 (customer_numberが100未満)の2番目のサブパーティション(postal_codeが他のすべての値)のデータを含むファイルが作成されます。

SQL> create table customer_list_dp_p1_sp2_xt
  2  organization external
  3    (type oracle_datapump default directory def_dir1 location('p1_sp2.dmp'))
  4  as
  5    select customer_number, customer_name, postal_code
  6      from customer_list_xt partition (p1)
  7      where to_number(postal_code) >= 50000;

Table created.

次のように、他のパーティションのファイルは同様に作成されます。

SQL> create table customer_list_dp_p2_sp1_xt
  2  organization external
  3    (type oracle_datapump default directory def_dir1 location('p2_sp1.dmp'))
  4  as
  5    select customer_number, customer_name, postal_code
  6      from customer_list_xt partition (p2)
  7      where to_number(postal_code) < 50000;

Table created.

SQL>
SQL> create table customer_list_dp_p2_sp2_xt
  2  organization external
  3    (type oracle_datapump default directory def_dir1 location('p2_sp2.dmp'))
  4  as
  5    select customer_number, customer_name, postal_code
  6      from customer_list_xt partition (p2)
  7      where to_number(postal_code) >= 50000;

Table created.

SQL>
SQL> create table customer_list_dp_p3_sp1_xt
  2  organization external
  3    (type oracle_datapump default directory def_dir1 location('p3_sp1.dmp'))
  4  as
  5    select customer_number, customer_name, postal_code
  6      from customer_list_xt partition (p3)
  7      where to_number(postal_code) < 50000;

Table created.

SQL>
SQL> create table customer_list_dp_p3_sp2_xt
  2  organization external
  3    (type oracle_datapump default directory def_dir1 location('p3_sp2.dmp'))
  4  as
  5    select customer_number, customer_name, postal_code
  6      from customer_list_xt partition (p3)
  7      where to_number(postal_code) >= 50000;

Table created.

SQL>

これらの各外部表から選択して、書き出すデータが含まれているかどうかを確認できます。SQL文CREATE TABLE AS SELECTを実行した後、これらの外部表を削除できます。

仮想列を使用して表をパーティション化するために、パーティション化されたORACLE_DATAPUMP表を作成します。この例でも、表はcustomer_number列でパーティション化され、postal_code列でサブパーティション化されます。postal_code列は数字を含む文字列フィールドですが、この例では、文字列ではなく数値に基づいてこの列をパーティション化します。これを行うために、仮想列postal_code_numを作成します。その値は、NUMBERデータ型に変換されたpostal_codeフィールドです。SUBPARTITION句では、行のサブパーティションを決定するために仮想列を使用します。

SQL> create table customer_list_dp_xt
  2  (customer_number    number,
  3   CUSTOMER_NAME      VARCHAR2(50),
  4   postal_code        CHAR(5),
  5   postal_code_NUM    as (to_number(postal_code)))
  6  organization external
  7    (type oracle_datapump default directory def_dir1)
  8  partition by range(customer_number)
  9  subpartition by range(postal_code_NUM)
 10  (
 11    partition p1 values less than (100)
 12     (subpartition p1_sp1 values less than (50000) location('p1_sp1.dmp'),
 13      subpartition p1_sp2 values less than (MAXVALUE) location('p1_sp2.dmp')),
 14    partition p2 values less than (200)
 15     (subpartition p2_sp1 values less than (50000) location('p2_sp1.dmp'),
 16      subpartition p2_sp2 values less than (MAXVALUE) location('p2_sp2.dmp')),
 17    partition p3 values less than (300)
 18     (subpartition p3_sp1 values less than (50000) location('p3_sp1.dmp'),
 19      subpartition p3_sp2 values less than (MAXVALUE) location('p3_sp2.dmp'))
 20  );

Table created.

SQL>

すべての行を選択した場合、返されるデータはORACLE_LOADERアクセス・ドライバを使用した前の例で返されたデータと同じになります。

SQL> select customer_number, customer_name, postal_code
  2    from customer_list_dp_xt
  3    order by customer_number;

customer_number CUSTOMER_NAME                                      POSTA
--------------- -------------------------------------------------- -----
              1  AAAAA Plumbing                                    01372
             13  Andi's Doughnuts                                  54570
             28  Sparkly Laundry                                   78907
             51  DIY Supplies                                      61614
             87  Fast Frames                                       22201
             89  Friendly Pharmacy                                 89901
            105  Le Bistrot du Chat Noir                           94114
            121  Pleasant Pets                                     33893
            130  Bailey the Bookmonger                             99915
            210  The Electric Eel Diner                            07101
            222  Everyt'ing General Store                          80118
            231  Big Rocket Market                                 01754

12 rows selected.

SQL>

WHERE句では、読み取る行を1つのサブパーティションに制限できます。次の問合せでは、最初のパーティションの最初のサブパーティションのみが読み取られます。

SQL> select customer_number, customer_name, postal_code
  2    from customer_list_dp_xt
  3    where customer_number < 20 and postal_code_NUM < 39998
  4    order by customer_number;

customer_number CUSTOMER_NAME                                      POSTA
--------------- -------------------------------------------------- -----
              1  AAAAA Plumbing                                    01372

1 row selected.

SQL>

次のように、問合せで特定のパーティションを指定することもできます。

SQL> select customer_number, customer_name, postal_code
  2    from customer_list_dp_xt subpartition (p2_sp2) order by customer_number;

customer_number CUSTOMER_NAME                                      POSTA
--------------- -------------------------------------------------- -----
            105  Le Bistrot du Chat Noir                           94114
            130  Bailey the Bookmonger                             99915

2 rows selected.

SQL>

関連トピック

ハイブリッド・パーティション表の管理

親トピック: Oracle Databaseの場合の外部表の例

18.4 ORACLE_BIGDATAアクセス・ドライバを使用したパーティション化された外部表の作成

この項の例では、サブパーティション化された外部表を作成する方法を示しています。

次の例では、表sales_extended.parquetにアクセスできるSALES_EXTENDED_EXTという表と、オブジェクト・ストアtab_from_csv_ossにアクセスできる表t.datを作成します。

例18-4 ORACLE_BIGDATAアクセス・ドライバを使用した作成

CREATE TABLE "SALES_EXTENDED_EXT"
        ("PROD_ID" NUMBER(10,0),
     "CUST_ID" NUMBER(10,0),
     "TIME_ID" VARCHAR2(4000 BYTE),
     "CHANNEL_ID" NUMBER(10,0),
     "PROMO_ID" NUMBER(10,0),
     "QUANTITY_SOLD" NUMBER(10,0),
     "AMOUNT_SOLD" NUMBER(10,2),
     "GENDER" VARCHAR2(4000 BYTE),
     "CITY" VARCHAR2(4000 BYTE),
     "STATE_PROVINCE" VARCHAR2(4000 BYTE),
     "INCOME_LEVEL" VARCHAR2(4000 BYTE)
        )
        ORGANIZATION EXTERNAL
         ( TYPE ORACLE_BIGDATA
           DEFAULT DIRECTORY "DATA_PUMP_DIR"
           ACCESS PARAMETERS
           ( com.oracle.bigdata.credential.name=oss
             com.oracle.bigdata.fileformat=PARQUET
       )
           LOCATION
            ( 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/adwc4pm/b/parquetfiles/o//sales_extended.parquet'
            )
         )
        REJECT LIMIT UNLIMITED
       PARALLEL ;

CREATE TABLE tab_from_csv_oss
     (
      c0 number,
      c1 varchar2(20)
     )
     ORGANIZATION external
     (
      TYPE oracle_bigdata
      DEFAULT DIRECTORY data_pump_dir
      ACCESS PARAMETERS
      (
       com.oracle.bigdata.fileformat=csv
       com.oracle.bigdata.credential.name=oci_swift
      )
      location
      (
       'https://objectstorage.us-sanjose-1.oraclecloud.com/n/axffbtla8jep/b/misc/o/t.dat'
      )
     )REJECT LIMIT 1
     ;

親トピック: Oracle Databaseの場合の外部表の例

18.5 ORA_PARTITION_VALIDATIONファンクションを使用したパーティション化された外部表の検証

パーティション化された外部表の行が正しいパーティションかどうかを確認するには、ORA_PARTITION_VALIDATIONファンクションを使用します。

パーティション化された外部表を使用する場合、Oracle Databaseでは、正しいパーティション・キー定義を持つパーティションにデータ配置を強制できません。ORA_PARTITION_VALIDATIONを使用すると、データ配置エラーを修正できます。

例18-5 ORA_PARTITION_VALIDATIONのパーティションのテストへの使用

ORA_PARTITION_VALIDATIONファンクションを使用すると、誤ったパーティションに配置されている外部表パーティション行のリストを取得できます。この機能を示すために、この例では、誤った部門セットで作成されたパーティションを示し、次にORA_PARTITION_VALIDATIONファンクションを使用して、誤ったパーティションのデータを識別する例を示します。

create or replace directory def_dir1 as '/tmp';

REM create the exact same data in files locally
REM
set feedback 1
spool /tmp/xp1_15.txt
select '12#dept_12#xp1_15#' from dual;
spool off

spool /tmp/xp2_30.txt
select '29#dept_29#xp2_30#' from dual;
spool off

spool /tmp/xp2_wrong.txt
select '99#dept_99#xp2_wrong#' from dual;
spool off

drop table ept purge;
create table ept(deptno number,dname char(14),loc char(13))
organization external
( type oracle_loader
  default directory def_dir1
  access parameters(
    records delimited by newline
    fields terminated by '#')
)
reject limit unlimited
partition by range (deptno)
(
  partition ep1 values less than (10),
  partition ep2 values less than (20) location ('xp1_15.txt'),
  partition epwrong values less than (30) location ('xp2_wrong.txt')
)
;

select pt.*, ora_partition_validation(rowid) from pt;

親トピック: Oracle Databaseの場合の外部表の例

18.6 ファイル・パスにパーティション値を含めることによる外部表に対するSQL*Loaderの使用

オブジェクト・ストア内の多数のデータ・ファイルの管理を促進するには、ファイル・パスの一部としてフォルダ名を含めて外部表パーティション化を使用します。外部表の列で、行ごとにソース・ファイルのファイル名を返すこともできます。

Oracle Database 23ai以降、パーティション・キーとパーティション値の組合せ(たとえば、/state=CA)、またはパーティション値のみ(たとえば、/state/CA/)を使用する外部表パーティション化では、ファイル・パスにフォルダ名が含まれます。また、外部表の列で、行ごとにソース・ファイルのファイル名を返すことができます。

オブジェクト・ストア内のデータを指す外部表は、多数のファイルからなる可能性があります。これらのファイルは、複数のディレクトリにまたがって編成でき、複数のディレクトリ・ツリーにまたがってもかまいません。パーティションの値は、ディレクトリ名またはファイル名で指定できます。たとえば、様々な月や様々な状態のファイルを別々のディレクトリに配置できます。これを、オブジェクト・ストアでの、Hiveで生成された表の要件にできます。

親トピック: Oracle Databaseの場合の外部表の例

18.7 外部表でのLOBのロード

外部表は、単一のファイルから多数のレコードをロードし、各レコードが表の独自の行になるようにする場合に特に役立ちます。

LOBおよび外部表の概要
データベースで外部表を使用してデータの読取りおよび書込みを行う利点と、その作成方法について説明します。
ORACLE_LOADERアクセス・ドライバを使用した外部表からのLOBのロード
プライマリ・データ・ファイル、LOBfileまたはLOB場所指定子(LLS)からLOB列をロードできます。
ORACLE_DATAPUMPアクセス・ドライバを使用したLOBのロード
この例を使用して、LOB ORACLE_LOADERアクセス・ドライバをロードする方法を確認します。

親トピック: Oracle Databaseの場合の外部表の例

18.7.1 LOBおよび外部表の概要

データベースで外部表を使用してデータの読取りおよび書込みを行う利点と、その作成方法を学習します。

外部表を使用すると、外部ファイルの内容をOracle Databaseの表の行として扱うことができます。外部表を作成したら、SQL文を使用して外部表から行を読み取り、別の表に挿入できます。

これらの操作を実行するために、Oracle Databaseは次のいずれかのアクセス・ドライバを使用します。

ORACLE_LOADERアクセス・ドライバは、SQL Loaderと同様に、テキスト・ファイルおよびその他のファイル形式を読み取ります。
ORACLE_DATAPUMPアクセス・ドライバは、問合せによって返されたデータを格納するバイナリ・ファイルを作成します。また、ファイルから行をバイナリ形式で返します。

外部表を作成するときは、外部表の列およびデータ型を指定します。アクセス・ドライバは、データ・ファイルに列のリストを持ち、データ・ファイルのフィールドの内容を外部表の同じ名前の列にマップします。アクセス・ドライバは、データ・ソース内のフィールドを検索し、これらのフィールドを外部表内の対応する列の適切なデータ型に変換します。外部表を作成した後、INSERT AS SELECT文を使用してターゲット表をロードできます。

外部表を使用してデータをSQL Loaderと比較してロードする利点の1つは、外部表がデータをパラレルにロードできることです。これを行う最も簡単な方法は、PARALLEL句を外部表とターゲット表の両方のCREATE TABLEの一部として指定することです。

例18-6

この例では、外部表でロードできる表CANDIDATEを作成します。ロードされると、外部表CANDIDATE_XTが作成されます。次に、INSERT文を実行して表をロードします。INSERT文には、ダイレクト・ロードを使用して行を表CANDIDATESに挿入する+APPENDヒントが含まれています。PARALLELパラメータは、表にパラレルでアクセスできることをSQLに通知します。

PARALLELパラメータ設定では、CANDIDATE_XTから読み取るパラレル問合せプロセスを4つ(4)指定し、CANDIDATEに挿入するパラレル・プロセスを4つ指定できます。BASICFILEとして格納されるLOBは、パラレルでロードできないことに注意してください。SECUREFILE LOBSはパラレルでのみロードできます。変数additional-external-table-infoは、追加の外部表情報を挿入できる場所を示します。

CREATE TABLE CANDIDATES

  (candidate_id       NUMBER,

   first_name         VARCHAR2(15),

   last_name          VARCHAR2(20),

   resume             CLOB,

   picture            BLOB

  ) PARALLEL 4;


CREATE TABLE CANDIDATE_XT

  (candidate_id       NUMBER,

   first_name         VARCHAR2(15),

   last_name          VARCHAR2(20),

   resume             CLOB,

   picture            BLOB

  ) PARALLEL 4;

ORGANIZATION EXTERNAL additional-external-table-info PARALLEL 4; 

INSERT /*+APPEND*/ INTO CANDIDATE SELECT * FROM CANDIDATE_XT;

アクセス・ドライバによって作成された外部表のファイルの場所

ORACLE_LOADERおよびORACLE_DATAPUMPによって作成または読み取られたすべてのファイルは、ディレクトリ・オブジェクトが指すディレクトリにあります。DBAまたはCREATE DIRECTORY権限を持つユーザーは、新しいオブジェクトをファイル・システム上のパスにマップするディレクトリ・オブジェクトを作成できます。これらのユーザーは、作成されたディレクトリ・オブジェクトに対するREAD、WRITEまたはEXECUTE権限を他のユーザーに付与できます。ディレクトリ・オブジェクトに対するREAD権限を付与されたユーザーは、外部表を使用して、ディレクトリ・オブジェクトのディレクトリからファイルを読み取ることができます。同様に、ディレクトリ・オブジェクトに対するWRITE権限を持つユーザーは、外部表を使用して、ディレクトリ・オブジェクトのディレクトリにファイルを書き込むことができます。

例18-7 ディレクトリ・オブジェクトの作成

次の例では、ディレクトリ・オブジェクトを作成し、READおよびWRITEアクセス権をユーザーHRに付与する方法を示します。

create directory HR_DIR as /usr/hr/files/exttab;

grant read, write on directory HR_DIR to HR;

ノート:

Oracle Real Application Clusters(Oracle RAC)環境で外部表を使用する場合は、ディレクトリ・オブジェクトが指すディレクトリが、すべてのノードからアクセス可能なディレクトリにマップされていることを確認する必要があります。

親トピック: 外部表でのLOBのロード

18.7.2 ORACLE_LOADERアクセス・ドライバを使用した外部表からのLOBのロード

プライマリ・データ・ファイル、LOBfileまたはLOB場所指定子(LLS)からLOB列をロードできます。

プライマリ・データ・ファイルからのLOBのロード
この例を使用して、ORACLE_LOADERアクセス・ドライバを使用してプライマリ・データ・データタイプ・ファイルからLOB列をロードする方法を確認します。
LOBFILEファイルからのLOBのロード
この例を使用して、ORACLE_LOADERアクセス・ドライバを使用してLOBFILEデータ型ファイルからLOB列をロードする方法を確認します。
LOB場所指定子からのLOBのロード
この例を使用して、ORACLE_LOADERアクセス・ドライバを使用してLOB場所指定子からLOBをロードする方法を確認します。

親トピック: 外部表でのLOBのロード

18.7.2.1プライマリ・データ・ファイルからのLOBのロード

この例を使用して、ORACLE_LOADERアクセス・ドライバを使用してプライマリ・データ・データタイプのファイルからLOB列をロードする方法を確認します。

LOBデータがプライマリ・データ・ファイルにある場合、それはデータ・ファイルのレコード形式に定義された別のフィールドです。アクセス・ドライバでのフィールドの定義方法は関係ありません。絶対的位置を使用してフィールドを定義するか、CHAR、VARCHARまたはVARCHARCを使用できます。ORACLE_LOADERのデータ型は、SQLのデータ型とは異なることに注意してください。

ノート:

Oracle Database 18c以降のリリースでは、シンボリック・リンクは、ORACLE_LOADERアクセス・ドライバで使用されるディレクトリ・オブジェクト・パス名では使用できません。

例18-8 プライマリ・データ・ファイルからのLOBのロード

この例では、各レコードのCOMMENTSフィールドは最大10000バイトです。SELECTを使用して表INTERVIEW_XTからCOMMENT列を選択すると、COMMENTSフィールドのデータはキャラクタ・ラージ・オブジェクト(CLOB)に変換され、Oracle SQLエンジンに表示されます。

CREATE TABLE INTERVIEW_XT

(candidate_id         NUMBER,

 interviewer_id       NUMBER,

 comments             CLOB

) 

ORGANIZATION EXTERNAL 

(type ORACLE_LOADER 

 default directory hr_dir

 access parameters

 (records delimited by newline

  fields terminated by ‘|’

  (candidate_id    CHAR(10),

   employee_id     CHAR(10),

   comments        CHAR(10000))

  )

  location ('interviews.dat')

);

親トピック: ORACLE_LOADERアクセス・ドライバを使用した外部表からのLOBのロード

18.7.2.2 LOBFILEファイルからのLOBのロード

この例を使用して、ORACLE_LOADERアクセス・ドライバを使用してLOBFILEデータ型ファイルからLOB列をロードする方法を確認します。

プライマリ・データ・ファイルに次のいずれかの特性がある場合は、プライマリ・データ・ファイルからLOBを読み取るよりも、LOBファイルを使用することをお薦めします。

レコード・デリミタ。

LOBフィールドのデータには、データにレコード・デリミタを含めることはできません。プライマリ・データ・ファイルでは、NEWLINEなどのレコード・デリミタをデータに含められます。ただし、ORACLE_LOADERアクセス・ドライバは、次のレコードにアクセスするときに、次のレコード・デリミタを検索します。レコード・デリミタもデータの一部である場合、LOB列の正しいデータは読み取られません。
フィールド終了記号。

LOB列のデータにフィールド終了記号を含めることはできません。プライマリ・データ・ファイルでは、データに|などのフィールド終了記号を含められます。ただし、レコード・デリミタと同様に、フィールド終了記号がデータの一部である場合、ORACLE_LOADERはLOB列の正しいデータを読み取りません。
サイズ制限を超えるレコード・サイズ。

LOB列のデータは、レコードのサイズ制限内に収まる必要があります。ORACLE_LOADERアクセス・ドライバでは、レコードが読取りバッファのサイズを超えないようにする必要があります。デフォルト値は1MBですが、READSIZEパラメータで変更できます。
バイナリ・データ

プライマリ・ファイルからバイナリ・データを読み取るには、ファイルの作成に特別な注意が必要です。レコード・デリミタまたはフィールド・デリミタがBLOBのデータ内に出現しないことを保証できる場合を除き、VARレコード形式を使用し、バイナリ・フィールドにVARRAWまたはVARRAWCデータ型を使用する必要があります。このようなファイルは通常、プログラムで生成する必要があります。

プライマリ・データ・ファイルにこれらの特性がある場合は、LOBFILEデータ型を使用してLOB列をロードすることをお薦めします。

ノート:

Oracle Database 18c以降のリリースでは、シンボリック・リンクは、ORACLE_LOADERアクセス・ドライバで使用されるディレクトリ・オブジェクト・パス名では使用できません。

例18-9 プライマリ・データ・ファイルからのLOBのロード

各レコードのLOB列ごとに、ORACLE_LOADERアクセス・ドライバにディレクトリ・オブジェクトと、LOBの内容を含むファイルのファイル名が必要です。通常、LOB列のすべてのファイルは1つのディレクトリにあり、データファイル内の各レコードのファイル名はディレクトリ内にあります。たとえば、ユーザーHRとしてLOBファイル用に作成された次のオブジェクトがあるとします。

create directory HR_LOB_DIR as /usr/hr/files/exttab/lobfile;

grant read, write on directory HR_LOB_DIR to HR;

データが次のレコードで構成されているとします。

cristina_resume.pdf

cristina.jpg

arvind_resume.pdf

arvind.jpg

データ・ファイルは次のようになり、フィールド終了記号、カンマ・デリミタ、文字列およびバイナリ・データが使用されます。

4378,Cristina,Garcia,cristina_resume.pdf,cristina.jpg

673289,Arvind,Gupta,arvind_resume.pdf,arvind.jpg

このシナリオでは、外部表のLOBファイルは次のようになります。

CREATE TABLE CANDIDATE_XT

  (candidate_id       NUMBER,

   first_name         VARCHAR2(15),

   last_name          VARCHAR2(20),

   resume             CLOB,

   picture            BLOB

  )

ORGANIZATION EXTERNAL 

(type oracle_loader

 default directory hr_dir

 access parameters

 (fields terminated by ‘,’

  (candidate_id       char(10),

   first_name         char(15),

   last_name          char(20),

   resume_file        char(40),

   picture_file       char(40)

  )

  column transforms

  (

    resume from lobfile (constant 'HR_LOB_DIR': resume_file,

    picture from lobfile (constant 'HR_LOB_DIR': picture_file

  )

親トピック: ORACLE_LOADERアクセス・ドライバを使用した外部表からのLOBのロード

18.7.2.3 LOB場所指定子からのLOBのロード

この例を使用して、ORACLE_LOADERアクセス・ドライバを使用してLOB場所指定子からLOBをロードする方法を確認します。

LOB場所指定子(LLS)は、1つのファイルに複数のLOBのデータがある場合に使用されます。LLSを使用してLOB列をロードする場合、プライマリ・データ・ファイルのデータには、LOBデータを含むファイルの名前、LOBの開始位置のオフセットおよびLOBのバイト数が含まれます。

ノート:

Oracle Database 18c以降のリリースでは、シンボリック・リンクは、ORACLE_LOADERアクセス・ドライバで使用されるディレクトリ・オブジェクト・パス名では使用できません。

例18-10 LOB場所指定子を使用したデータのロード

次の例では、履歴書と画像を含むディレクトリHR_LOB_DIRがあるとします。このディレクトリでは、履歴書を1つのファイルに連結し、画像を別のファイルに連結しました。

resumes.dat
pictures.dat

データ・ファイルは次のようになります。

4378,Cristina,Garcia,resumes.dat.1.10928/,picture.dat.1.38679/

673289,Arvind,Gupta,resumes.dat.10929.8439,picture.dat.38680,45772/

このシナリオでは、外部表のLOBファイルは次のようになります。

CREATE TABLE CANDIDATE_XT

  (candidate_id       NUMBER,

   first_name         VARCHAR2(15),

   last_name          VARCHAR2(20),

   resume             CLOB,

   picture            BLOB

  )

ORGANIZATION EXTERNAL 

(type oracle_loader

 default directory hr_dir

 access parameters

 (fields terminated by ‘,’

  (candidate_id       char(10),

   first_name         char(15),

   last_name          char(20),

   resume_file        lls directory ‘HR_LOB_DIR’,

   picture_file       lls directory ‘HR_LOB_DIR’

  )

 )

 location ('candidates.dat')

);

関連トピック

LLS句

親トピック: ORACLE_LOADERアクセス・ドライバを使用した外部表からのLOBのロード

18.7.3 ORACLE_DATAPUMPアクセス・ドライバを使用したLOBのロード

この例を使用して、LOB ORACLE_LOADERアクセス・ドライバのロード方法を確認します。

ORACLE_DATAPUMPアクセス・ドライバでは、コマンドCREATE TABLE AS SELECTを使用してSELECT文からデータをアンロードできます。このコマンドは、SELECT文によって返されるすべての行のデータを含むバイナリ・ファイルを作成します。このファイルを作成したら、ターゲット・データベースにORACLE_DATAPUMP外部表を作成し、文INSERT INTO target_table SELECT * FROM external_tableを使用して表をロードできます。

ノート:

Oracle Database 18c以降のリリースでは、シンボリック・リンクは、ORACLE_DATAPUMPアクセス・ドライバで使用されるディレクトリ・オブジェクト・パス名では使用できません。

例18-11 CREATE TABLE AS SELECTを使用した外部表の作成

この例では、CREATE TABLE AS SELECTを使用して、データベース内の表からデータをアンロードします。hr_dirのディレクトリにcandidate.dmpという名前のファイルが作成されます。次に、外部表を作成し(別のデータベースまたは同じデータベース内の別のスキーマに存在できます)、INSERTを使用してターゲット表をロードします。ターゲット表が別のデータベースにある場合、ファイルcandidates.dmpをそのデータベースのHR_DIRのディレクトリにコピーする必要があります。

CREATE TABLE CANDIDATE_XT

  (candidate_id       NUMBER,

   first_name         VARCHAR2(15),

   last_name          VARCHAR2(20),

   resume             CLOB,

   picture            BLOB

  )

ORGANIZATION EXTERNAL 

(type oracle_datapump

 default directory hr_dir

 location ('candidates.dmp')

)

as select * from candidates;

次に、別のスキーマまたは別のデータベースで、前述の手順で作成したファイルを使用して外部表を作成します。このコマンドを別のデータベースで実行する場合は、そのデータベースのHR_DIRのディレクトリにファイルをコピーする必要があります。

CREATE TABLE CANDIDATE_XT

  (candidate_id       NUMBER,

   first_name         VARCHAR2(15),

   last_name          VARCHAR2(20),

   resume             CLOB,

   picture            BLOB

  )

ORGANIZATION EXTERNAL 

(type oracle_datapump

 default directory hr_dir

 location ('candidates.dmp')

);

INSERT INTO CANDIDATES SELECT * FROM CANDIDATE_XT;

親トピック: 外部表でのLOBのロード

18.8 外部表からのCSVファイルのロード

このトピックでは、様々な条件で外部表からCSVファイルをロードする方法の例を示します。

一部の例は、前の例に基づいて作成されています。

例18-12 アクセス・パラメータを使用しないCSVファイルからのデータのロード

この例には、次の条件があります。

表の列の順序は、データ・ファイルのフィールドの順序に一致する必要があります。
データ・ファイル内のレコードは、改行で終了する必要があります。
データ・ファイル内のレコードのフィールドはカンマで区切る必要があります(フィールド値が引用符で囲まれている場合は、フィールドから引用符を削除しません)。
フィールドの途中では改行文字を使用できません。

外部表のデータは次のとおりです。

events_all.csv
Winter Games,10-JAN-2010,10,
Hockey Tournament,18-MAR-2009,3,
Baseball Expo,28-APR-2009,2,
International Football Meeting,2-MAY-2009,14,
Track and Field Finale,12-MAY-2010,3,
Mid-summer Swim Meet,5-JUL-2010,4,
Rugby Kickoff,28-SEP-2009,6,

外部表の定義は次のとおりです。

SQL> CREATE TABLE EVENTS_XT_1
  2  (EVENT       varchar2(30),
  3   START_DATE  date,
  4   LENGTH      number)
  5  ORGANIZATION EXTERNAL
  6  (default directory def_dir1 location ('events_all.csv'));

Table created.

次に、外部表EVENTS_XT_1でのSELECT操作を示します。

SQL> select START_DATE, EVENT, LENGTH
  2    from EVENTS_XT_1
  3    order by START_DATE;

START_DAT EVENT                              LENGTH
--------- ------------------------------ ----------
18-MAR-09 Hockey Tournament                       3
28-APR-09 Baseball Expo                           2
02-MAY-09 International Football Meeting         14
28-SEP-09 Rugby Kickoff                           6
10-JAN-10 Winter Games                           10
12-MAY-10 Track and Field Finale                  3
05-JUL-10 Mid-summer Swim Meet                    4

7 rows selected.

SQL>

例18-13 データ・ファイルの日付フィールドの書式と一致しないセッションのデフォルトの日付マスク

この例は、セッションのデフォルトの日付マスクがデータ・ファイルの日付フィールドの書式と一致しないことを除いて前の例と同じです。次の例では、セッションの日付書式はDD-Mon-YYYYですが、データ・ファイルの日付書式はMM/DD/YYYYです。外部表定義に日付マスクがない場合、ORACLE_LOADERアクセス・ドライバはセッションの日付マスクを使用してデータ・ファイルの文字データを日付データ型に変換しようとします。または、外部表の日付列のロードに使用されるデータ・ファイルのすべてのフィールドに使用する日付マスクのアクセス・パラメータを指定します。

次に、外部表のデータ・ファイルの内容を示します。

events_all_date_fmt.csv
Winter Games,1/10/2010,10
Hockey Tournament,3/18/2009,3
Baseball Expo,4/28/2009,2
International Football Meeting,5/2/2009,14
Track and Field Finale,5/12/2009,3
Mid-summer Swim Meet,7/5/2010,4
Rugby Kickoff,9/28/2009,6

外部表の定義は次のとおりです。

SQL> CREATE TABLE EVENTS_XT_2
  2  (EVENT       varchar2(30),
  3   START_DATE  date,
  4   LENGTH      number)
  5  ORGANIZATION EXTERNAL
  6  (default directory def_dir1
  7   access parameters (fields date_format date mask "mm/dd/yyyy")
  8   location ('events_all_date_fmt.csv'));

Table created.

SQL>

次に、外部表EVENTS_XT_2でのSELECT操作を示します。

SQL> select START_DATE, EVENT, LENGTH
  2    from EVENTS_XT_2
  3    order by START_DATE;

START_DAT EVENT                              LENGTH
--------- ------------------------------ ----------
18-MAR-09 Hockey Tournament                       3
28-APR-09 Baseball Expo                           2
02-MAY-09 International Football Meeting         14
12-MAY-09 Track and Field Finale                  3
28-SEP-09 Rugby Kickoff                           6
10-JAN-10 Winter Games                           10
05-JUL-10 Mid-summer Swim Meet                    4

7 rows selected.

例18-14 データが2つのデータ・ファイルに分割されている場合

この例は、次のことを除き、この項の最初の例と同じです。

データが2つのデータ・ファイルに分割されています。
各データ・ファイルにフィールドの名前を含む行が格納されています。
データ・ファイルの一部のフィールドが引用符で囲まれています。

FIELD NAMES ALL FILESは、アクセス・ドライバに対し、各ファイルの最初の行にファイルのフィールドの名前を含む行が含まれることを示します。アクセス・ドライバはフィールドの名前を表の列の名前と照合します。したがって、ファイルのフィールドの順序が表の列の順序と同じである必要はありません。最初の行内のフィールド名が引用符で囲まれていない場合、アクセス・ドライバでは、表の列名と照合する前に、フィールド名を大文字に変更します。フィールド名が引用符で囲まれている場合、アクセス・ドライバでは、一致する名前を検索する前に、フィールド名を大文字に変更しません。

フィールドは引用符で囲まれているため、アクセス・パラメータを使用する場合は、CSV WITHOUT EMBEDDED RECORD TERMINATORS句が必要になります。この句では、次のことを指定しています。

データ・ファイルのフィールドはカンマで区切られています。
フィールドが二重引用符で囲まれている場合、アクセス・ドライバはフィールド値からそれらを削除します。
フィールド値に改行は埋め込まれていません(このオプションを使用すると、アクセス・ドライバは外部表でのSELECT操作のパフォーマンスが低下する可能性がある一部のチェックをスキップできます)。

2つのデータ・ファイルは次のとおりです。

events_1.csv

"EVENT","START DATE","LENGTH",
"Winter Games", "10-JAN-2010", "10"
"Hockey Tournament", "18-MAR-2009", "3"
"Baseball Expo", "28-APR-2009", "2"
"International Football Meeting", "2-MAY-2009", "14"

events_2.csv

Event,Start date,Length,
Track and Field Finale, 12-MAY-2009, 3
Mid-summer Swim Meet, 5-JUL-2010, 4
Rugby Kickoff, 28-SEP-2009, 6

外部表の定義は次のとおりです。

SQL> CREATE TABLE EVENTS_XT_3
  2  ("START DATE"  date,
  3   EVENT         varchar2(30),
  4   LENGTH        number)
  5  ORGANIZATION EXTERNAL
  6  (default directory def_dir1
  7   access parameters (records field names all files
  8                      fields csv without embedded record terminators)
  9   location ('events_1.csv', 'events_2.csv'));

Table created.

次に、EVENTS_XT_3外部表でのSELECT操作の結果を示します。

SQL> select "START DATE", EVENT, LENGTH
  2    from EVENTS_XT_3
  3    order by "START DATE";

START DAT EVENT                              LENGTH
--------- ------------------------------ ----------
18-MAR-09 Hockey Tournament                       3
28-APR-09 Baseball Expo                           2
02-MAY-09 International Football Meeting         14
12-MAY-09 Track and Field Finale                  3
28-SEP-09 Rugby Kickoff                           6
10-JAN-10 Winter Games                           10
05-JUL-10 Mid-summer Swim Meet                    4

7 rows selected.

例18-15 データが2つのファイルに分割され、最初のファイルにのみフィールド名の行がある場合

この例は、最初のファイルにのみフィールド名の行があることを除き、例3と同じです。2番目のファイルの最初の行には、実際のデータが含まれています。RECORDS句は「field names first file」に変更されます。

2つのデータ・ファイルは次のとおりです。

events_1.csv (same as for example 3)

"EVENT","START DATE","LENGTH",
"Winter Games", "10-JAN-2010", "10"
"Hockey Tournament", "18-MAR-2009", "3"
"Baseball Expo", "28-APR-2009", "2"
"International Football Meeting", "2-MAY-2009", "14"

events_2_no_header_row.csv

Track and Field Finale, 12-MAY-2009, 3
Mid-summer Swim Meet, 5-JUL-2010, 4
Rugby Kickoff, 28-SEP-2009, 6

外部表の定義は次のとおりです。

SQL> CREATE TABLE EVENTS_XT_4
  2  ("START DATE"  date,
  3   EVENT         varchar2(30),
  4   LENGTH        number)
  5  ORGANIZATION EXTERNAL
  6  (default directory def_dir1
  7   access parameters (records field names first file
  8                      fields csv without embedded record terminators)
  9   location ('events_1.csv', 'events_2_no_header_row.csv'));

Table created.

次に、EVENTS_XT_4外部表でのSELECT操作を示します。

SQL> select "START DATE", EVENT, LENGTH
  2    from EVENTS_XT_4
  3    order by "START DATE";

START DAT EVENT                              LENGTH
--------- ------------------------------ ----------
18-MAR-09 Hockey Tournament                       3
28-APR-09 Baseball Expo                           2
02-MAY-09 International Football Meeting         14
12-MAY-09 Track and Field Finale                  3
28-SEP-09 Rugby Kickoff                           6
10-JAN-10 Winter Games                           10
05-JUL-10 Mid-summer Swim Meet                    4

7 rows selected.

例18-16 ファイルのフィールドの順序が表の列の順序と一致する場合

この例には、次の条件があります。

ファイルのフィールドの順序は表の列の順序と一致します。
フィールドは改行で区切られ、オプションで二重引用符で囲まれています。
値に改行が埋め込まれたフィールドがあり、それらのフィールドは二重引用符で囲まれています。

データ・ファイルの内容は次のとおりです。

event_contacts_1.csv

Winter Games, 10-JAN-2010, Ana Davis,
Hockey Tournament, 18-MAR-2009, "Daniel Dube
Michel Gagnon",
Baseball Expo, 28-APR-2009, "Robert Brown"
Internation Football Meeting, 2-MAY-2009,"Pete Perez
Randall Barnes
Melissa Gray",

event_contacts_2.csv

Track and Field Finale, 12-MAY-2009, John Taylor,
Mid-summer Swim Meet, 5-JUL-2010, "Louise Stewart
Cindy Sanders"
Rugby Kickoff, 28-SEP-2009, "Don Nguyen
Ray Lavoie"

表定義は次のとおりです。CSV WITH EMBEDDED RECORD TERMINATORS句では、二重引用符で囲まれ、改行も埋め込まれているフィールドの処理方法をアクセス・ドライバに指示します。

SQL> CREATE TABLE EVENTS_CONTACTS_1
  2  (EVENT       varchar2(30),
  3   START_DATE  date,
  4   CONTACT     varchar2(120))
  5  ORGANIZATION EXTERNAL
  6  (default directory def_dir1
  7   access parameters (fields CSV with embedded record terminators)
  8   location ('event_contacts_1.csv', 'event_contacts_2.csv'));

Table created.

次に、EVENT_CONTACTS_1外部表でのSELECT操作の結果を示します。

SQL> column contact format a30 
SQL> select START_DATE, EVENT, CONTACT
  2    from EVENTS_CONTACTS_1
  3    order by START_DATE;

START_DAT EVENT                          CONTACT
--------- ------------------------------ ------------------------------
18-MAR-09 Hockey Tournament              Daniel Dube
                                         Michel Gagnon

28-APR-09 Baseball Expo                  Robert Brown
02-MAY-09 Internation Football Meeting   Pete Perez
                                         Randall Barnes
                                         Melissa Gray

12-MAY-09 Track and Field Finale         John Taylor
28-SEP-09 Rugby Kickoff                  Don Nguyen
                                         Ray Lavoie

10-JAN-10 Winter Games                   Ana Davis
05-JUL-10 Mid-summer Swim Meet           Louise Stewart
                                         Cindy Sanders

7 rows selected.

例18-17 データ・ファイルの一部のフィールドでアクセス・パラメータのデフォルト設定が使用されない場合

この例では、データ・ファイルのほとんどのフィールドでアクセス・パラメータのデフォルト設定が使用されるものの、少数のフィールドではデフォルト設定が使用されない場合に行う操作を示します。この例では、すべてのフィールドの設定を一覧表示するのではなく、デフォルトとは異なるフィールドの属性のみを設定する方法を示します。相違点は以下のとおりです。

2つの日付フィールドがあり、その1つではセッションの書式が使用されますが、registration_deadlineでは異なる書式が使用されます。
registration_deadlineでは、NULL値を示すためにNONEの値も使用されます。

データ・ファイルの内容は次のとおりです。

events_reg.csv

Winter Games,10-JAN-2010,10,12/1/2009,
Hockey Tournament,18-MAR-2009,3,3/11/2009,
Baseball Expo,28-APR-2009,2,NONE
International Football Meeting,2-MAY-2009,14,3/1/2009
Track and Field Finale,12-MAY-2010,3,5/10/010
Mid-summer Swim Meet,5-JUL-2010,4,6/20/2010
Rugby Kickoff,28-SEP-2009,6,NONE

表定義は次のとおりです。ALL FIELDS OVERRIDE句を使用すると、そのフィールドの情報を指定しながら、残りのフィールドでデフォルトを使用できます。残りのフィールドのデータ型はCHAR(255)で、フィールド・データはカンマで終了し、切捨てオプションはLDRTRIMになります。

SQL> CREATE TABLE EVENT_REGISTRATION_1
  2  (EVENT                 varchar2(30),
  3   START_DATE            date,
  4   LENGTH                number,
  5   REGISTRATION_DEADLINE date)
  6  ORGANIZATION EXTERNAL
  7  (default directory def_dir1
  8   access parameters
  9  (fields all fields override
 10     (REGISTRATION_DEADLINE CHAR (10) DATE_FORMAT DATE MASK "mm/dd/yyyy"
 11                           NULLIF REGISTRATION_DEADLINE = 'NONE'))
 12   location ('events_reg.csv'));

Table created.

次に、EVENT_REGISTRATION_1外部表でのSELECT操作の結果を示します。

SQL> select START_DATE, EVENT, LENGTH, REGISTRATION_DEADLINE
  2    from EVENT_REGISTRATION_1
  3    order by START_DATE;

START_DAT EVENT                              LENGTH REGISTRAT
--------- ------------------------------ ---------- ---------
18-MAR-09 Hockey Tournament                       3 11-MAR-09
28-APR-09 Baseball Expo                           2
02-MAY-09 International Football Meeting         14 01-MAR-09
28-SEP-09 Rugby Kickoff                           6
10-JAN-10 Winter Games                           10 01-DEC-09
12-MAY-10 Track and Field Finale                  3 10-MAY-10
05-JUL-10 Mid-summer Swim Meet                    4 20-JUN-10

7 rows selected.

親トピック: Oracle Databaseの場合の外部表の例

18.9 外部表でのベクトル・データ型の使用

ベクトル類似検索に外部表内のベクトル・データ型を使用する方法の例を参照してください。

外部表でのベクトル・データ型の理解
特に、大規模なデータセットの場合は、外部表はデータベース・ストアの外部にデータを格納するための効率的な方法である可能性があります。
Oracle Loaderドライバを使用した外部表の作成
この例では、ORACLE_LOADERドライバを使用して外部表のベクトル・ストアを作成する方法を確認できます。
ORACLE_DATAPUMPドライバを使用した外部表の作成
ORACLE_DATAPUMPを使用して外部表のベクトル・ストアを作成するには、SQL*Loaderを使用します。
インライン外部表の問合せ
この例では、ORACLE_BIGDATA型の外部表内のベクトルをベクトル検索の一部として問い合せています。
外部表を使用したセマンティック類似検索の実行
外部表をセマンティック類似検索のデータセットとして使用する方法を示すSQL計画例を参照してください

親トピック: Oracle Databaseの場合の外部表の例

18.9.1 外部表でのベクトル・データ型の理解

特に、大規模なデータセットの場合は、外部表はデータベース・ストアの外部にデータを格納するための効率的な方法である可能性があります。

Oracle Database 23ai (リリース更新23.7)以降では、外部表内のベクトル・データを使用できます。ベクトル埋込みでは、非常に大きなデータセットが必要になることがよくあります(特に、非構造化データを使用する場合)。外部表により、非構造化データをデータベースの外部に格納し、それを外部表を使用して処理できるようにすることができます。外部表の使用は、データをデータベース・ストアの外部に格納して、その外部表を類似検索に使用するためや、ベクトル索引を作成および保守するための効率的な方法である可能性があります。

次の場所にある外部データソースを使用できます:

ローカル・ディレクトリ
Oracle Cloud Infrastructure(OCI)オブジェクト・ストア
Microsoft Azure Blob Storage
Amazon Web Service (AWS) S3クラウド・オブジェクト・ストレージ
GitHubストレージ

次のドライバを使用して外部表からデータをロード、取得およびアンロードできます。

ORACLE_LOADERアクセス・ドライバ
Oracle Data Pump (ORACLE_DATAPUMP)アクセス・ドライバ
Oracle Big Data (ORACLE_BIGDATA)アクセス・ドライバ

親トピック: 外部表でのベクトル・データ型の使用

18.9.2 Oracle Loaderドライバを使用した外部表の作成

この例では、ORACLE_LOADERドライバを使用して外部表のベクトル・ストアを作成する方法を確認できます。

この例では、外部表名はext_table_3です。この表は、ORACLE_LOADERドライバを使用して作成されます。ここでは、レコードは改行で区切られ、フィールドは:で終了しています。

create table ext_table_3
(
    v1 vector,
    v2 vector
)
organization external
(
    type oracle_loader
    default directory dir1
    access parameters
    (
        records delimited by newline
        fields terminated by ':'
        missing field values are null
    )
    location ('tkexvect3.csv')
)
reject limit unlimited;

親トピック: 外部表でのベクトル・データ型の使用

18.9.3 ORACLE_DATAPUMPドライバを使用した外部表の作成

ORACLE_DATAPUMPを使用して外部表のベクトル・ストアを作成するには、SQL*Loaderを使用します。

次の手順を実行します。

SQL*Loaderを使用して外部表を作成します

例:

CREATE TABLE vector_ext_tab (
        country_code         VARCHAR2(5),
        country_name         VARCHAR2(50),
        country_language     VARCHAR2(50),
        country_vector       VECTOR(*,*)
     )
     ORGANIZATION EXTERNAL (
        TYPE ORACLE_LOADER
        DEFAULT DIRECTORY DIR1
        ACCESS PARAMETERS (
           RECORDS DELIMITED BY NEWLINE
           FIELDS TERMINATED BY ":"
           MISSING FIELD VALUES ARE NULL
           (
              country_code         CHAR(5),
              country_name         CHAR(50),
              country_language     CHAR(50),
              country_vector       CHAR(10000)
           )
        )
        LOCATION ('tklgvectorcountries.dat')
     )
     PARALLEL 5
     REJECT LIMIT UNLIMITED;

ダンプ・ファイル(dmp)を生成します。

例:

create table export_table
organization external
(
    type oracle_datapump
    default directory dir1
    location ('exp1.dmp')
)
as select * from vector_ext_tab;

親トピック: 外部表でのベクトル・データ型の使用

18.9.4 インライン外部表の問合せ

この例では、ORACLE_BIGDATA型の外部表内のベクトルをベクトル検索の一部として問い合せています。

select * from external (
    (
      COL1 vector,
      COL2 vector,
      COL3 vector,
      COL4 vector
    )
    TYPE ORACLE_BIGDATA
    DEFAULT DIRECTORY DEF_DIR1
    ACCESS PARAMETERS
    (
      com.oracle.bigdata.credential.name\=OCI_CRED
      com.oracle.bigdata.credential.schema\=PDB_ADMIN
      com.oracle.bigdata.fileformat=parquet
      com.oracle.bigdata.debug=true
    )
    location ( 'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/myvdataproject/BIGDATA_PARQUET/vector_data/basic_vector_data.parquet' )
    REJECT LIMIT UNLIMITED
  ) tkexobd_bd_vector_inline;

親トピック: 外部表でのベクトル・データ型の使用

18.9.5 外部表を使用したセマンティック類似検索の実行

外部表をセマンティック類似検索のデータセットとして使用する方法を示すSQL計画例を参照してください

次に、select idの実行計画の例を示します。ext_table_3から埋め込み、order by vector_distance('[1,1]', embedding, cosine)を使用して、目標精度90%でデータの先頭3行のみを返します。

SQL> select * from table(dbms_xplan.display('plan_table', null, 'advanced predicate'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1784440045
 
--------------------------------------------------------------------------------
---------------------
 
| Id  | Operation                     | Name        | Rows  | Bytes |TempSpc| Co
st (%CPU)| Time     |
 
--------------------------------------------------------------------------------
---------------------
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     3 | 48945 |       |  1
466K  (2)| 00:00:58 |
 
|*  1 |  COUNT STOPKEY                |             |       |       |       |
         |          |
 
|   2 |   VIEW                        |             |   102K|  1588M|       |  1
466K  (2)| 00:00:58 |
 
|*  3 |    SORT ORDER BY STOPKEY      |             |   102K|  1589M|   798M|  1
466K  (2)| 00:00:58 |
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
|   4 |     EXTERNAL TABLE ACCESS FULL| EXT_TABLE_3 |   102K|  1589M|       |
362   (7)| 00:00:01 |
 
--------------------------------------------------------------------------------
---------------------
 
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   1 - SEL$2
   2 - SEL$1 / "from$_subquery$_002"@"SEL$2"
   3 - SEL$1
   4 - SEL$1 / "EXT_TABLE_3"@"SEL$1"
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "EXT_TABLE_3"@"SEL$1")
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      NO_ACCESS(@"SEL$2" "from$_subquery$_002"@"SEL$2")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      OPT_PARAM('_fix_control' '6670551:0')
      OPT_PARAM('_optimizer_cost_model' 'fixed')
      DB_VERSION('26.1.0')
      OPTIMIZER_FEATURES_ENABLE('26.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM<=3)
   3 - filter(ROWNUM<=3)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "from$_subquery$_002"."ID"[NUMBER,22], "from$_subquery$_002"."EMBEDDING"[
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
VECTOR,32600]
 
   2 - "from$_subquery$_002"."ID"[NUMBER,22], "from$_subquery$_002"."EMBEDDING"[
VECTOR,32600]
 
   3 - (#keys=1) VECTOR_DISTANCE(VECTOR('[1,1]', *, *, * /*+  USEBLOBPCW_QVCGMD
*/ ),
 
       "EMBEDDING" /*+ LOB_BY_VALUE */ , COSINE)[BINARY_DOUBLE,8], "ID"[NUMBER,2
2], "EMBEDDING" /*+
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
       LOB_BY_VALUE */ [VECTOR,32600]
   4 - "ID"[NUMBER,22], "EMBEDDING" /*+ LOB_BY_VALUE */ [VECTOR,32600],
       VECTOR_DISTANCE(VECTOR('[1,1]', *, *, * /*+  USEBLOBPCW_QVCGMD */ ), "EMB
EDDING" /*+
 
       LOB_BY_VALUE */ , COSINE)[BINARY_DOUBLE,8]
 
Query Block Registry:
---------------------
 
  SEL$1 (PARSER) [FINAL]
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
  SEL$2 (PARSER) [FINAL]

+

親トピック: 外部表でのベクトル・データ型の使用

18 Oracle Databaseの場合の外部表の例

18.1 ORACLE_LOADERアクセス・ドライバを使用したパーティション化された外部表の作成

18.2 ORACLE_LOADERアクセス・ドライバを使用したパーティション化されたハイブリッド表の作成

18.3 ORACLE_DATAPUMPアクセス・ドライバを使用したパーティション化された外部表の作成

18.4 ORACLE_BIGDATAアクセス・ドライバを使用したパーティション化された外部表の作成

18.5 ORA_PARTITION_VALIDATIONファンクションを使用したパーティション化された外部表の検証

18.6 ファイル・パスにパーティション値を含めることによる外部表に対するSQL*Loaderの使用

18.7 外部表でのLOBのロード

18.7.1 LOBおよび外部表の概要

18.7.2 ORACLE_LOADERアクセス・ドライバを使用した外部表からのLOBのロード

18.7.2.1プライマリ・データ・ファイルからのLOBのロード

18.7.2.2 LOBFILEファイルからのLOBのロード

18.7.2.3 LOB場所指定子からのLOBのロード

18.7.3 ORACLE_DATAPUMPアクセス・ドライバを使用したLOBのロード

18.8 外部表からのCSVファイルのロード

18.9 外部表でのベクトル・データ型の使用

18.9.1 外部表でのベクトル・データ型の理解

18.9.2 Oracle Loaderドライバを使用した外部表の作成

18.9.3 ORACLE_DATAPUMPドライバを使用した外部表の作成

18.9.4 インライン外部表の問合せ

18.9.5 外部表を使用したセマンティック類似検索の実行