一、直接路径插入与间接路径插入的不同
这个问题相信很多人都已经知道了,为了方便初学者,我再来重审一遍。
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 bjd=(select object_id from user_objects where object_name='AA');
FILE# BLOCK#
---------- ----------
4 18491
4 18491
4 18494
4 18492
4 18495
4 18493 4 18496
已选择7行。
由于对AA表进行了全表扫描,因此,AA表中高水点下的所有块都被读进了Buffer cache,这其中当然包括包含数据的块18493。
SQL> select file#,block# from v$bh where bjd=(select 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 bjd=(select object_id from user_objects where object_name='BB');
FILE# BLOCK#
---------- ----------
4 18504 4 18499
4 18499
4 18502
4 18497
4 18500
4 18503
4 18498
4 18501
已选择9行。
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
4 18504
4 18504
4 18504
4 18504
已选择8行。
从上面的实验可以证明,间接路径插入,要先将数据块传进Buffer cache。这是Oracle通常修改数据的方式,不对数据文件直接进行修改,而是在内存中完成修改,再由日志提供保护。对于小量数据的修改,这种方法的性能还是很不错的。但是大量数据的修改,直接路径插入将可以提供更好的性能。
直接路径插入除去少了将BB表的块传进Buffer cache这一步外,它还不产生回滚信息,下面来进一步的实验:
二、直接路径插入与回滚:
步1:再次向BB中直接路径插入:
SQL> insert /*+ append*/ into bb select id+4,name from aa;
步2:查看事务信息:
SQL> select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn from v$transaction;
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN
---------- ---------- ---------- ---------- ---------- ----------
11 23 854 0 0 0
因为当前只有一个事务,因此选择 v$transaction 视图时没有加条件。从上面的显示结果可以看到,UBAFIL、UBABLK为0。也就是此事务并没有对应的回滚块,只在回滚段头的事务表中占用了一行而已。
直接路径插入是如何提供回滚的呢?观察BB表高水点的变化,就可以解答这个问题:
步3: 查找BB表的高水点:
SQL> select header_file,header_block from dba_segments where segment_name='BB';
HEADER_FILE HEADER_BLOCK
----------- ------------
4 18499
SQL> alter system dump datafile 4 block 18499;
系统已更改。
查找转储文件:
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 16
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x01004849(高水点) ext#: 0 blk#: 8 ext size: 8
高水点是4号文件18505块。
步4:提交后查看直接路径插入到哪个块中:
SQL> commit;
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from bb where id>=5;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
4 18505
4 18505
4 18505
4 18505
本次插入的数据ID列值为5、6、7、8,通过上面的查询,本次直接路径插入,数据被存进18505号块。而提交前的高水点正是18505。
|--------------|--------|--------|----------
|数据块 ...... |18503|18504|18505
|--------------|--------|--------|----------
^
|
|
此处是高水点,直接路径插入从此块开始分配空间
直接路径插入,是在高水点之上分配临时段,将数据插入时进此临时段中。在提交后将高水点提升至临时段之上。
现在已经提交,再查看高水点信息:
SQL> alter system dump datafile 4 block 18499;
系统已更改。
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 16
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x0100484a(刚才是4849) ext#: 1 blk#: 1 ext size: 8
高水点升至18506块,如下图:
|--------------|--------|--------|--------|-----
|数据块 ...... |18503|18504|18505|18506
|--------------|--------|--------|--------|-----
^
|
|
高水点上升至此处
步5:再试一次直接路径插入回滚时的情况:
SQL> insert /*+ append*/ into bb select id+8,name from aa;
已创建4行。
猜想一下,此次插入应该插入进18506,如果提交的话,就提升高水点到18507,如果回滚的话,保持高水点不变。
查看高水点,当前仍是18506:
SQL> alter system dump datafile 4 block 18499;
系统已更改。
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 16
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x0100484a ext#: 1 blk#: 1 ext size: 8
如果提交,肯定会变为18507,这个我们在步4已经被证明了,现在我们回滚:
SQL> rollback;
回退已完成。
现在已经回滚,查看高水点信息:
SQL> alter system dump datafile 4 block 18499;
系统已更改。
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 16
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x0100484a ext#: 1 blk#: 1 ext size: 8
仍是18506。
也就是说:
|--------------|--------|--------|--------|--------|------
|数据块 ...... |18503|18504|18505|18506|18507
|--------------|--------|--------|--------|--------|-----
^
|
|
高水点在此处
数据插入至此处
提交后,高水点升至18507,而如果回滚的话,高水点不变:
|--------------|--------|--------|--------|--------|------
|数据块 ...... |18503|18504|18505|18506|18507
|--------------|--------|--------|--------|--------|------
^
|
|
回滚后高水点仍在此处
三、直接路径插入与索引
直接路径插入时,不产生表块的回滚信息,依赖高水点实现回滚。但时,如果表有索引,将会产生索引的回滚信息,而且索引的块会被读时Buffer cache。也就是说,数据不能“直接插入”进索引。下面实验一下:
步1:为表BB创建一个索引:
SQL> create index bb_id on bb(id);
SQL> select file#,block# from v$bh where bjd=(select object_id from user_objects where object_name='BB_ID');
FILE# BLOCK#
---------- ----------
4 18515 4 18513 4 18516 4 18514 重启数据库,清空Buffer cache
步2:
SQL> insert /*+ append*/ into bb select * from aa;
已创建4行。
步3:
SQL> select file#,block# from v$bh where bjd=(select object_id from user_objects where object_name='BB_ID');
FILE# BLOCK#
---------- ----------
4 18516
直接路径插入时,索引块将会被调入Buffer cache。
步4:
SQL> select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn from v$transaction;
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN
---------- ---------- ---------- ---------- ---------- ----------
12 9 548 5 2896 255
并且,对于索引块的修改,将会产生回滚信息,回滚信息保存在回滚块2896处。
因此,索引并不会“直接路径插入”,因此,插入的索引数据,应该是在高水点之下:
SQL> select header_file,header_block from dba_segments where segment_name='BB_ID';
HEADER_FILE HEADER_BLOCK
----------- ------------
4 18515
SQL> alter system dump datafile 4 block 18515;
系统已更改。
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x01004855(高水点) ext#: 0 blk#: 4 ext size: 8
高水点在18517处。插入的索引数据在18515处,在高水点之下。
在文档中也曾建议,如果使用直接路径插入,向表中传送大量数据,可以先将表上的索引删掉,插入结束后,再重新建立索引。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10678398/viewspace-719030/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10678398/viewspace-719030/