外部表：不仅仅是一个ETL工具

Oracle9i为继承数据打开了方便之门，在近几年里，更是最大限度地增强了关系技术。 

许多公司想通过合并数据和自动化系统的方式，从而获取如Oracle在近期内所获得的超过10亿美元的节省。他们想把数据从完全不同的系统中迁移到Oracle数据库里，这样，这些数据便能享有Oracle数据库提供的有用性和可恢复性。但直到把数据完全转移到Oracle数据库之前，他们都需要通过一个中间步骤来访问数据。他们需要从平面文件访问数据。把数据加载到Oracle数据库的成本成为从遗留系统迁移到Oracle系统的最大障碍。 

进入Oracle9i和外部表。Oracle9i的外部表把关系模型扩展到了数据库之外，使你可以访问存储在平面文件中的原有数据或者其中的一些副本。 


不仅仅是为了ETL（提取、传送、加载）
数据仓库和商务智能的提取、传送、加载（ETL）过程中，开销最大的元素之一是把数据加载到临时表中使得它能被存在于数据库中的其他表所使用。外部表最初是被引入用来协助ETL过程的，而现在则有很多其他的用途。外部表能够把整台机器的数据装入数据库并改变数据操作规则。 
为了帮助你理解怎样使用外部表，我创建了几个例子。我需要做的第一件事是为这些例子建一个用以存取的数据平面文件。我用Spool命令从一个好朋友的EMP表获取了一些数据： 

SQL> spool emp4.dat 
SQL> select empno||','||ename ||','|| 
job||','||deptno 
from scott.emp; 
SQL> spool off 

7369,SMITH,CLERK,20 
7499,ALLEN,SALESMAN,30 
7521,WARD,SALESMAN,30 
7566,JONES,MANAGER,20 
7654,MARTIN,SALESMAN,30 

然后，我用SQL*Plus创建了一个目录，这样，Oracle便能知道从哪里找到我的外部表： 

SQL> create directory rich_new as 
'/u01/home/oracle/rich'; 
目录已创建。 

接下来，我创建了一个真实的表定义来引用外部平面文件。注意，如果数据不是储存在你的表定义的字段中，在你选择真实数据的时候，你将得到一条出错信息。清单1给出了一个创建表命令的例子。 


统计记录
一个数据库中的外部表能作为虚拟的只读表那样被访问，但是因为数据是在平面文件里，所以能在数据库外部通过使用外壳脚本来执行插入、更新和删除命令。虽然目前还不能创建索引，但外部表的速度还是快得惊人。 
为了统计记录条数，你可以使用Unix命令，也可以在数据库内部进行统计。用这两种方式，你都可以在平面文件而不是数据库中处理数据。下面的代码是一条用于在平面文件中统计记录的简单的UNIX命令。注意包含"？"的word count(wc) 命令，这条命令告诉操作系统统计行数。我建了一个有200,020行的文件。 

$ wc ?emp4.dat 
200020 200020 4400400 emp4.dat 
$ ls -l emp4.dat 
-rwxr-xr-x 1 oracle oinstall 4400400 Aug 9 06:31 emp4.dat 

我能使用SQL统计平面文件中的记录，因为现在我已经建好了一个外部表。下面的命令将在1秒钟之内返回结果。 

SQL> select count(*) from emp_external4; 
COUNT(*)
_________________ 

200020 
耗时： 00:00:00.63 

一旦你知道了能在一秒钟之内统计记录，你就能查找特定的信息，比如平面文件中的一个特殊的雇员编号（empno），现在可以通过一个外部表引用。你能用多快的速度扫描数百万条记录？在这个例子中，我建了第二个表并把它与第一个表结合起来。 

create table emp_external5 
(empno char(4), ename char(10), job char(9), deptno 
char(2)) 
organization external 
... 

location ('emp5.dat')); 

现在我把第一个结果中的20行与第二个表中的20行合并，用以连接那两个200,000行的表。结果是400行的结果集合，获得了4百万条行的联合访问能力。使用一般的硬件配置（我是用单处理器的机器），结果才花了不到3秒钟的时间。 

select a.empno, b.job, a.job 
from emp_external4 a, emp_external5 b 
where a.empno = b.empno 
and a.empno = 7900 
and b.empno = 7900; 
400 rows selected. 
Elapsed: 00:00:02.46 

这是上述连接的执行计划。 

执行计划 

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

0 SELECT STATEMENT Optimizer=CHOOSE 
1 0 MERGE JOIN 
2 1 SORT (JOIN) 
3 2 EXTERNAL TABLE ACCESS (FULL) OF 'EMP_EXTERNAL5'
4 1 SORT (JOIN) 
5 4 EXTERNAL TABLE ACCESS (FULL) OF 'EMP_EXTERNAL4' 

你也能通过外部表使用提示信息，并且把外部表加入到常规表中。你能并行化进行操作，而且，你甚至可以在任何时间从外部表中把数据直接插入到数据库里。其潜力无穷。 

Rich Niemiec 是TUSC(www.tusc.com) 的首席执行官，并且是Oracle国际用户团体（International Oracle Users Group，www.ioug.org) 的总裁。