直接路径插入使用直接写(direct write)操作,在要修改的段的高水位(high watermark)以上直接加载数据,这个事实产生以下重要的结果:
1. 由于直接写的缘故,高速缓存被辟开了.
2. 不允许在被修改的表上同步的执行DELETE,INSERT,MERGE,UPDATE语句,以及索引的创建(重建)操作.
3.在高水位以下的包含空闲空间的块也不会被利用.
直接路径插入之所以可以获得更好的性能,其中一个原因是,只为这个段生成了最少量的回滚.实际上,只为空间管理操作做了回滚.
有以下两种方式可以使一个INSERT INTO...SELECT...语句使用直接路径插入.
1.在SQL语句中指定append提示:INSERT /*+ append*/ INTO...SELECT...
2.并行地执行SQL语句.注意,在这里,插入(INSERT)与查询(SELECT)都可以独立地被并行处理.要利用直接路径插入,至少INSERT部分必须被并行执行.
测试如下:
1.以常规方式插入数据(noarchivelog mode,logging mode, no direct-path insert)
LIBIN@dextrys>DROP TABLE t;
DROP TABLE t
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
已用时间: 00: 00: 00.15
LIBIN@dextrys>
LIBIN@dextrys>CREATE TABLE t (id NUMBER, pad VARCHAR2(1000));
表已创建。
已用时间: 00: 00: 00.06
为表添加主键约束:
LIBIN@dextrys>ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);
表已更改。
已用时间: 00: 00: 00.21
LIBIN@dextrys>TRUNCATE TABLE t;
表被截断。
已用时间: 00: 00: 00.12
LIBIN@dextrys>SELECT value
2 FROM v$mystat NATURAL JOIN v$statname
3 WHERE name = 'redo size';
VALUE
----------
29332
已用时间: 00: 00: 00.07
LIBIN@dextrys>ALTER TABLE t LOGGING;
表已更改。
已用时间: 00: 00: 00.01
LIBIN@dextrys>INSERT INTO t
2 SELECT rownum AS id, rpad('*',1000,'*') AS pad
3 FROM dual
4 CONNECT BY level <= 1000000;
已创建1000000行。
已用时间: 00: 03: 11.98
检查生成的已用回滚信息.
LIBIN@dextrys>SELECT t.used_ublk, t.used_urec
2 FROM v$transaction t, v$session s
3 WHERE t.addr = s.taddr
4 AND s.audsid = userenv('sessionid');
USED_UBLK USED_UREC
---------- ----------
4320 378280
已用时间: 00: 00: 00.12
LIBIN@dextrys>SELECT value
2 FROM v$mystat NATURAL JOIN v$statname
3 WHERE name = 'redo size';
VALUE
----------
1192914920
已用时间: 00: 00: 00.03
说明:常规方式下,插入100万条数据,花费大约192秒,生成大量回滚信息,生成(1192914920-29332)字节redo log;
2.以(direct-path insert)方式插入数据(noarchivelog mode,logging mode, direct-path insert)
LIBIN@dextrys>TRUNCATE TABLE t;
表被截断。
已用时间: 00: 00: 01.43
LIBIN@dextrys>ALTER TABLE t LOGGING;
表已更改。
已用时间: 00: 00: 00.07
LIBIN@dextrys>INSERT /*+ append */ INTO t
2 SELECT rownum AS id, rpad('*',1000,'*') AS pad
3 FROM dual
4 CONNECT BY level <= 1000000;
已创建1000000行。
已用时间: 00: 00: 37.04
LIBIN@dextrys>SELECT t.used_ublk, t.used_urec
2 FROM v$transaction t, v$session s
3 WHERE t.addr = s.taddr
4 AND s.audsid = userenv('sessionid');
USED_UBLK USED_UREC
---------- ----------
1070 7490
已用时间: 00: 00: 00.03
LIBIN@dextrys>SELECT value
2 FROM v$mystat NATURAL JOIN v$statname
3 WHERE name = 'redo size';
VALUE
----------
1251788232
已用时间: 00: 00: 00.01
说明:(direct-path insert)方式下,插入100万条数据,花费大约37秒,生成少量回滚信息(回滚信息是为维护索引而产生的),生成(1251788232-1192914920)字节redo log;
3.以(direct-path insert)方式插入数据(noarchivelog mode,nologging mode, direct-path insert)
LIBIN@dextrys>TRUNCATE TABLE t;
表被截断。
已用时间: 00: 00: 02.82
LIBIN@dextrys>ALTER TABLE t NOLOGGING;
表已更改。
已用时间: 00: 00: 00.03
LIBIN@dextrys>INSERT /*+ append */ INTO t
2 SELECT rownum AS id, rpad('*',1000,'*') AS pad
3 FROM dual
4 CONNECT BY level <= 1000000;
已创建1000000行。
已用时间: 00: 00: 36.18
LIBIN@dextrys>SELECT t.used_ublk, t.used_urec
2 FROM v$transaction t, v$session s
3 WHERE t.addr = s.taddr
4 AND s.audsid = userenv('sessionid');
USED_UBLK USED_UREC
---------- ----------
1071 7496
已用时间: 00: 00: 00.03
LIBIN@dextrys>SELECT value
2 FROM v$mystat NATURAL JOIN v$statname
3 WHERE name = 'redo size';
VALUE
----------
1311002048
已用时间: 00: 00: 00.01
说明:(direct-path insert)方式下,插入100万条数据,花费大约36秒,生成少量回滚信息(回滚信息是为维护索引而产生的),生成(1311002048-1251788232)字节redo log;
2和3的比较说明了:即使没有使用最小日志模式,在非归档(noarchivelog)模式下运行的数据库也不会为直接路径插入操作生成重做日志.
4.以(direct-path insert)方式插入数据(noarchivelog mode,nologging mode, direct-path insert),且删除表上的索引
LIBIN@dextrys>TRUNCATE TABLE t;
表被截断。
已用时间: 00: 00: 03.07
LIBIN@dextrys>alter table t drop constraint t_pk ;
表已更改。
已用时间: 00: 00: 00.48
LIBIN@dextrys>SELECT value
2 FROM v$mystat NATURAL JOIN v$statname
3 WHERE name = 'redo size';
VALUE
----------
1311588524
已用时间: 00: 00: 00.07
LIBIN@dextrys>ALTER TABLE t NOLOGGING;
表已更改。
已用时间: 00: 00: 00.01
LIBIN@dextrys>INSERT /*+ append */ INTO t
2 SELECT rownum AS id, rpad('*',1000,'*') AS pad
3 FROM dual
4 CONNECT BY level <= 1000000;
已创建1000000行。
已用时间: 00: 00: 25.68
LIBIN@dextrys>SELECT t.used_ublk, t.used_urec
2 FROM v$transaction t, v$session s
3 WHERE t.addr = s.taddr
4 AND s.audsid = userenv('sessionid');
USED_UBLK USED_UREC
---------- ----------
1 1
已用时间: 00: 00: 00.03
LIBIN@dextrys>SELECT value
2 FROM v$mystat NATURAL JOIN v$statname
3 WHERE name = 'redo size';
VALUE
----------
1312384532
已用时间: 00: 00: 00.01
说明:删除表上的索引以后,(direct-path insert)方式下,插入100万条数据,花费大约26秒,生成少量回滚信息为1,生成(1312384532-1311002048)字节redo log;以上说明了若表上有索引,在执行DML时,索引段上会生成回滚信息,如果要加快操作速度,避免生成索引相关的回滚,可以在加载数据之前将索引改为不可用,并在加载之后来重建这些索引.
在执行直接路径插入的过程中,高水位不会被提高.只有到提交事务的时候才会执行这个操作.因此,在加载之后提交(或回滚)事务之前,执行直接路径插入的会话无法访问被修改的表.在提交(或回滚)之前执行的SQL语句会终止执行,并报一个"ORA-12838"错误.
LIBIN@dextrys>select count(*) from t;
select count(*) from t
*
第 1 行出现错误:
ORA-12838: 无法在并行模式下修改之后读/修改对象
已用时间: 00: 00: 00.06
LIBIN@dextrys>commit;
提交完成。
已用时间: 00: 00: 00.06
LIBIN@dextrys>select count(*) from t;
COUNT(*)
----------
1000000
已用时间: 00: 00: 22.09