总结:
直接路径插入直接在高水位线以外分配空间,绕过了数据缓冲区,直接将数据插入进表所在数据文件中。
直接路径插入不产生Redo和Undo Log,依赖高水点实现回滚。
直接路径插入结束的时候Oracle会维护索引,为了避免维护索引的性能影响,可以先删除索引,等插入完成后重新建立。
直接路径插入会导致对被插入的表加表级锁,在提交之前,别的会话不能再对此表进行insert, update, delete等操作。
一、直接路径插入与间接路径插入的不同
这个问题相信很多人都已经知道了,为了方便初学者,我再来重审一遍。
create table 表1 as select 列1,列2,... select 表2
insert /*+append*/ into 表1 select 列1,列2,... select 表2
如上形式的插入,都叫做直接路径插入。当然,在SQL*Loader中也有直接路径插入的形式。
所谓直接路径插入,就是绕过Buffer cache,直接将数据插入进表所在数据文件中。
假如有表AA,要将AA中的数据插入进表BB,在普通的间接插入下,先将AA的数据块传进Buffer cache,再将BB的块也传进Buffer cache,在Buffer cache中从AA的块中读出行,插入进BB的块中。BB的块就都变成了脏块,再等待DBWn把它们写进数据文件。因此,间接路径插入后,AA表的块和BB表的块都会在Buffer cache中出现。
而直接路径插入下,将AA表的数据块传进Buffer cache中,读出行,直接写进BB表所在的数据文件。插入完毕后,除了表头块外,BB表的数据块并不会出现在Buffer cache中。
下面来试验一下:
步1:准备试验用表:
SQL> create table aa(id number(4),name varchar2(5));
表已创建。
SQL> create table bb(id number(4),name varchar2(5));
表已创建。
SQL> insert into aa values(1,'aa');
已创建 1 行。
SQL> insert into aa values(2,'bb');
已创建 1 行。
SQL> insert into aa values(3,'cc');
已创建 1 行。
SQL> insert into aa values(4,'dd');
已创建 1 行。
SQL> commit;
提交完成。
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from aa;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
4 18493
4 18493
4 18493
4 18493
现在AA表中有4行,占用块18493。BB表中没有数据。
步2:将buffer cache清空,我这里使用重启数据库的方法:
SQL> shutdown immediate
SQL> startup
步3:先用直接路径插入,从AA表向BB表插入数据:
SQL> insert /*+ append*/ into bb select * from aa;
已创建4行。
SQL> commit;
提交完成。
步4:使用V$bh查看Buffer cache中的块:
SQL> select file#,block# from v$bh where objd=(select data_object_id from user_objects where object_name='AA');
FILE# BLOCK#
---------- ----------
4 18491
4 18491
4 18494
4 18492
4 18495
4 18493 select file#,block# from v$bh where objd=(select data_object_id from user_objects where object_name='BB');
FILE# BLOCK#
---------- ----------
4 18499
4 18499
4 18497
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from bb;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
4 18500
4 18500
4 18500
4 18500
上面两个查询可以看到,BB表中的数据占用第18500块,但是,直接路径插入后,18500块并没被调进Buffer cache。Buffer cache中只有18499和18497。 其中18499是段头块,而18497是L1块,直接路径插入后,要修改L1块中的数据块使用情况。
步5:再试一次间接路径插入:
SQL> insert into bb select * from aa;
已创建4行。
SQL> commit;
提交完成。
SQL> select file#,block# from v$bh where objd=(select data_object_id from user_objects where object_name='BB');
FILE# BLOCK#
---------- ----------
4 18504 select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from bb;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
4 18500
4 18500
4 18500
4 18500
4 18504
4 18504
4 18504
4 18504
已选择8行。
从上面的实验可以证明,间接路径插入,要先将数据块传进Buffer cache。这是Oracle通常修改数据的方式,不对数据文件直接进行修改,而是在内存中完成修改,再由日志提供保护。对于小量数据的修改,这种方法的性能还是很不错的。但是大量数据的修改,直接路径插入将可以提供更好的性能。
直接路径插入除去少了将BB表的块传进Buffer cache这一步外,它还不产生回滚信息,下面来进一步的实验: