Oracle1213数据导入方法比较.docx
《Oracle1213数据导入方法比较.docx》由会员分享，可在线阅读，更多相关《Oracle1213数据导入方法比较.docx（9页珍藏版）》请在冰豆网上搜索。
Oracle1213数据导入方法比较
Oracle数据导入方法比较
每个数据库管理员都会面临数据导入的问题，这有可能发生在数据库的新老移植过程中，或者是在数据库崩溃后的恢复重建过程中，还有可能是在创建测试数据库的模拟环境过程中，总之作为一名合格的数据库管理员，你应该做好接受各种数据导入请求的技术储备，同时还要尽量满足人本能的对导入速度的苛求。
本文仅针对Oracle数据库所提供的加速数据导入的各种特性和技术进行探讨，其中的一些方法也可以转化应用于其他数据库。
以下七种数据导入方法哪个最适用需要针对具体情况具体分析，我也附带列举了影响导入速度的各种因素供斟酌。
为了比较各种数据导入方法的效果，我创建了示例表和数据集，并用各种方法导入示例数据集来计算总体导入时间和导入进程占用CPU时间，这里得出的时间仅供参考。
需要说明的是，建议你使用Oracle9i企业版数据库，当然你也可以尝试使用Oracle7.3以上的标准版数据库。
本文使用的机器配置为：
CPUIntelP4，内存256M，数据库Oracle9i企业版
示例表结构和数据集
为了演示和比较各种数据导入方法，我假定数据导入任务是将外部文件数据导入到Oracle数据库的CALLS表中，外部数据文件包含十万条呼叫中心记录，将近6MB的文件大小，具体的数据示例如下:
82302284384,2003-04-18:
13:
18:
58,5001,投诉,手机三包维修质量82302284385,2003-04-18:
13:
18:
59,3352,咨询,供水热线的号码82302284386,2003-04-18:
13:
19:
01,3142,建议,增设公交线路
接受导入数据的表名是CALLS，表结构如下：
NameNull?
TypeComment---------------------------------------------------CALL_IDNOTNULLNUMBERPrimarykeyCALL_DATENOTNULLDATENon-uniqueindexEMP_IDNOTNULLNUMBERCALL_TYPENOTNULLVARCHAR2（12）DETAILSVARCHAR2（25）
逐条数据插入INSERT
数据导入的最简单方法就是编写INSERT语句，将数据逐条插入数据库。
这种方法只适合导入少量数据，如SQL*Plus脚本创建某个表的种子数据。
该方法的最大缺点就是导入速度缓慢，占用了大量的CPU处理时间，不适合大批量数据的导入；而其主要优点就是导入构思简单又有修改完善的弹性，不需要多做其它的准备就可以使用。
如果你有很多时间没法打发，又想折磨一下数据库和CPU，那这种方法正适合你。
:
）
为了与其它方法做比较，现将十万条记录通过此方法导入到CALLS表中，总共消耗172秒，其中导入进程占用CPU时间为52秒。
逐条数据插入INSERT，表暂无索引
为什么上一种方法占用了较多的CPU处理时间，关键是CALLS表中已创建了索引，当一条数据插入到表中时，Oracle需要判别新数据与老数据在索引方面是否有冲突，同时要更新表中的所有索引，重复更新索引会消耗一定的时间。
因此提高导入速度的好办法就是在创建表时先不创建索引或者在导入数据之前删除所有索引，在外部文件数据逐条插入到表中后再统一创建表的索引。
这样导入速度会提高，同时创建的索引也很紧凑而有效，这一原则同样适用于位图索引（BitmapIndex）。
对于主要的和唯一的关键约束（keyconstraints），可以使之先暂时失效（disabling）或者删除约束来获得同样的效果，当然这些做法会对已经存在的表的外键约束产生相关的影响，在删除前需要通盘斟酌。
需要说明的是，这种方法在表中已存在很多数据的情况下不太合适。
例如表中已有九千万条数据，而此时需要追加插入一千万条数据，实际导入数据节省的时间将会被重新创建一亿条数据的索引所消耗殆尽，这是我们不希望得到的结果。
但是，如果要导入数据的表是空的或导入的数据量比已有的数据量要大得多，那么导入数据节省的时间将会少量用于重新创建索引，这时该方法才可以考虑使用。
加快索引创建是另一个需要考虑的问题。
为了减少索引创建中排序的工作时间，可以在当前会话中增加SORT_AREA_SIZE参数的大小，该参数允许当前会话在内存的索引创建过程中执行更多的排序操作。
同样还可以使用NOLOGGING关键字来减少因创建索引而生成的REDO日志量，NOLOGGING关键字会对数据库的恢复和Standby备用数据库产生明显的影响，所以在使用之前要仔细斟酌，到底是速度优先还是稳定优先。
运用这种方法，先删除CALLS表的主键和不唯一的索引，然后逐条导入数据，完成后重新创建索引（表在导入数据前是空的）。
该方法总共消耗130秒，包括重建索引的时间，其中导入进程占用CPU时间为35秒。
这种方法的优点是可以加快导入的速度并使索引更加紧凑有效；缺点是缺乏通用性，当你对表增加新的复杂的模式元素（索引、外键等）时你需要添加代码、修改导入执行程序。
另外针对7*24在线要求的数据库在线导入操作时，删除表的索引会对在线用户的查询有很大的性能影响，同时也要考虑，主要或唯一的关键约束条件的删除或失效可能会影响到引用它们的外键的使用。
批量插入，表暂无索引
在OracleV6中OCI编程接口加入了数组接口特性。
数组操作允许导入程序读取外部文件数据并解析后，向数据库提交SQL语句，批量插入SQL语句检索出的数据。
Oracle仅需要执行一次SQL语句，然后在内存中批量解析提供的数据。
批量导入操作比逐行插入重复操作更有效率，这是因为只需一次解析SQL语句，一些数据绑订操作以及程序与数据库之间来回的操作都显著减少，而且数据库对每一条数据的操作都是重复可知的，这给数据库提供了优化执行的可能。
其优点是数据导入的总体时间明显减少，特别是进程占用CPU的时间。
需要提醒的是，通过OCI接口确实可以执行数据批量导入操作，但是许多工具和脚本语言却不支持使用此功能。
如果要使用该方法，需要研究你所使用的开发工具是否支持OCI批量操作功能。
导入程序需要进行复杂的编码并可能存在错误的风险，缺乏一定的弹性。
运用上述方法，程序将外部数据提取到内存中的数组里，并执行批量插入操作（100行/次），保留了表的删除/重建索引操作，总的导入时间下降到14秒，而进程占用CPU的时间下降到7秒，可见实际导入数据所花费的时间显著下降了95%。
CREATETABLEASSELECT，使用Oracle9i的ExternalTable
Oracle9i的一项新特性就是ExternalTable，它就象通常的数据库表一样，拥有字段和数据类型约束，并且可以查询，但是表中的数据却不存储在数据库中，而是在与数据库相关联的普通外部文件里。
当你查询ExternalTable时，Oracle将解析该文件并返回符合条件的数据，就象该数据存储在数据库表中一样。
需要注意的是，你可以在查询语句中将ExternalTable与数据库中其他表进行连接（Join），但是不能给ExternalTable加上索引，并且不能插入/更新/删除数据，毕竟它不是真正的数据库表。
另外，如果与数据库相关联的外部文件被改变或者被删除，这会影响到ExternalTable返回查询结果，所以在变动前要先跟数据库打招呼。
这种方法为导入数据打开了新的一扇门。
你可以很容易的将外部文件与数据库相关联，并且在数据库中创建对应的ExternalTable，然后就可以立即查询数据，就象外部数据已经导入到数据库表中一样。
唯一的不足需要明确，数据并未真正导入到数据库中，当外部文件被删除或覆盖时，数据库将不能访问ExternalTable里的数据，而且索引没有被创建，访问数据速度将有所缓慢。
创建CALLS_EXTERNAL（ExternalTable表）如下，使之与外部数据文件关联：
CREATETABLEcalls_external（call_idNUMBER,call_dateDATE,emp_idNUMBER,call_typeVARCHAR2（12）,detailsVARCHAR2（25））ORGANIZATIONEXTERNAL（TYPEoracle_loaderDEFAULTDIRECTORYextract_files_dirACCESSPARAMETERS（RECORDSDELIMITEDBYNEWLINEFIELDSTERMINATEDBY','MISSINGFIELDVALUESARENULL（call_id,call_dateCHARDATE_FORMATDATEMASK"yyy-mm-dd:
hh24:
mi:
ss",emp_id,call_type,details））LOCATION（'calls.dat'））;
然后将ExternalTable与真正被使用的表CALLS关联同步，删除CALLS表并重建它：
CREATETABLEcalls（call_idNUMBERNOTNULL,call_dateDATENOTNULL,emp_idNUMBERNOTNULL,call_typeVARCHAR2（12）NOTNULL,detailsVARCHAR2（25））TABLESPACEtbs1NOLOGGINGASSELECTcall_id,call_date,emp_id,call_type,detailsFROMcalls_external;
因为CALLS表是真正的数据库表，可以创建索引来加快访问，表中的数据将被保留，即使外部数据文件被更新或被删除。
在建表语句中NOLOGGING关键字用于加快索引重建。
运用这种方法导入数据，总的导入时间为15秒，进程占用CPU的时间为8秒，这比前一种方法稍微慢些，但不能就此认为使用ExternalTable导入数据一定比OCI批量插入慢。
这种方法的优点是，未经进行大量的编写代码就取得了不错的结果，不象OCI批量插入存在编码错误风险，它还可以使用dbms_job包调度数据导入进程，实现数据导入的自动化。
其缺点是目标表必须先删除后重建，如果只需要导入增量数据时此方法就不合适了，另外用户在表的重建过程中访问数据时会遇到"tableorviewdoesnotexist"的错误，它仅适用于Oracle9i以上版本的数据库。
INSERTAppendasSELECT，使用Oracle9i的ExternalTable
上一种方法演示了如何创建与外部数据文件关联的数据库表，其表的数据是由外部数据文件映射过来。
缺点是数据库表需要被先删除再重建来保持与外部数据文件的一致和同步，对导入增量的数据而不需要删除已有数据的情况不合适。
针对这种需求，Oracle提供了INSERT语句外带APPEND提示来满足。
INSERT/*+APPEND*/INTOcalls（call_id,call_date,emp_id,call_type,details）SELECTcall_id,call_date,emp_id,call_type,detailsFROMcalls_external;
该语句读取引用外部数据文件的CALLS_EXTERNAL表中内容，并将之增加到表CALLS中。
Append提示告诉Oracle使用快速机制来插入数据，同时可以配合使用表的NOLOGGING关键字。
可以预见这种方法与前一方法消耗了相同的时间，毕竟它们是使用ExternalTable特性导入数据的不同阶段解决方法。
如果目标表不是空的，那将会消耗稍微长的时间（因为要重建更长的索引），而前一CREATETABLEasSELECT方法是整体创建索引。
SQL*Loader的强大功能
SQL*Loader是Oracle提供的导入实用程序，特别针对从外部文件导入大批量数据进入数据库表。
该工具已经有多年的历史，每一次版本升级都使其更加强大、灵活和快捷，但遗憾的是它的语法却是神秘而不直观，并且只能从命令行窗口处进行调用。
尽管它有不直观的缺点，但却是最快最有效的导入数据方法。
缺省情况下它使用"conventionalpath"常规选项来批量导入数据，其性能提高度并不明显。
我建议使用更快速的导入参数选项，在命令行添加"direct=true"选项调用"directpath"导入选项。
在"directpath"导入实现中，程序在数据库表的新数据块的highwatermark处直接写入导入数据，缩短了数据插入的处理时间，同时优化使用了非常有效的B+二叉树方法来更新表的索引。
运用这种方法，如果使用缺省的conventionalpath导入选项，总的导入时间是81秒，进程占用CPU时间大约是12秒，这包括了更新表的索引时间。
如果使用directpath导入选项，总的导入时间竟是9秒，进程占用CPU时间也仅仅是3秒，也包括了更新表的索引时间。
由此可见，尽管表中的索引在数据导入之前并没有被删除，使用SQL*Loader的directpath导入选项仍然是快速和有效的。
当然它也有缺点，就像NOLOGGING关键字一样该方法不生成REDO日志数据，导入进程出错后将无法恢复到先前状态；在数据导入过程中表的索引是不起作用的，用户此时访问该表时将出现迟缓，当然在数据导入的过程中最好不要让用户访问表。
分区交换（PartitionExchange）
以上讨论的数据导入方法都有一个限制，就是要求用户在导入数据完成之后才可以访问数据库表。
面对7×24不间断访问数据库来说，如果我们只是导入需要增加的数据时，这种限制将对用户的实时访问产生影响。
Oracle在这方面提供了表分区功能，它可以减少导入数据操作对用户实时访问数据的影响，操作模式就象使用可热插拔的硬盘一样，只不过这里的硬盘换成了分区（Partition）而已。
需要声明的是Partitioning分区功能只有在企业版数据库中才提供。
在一个被分区过的表中，呈现给用户的表是多个分区段（segments）的集合。
分区可以在需要时被添加，在维护时被卸载或删除，分区表可以和数据库中的表交换数据，只要它们的表结构和字段类型是一致的，交换后的分区表将拥有与之互动的表的数据。
需要注意的是，这种交换只是在Oracle数据库的数据字典层面上进行，并没有数据被实际移动，所以分区表交换是极其快速的。
为了创建实验环境，先假设CALLS表是个分区表，要创建一个空的分区PART_01012004，用来保存2004年1月1日的呼叫数据。
然后需要再创建一临时表为CALLS_TEMP，该表与CALLS表拥有相同的字段和数据类型。
我们使用先前介绍的导入方法将十万条数据导入到CALLS_TEMP表中，可以耐心等待数据完全导入到CALLS_TEMP表中，并且创建好索引和相关约束条件，所有这一切操作并不影响用户实时访问CALLS表，因为我们只对CALLS_TEMP临时表进行了操作。
一旦数据导入完成，CALLS_TEMP表就存有2004年1月1日的呼叫数据。
同时利用CALLS表中名为PART_01012004的空分区，使用如下语句执行分区交换：
ALTERTABLEcallsEXCHANGEPARTITIONpart_01012004WITHTABLEcalls_tempINCLUDINGINDEXESWITHOUTVALIDATION;
分区交换操作将非常快速地只更新CALLS表的数据字典，PART_01012004分区表即刻拥有CALLS_TEMP表的所有数据，而CALLS_TEMP表变为空表。
假定CALLS表使用局部索引而非全局索引，上述语句中的INCLUDINGINDEXES将保证分区交换包括索引的可用性，WITHOUTVALIDATION指明不检查交替表中数据的匹配，加快了交换的速度。
结论
以上探讨了Oracle数据库的多种数据导入方法，每种方法都有其优缺点和适用环境，能够满足你不同的导入需求，当然你需要在了解了这些方法后，在速度、简易性、灵活性、可恢复性和数据可用性之间寻求最佳导入方案。
为了对比各种方法的效果，我们创建了一个实例来展示各种方法的导入效率和效果，从中你可以选择最适合的方法用于今后的数据导入工作。
同时请记住，本文并未囊括所有的ORACLE数据导入技术（比如并行数据导入技术），这需要我们继续不懈的探索和尝试。
数据导入方法总体导入时间（秒）导入进程占用CPU时间（秒）
逐条数据插入INSERT17252
逐条数据插入INSERT，表暂无索引13035
批量插入，表暂无索引147
CreateAsSelect，使用Oracle9i的ExternalTable158
INSERTAppendasSELECT，使用Oracle9i的ExternalTable158
SQL*Loaderconventionalpath缺省导入选项8112
SQL*Loaderdirectpath导入选项93
数值函数：
abs（m）m的绝对值
mod（m,n）m被n除后的余数
power（m,n）m的n次方
round（m[,n]）m四舍五入至小数点后n位的值（n缺省为0）
trunc（m[,n]）m截断n位小数位的值（n缺省为0）
字符函数：
initcap（st）返回st将每个单词的首字母大写，所有其他字母小写
lower（st）返回st将每个单词的字母全部小写
upper（st）返回st将每个单词的字母全部大写
concat（st1,st2）返回st为st2接st1的末尾（可用操作符"||"）
lpad（st1,n[,st2]）返回右对齐的st,st为在st1的左边用st2填充直至长度为n,st2的缺省为空格
rpad（st1,n[,st2]）返回左对齐的st,st为在st1的右边用st2填充直至长度为n,st2的缺省为空格
ltrim（st[,set]）返回st,st为从左边删除set中字符直到第一个不是set中的字符。
缺省时，指的是空格
rtrim（st[,set]）返回st,st为从右边删除set中字符直到第一个不是set中的字符。
缺省时，指的是空格
replace（st,search_st[,replace_st]）将每次在st中出现的search_st用replace_st替换，返回一个st。
缺省时，删除search_st
substr（st,m[,n]）n=返回st串的子串，从m位置开始，取n个字符长。
缺省时，一直返回到st末端
length（st）数值，返回st中的字符数
instr（st1,st2[,m[,n]]）数值，返回st1从第m字符开始，st2第n次出现的位置，m及n的缺省值为1
例：
1.
selectinitcap（'THOMAS'）,initcap（'thomas'）fromtest;
initcainitca
------------
ThomasThomas
2.
selectconcat（'abc','def'）"first"fromtest;
first
-----
abcdef
3.
select'abc'||''||'def'"first"fromtest;
first
-----
abcdef
4.
selectlpad（name,10）,rpad（name,5,'*'）fromtest;
lpad（name,10）rpad（name,5,'*'）
----------------------------
mmxmmx**
abcdefabcde
5.
去掉地址字段末端的点及单词st和rd
selectrtrim（address,'.strd'）fromtest
6.
selectname,replace（name,'a','*'）fromtest;
namereplace（name,'a','*'）
-------------------------
greatgre*t
7.
selectsubstr（'archibaldbearisol',6,9）a,substr（'archibaldbearisol',11）bfromtest;
ab
--------------
baldbearbearisol
8.
selectname,instr（name,''）a,instr（name,'',1,2）bfromtest;
nameab
------------------------
lilei30
lil24
转换函数：
nvl（m,n）如果m值为null,返回n,否则返回m
to_char（m[,fmt]）m从一个数值转换为指定格式的字符串fmt缺省时，fmt值的宽度正好能容纳所有的有效数字
to_number（st[,fmt]）st从字符型数据转换成按指定格式的数值，缺省时数值格式串的大小正好为整个数
附：
to_char（）函数的格式：
---------------------------------
符号说明
---------------------------------
9每个9代表结果中的一位数字
0代表要显示的先导0
$美元符号打印在数的左边
L任意的当地货币符号
.打印十进制的小数点
打印代表千分位的逗号
---------------------------------
例：
1.
selectto_number（'123.45'）+to_number（'234.56'）formtest;
to_number（'123.45'）+to_number（'234.56'）
----------------------------------------
358.01
2.
selectto_char（987654321）fromtest;
to_char（987654321）
------------------
987654321
3.
selectto_char（123,'$9,999,999'）a,to_char（54321,'$9,999,999'）b,to_char（9874321,'$9,999,999'）cfromtest;
abc
----------------------------
$123$54,321$9,874,321
4.
s
升级会员 