利用外部表，Oracle 數(shù)據(jù)庫(kù)方便用戶在不使用 ETL 工具或者文件導(dǎo)入數(shù)據(jù)庫(kù)的情況下，實(shí)現(xiàn) Oracle 數(shù)據(jù)庫(kù)內(nèi)外數(shù)據(jù)的連通。外部表的概念在 Oracle 數(shù)據(jù)庫(kù)中已經(jīng)存在了很久，一個(gè)外部表代表數(shù)據(jù)庫(kù)中一個(gè)對(duì)文件或數(shù)據(jù)源的引用。一旦外部表被創(chuàng)建，您就可以在 Oracle 數(shù)據(jù)庫(kù)中查詢它，同時(shí)它顯得和一張數(shù)據(jù)庫(kù)內(nèi)部表很像。

我們可以看到 Oracle 數(shù)據(jù)庫(kù)提供非常強(qiáng)大的外部表功能，下面我們從各個(gè)方面來(lái)了解一下 Oracle 的外部表是如何進(jìn)行操作的。

1.創(chuàng)建外部表

假設(shè) ?_employee.txt? 的表有如下內(nèi)容：

100 John Smith
110 Jane Williams
120 Paul Smith

我們可以通過(guò)以下 SQL 語(yǔ)句來(lái)創(chuàng)建外部表 TMP_EMPLOYEE：

CREATE TABLE TMP_EMPLOYEE
(
EMP_NO INTEGER(4),
LAST_NAME VARCHAR2(15),
FIRST_NAME VARCHAR2(15)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY DIR_TEMP
ACCESS PARAMETERS 
(RECORDS DELIMITED BY NEWLINE 
BADFILE DIR_TEMP:badfile.bad 
LOGFILE DIR_TEMP:logfile.log 
FIELDS TERMINATED BY WHITESPACE 
MISSING FIELD VALUES ARE NULL
)
LOCATION 
('?_employee.txt?')
)
PARALLEL REJECT LIMIT UNLIMITED;

該語(yǔ)句有幾個(gè)重點(diǎn)說(shuō)明：

ORGANIZATION EXTERNAL

這部分內(nèi)容表示這是一個(gè)外部表。

(TYPE ORACLE_LOADER

這部分告訴 Oracle 該外部表的數(shù)據(jù)源是由 Oracle 裝載器 (Oracle Loader) 裝載的。

DEFAULT DIRECTORY DIR_TEMP

這里指定了讀取數(shù)據(jù)源的位置。這里的 DIR_TEMP 是 Oracle 的邏輯卷，請(qǐng)事先確認(rèn)該邏輯卷相應(yīng)的物理位置規(guī)劃。

ACCESS PARAMETERS 
(RECORDS DELIMITED BY NEWLINE 
BADFILE DIR_TEMP:badfile.bad 
LOGFILE DIR_TEMP:logfile.log 
FIELDS TERMINATED BY ',' 
MISSING FIELD VALUES ARE NULL
)

這塊是定義讀取數(shù)據(jù)源的相關(guān)參數(shù)，包括記錄的分隔符，字段間的分隔符等。我們可以根據(jù)實(shí)際情況調(diào)整。

LOCATION 
('?_employee.txt?')

這里就是外部表讀取的源文件。發(fā)現(xiàn)不用輸入資源的路徑，說(shuō)明是通過(guò) DIRECTORY 中定義的邏輯卷來(lái)定位源文件。

2.查詢外部表

查詢外部表與普通表查詢基本類似，下面是查詢外部表的示例代碼：

SELECT EMP_NO, LAST_NAME, FIRST_NAME
FROM TMP_EMPLOYEE
WHERE EMP_NO >100;

3.更新外部表

對(duì)外部表更新的主要方式是刪除和插入，示例代碼如下:

DELETE FROM TMP_EMPLOYEE  WHERE EMP_NO=100;

INSERT INTO TMP_EMPLOYEE (EMP_NO, LAST_NAME, FIRST_NAME)
VALUES(130, 'Johnson', 'Johnson');

但是有個(gè)特別要注意的地方，就是外部表不支持 UPDATE 語(yǔ)句。

4.優(yōu)化外部表

創(chuàng)建外部表后，程序可能需要用到，下面介紹一些優(yōu)化手段提高性能。

為了盡量提高數(shù)據(jù)訪問(wèn)的速度，可以讓整個(gè)工作被分成小塊。Oracle 采用的是查詢平行化，將數(shù)據(jù)分為多個(gè)數(shù)據(jù)塊（DOP）。其他的數(shù)據(jù)訪問(wèn)備份的是從外部表中掃描到的子數(shù)據(jù)塊。

我們可以對(duì)外部表做分區(qū)，將一個(gè)大的數(shù)據(jù)塊劃分成若干個(gè)小的數(shù)據(jù)塊。下面這個(gè)示例演示如何對(duì)一個(gè)問(wèn)卷做垂直（按列劃分）、水平（按行劃分）、混合分區(qū)：

CREATE TABLE SALES_JOURNAL_EXT
(
YEAR INTEGER EXTERNAL COMPRESS 0,
MONTH INTEGER EXTERNAL COMPRESS 0,
DAY INTEGER EXTERNAL COMPRESS 0, 
SALES_PERSON VARCHAR2(50) EXTERNAL COMPRESS 2, 
TOTAL_AMOUNTDOLLARS NUMBER(10,2) EXTERNAL COMPRESS 2
)
ORGANIZATION EXTERNAL 
(TYPE ORACLE_LOADER 
DEFAULT DIRECTORY SALES_DATA 
ACCESS PARAMETERS 
(RECORDS DELIMITED BY NEWLINE 
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
)
LOCATION 
(
'SALES_JOURNAL_EXT.01',
'SALES_JOURNAL_EXT.02',
'SALES_JOURNAL_EXT.03'
)
)
PARALLEL
(
DEGREE_ANY
INSTANCES_ANY
FORCE
)
REJECT LIMIT 0
NOMONITORING;
ALTER TABLE SALES_JOURNAL_EXT ADD PRIMARY KEY(YEAR,MONTH,DAY,SALES_PERSON);

通過(guò)以上代碼，我們可以看到如何做分區(qū)以及如何優(yōu)化其性能。

5.刪除外部表

當(dāng)我們已經(jīng)沒(méi)有使用外部表的需求時(shí)，需要?jiǎng)h除外部表以免造成內(nèi)存浪費(fèi)。

DROP TABLE TMP_EMPLOYEE;

注意：刪除外部表不會(huì)刪除數(shù)據(jù)源。

總結(jié)

外部表是 Oracle 數(shù)據(jù)庫(kù)非常有用的一個(gè)概念，人們可以通過(guò)它將 Oracle 數(shù)據(jù)庫(kù)內(nèi)部數(shù)據(jù)與外部數(shù)據(jù)進(jìn)行互聯(lián)。外部表使用非常方便，就像使用普通的 Oracle 內(nèi)部表那樣，在外部表中查詢數(shù)據(jù)、更新數(shù)據(jù)都非常方便。