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

一、直接路径插入与间接路径插入的不同
UU3G?M3H0     这个问题相信很多人都已经知道了，为了方便初学者，我再来重审一遍。
NM7L9ZC5a#I{Xq0 create table 表1 as select 列1，列2，... select 表2
8h E4ZVG*^,@!C5aS3q0 insert /*+append*/ into 表1 select 列1，列2，... select 表2
&x6e\yb,{D8kO0     如上形式的插入，都叫做直接路径插入。当然，在SQL*Loader中也有直接路径插入的形式。ITPUB个人空间;^]cpG9[h!H
    所谓直接路径插入，就是绕过Buffer cache，直接将数据插入进表所在数据文件中。ITPUB个人空间G4vG.|%WD n`
    假如有表AA，要将AA中的数据插入进表BB，在普通的间接插入下，先将AA的数据块传进Buffer cache，再将BB的块也传进Buffer cache，在Buffer cache中从AA的块中读出行，插入进BB的块中。BB的块就都变成了脏块，再等待DBWn把它们写进数据文件。因此，间接路径插入后，AA表的块和BB表的块都会在Buffer cache中出现。ITPUB个人空间;sjfRRQqN
    而直接路径插入下，将AA表的数据块传进Buffer cache中，读出行，直接写进BB表所在的数据文件。插入完毕后，除了表头块外，BB表的数据块并不会出现在Buffer cache中。
q"`.|FG v_Mq0     下面来试验一下：ITPUB个人空间7rF#z(Y"F\2mv
步1：准备试验用表：
B-wAM~4X7uX-`0 SQL> create table aa(id number(4),name varchar2(5));
iq!e'COl&yjF%j {0 表已创建。
d}6?W#Yn6P8tO)W0 SQL> create table bb(id number(4),name varchar2(5));ITPUB个人空间n6XPAF7p+p
表已创建。
J$\1XsJ+h:b0 SQL> insert into aa values(1,'aa');
(WE|*Z
C0 已创建 1 行。
2]x%KR6~"glrd0 SQL> insert into aa values(2,'bb');ITPUB个人空间.`#M$H.qT6M3B
已创建 1 行。ITPUB个人空间;qz| i+Mk2NB
SQL> insert into aa values(3,'cc');ITPUB个人空间2_Xblw`TN$a
已创建 1 行。
ulf^A0 SQL> insert into aa values(4,'dd');ITPUB个人空间*Z6KX0|7CF2[*\IL$e7h
已创建 1 行。
e!te+F
b9H#J4m-{y-u0 SQL> commit;ITPUB个人空间0hr{-BFMBc-L
提交完成。ITPUB个人空间;[ W*~ c0G%FZ{
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from aa;
L$p9iCh2b0 DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
d _!X0~8i.Bs2s0 ------------------------------------ ------------------------------------ITPUB个人空间jR%a9Mn7N
                                   4                                18493
^3xV8vF$pYUvMF0                                    4                                18493ITPUB个人空间YnR"s'Tv s
                                   4                                18493
Hq#NJ$z9q"fT&E0                                    4                                18493
KA$A+]7^uFLvF0     现在AA表中有4行，占用块18493。BB表中没有数据。ITPUB个人空间8|CnO g,b)wS"D
步2：将buffer cache清空，我这里使用重启数据库的方法：ITPUB个人空间2Z\4u@z9o%^
SQL> shutdown immediateITPUB个人空间 j#R(aaI SH2S?
SQL> startup
w:m:EYv~0 步3：先用直接路径插入，从AA表向BB表插入数据：ITPUB个人空间(H|H8_ nYK+s7N
SQL> insert /*+ append*/ into bb select * from aa;
;l#OX"}5P0 已创建4行。ITPUB个人空间+JW3e T&I1HZNI
SQL> commit;
0lv
M#@[(NP,x0 提交完成。ITPUB个人空间uzfR ox|@6u`-q
步4：使用V$bh查看Buffer cache中的块：
)VP8wxZ7`
B `(w0 SQL> select file#,block# from v$bh where objd=(select object_id from user_objects where object_name='AA');
b$x8r_~#Uan
U8Rrv0      FILE#     BLOCK#
/c^$mv!X'cU0 ---------- ----------ITPUB个人空间zSS!Z2mo*Z
         4      18491ITPUB个人空间.W TYkS+t,t:H
         4      18491ITPUB个人空间MMm^ R
         4      18494ITPUB个人空间$psL(r(_?$\yt
         4      18492
"L)jO\d)SifQ0          4      18495
CS1~e_\m.H|lG0          4      18493  <---- 当前包含数据的块ITPUB个人空间i$GX` \
lU j'Vj
         4      18496

{0?Q5w-rrT-K0 已选择7行。
G{
Fo"]l}QX0 由于对AA表进行了全表扫描，因此，AA表中高水点下的所有块都被读进了Buffer cache，这其中当然包括包含数据的块18493。ITPUB个人空间"AoWb*\}$S
SQL> select file#,block# from v$bh where objd=(select object_id from user_objects where object_name='BB');ITPUB个人空间 Wvg*]!\1}0s)U
     FILE#     BLOCK#ITPUB个人空间0q:HDszh#{(mS
---------- ----------ITPUB个人空间IJV&axC
         4      18499
.~-f8JRiRT0          4      18499
bf
`&Ozy0          4      18497
c7fH$]Ha,Gq7m?3y8B0 SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from bb;ITPUB个人空间,[ Xk+dE9C _
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)ITPUB个人空间|;T#S#D.k$_
------------------------------------ ------------------------------------
DCo2zZn+p$[0                                    4                                18500ITPUB个人空间C'ZHrPSWUd"@!E
                                   4                                18500
7iB9?#us0                                    4                                18500
~Qk)os0                                    4                                18500ITPUB个人空间fO[5T|{L
    上面两个查询可以看到，BB表中的数据占用第18500块，但是，直接路径插入后，18500块并没被调进Buffer cache。Buffer cache中只有18499和18497。 其中18499是段头块，而18497是L1块，直接路径插入后，要修改L1块中的数据块使用情况。ITPUB个人空间]-F5b!o9u
步5：再试一次间接路径插入：ITPUB个人空间lX8|D+\2M8W
SQL> insert into bb select * from aa;ITPUB个人空间5GX/czn6\
已创建4行。ITPUB个人空间-KCi'eVL
SQL> commit;
M%`)t$p2TEu2?0 提交完成。
l6Gs NWKY0 SQL> select file#,block# from v$bh where objd=(select object_id from user_objects where object_name='BB');
@u!]`?n5t0      FILE#     BLOCK#
f+e)d4s j+S_+D0 ---------- ----------
6QUS/j|
M3qX0          4      18504   <---- 本次间接路径插入的数据所在块ITPUB个人空间&NI^V k$`/b
         4      18499ITPUB个人空间 E!Uv(R[ |.C'@3m
         4      18499
o$s GCG3BNU0          4      18502ITPUB个人空间` s)G9?^ uDc
         4      18497
yt5Zr#zx f0          4      18500ITPUB个人空间E0P,BKi
         4      18503ITPUB个人空间C(I;P'AG"d9?m
         4      18498ITPUB个人空间A9_t\(|
         4      18501ITPUB个人空间 Q+g"k@+yT4^$p7A;I(~
已选择9行。
.[j/RB.JZ Z O0 SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from bb;ITPUB个人空间VE
M9BYEp+I%F,P
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
2i&t&c0Ue k0 ------------------------------------ ------------------------------------ITPUB个人空间lN/d:z)r5W%G$_:S
                                   4                                18500ITPUB个人空间$h2K'q @P bGr
                                   4                                18500
&We'u[C0                                    4                                18500ITPUB个人空间Ai4~$VZ*T.P5I
                                   4                                18500
%s)lTj lP @Yz0                                    4                                18504
6B`7P7tK([9M0                                    4                                18504ITPUB个人空间F?knH.g| _
                                   4                                18504ITPUB个人空间AEE(i{?
                                   4                                18504
R'?%K B#d |\W p0 已选择8行。ITPUB个人空间^4p5{2S6~(p'O6M
    从上面的实验可以证明，间接路径插入，要先将数据块传进Buffer cache。这是Oracle通常修改数据的方式，不对数据文件直接进行修改，而是在内存中完成修改，再由日志提供保护。对于小量数据的修改，这种方法的性能还是很不错的。但是大量数据的修改，直接路径插入将可以提供更好的性能。ITPUB个人空间?#guf s@D"],C$na
    直接路径插入除去少了将BB表的块传进Buffer cache这一步外，它还不产生回滚信息，下面来进一步的实验：ITPUB个人空间3uK!Lp,xn6Ys1^A
二、直接路径插入与回滚：ITPUB个人空间$bO9m#PI0S-u-Hr
步1：再次向BB中直接路径插入：
(l?QBO0 SQL> insert /*+ append*/ into bb select id+4,name from aa;ITPUB个人空间k'H+W1` a/p!LpoPx
步2：查看事务信息：ITPUB个人空间2n Sr%F%dU)e!N4f
SQL> select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn from v$transaction;
!e)g%d-tv-Q1CG1b0     XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN
\sblG4J-J0 ---------- ---------- ---------- ---------- ---------- ----------
hdft0m m0         11         23        854          0          0          0
j!v|J*_Y t%Z0     因为当前只有一个事务，因此选择 v$transaction 视图时没有加条件。从上面的显示结果可以看到，UBAFIL、UBABLK为0。也就是此事务并没有对应的回滚块，只在回滚段头的事务表中占用了一行而已。ITPUB个人空间-};~*egk9l
    直接路径插入是如何提供回滚的呢？观察BB表高水点的变化，就可以解答这个问题：ITPUB个人空间8lzLm^N*vma%D
步3： 查找BB表的高水点：
\Va*D6L;{ q0 SQL> select header_file,header_block from dba_segments where segment_name='BB';
G5P&N zn Ie3h yW0 HEADER_FILE HEADER_BLOCK
!hkP3?0v.q0 ----------- ------------
_/EQR'[0           4        18499
Q.z I1wr,T"s0 SQL> alter system dump datafile 4 block 18499;
(M9u1t mw;K%i0 系统已更改。
Z)y7f"z0?$mG`o'X2b0 查找转储文件：ITPUB个人空间y5rR#T/`g
  Extent Control HeaderITPUB个人空间jt!bq-r-G/v5].O
  -----------------------------------------------------------------ITPUB个人空间 _V+X3DIn4P
  Extent Header:: spare1: 0      spare2: 0      #extents: 2      #blocks: 16   
T-n k'}%s EC0K)W(G-@0                   last map  0x00000000  #maps: 0      offset: 2716  
%|~(DoW,C0       Highwater::  0x01004849（高水点）  ext#: 0      blk#: 8      ext size: 8     ITPUB个人空间:exK7_wH
高水点是4号文件18505块。ITPUB个人空间`sg@%Lr2c2Q
步4：提交后查看直接路径插入到哪个块中：
S6Ra(N,F1XL(dP0 SQL> commit;ITPUB个人空间1zw._ WMu6s-w)c
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from bb where id>=5;
3j1P-yT5M'\:OC0 DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)ITPUB个人空间"{at^L [7}e
------------------------------------ ------------------------------------ITPUB个人空间U6O(@a} TI#o"a
                                   4                                18505ITPUB个人空间Q+|6A/J6faV?%d
                                   4                                18505
+i\ ?c0d%Z2e0                                    4                                18505ITPUB个人空间O^(}/[y"l;dp,`
                                   4                                18505ITPUB个人空间7Mm PpXIL$W3?
本次插入的数据ID列值为5、6、7、8，通过上面的查询，本次直接路径插入，数据被存进18505号块。而提交前的高水点正是18505。
P h ^5\ {%z'Tr0 |--------------|--------|--------|----------
8ul&?9y9w c3B*tV0 |数据块 ...... |18503|18504|18505ITPUB个人空间U$]Ov)P
|--------------|--------|--------|----------ITPUB个人空间!D'n\ X'b0e9`{
                                               ^ITPUB个人空间N4fa4Py6ya/YuA
                                                |ITPUB个人空间;v}
T$i#\Kb!T
                                                |ITPUB个人空间 |#E*r6|ZLI
         此处是高水点，直接路径插入从此块开始分配空间ITPUB个人空间&tJ!KV(rqOA8f
直接路径插入，是在高水点之上分配临时段，将数据插入时进此临时段中。在提交后将高水点提升至临时段之上。
SvaJUT0 现在已经提交，再查看高水点信息：ITPUB个人空间J:zq2tWu
SQL> alter system dump datafile 4 block 18499;ITPUB个人空间w!s"O.O5jc9L(Kd
系统已更改。
9J:iO*dei0   Extent Control HeaderITPUB个人空间A/z-^ }T8?!h}A
  -----------------------------------------------------------------ITPUB个人空间-b:vBh*{ d
  Extent Header:: spare1: 0      spare2: 0      #extents: 2      #blocks: 16    ITPUB个人空间5L$mp6ug!JQ
                  last map  0x00000000  #maps: 0      offset: 2716  
4N} P{,q%Lnd3nil0       Highwater::  0x0100484a（刚才是4849）  ext#: 1      blk#: 1      ext size: 8     
3R.D%o
lT_7H{K0 高水点升至18506块，如下图：
IP`6]-~*B){W?0 |--------------|--------|--------|--------|-----ITPUB个人空间.b3i$q3K;D$\{
|数据块 ...... |18503|18504|18505|18506
9I)a)u$T@K;zA0 |--------------|--------|--------|--------|-----
'Tj$zs*@FG0P0                                                           ^
W5zfGDg0                                                            |
)?_w-d/}kf0                                                            |ITPUB个人空间+Z$?]f
Ebk
                                               高水点上升至此处
