网站app公众号先做哪个比较好,建设眼镜网站风格,高端网站开发哪家好,手机软件网站直接路径插入#xff08;Direct Path Insert#xff09;是Oracle一种数据加载提速技术#xff0c;可以在使用insert语句或SQL*Loader工具大批量加载数据时使用。直接路径插入处理策略与普通insert语句完全不同#xff0c;Oracle会通过牺牲空间#xff0c;安全性#xff0…直接路径插入Direct Path Insert是Oracle一种数据加载提速技术可以在使用insert语句或SQL*Loader工具大批量加载数据时使用。直接路径插入处理策略与普通insert语句完全不同Oracle会通过牺牲空间安全性并发性能来换取加载速度。
一、Direct-path Insert简介
普通insert语句叫做传统插入Conventional Insert数据在插入过程中会先缓存在buffer cache中写入磁盘时会检查并重用数据块中的可重用空间记录redo日志维护完整性约束等这些维护操作都是性能开销而直接路径插入会忽略这些维护操作换取插入性能的提升。
在海量数据加载场景特别是向新表大批量加载数据时加载数据存在原始备份、新表没有可重用空间、并发访问很低。我们的第一需求可能是加载速度。针对此类场景Oracle提供了一种性能更高的数据加载方式“直接路径插入”Direct-Path Insert。
传统插入与直接路径插入主要有以下5点区别
传统插入会经过buffer cache缓存后再写数据文件而直接路径插入会直接写数据文件这也是Direct-Path Insert名称的由来。传统插入会重用数据块中的空闲空间即新旧数据混在一起。而Direct-Path Insert会直接在高水位线High-Water Mark, HWM之上追加写数据即只在新的数据块中写数据旧数据块中即使有空间也不会重用更多的空间消耗传统插入会维护引用完整性约束Direct-Path Insert不会维护完整性约束必须删除或禁用引用完整性约束传统插入必须生成redo日志Direct-Path Insert可以选择关闭redo日志无法进行Media Recovery传统插入不会影响表上其他DML操作而Direct-Path Insert会获取表级的X锁因此表上的insert, delete, update都会被阻塞无法并发
二、Direct-Path Insert应用场景
Direct-Path Insert可以在下列场景中使用
使用insert into … values … 语句时通过hint指示Oracle使用Direct-Path Insert使用insert into … as select … 语句时通过hint指示Oracle使用Direct-Path Insert使用并行执行Oracle会自动使用Direct-Path Insert使用SQL*Loader工具向加载数据时指定使用Direct-Path Insert
2.1 insert into … values … 语句使用Direct-Path Insert
少量的insert into … values …语句通常没必要使用直接路径插入。而在PL/SQL程序中如果需要通过insert into … values … 语句插入大量数据则可以选择直接路径插入来提升执行速度。通过在insert关键字后附加/* append_values */提示来指示Oracle使用直接路径插入。
示例建立2张同样的表分别用传统插入和直接路径插入向表中加载1000万的数据并记录执行时间
create table t1(id integer, name varchar2(32));
create table t2(id integer, name varchar2(32));declaretype idtype is table of t1.id%type index by pls_integer;type nametype is table of t1.name%type index by pls_integer;pids idtype;pnames nametype;iterations constant pls_integer : 10000000;moment1 integer;moment2 integer;moment3 integer;
beginfor j in 1..iterations looppids(j) : j;pnames(j) : No. || to_char(j);end loop;moment1 : dbms_utility.get_time;forall x in 1..iterationsinsert into t1(id, name) values(pids(x), pnames(x)); commit;moment2 : dbms_utility.get_time;forall x in 1..iterationsinsert /* append_values */ into t2(id, name) values(pids(x), pnames(x)); commit;moment3 : dbms_utility.get_time;dbms_output.put_line(Execution Time Compare (seconds):);dbms_output.put_line(----------------------------------);dbms_output.put_line(Conventional Insert: || to_char((moment2 - moment1)/100));dbms_output.put_line(Direct-Path Insert: || to_char((moment3 - moment2)/100));
end;
/表t1和t2的表结构相同使用循环向其中插入1000万条数据第一个循环使用传统插入耗时7.14秒第二个循环使用直接路径插入耗时3.52秒
2.2 insert into … select … 子查询直接路径插入
使用insert into … select … 通过子查询向表中加载数据时在insert或select关键字后附加/* append */提示来使用直接路径插入。
示例将表t2的数据使用Direct-Path Insert加载到t1中
insert /* append */ into t1 select * from t2;
commit;
insert into t1 select /* append */ * from t2;
commit;注意使用直接路径插入的数据在提交前是不能查询和更新的必须显式commit之后才可以使用。上面的两个insert语句中间必须有一个commit否则第二条insert会失败ORA-12838
2.3 并行模式下使用Direct-Path Insert
当开启并行模式后insert语句会自动变为Direct-Path Insert但也可以选择使用提示/* noappend parallel */来禁用Direct-Path Insert。
示例使用并行模式首先要在会话级别打开并行DML
alter session enable parallel dml;检查是否满足下面3个条件中的任意一个满足任意条件即可使用Direct-Path Insert
表已经打开并行属性insert的时候显式使用parallel提示将初始化参数parallel_degree_poicy设置为auto
修改表的并行属性和在insert语句中显式使用parallel提示
alter table t1 parallel;
insert /* parallel(t1,4) */ into t1 select * from t2;修改parallel_degree_policy参数需要较高的权限
alter system set parallel_degree_policyauto;2.4 使用SQL*Loader工具时指定Direct-Path Insert
SQL* Loader是Oracle提供的一个数据加载工具用于将数据从外部文件加载到数据库的表中。在加载数据时可以采用Direct-Path Insert提升加载速度。由于SQL* Loader的功能非常强大使用也稍复杂下面仅使用SQL*Loader的Express模式不需要控制文件且有大量默认选项演示直接路径插入。
SQL* Loader加载数据时指定directtrue选项可以指示其使用Direct-Path Insert这里准备了一个简单的数据文件t1.dat只有3行数据。 采用SQL* Loader的express模式将数据加载进入表t1加载时指定directture
sqlldr hr/hr tablet1 directtrueSQL*Loader的express模式会自动在当前目录下搜索table_name.dat文件所以这里不需要指定数据文件日志的Path used: Direct代表其采用了直接路径插入
三、Direct-Path Insert与重做日志
与传统插入强制生成重做日志不同Direct-Path Insert可以选择关闭重做日志的生成减少性能开销但也意味着无法进行Media Recovery。
如果关闭了重做日志Oracle只会生成很少量的无效重做日志万一数据库崩溃了这些使用Direct-Path Insert插入的数据块会被标记为损坏因为没有重做日志无法进行Media Recovery因此建议使用nologging模式插入数据后进行一次备份。
通过修改表/索引/分区/LOB的logging模式可以关闭和打开该对象上重做日志的生成
alter table t1 nologging;
alter table t1 logging;注意如果DBA在数据库或表空间级别设置的了force logging那么你在表级别是无法关闭重做日志的即使使用nologging选项也会被忽略。
alter database force logging;
alter database no force logging;alter tablespace users force logging;
alter tablespace users no force logging;
