㈢ 直接路径加载与index
直接路径插入时,不产生表块的回滚信息,而是依赖高水位点实现回滚
但是,如果表有索引,将会产生索引的回滚信息,而且索引的块会被读进buffer cache
测试:
--为b表创建一个索引
hr@ORCL> create index idx_b on b (id);
Index created.
hr@ORCL> select file#,block# from v$bh where objd=(select data_object_id from user_objects where object_name='IDX_B');
FILE# BLOCK#
---------- ----------
4 2587 ←← 段头块
4 2585 ←← L1块
4 2588 ←← 第一个索引数据块
4 2586 ←← L2块
--重启数据库,清空buffer cache
hr@ORCL> select file#,block# from v$bh where objd=(select data_object_id from user_objects where object_name='IDX_B');
no rows selected
hr@ORCL> insert /*+ append */ into b select * from a;
4 rows created.
hr@ORCL> select file#,block# from v$bh where objd=(select data_object_id from user_objects where object_name='IDX_B');
FILE# BLOCK#
---------- ----------
4 2588
--直接路径插入时,索引块仍然会被调入buffer cache
hr@ORCL> select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn from v$transaction;
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN
---------- ---------- ---------- ---------- ---------- ----------
6 41 399 2 1456 261
--并且,对于索引块的修改,将会产生回滚信息,回滚信息保存在回滚块1456处
--因此,索引并不会"直接路径插入"
--因此,插入的索引数据,应该是在高水位点下:
hr@ORCL> select header_file,header_block from dba_segments where segment_name='IDX_B';
HEADER_FILE HEADER_BLOCK
----------- ------------
4 2587
hr@ORCL> alter system dump datafile 4 block 2587;
System altered.
--trc文件摘入如下:
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x01000a1d ext#: 0 blk#: 4 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 1
mapblk 0x00000000 offset: 0
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x01000a1d ext#: 0 blk#: 4 ext size: 8
--高水位点在2589块处,插入的索引数据在2588处,在高水位点下
Oracle官方文档建议,如果使用直接路径插入,向表中传送大量数据,可先将表上的索引删掉,插入结束后,再重新建立索引