Oracle外部表进阶
用最新的武器装备自己作者：JonathanGenn
在Oracle9i中，历史悠久的SQL*Loader实用程序实现了许多提取、转换和装载的新特性。
如果想把大量数据快速载入Oracle数据库，则您首先能想到的恐怕便是SQL*Loader。到目前为止，这一想法在很大程序上仍是正确的。不过，Oracle9i数据库提供的几项有趣的新特性，可能（或许应该）改变您进行数据装载和转换的方式。这些新特性属于Oracle称为“提取、转换和装载”（Extraction、Transformation和Loading，ETL）类别，它们共同为您提供了一个功能强大的ETL工具箱。我感觉特别有趣的三个ETL特性是：外部表表函数MERGE语句
在本文中，我打算以一个典型的数据装载问题为例，向大家说明这些特性。假定有以下人口普查数据需要载入数据库，您按以下标准对这些数据进行了处理：用一行来存放每个城市／年的组合数据：
City1990201*Baraga12311285Ishpeming7200Munising27832539
使用Oracle8i，您需要使用至少两个单独的步骤来完成数据的装载与转换。图1向您展示的便是一种可能的操作方式。然而，利用Oracle9i的ETL特性，您可用一种更有趣的方式来完成这项任务--只需一个步骤，便能完成装载与转换。
图1:装载和转换
图1:一个多步骤、单线程的装载过程外部表
Oracle9i的新特性之一是外部表的概念。这是在数据库的数据字典中定义的一个表，但数据本身却存储在数据库的外部。例如，您可定义一个外部表，它的数据来自用SQL*Loader装载的文本文件。这样其实正好，因为您需要装载的人口普查数据就放在这样一个文本文件中。创建外部表之前，需要先创建一个Oracle目录对象，该Oracle目录对象指向您的文本文件所在的操作系统目录。CREATEDIRECTORYcensus_dataAS"/data/census";
现在，您可使用一种新形式的CREATETABLE语句，它看起来就像SQL语句和SQL*Loader控制文件的混合：CREATETABLEcity_populations_ext(city_nameVARCHAR(9),pop_1990NUMBER,pop_201*NUMBER)
ORGANIZATIONEXTERNAL(TYPEoracle_loader
DEFAULTDIRECTORYcensus_dataACCESSPARAMETERS(RECORDSFIXED20LOGFILEcensus_data:"city_populations.log"BADFILEcensus_data:"city_populations.bad"FIELDS
MISSINGFIELDVALUESARENULL(city_name(1:10)CHAR(9),pop_1990(11:15)INTEGEREXTERNAL(4),
POP_201*(16:20)INTEGEREXTERNAL(4)))
LOCATION("city_populations.dat"))
PARALLEL4REJECTLIMITUNLIMITED;
当创建一个外部表时，实际只创建了一些数据字典项。您可像对其他任何SQL表那样对外部表进行查询。现在，假定您有下面这张工作表：
CREATETABLEcity_populations(city_nameVARCHAR(9),census_yearNUMBER,populationNUMBER,
CONSTRAINTcity_populations_pkPRIMARYKEY(city_name,census_year));
有了这张表后，您可用下列INSERT...SELECTFROM语句以标准化格式从外部数据文件中提取人口普查数据，并将其插入工作表。
INSERTINTOcity_populations(city_name,
census_year,population)SELECTcity_name,1990,pop_1990FROMcity_populations_extWHEREpop_1990ISNOTNULLUNIONALL
SELECTcity_name,201*,pop_201*FROMcity_populations_extWHEREpop_201*ISNOTNULL;
创建外部表时，由于将并行度设为4，所以数据库将对文件进行划分，使其由4个并行运行的进程读取。并行处理是自动进行的，不需要由您采取额外的操作，这确实相当方便。要用SQL*Loader并行进行装载，您必须将输入文件人工分割为多个较小的文件。
图2:表函数示例
图2:表函数以一系列行作为输入，返回的是一个不同的行集合
外部表性能
用SQL*Loader进行原始数据装载和通过一个外部表进行相同的装载，两者的性能有何差异呢？这个问题实际上是当我听说Oracle新的外部表特性时所首先想到的，而且我相信，它也是大多数人关心的问题。我向Oracle公司的HermanBaer提出这一问题时，他的答复是假如将一个外部表作为数据源，那么一个INSERT/*+APPEND*/语句采用的是同SQL＊Loader相同的内部直接路径插入机制。
按我的设想，在比较外部表同SQL*Loader的性能时，需要关心的并不仅仅是完成原始数据装载所需花费的时间，应考察整个装载和转换过程的性能。使用SQL*Loader，如果在装载期间需要使用任何类型的SQL函数，那么必须采用常规。如您所知，常规路径装载的性能通常远不及直接路径装载的性能。采用外部表时，则不会在使用SQL函数时出现对性能的影响。同样，在某些情况下，使用外部表使您能避免创建一个中间工作表。如果正在对数据进行归纳，则可用外部表一次完成归纳与装载。但如果使用SQL*Loader，则必须将数据载入一个工作表，进行归纳，再将归纳结果插入到目标表。最后，从易用性的角度出发很重要的一点是，外部表使您能"透明地"并行访问外部数据文件。
不管怎样，在您考察外部表的性能，或在考察任何Oracle9i的新ETL（提取、转换和装载）特性性能时，一定要综合考察整个过程，而不是仅仅考察这个过程中的单独一个步骤。表函数
前面展示的INSERT语句是通过联合两条SELECT语句而实现的。这意味着外部数据文件被读了两遍--每条SELECT语句都要读一遍。但是，读两遍输入文件并不合人心意，特别是在文件非常大的情况下。幸运的是，用表函数可以解决这方面的问题，这是我在本文中将要介绍的第二个ETL特性。可将表函数想象成一个高度简化的转换引擎。如图2所示，表函数将一组行作为输入，然后返回一组不同的行作为输出。和传统函数不同的是，表函数可从SELECT语句的FROM子句中调用。
对于人口普查数据来说，您打算取得每一个输入行，并将其转换成两个输出行。每个输入行都拥有来自两个不同的人口普查年度的数据，但是，标准目标表要求每一年的数据占一行，所以表函数必须能将来自每个输入行的两次人口计数转换成两个单独的输出行。
创建表函数之前，您需要创建一些类型。表函数返回的总是一个记录集，所以在最开始的时候，请创建一个表类型，令其对应于目标数据表的定义。为此，首先创建一个对象类型来定义记录，然后根据那个对象类型创建一个表类型。
CREATETYPEcity_populations_rowASOBJECT(city_nameVARCHAR2(9),census_yearNUMBER,populationNUMBER);/
CREATETYPEcity_populations_tableASTABLEOFcity_populations_row;/
函数的输入是由对外部表city_populations_ext执行一条SELECT语句而返回的数据行，因此，您需要一个恰当的REFCURSOR类型。下面的语句将创建一个包，其中包含一个名为pop_cursor_type的REFCURSOR类型,它与city_populations_ext表的记录结构相匹配。这个包还定义了一个表函数，该表函数将这样一个游标作为输入参数。注意您首先必须创建表类型city_populations_table，以便可在表函数的RETURN子句中使用那种类型：
CREATEORREPLACEPACKAGEcensus_packageAS
TYPEpop_cursor_typeISREFCURSORRETURNcity_populations_ext%ROWTYPE;FUNCTIONcensus_transform(indataINpop_cursor_type)RETURNcity_populations_tablePARALLEL_ENABLE(PARTITIONindataBYANY)PIPELINED;END;/
函数中的PARALLEL_ENABLE子句使数据库可并行执行函数。PARTITIONindataBYANY子句指出输入行可被分割为任意数量的数据桶，然后便可对其进行并行处理。PIPELINED子句使函数能递增地返回结果集，同时其他输入数据仍能得到处理。在调用函数的查询执行期间，可设想数据行都“经过（flowingthrough）”此函数。在清单1中，在包主体（PACKAGEBODY）中定义的函数代码负责将每个输入行转换成要求的两个输出行。
我知道这些设置起来似乎比较复杂。但它最终带来的好处可以说明这些麻烦是完全值得的，特别是对那些需要经常重复的有相同规律的装载操作来说。通过启用并行DML，然后使用表函数，现在只需一次操作，即可实现人口普查数据的装载和转换：
ALTERSESSIONENABLEPARALLELDML;INSERT/*+APPENDPARALLEL(t,4)*/INTOcity_populationstSELECT*
FROMTABLE(census_package.census_transform(
CURSOR(SELECTcity_name,pop_1990,pop_201*
FROMcity_populations_ext)));
图3:并行装载和智能更新／插入
图3:一个单步、流水线的和并行的装载过程
人口普查数据将从外部表读取，采用文件内的并行机制，这一过程将被并行化处理。每个并行操作的输出结果都会送入单独的进程，这些进程也是并行运行的，可将每个输入行转换成您希望的两个输出行。由于表函数负责进行转换，所以只需对外部文件遍历一次即可。在此，还应提醒您注意的是不必用任何形式的工作表来暂存数据，数据以流水线的方式从外部数据文件提取，经过表函数的处理，然后直接传送给目标表。由于减少了数据复制的次数，所以有效地减少了对磁盘空间的占用。
MERGE语句
MERGE语句解决了长期存在的一个问题，那就是如何对以前装载过的数据进行重新装载。在Oracle9i问世之前，您需要编写过程代码来判断一行是否已经存在，然后相应地执行INSERT或UPDATE语句。而在Oracle9i中，只需使用MERGE语句，然后让数据库处理所有细节。
最后，让我们考虑一下和我们目前假设的情况有关的非常常见的数据装载问题。假定我们以前已将人口普查数据装载到city_population表中，而且当前的输入文件下包含了新数据和对已有数据的更新。在这种情况下，对于文件中作了更新的数据，我们希望更新city_population表中已存在的行；对于文件中的新数据，我们希望在city_population表中插入新行。这是一个极其常见的问题，通常通过执行用PL／SQL编写的过程逻辑来处理。例如，清单2中的PL/SQL代码每次可从city_population_ext中读取一行，然后根据情况执行UPDATE或INSERT语句。
但是，这样的处理方式会带来几方面的问题。首先，由于不再使用表函数，所以不能对装载过程的"转换"部分进行并行处理。另外，还不得不为每一条输入记录都执行多达两条DML语句。另外，我们不得不去关心最有可能的情况是UPDATE；还是INSERT。最后，由于要为这种类型的逻辑编制代码，所以对我们来说无疑是一个痛苦的过程。那么，有没有单独一条语句即可做完所有这些工作呢？在Oracle9i中，终于出现这样的语句，这便是MERGE（合并）语句。它可根据情况要么INSERT（插入）新行，要么UPDATE（更新）已存在的行。
编写一条MERGE语句时，必须指定下述项：
目标表的名称
一条SELECT语句，用作数据源一个条件，用于标识对现有数据进行更新的输入行UPDATE和INSERT子句
下述MERGE语句可从外部表将数据正确地合并到city_populations表中。其中，ON子句引用了目标表的主键列。数据库从源查询读一个数据行时，它会考察目标表中的每一行（本例通过主键索引），对其ON条件求值。如果在目标表中发现一行的ON条件为TRUE，那么数据库会对源行作更新。
MERGEINTOcity_populationsdest
USING(SELECT*FROMTABLE(census_package.census_transform(CURSOR(SELECTcity_name,pop_1990,pop_201*FROMcity_populations_ext))))srcON(dest.city_name=src.city_nameANDdest.census_year=src.census_year)WHENMATCHEDTHENUPDATESET
dest.population=src.populationWHENNOTMATCHEDTHEN
INSERT(city_name,census_year,population)
VALUES(src.city_name,src.census_year,src.population);
这太令人激动了！只需一个语句，便可从外部数据文件读取数据，通过表函数，对其进行转换以符合我们数据表的要求，然后在我们数据库表中相应地要么更新现有的行，要么插入新行。图3对这个过程进行了图示。另外，整个过程中不需要创建临时表，所有这些工作可按您的愿望进行任何程度的并行化处理。总结
合并数据是一个由来已久的问题，令人高兴的是，现在终于有了一个方法能够在数据库一级解决它，同时还能充分利用数据库的伸缩性。我预计，尽管表函数是为简化装载过程而设计的，但它将会还有许多其他用途。现在想起来，在我过去几年写的数份报告中，其实都可利用它把事情做得更好。外部表承诺可以消除外部和内部数据之间的界限，以便更容易地将数据从外部系统载入数据库。使用这些崭新的ETL特性，一个多步骤的、单线程的装载过程（如图1所示）可以转换成单步骤的、流水线的以及高度并行的装载过程（如图3所示）。
JonathanGennick(jonathan@gennick.com)是一名经验丰富的Oracle数据库管理员，也是一名获得Oracle认证的专家，非常喜欢探索新的Oracle技术。他是最近出版的新书《SQL*Loader:TheDefinitiveGuide》（O"Reilly&Associates,201*）的作者。
扩展阅读：Oracle之外部表
Oracle之外部表
有时候，数据库海量数据的存储，是一个令人头疼的问题。别的不说，光是频繁的执行insert（1000次/秒）都是一场恶梦。但是，如果将数据保存到文家里，而不是数据库中，数据序列化的开销就小得多了。但是，对于文件的各种复杂检索，又是一件相当麻烦的事。
幸好，Oracle有这样一种特性，它可以将某些特定格式的文件映射到数据库中，形成一个“表”，称为“外部表”。单用户更改文件内容时，外部表中的数据即随之改变。同时，用户又可以像检索普通表一样，以只读的方式对外部表进行检索。
我们假设有这样一个文件（DATA.TXT）：1|thisisastring2|这里是个字符串3|ABC
要把这样一个文件映射成外部表，有以下工作要做：首先，我们需要为Oracle创建一个Directory，
创建方式为，在数据库中执行，须用DBA用户创建，并给应用授权。createdirectoryEXT_TABLE_DIRas"/home/oracle/app/oracle/oradata/php/"
注意“/home/oracle/app/oracle/oradata/php/”是一个存在于Oracle数据库服务器本身上边的实际存在的文件夹；
然后，将DATA.TXT文件拷贝到上述文件夹下；最后，创建一个对应外部表，createtableEXT_TABLE_NAME(
COL_1NUMBER,
COL_2VARCHAR2(512))
organizationexternal(
typeoracle_loader
defaultdirectoryEXT_TABLE_DIR
accessparameters(fieldsterminatedby"|")location("DATA.TXT"))
rejectlimitunlimited;
注意蓝色部分，EXT_TABLE_NAME是要映射成的外部表名称，EXT_TABLE_DIR是第一步里我们创建的Oracle的Directory，“|”是文件里的分割符，DATA.TXT是文件名。
需要补充的是，最后有一句“rejectlimit
unlimited”，告诉Oracle这个外部表没有行数限制。否则，当文件中的数据量超过200万行时，在对表进行检索时，就会出现ORA-30653，“rejectlimitreached”错误。
Oracle9i的一项新特性就是External
Table，它就象通常的数据库表一样，拥有字段和数据类型约束，并且可以查询，但是表中的数据却不存储在数据库中，而是在与数据库相关联的普通外部文件里。当你查询
ExternalTable时，Oracle将解析该文件并返回符合条件的数据，就象该数据存储在数据库表中一样。
具体的定义可以参见《Oracle概念手册》，以下的几点需要注意：:外部表的描述:
>创建的语法类似于："CREATETABLE...ORGANIZATIONEXTERNAL">数据在数据库的外部组织，是操作系统文件。
>操作系统文件在数据库中的标志是通过一个逻辑目录来映射的。>数据是只读的。（外部表相当于一个只读的虚表）
>不可以在上面运行任何DML操作，不可以创建索引。>可以查询操作和连接。可以并行操作。例子:
假如有如下两个数据文件:1:数据文件的格式F1.TXT文件:13234,FIRSTS46464,TESTAF2.TEXT文件:13234,SECONDS46464,TEST
2:创建目录,并用DBA进行授权;
sql>createdirectorytest_diras"E:temp";
sql>grantread,writeondirectorytest_dirtousers;注意：创建完毕逻辑目录之后要把平面文件拷贝到该目录下，另外还要注意文件名字不要写错。
一定要给oracle用户对这个目录可读可写的权限，操作系统层面，如使用chmod-R777test_dir；
3:使用被授权的用户users创建外部表:createtabletest_table(ms_novarchar(20),tipvarchar(20),descsvarchar(20))
ORGANIZATIONEXTERNAL(
TYPEORACLE_LOADER
DEFAULTDIRECTORYtest_dirACCESSParameters(
RECORDSDELIMITEDBYNEWLINEbadfile"bad_dev.txt"LOGFILE"log_dev.txt"
FIELDSTERMINATEDBY","
MISSINGFIELDVALUESARENULL(ms_no,tip,descs))
LOCATION("F1.txt","F2.txt"));
表创建完成.当然也可以导入一个文件
4:进行SELECT操作看是否正确;SQL>select*fromtest_table结果如下:
MS_NOTIPDESCS
------------------------------------------------------------13234FIRSTS46464TESTA13234SECONDS46464TEST
:如何得到外部表的有关信息:
SQL>DESCDBA_EXTERNAL_TABLES;NameTypeNullable
-----------------------------------------OWNERVARCHAR2(30)
TABLE_NAMEVARCHAR2(30)TYPE_OWNERCHAR(3)YTYPE_NAMEVARCHAR2(30)
DEFAULT_DIRECTORY_OWNERCHAR(3)YDEFAULT_DIRECTORY_NAMEVARCHAR2(30)REJECT_LIMITVARCHAR2(40)YACCESS_TYPEVARCHAR2(7)Y
ACCESS_PARAMETERSVARCHAR2(4000)YSQL>SELECT
OWNER,TABLE_NAME,DEFAULT_DIRECTORY_NAME,ACCESS_PARAMETERSFRFROM
DBA_EXTERNAL_TABLES;可以得到外部表的相关信息;
:如何得到外部路径的信息:
SQL>descDBA_EXTERNAL_LOCATIONS;得到该表结构:
NameTypeNullable
-------------------------------------OWNERVARCHAR2(30)
TABLE_NAMEVARCHAR2(30)LOCATIONVARCHAR2(4000)YDIRECTORY_OWNERCHAR(3)Y
DIRECTORY_NAMEVARCHAR2(30)Y
SQL>select*fromDBA_EXTERNAL_LOCATIONS;得到具体信息
友情提示：本文中关于《Oracle外部表进阶》给出的范例仅供您参考拓展思维使用，Oracle外部表进阶：该篇文章建议您自主创作。
来源：网络整理 免责声明：本文仅限学习分享，如产生版权问题，请联系我们及时删除。
《Oracle外部表进阶》
由互联网用户整理提供,转载分享请保留原作者信息,谢谢!
http://m.bsmz.net/gongwen/747144.html
- 上一篇：静态注册监听和动态注册监听总结
- 下一篇：学好Oracle的六条总结