晶晶实验二十二之直接路径插入篇

晶晶实验二十二之直接路径插入篇上一篇/下一篇2008-04-0223:24:28/个人分类：晶晶oracle实验系列查看(2019)/评论(33)一、直接路径插入与间接路径插入的不同这个问题相信很多人都已经知道了，为了方便初学者，我再来重审一遍。createtable表1asselect列1，列2，...select表2insert/*+append*/into表1select列1，列2，...select表2如上形式的插入，都叫做直接路径插入。当然，在SQL*Loader中也有直接路径插入的形式。所谓直接路径插入，就是绕过Buffercache，直接将数据插入进表所在数据文件中。假如有表AA，要将AA中的数据插入进表BB，在普通的间接插入下，先将AA的数据块传进Buffercache，再将BB的块也传进Buffercache，在Buffercache中从AA的块中读出行，插入进BB的块中。BB的块就都变成了脏块，再等待DBWn把它们写进数据文件。因此，间接路径插入后，AA表的块和BB表的块都会在Buffercache中出现。而直接路径插入下，将AA表的数据块传进Buffercache中，读出行，直接写进BB表所在的数据文件。插入完毕后，除了表头块外，BB表的数据块并不会出现在Buffercache中。下面来试验一下：步1：准备试验用表：SQLcreatetableaa(idnumber(4),namevarchar2(5));表已创建。SQLcreatetablebb(idnumber(4),namevarchar2(5));表已创建。SQLinsertintoaavalues(1,'aa');已创建1行。SQLinsertintoaavalues(2,'bb');已创建1行。SQLinsertintoaavalues(3,'cc');已创建1行。SQLinsertintoaavalues(4,'dd');已创建1行。SQLcommit;提交完成。SQLselectdbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid)fromaa;DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)------------------------------------------------------------------------418493418493418493418493现在AA表中有4行，占用块18493。BB表中没有数据。步2：将buffercache清空，我这里使用重启数据库的方法：SQLshutdownimmediateSQLstartup步3：先用直接路径插入，从AA表向BB表插入数据：SQLinsert/*+append*/intobbselect*fromaa;已创建4行。SQLcommit;提交完成。步4：使用V$bh查看Buffercache中的块：SQLselectfile#,block#fromv$bhwhereobjd=(selectobject_idfromuser_objectswhereobject_name='AA');FILE#BLOCK#--------------------418491418491418494418492418495418493----当前包含数据的块418496已选择7行。由于对AA表进行了全表扫描，因此，AA表中高水点下的所有块都被读进了Buffercache，这其中当然包括包含数据的块18493。SQLselectfile#,block#fromv$bhwhereobjd=(selectobject_idfromuser_objectswhereobject_name='BB');FILE#BLOCK#--------------------418499418499418497SQLselectdbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid)frombb;DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)------------------------------------------------------------------------418500418500418500418500上面两个查询可以看到，BB表中的数据占用第18500块，但是，直接路径插入后，18500块并没被调进Buffercache。Buffercache中只有18499和18497。其中18499是段头块，而18497是L1块，直接路径插入后，要修改L1块中的数据块使用情况。步5：再试一次间接路径插入：SQLinsertintobbselect*fromaa;已创建4行。SQLcommit;提交完成。SQLselectfile#,block#fromv$bhwhereobjd=(selectobject_idfromuser_objectswhereobject_name='BB');FILE#BLOCK#--------------------418504----本次间接路径插入的数据所在块418499418499418502418497418500418503418498418501已选择9行。SQLselectdbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid)frombb;DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)------------------------------------------------------------------------418500418500418500418500418504418504418504418504已选择8行。从上面的实验可以证明，间接路径插入，要先将数据块传进Buffercache。这是Oracle通常修改数据的方式，不对数据文件直接进行修改，而是在内存中完成修改，再由日志提供保护。对于小量数据的修改，这种方法的性能还是很不错的。但是大量数据的修改，直接路径插入将可以提供更好的性能。直接路径插入除去少了将BB表的块传进Buffercache这一步外，它还不产生回滚信息，下面来进一步的实验：二、直接路径插入与回滚：步1：再次向BB中直接路径插入：SQLinsert/*+append*/intobbselectid+4,namefromaa;步2：查看事务信息：SQLselectxidusn,xidslot,xidsqn,ubafil,ubablk,ubasqnfromv$transaction;XIDUSNXIDSLOTXIDSQNUBAFILUBABLKUBASQN------------------------------------------------------------1123854000因为当前只有一个事务，因此选择v$transaction视图时没有加条件。从上面的显示结果可以看到，UBAFIL、UBABLK为0。也就是此事务并没有对应的回滚块，只在回滚段头的事务表中占用了一行而已。直接路径插入是如何提供回滚的呢？观察BB表高水点的变化，就可以解答这个问题：步3：查找BB表的高水点：SQLselectheader_file,header_blockfromdba_segmentswheresegment_name='BB';HEADER_FILEHEADER_BLOCK-----------------------418499SQLaltersystemdumpdatafile4block18499;系统已更改。查找转储文件：ExtentControlHeader-----------------------------------------------------------------ExtentHeader::spare1:0spare2:0#extents:2#blocks:16lastmap0x00000000#maps:0offset:2716Highwater::0x01004849（高水点）ext#:0blk#:8extsize:8高水点是4号文件18505块。步4：提交后查看直接路径插入到哪个块中：SQLcommit;SQLselectdbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid)frombbwhereid=5;DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)------------------------------------------------------------------------418505418505418505418505本次插入的数据ID列值为5、6、7、8，通过上面的查询，本次直接路径插入，数据被存进18505号块。而提交前的高水点正是18505。|--------------|--------|--------|----------|数据块......|18503|18504|18505|--------------|--------|--------|----------^||此处是高水点，直接路径插入从此块开始分配空间直接路径插入，是在高水点之上分配临时段，将数据插入时进此临时段中。在提交后将高水点提升至临时段之上。现在已经提交，再查看高水点信息：SQLaltersystemdumpdatafile4block18499;系统已更改。ExtentControlHeader-----------------------------------------------------------------ExtentHeader::spare1:0spare2:0#extents:2#blocks:16lastmap0x00000000#maps:0offset:2716Highwater::0x0100484a（刚才是4849）ext#:1blk#:1extsize:8高水点升至18506块，如下图：|--------------|--------|--------|--------|-----|数据块......|18503|18504|18505|18506|--------------|--------|--------|--------|-----^||高水点上升至此处步5：再试一次直接路径插入回滚时的情况：SQLinsert/*+append*/intobbselectid+8,namefromaa;已创建4行。猜想一下，此次插入应该插入进18506，如果提交的话，就提升高水点到18507，如果回滚的话，保持高水点不变。查看高水点，当前仍是18506：SQLaltersystemdumpdatafile4block18499;系统已更改。ExtentControlHeader-----------------------------------------------------------------ExtentHeader::spare1:0spare2:0#extents:2#blocks:16lastm