"XcLOI@
_0 步5：再试一次直接路径插入回滚时的情况：
nik4j#FfYQ
g0 SQL> insert /*+ append*/ into bb select id+8,name from aa;
5W!o!csXkY0 已创建4行。
8o}p&V#|V0 猜想一下，此次插入应该插入进18506，如果提交的话，就提升高水点到18507，如果回滚的话，保持高水点不变。ITPUB个人空间 @!gH
YF+{9ehs
查看高水点，当前仍是18506：
vJ)\%s:L0v4E0 SQL> alter system dump datafile 4 block 18499;ITPUB个人空间z+r}fg
Iq
r&~
系统已更改。
T-}7].f9qf0   Extent Control HeaderITPUB个人空间"t)t/W
d$|-E3Cp
  -----------------------------------------------------------------ITPUB个人空间j-f [Ie i
  Extent Header:: spare1: 0      spare2: 0      #extents: 2      #blocks: 16    ITPUB个人空间e
O$p!oV]S6uH*?IH
                  last map  0x00000000  #maps: 0      offset: 2716  ITPUB个人空间a!B#Ro?jB
      Highwater::  0x0100484a  ext#: 1      blk#: 1      ext size: 8     ITPUB个人空间#W Z8[#r{%Zm;G
如果提交，肯定会变为18507，这个我们在步4已经被证明了，现在我们回滚：ITPUB个人空间4V&Gx'LN5k&_e$l-k
SQL> rollback;ITPUB个人空间&k#{ C+Y}N!H
回退已完成。ITPUB个人空间eG)J(]x0C+Vv
现在已经回滚，查看高水点信息：
3V,JR P]L5r A(Kz(P0 SQL> alter system dump datafile 4 block 18499;
%\E/iM,M Ogst6w0 系统已更改。ITPUB个人空间-I^k2m0eg
  Extent Control Header
)Wop;Q_ s!H&C#x0   -----------------------------------------------------------------
n/^[@!j5W\0   Extent Header:: spare1: 0      spare2: 0      #extents: 2      #blocks: 16   
4I8y|4J;a+`9{0                   last map  0x00000000  #maps: 0      offset: 2716  
#CR.LVN0       Highwater::  0x0100484a  ext#: 1      blk#: 1      ext size: 8     
Pl.@f"G5h0v,o1D0 仍是18506。
iD*YJ,|+u0 也就是说：ITPUB个人空间 n!TuK$FKi
|--------------|--------|--------|--------|--------|------
|ff"UZktJ*I0L0 |数据块 ...... |18503|18504|18505|18506|18507ITPUB个人空间nkV4DC8A'o
|--------------|--------|--------|--------|--------|-----
,@N.dCw*q#B0                                                         ^ITPUB个人空间G&@f*{!g,Ud6D/MD
                                                         | ITPUB个人空间
Qw/z
I7L\p1Hk^
                                                         |
Ya3b+kWl0                                                    高水点在此处
@?*w[B S z0                                                  数据插入至此处ITPUB个人空间Jzm-x0`j5E+MN}f1b7g
提交后，高水点升至18507，而如果回滚的话，高水点不变：ITPUB个人空间? W t*P D&n3E2nc
|--------------|--------|--------|--------|--------|------
_QL1aa3cHR0 |数据块 ...... |18503|18504|18505|18506|18507ITPUB个人空间H
Ft-|yR$lTw
|--------------|--------|--------|--------|--------|------
2u9x8aJxW8_Kbg0                                                           ^
0S.P\-G8mvG0                                                            |
Wl3QE,m^ e Y0                                                            | ITPUB个人空间&Nlc&l)G` qs^
                                            回滚后高水点仍在此处ITPUB个人空间!UDjHi$}Tm

(Qi7{I2O[;u0 三、直接路径插入与索引
%w9T&W/k*_He&t2j0 直接路径插入时，不产生表块的回滚信息，依赖高水点实现回滚。但时，如果表有索引，将会产生索引的回滚信息，而且索引的块会被读时Buffer cache。也就是说，数据不能“直接插入”进索引。下面实验一下：ITPUB个人空间2T(aq3Qz3I&D\
步1：为表BB创建一个索引：ITPUB个人空间GbTXh}'H
SQL> create index bb_id on bb(id);ITPUB个人空间_zL$PW5Mg
SQL> select file#,block# from v$bh where objd=(select object_id from user_objects where object_name='BB_ID');ITPUB个人空间q RX4k+vv:X
     FILE#     BLOCK#ITPUB个人空间f^f"}'hP V'L
---------- ----------
;[3N%V4vi7n0          4      18515  <--- 段头
tCY k`3J Z0          4      18513  <--- L1块
wDQ5q ?~0          4      18516  <--- 第一个索引数据块ITPUB个人空间 ~5R%oN,v[#N
         4      18514  <--- L2块ITPUB个人空间Y5]t(?YW1?7w
重启数据库，清空Buffer cacheITPUB个人空间$K*A+Q+K*M?
步2：ITPUB个人空间KEL/G4V;V?RE3sT
SQL> insert /*+ append*/ into bb select * from aa;
J)i }V!AL.g'q0 已创建4行。ITPUB个人空间}Zd4`g)U&Z
步3：
E"j$e$ZS#^'xQ!@0 SQL> select file#,block# from v$bh where objd=(select object_id from user_objects where object_name='BB_ID');

