前言
每个数据库管理员都会面临数据导入
问题
这有可能发生在数据库新老移植过程中或者是在数据库崩溃后恢复重建过程中还有可能是在创建测试数据库模拟环境过程中总的作为
名合格数据库管理员你应该做好接受各种数据导入请求技术储备同时还要尽量满足人本能对导入速度苛求
本文仅针对 Oracle 数据库所提供加速数据导入各种特性和技术进行探讨其中些思路方法也可以转化应用于其他数据库以下 7种数据导入思路方法哪个最适用需要针对具体情况具体分析我也附带列举了影响导入速度各种原因供斟酌为了比较各种数据导入思路方法效果我创建了举例表和数据集并用各种思路方法导入举例数据集来计算总体导入时间和导入进程占用 CPU 时间这里得出时间仅供参考需要介绍说明是建议你使用 Oracle 9i 企业版数据库当然你也可以尝试使用 Oracle 7.3 以上标准版数据库本文使用机器配置为:CPU Intel P4内存 256M数据库 Oracle 9i 企业版举例表结构和数据集
为了演示和比较各种数据导入思路方法我假定数据导入任务是将外部文件数据导入到 Oracle 数据库CALLS表中外部数据文件包含十万条呼叫中心记录将近 6MB 文件大小具体数据举例如下:82302284384,2003-04-18:13:18:58,5001,投诉,手机 3包维修质量82302284385,2003-04-18:13:18:59,3352,咨询,供水热线接受导入数据
表名是 CALLS表结构如下:Name Null? Type Comment ------------ --------- ------------- ----------------- CALL_ID NOT NULL NUMBER Primary key CALL_DATE NOT NULL DATE Non-unique index EMP_ID NOT NULL NUMBER CALL_TYPE NOT NULL VARCHAR2(12) DETAILS VARCHAR2(25)
逐条数据插入INSERT
数据导入最简单思路方法就是编写 INSERT 语句将数据逐条插入数据库这种思路方法只适合导入少量数据如 SQL*Plus 脚本创建某个表种子数据该思路方法最大缺点就是导入速度缓慢占用了大量 CPU 处理时间不适合大批量数据导入；而其主要优点就是导入构思简单又有修改完善弹性不需要多做其它准备就可以使用如果你有很多时间没法打发又想折磨下数据库和 CPU那这种思路方法正适合你:)为了和其它思路方法做比较
现将十万条记录通过此思路方法导入到 CALLS 表中总共消耗 172 秒其中导入进程占用 CPU 时间为 52 秒逐条数据插入 INSERT表暂无索引
为什么上种思路方法占用了较多 CPU 处理时间关键是 CALLS 表中已创建了索引当条数据插入到表中时Oracle 需要判别新数据和老数据在索引方面是否有冲突同时要更新表中所有索引重复更新索引会消耗定时间因此提高导入速度好办法就是在创建表时先不创建索引或者在导入数据的前删除所有索引在外部文件数据逐条插入到表中后再统创建表索引这样导入速度会提高同时创建索引也很紧凑而有效这原则同样适用于位图索引(Bitmap Index)对于主要和唯关键约束(key constra
s)可以使的先暂时失效(disabling)或者删除约束来获得同样效果当然这些做法会对已经存在表外键约束产生相关影响在删除前需要通盘斟酌需要介绍说明
是这种思路方法在表中已存在很多数据情况下不太合适例如表中已有 9千万条数据而此时需要追加插入千万条数据实际导入数据节省时间将会被重新创建亿条数据索引所消耗殆尽这是我们不希望得到结果但是如果要导入数据表是空或导入数据量比已有数据量要大得多那么导入数据节省时间将会少量用于重新创建索引这时该思路方法才可以考虑使用加快索引创建是另
个需要考虑问题为了减少索引创建中排序工作时间可以在当前会话中增加 SORT_AREA_SIZE 参数大小该参数允许当前会话在内存索引创建过程中执行更多排序操作同样还可以使用 NOLOGGING 关键字来减少因创建索引而生成 REDO 日志量NOLOGGING 关键字会对数据库恢复和 Standby 备用数据库产生明显影响所以在使用的前要仔细斟酌到底是速度优先还是稳定优先运用这种思路方法
先删除 CALLS 表主键和不唯索引然后逐条导入数据完成后重新创建索引( 表在导入数据前是空)该思路方法总共消耗 130 秒包括重建索引时间其中导入进程占用 CPU 时间为 35秒这种思路方法
优点是可以加快导入速度并使索引更加紧凑有效；缺点是缺乏通用性当你对表增加新复杂模式元素(索引、外键等)时你需要添加代码、修改导入执行
另外针对 7*24 在线要求数据库在线导入操作时删除表索引会对在线用户查询有很大性能影响同时也要考虑主要或唯关键约束条件删除或失效可能会影响到引用它们外键使用批量插入表暂无索引
在Oracle V6 中 OCI 编程接口加入了
接口特性操作允许导入读取外部文件数据并解析后向数据库提交SQL语句批量插入 SQL 语句检索出数据Oracle 仅需要执行次 SQL 语句然后在内存中批量解析提供数据批量导入操作比逐行插入重复操作更有效率这是
只需次解析 SQL 语句些数据绑订操作以及和数据库的间来回操作都显著减少而且数据库对每条数据操作都是重复可知这给数据库提供了优化执行可能其优点是数据导入总体时间明显减少特别是进程占用 CPU 时间
需要提醒
是通过 OCI 接口确实可以执行数据批量导入操作但是许多工具和脚本语言却不支持使用此功能如果要使用该思路方法需要研究你所使用开发工具是否支持 OCI 批量操作功能导入需要进行复杂编码并可能存在
风险缺乏定弹性运用上述思路方法
将外部数据提取到内存中里并执行批量插入操作(100行/次)保留了表删除/重建索引操作总导入时间下降到 14 秒而进程占用 CPU 时间下降到7秒可见实际导入数据所花费时间显著下降了 95%CREATE TABLE AS SELECT使用Oracle9iExternal Table
Oracle 9i 项新特性就是 External Table它就象通常数据库表样拥有字段和数据类型约束并且可以查询但是表中数据却不存储在数据库中而是在和数据库相关联普通外部文件里当你查询 External Table 时Oracle 将解析该文件并返回符合条件数据就象该数据存储在数据库表中样需要注意
是你可以在查询语句中将 External Table 和数据库中其他表进行连接(Join)但是不能给 External Table 加上索引并且不能插入/更新/删除数据毕竟它不是真正数据库表另外如果和数据库相关联外部文件被改变或者被删除这会影响到 External Table 返回查询结果所以在变动前要先跟数据库打招呼 这种思路方法为导入数据打开了新
扇门你可以很容易将外部文件和数据库相关联并且在数据库中创建对应 External Table然后就可以立即查询数据就象外部数据已经导入到数据库表中样唯不足需要明确数据并未真正导入到数据库中当外部文件被删除或覆盖时数据库将不能访问 External Table 里数据而且索引没有被创建访问数据速度将有所缓慢创建 CALLS_EXTERNAL(External Table表)如下使的和外部数据文件关联:CREATE TABLE calls_external (call_id NUMBER, call_date DATE, emp_id NUMBER, call_type VARCHAR2(12), details VARCHAR2(25)) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY extract_files_dir ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY \',\' MISSING FIELD VALUES ARE NULL ( call_id, call_date CHAR DATE_FORMAT DATE MASK "yyyy-mm-dd:hh24:mi:ss", emp_id, call_type, details ) ) LOCATION (\'calls.dat\') );
然后将 External Table 和真正被使用
表 CALLS 关联同步删除 CALLS 表并重建它:CREATE TABLE calls ( call_id NUMBER NOT NULL, call_date DATE NOT NULL, emp_id NUMBER NOT NULL, call_type VARCHAR2(12) NOT NULL, details VARCHAR2(25) ) TABLESPACE tbs1 NOLOGGING AS SELECT call_id, call_date, emp_id, call_type, details FROM calls_external;
CALLS 表是真正数据库表可以创建索引来加快访问表中数据将被保留即使外部数据文件被更新或被删除在建表语句中NOLOGGING关键字用于加快索引重建运用这种思路方法导入数据
总导入时间为 15 秒进程占用 CPU 时间为8秒这比前种思路方法稍微慢些但不能就此认为使用 External Table 导入数据定比 OCI 批量插入慢这种思路方法
优点是未经进行大量编写代码就取得了不错结果不象 OCI 批量插入存在编码风险它还可以使用 dbms_job 包调度数据导入进程实现数据导入自动化其缺点是目标表必须先删除后重建如果只需要导入增量数据时此思路方法就不合适了另外用户在表重建过程中访问数据时会遇到 "table or view does not exist" 它仅适用于 Oracle 9i 以上版本数据库INSERT Append as SELECT使用 Oracle9i External Table
上种思路方法演示了如何创建和外部数据文件关联数据库表其表数据是由外部数据文件映射过来缺点是数据库表需要被先删除再重建来保持和外部数据文件致和同步对导入增量数据而不需要删除已有数据情况不合适针对这种需求Oracle 提供了 INSERT 语句外带 APPEND 提示来满足INSERT /*+ APPEND */ INTO calls (call_id, call_date, emp_id, call_type, details) SELECT call_id, call_date, emp_id, call_type, details FROM calls_external;
该语句读取引用外部数据文件
CALLS_EXTERNAL 表中内容并将的增加到表 CALLS 中Append 提示告诉 Oracle 使用快速机制来插入数据同时可以配合使用表 NOLOGGING 关键字可以预见这种思路方法和前
思路方法消耗了相同时间毕竟它们是使用 External Table 特性导入数据区别阶段解决思路方法如果目标表不是空那将会消耗稍微长时间(要重建更长索引)而前 CREATE TABLE as SELECT 思路方法是整体创建索引SQL*Loader强大功能
SQL*Loader 是 Oracle 提供导入实用特别针对从外部文件导入大批量数据进入数据库表该工具已经有多年历史每次版本升级都使其更加强大、灵活和快捷但遗憾是它语法却是神秘而不直观并且只能从命令行窗口处进行
尽管它有不直观
缺点但却是最快最有效导入数据思路方法缺省情况下它使用 "conventional path" 常规选项来批量导入数据其性能提高度并不明显我建议使用更快速导入参数选项在命令行添加"direct=true" 选项 "direct path" 导入选项在 "direct path" 导入实现中在数据库表新数据块 high water mark 处直接写入导入数据缩短了数据插入处理时间同时优化使用了非常有效B+ 2叉树思路方法来更新表索引 运用这种思路方法
如果使用缺省 conventional path 导入选项总导入时间是 81 秒进程占用 CPU 时间大约是 12 秒这包括了更新表索引时间如果使用 direct path 导入选项总导入时间竟是 9 秒进程占用 CPU 时间也仅仅是 3 秒也包括了更新表索引时间由此可见
尽管表中索引在数据导入的前并没有被删除使用SQL*Loaderdirect path 导入选项仍然是快速和有效当然它也有缺点就像NOLOGGING关键字样该思路方法不生成REDO日志数据导入进程出错后将无法恢复到先前状态；在数据导入过程中表索引是不起作用用户此时访问该表时将出现迟缓当然在数据导入过程中最好不要让用户访问表分区交换 (Partition Exchange)
以上讨论数据导入思路方法都有个限制就是要求用户在导入数据完成的后才可以访问数据库表面对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空分区使用如下语句执行分区交换: ALTER TABLE calls EXCHANGE PARTITION part_01012004 WITH TABLE calls_temp INCLUDING INDEXES WITHOUT VALIDATION;分区交换操作将非常快速地只更新CALLS表
数据字典PART_01012004分区表即刻拥有CALLS_TEMP表所有数据而CALLS_TEMP表变为空表假定CALLS表使用局部索引而非全局索引上述语句中INCLUDING INDEXES将保证分区交换包括索引可用性WITHOUT VALIDATION 指明不检查交替表中数据匹配加快了交换速度结论
以上探讨了Oracle数据库多种数据导入思路方法每种思路方法都有其优缺点和适用环境能够满足你区别导入需求当然你需要在了解了这些思路方法后在速度、简易性、灵活性、可恢复性和数据可用性的间寻求最佳导入方案为了对比各种思路方法
效果我们创建了个例子来展示各种思路方法导入效率和效果从中你可以选择最适合思路方法用于今后数据导入工作同时请记住本文并未囊括所有ORACLE数据导入技术(比如并行数据导入技术)这需要我们继续不懈探索和尝试 数据导入思路方法 总体导入时间(秒) 导入进程占用CPU时间(秒)
逐条数据插入INSERT 172 52
逐条数据插入INSERT
表暂无索引 130 35 批量插入
表暂无索引 14 7 Create As Select
使用Oracle9iExternal Table 15 8 INSERT Append as SELECT
使用Oracle9iExternal Table 15 8 SQL*Loader conventional path 缺省导入选项 81 12
SQL*Loader direct path 导入选项 9 3
最新评论