Behavior Differences Between SQL*Loader and External Tables

Oracle提供了两种使用external table的驱动：ORACLE_LOADER and ORACLE_DATAPUMP

Oracle Database provides two access drivers: ORACLE_LOADER and ORACLE_DATAPUMP. By providing the database with metadata describing an external table, the database is able to expose the data in the external table as if it were data residing in a regular database table.

An external table load creates an external table for data that is contained in an external data file. The load executes INSERT statements to insert the data from the data file into the target table.

相对于conventional path and direct path loads它的优点在于可使用并行及可对数据进行转化

Use external tables for the best load performance in the following situations:

1. You want to transform the data as it is being loaded into the database. An external table load allows modification of the data being loaded by using SQL functions and PL/SQL functions as part of the INSERT statement that is used to create the external table.
2. You want to use transparent parallel processing without having to split the external data first

1. Multiple Primary Input Data Files

If there are multiple primary input data files with SQL*Loader loads, then a bad file and a discard file are created for each input data file.

With external table loads, there is only one bad file and one discard file for all input data files. If parallel access drivers are used for the external table load, then each access driver has its own bad file and discard file.

1. Syntax and Data Types

以下external table不支持

1. Use of CONTINUEIF or CONCATENATE to combine multiple physical records into a single logical record.
2. Loading of the following SQL*Loader data types: GRAPHIC, GRAPHIC EXTERNAL, and VARGRAPHIC
3. Use of the following database column types: LONG, nested table, VARRAY, REF, primary key REF, and SID

1. Byte-Order Marks

With SQL*Loader, if a primary data file uses a Unicode character set (UTF8 or UTF16) and it also contains a byte-order mark (BOM), then the byte-order mark is written at the beginning of the corresponding bad and discard files.

With external table loads, the byte-order mark is not written at the beginning of the bad and discard files.

1. Default Character Sets, Date Masks, and Decimal Separator

For fields in a data file, the settings of NLS environment variables on the client determine the default character set, date mask, and decimal separator.

For fields in external tables, the database settings of the NLS parameters determine the default character set, date masks, and decimal separator.

1. Use of the Backslash Escape Character

In SQL*Loader, you can use the backslash (\) escape character to identify a single quotation mark as the enclosure character, as follows:

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

In external tables, the use of the backslash escape character within a string raises an error. The workaround is to use double quotation marks to identify a single quotation mark as the enclosure character, as follows:

TERMINATED BY ',' ENCLOSED BY "'"

示例：

create table t2 (col1 varchar2(20), col2 varchar2(20));

$ cat t2.ctl

LOAD DATA

INFILE  *

INTO TABLE t2

APPEND

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

( col1 char, col2 char)

BEGINDATA

Tom, cat

Lin, suhang

$ sqlldr scott/tiger control=t2.ctl external_table=GENERATE_ONLY

$ cat t2.log   --可以手动在sqlpus中执行

....略

CREATE TABLE statement for external table:

------------------------------------------------------------------------

CREATE TABLE "SYS_SQLLDR_X_EXT_T2"

(

  "COL1" VARCHAR2(20),

  "COL2" VARCHAR2(20)

)

ORGANIZATION external

(

  TYPE oracle_loader

  DEFAULT DIRECTORY MY_DIR

  ACCESS PARAMETERS

  (

    RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252

    BADFILE 'MY_DIR':'t2.bad'

    LOGFILE 't2.log_xt'

    READSIZE 1048576

    SKIP 7

    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM

    REJECT ROWS WITH ALL NULL FIELDS

    (

      "COL1" CHAR(255)

        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',

      "COL2" CHAR(255)

        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'

    )

  )

  location

  (

    't2.ctl'

  )

)REJECT LIMIT UNLIMITED

INSERT statements used to load internal tables:

------------------------------------------------------------------------

INSERT /*+ append */ INTO T2

(

  COL1,

  COL2

)

SELECT

  "COL1",

  "COL2"

FROM "SYS_SQLLDR_X_EXT_T2"

...

select count(*) from t2;  ==0

$ sqlldr scott/tiger control=t2.ctl external_table=EXECUTE

select * from t2;    --相关外部表会被drop

COL1                 COL2

-------------------- --------------------

Tom                  cat

Lin                  suhang