ak M(e F` |0      FILE#     BLOCK#ITPUB个人空间"D4pvX!A#qU,]Mq
---------- ----------
obm)dbO0          4      18516
%hP:w5{?\B8A0 直接路径插入时，索引块将会被调入Buffer cache。
6H9Fi/a ~$p.`O0 步4：
VtuI3V:W#k0 SQL> select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn from v$transaction;ITPUB个人空间r_"h;r ]g~2[9L
    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN
a-M!MKL(hsO0 ---------- ---------- ---------- ---------- ---------- ----------
&v
[;ap,R'iS;gu0         12          9        548          5       2896        255
K$CsI2U0ZC6W$E(U7I0 并且，对于索引块的修改，将会产生回滚信息，回滚信息保存在回滚块2896处。ITPUB个人空间"MH%U6QRfu6L
因此，索引并不会“直接路径插入”，因此，插入的索引数据，应该是在高水点之下：
p*j)h$QC7B0 SQL> select header_file,header_block from dba_segments where segment_name='BB_ID';ITPUB个人空间\\ F8mQ.Qm5wfF
HEADER_FILE HEADER_BLOCKITPUB个人空间jX#V2Vg;f(XH1x
----------- ------------
^${ pg|,~,Kq&W0           4        18515
hWnP-M0 SQL> alter system dump datafile 4 block 18515;
t Oy6E}S0W0 系统已更改。ITPUB个人空间q*p,J)l}7Nd
  Extent Control Header
1?6lqbf cR#`0   -----------------------------------------------------------------
e2a ~2?4z
m0QT0   Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 8     ITPUB个人空间4Y }4~ A'~\2Cv T
                  last map  0x00000000  #maps: 0      offset: 2716  
kmJjBgF3|0       Highwater::  0x01004855（高水点）  ext#: 0      blk#: 4      ext size: 8     
Y2D{ S1G*C8@T"R)q0 高水点在18517处。插入的索引数据在18515处，在高水点之下。
.[2K$d%Q4JJt0 在文档中也曾建议，如果使用直接路径插入，向表中传送大量数据，可以先将表上的索引删掉，插入结束后，再重新建立索引。