直接路径插入 -- insert /+append/ into [zt]

最新推荐文章于 2024-02-28 16:25:56 发布

cuixie2370

最新推荐文章于 2024-02-28 16:25:56 发布

阅读量613

点赞数

http://www.itpub.net/thread-965298-1-1.html

一、直接路径插入与间接路径插入的不同
这个问题相信很多人都已经知道了，为了方便初学者，我再来重审一遍。
create table 表1 as select 列1，列2，... select 表2
insert /*+append*/ into 表1 select 列1，列2，... select 表2
如上形式的插入，都叫做直接路径插入。当然，在SQL*Loader中也有直接路径插入的形式。
所谓直接路径插入，就是绕过Buffer cache，直接将数据插入进表所在数据文件中。

[@more@]

假如有表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 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 objd=(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 objd=(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 objd=(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 objd=(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/35489/viewspace-1001818/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-1001818/