ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 教你如何成为Oracle 10g OCP - 第九章 对象管理(7) - B树索引的对于DELETE的管理

教你如何成为Oracle 10g OCP - 第九章 对象管理(7) - B树索引的对于DELETE的管理

原创 Linux操作系统 作者:tolywang 时间:2011-02-17 17:43:44 0 删除 编辑


9.2.2.2  B树索引的对于刪除(DELETE)的管理  

我们知道, 可以通过index_stats来查看索引内部的信息,此视图正常情况下没有
数据, 只有运行 analyze index INDEX_NAME validate structure; 后才会有,且
只有这个session能看到, 所以此视图永远只有小于等于一笔记录.

  注意,该命令会锁定整个表(TM锁),从而阻塞其他session对此表的DML,这是因为
此命令主要不是用来填充index_stats视图的,而是在于校验索引中的每个有效的
索引条目都对应到表里的一行,同时表里的每一行数据在索引中都存在一个对应的
索引条目,所以需要锁定整个表,对于很大的表,运行需要消耗很多时间。

   在视图index_stats中:
height表示B树索引的高度;
blocks表示分配了的索引块数,包括还没有被使用的;
pct_used表示当前索引中被使用了的空间的百分比。其值是根据视图中的
(used_space/btree_space)*100计算而来;
del_lf_rows表示被删除的记录行数(表中数据被删除,并不会立即将对应于索引
里面的索引条目清除出索引块,后面会讲到)。
del_lf_rows_len 表示被删除的记录所占的总空间。
lf_rows 表示索引中包含的总记录行数,包括已经被删除的记录行数。这样的话,
索引中未被删除的记录行数就是 lf_rows-del_lf_rows, 同时我们可以计算未被
删除的记录所对应的索引条目(即有效索引条目)所占用的空间:
((used_space – del_lf_rows_len) / btree_space) * 100。


接着上节中的例子(最后插入了12*2的例子)来测试一下,我们知道,例子中的索引
具有两个叶子节点,一个叶子节点(块号419),包含10,12,14,16,18,20,22,24和2a,
另外一个叶子节点(块号420),含有4a,6a,8a。这时我们插入41,42,43,44,45,46,47,
48各8条记录,可以知道这8条记录对应的索引条目将会进入索引块420中,从而420
块被充满。


SQL> begin
 2    for i in 1..8 loop
 3        insert into index_test values (rpad('4'||to_char(i),150,'a'));
 4    end loop;
 5 end;
 6 /

我们先分析索引从而填充index_stats视图。

SQL> analyze index idx_test validate structure;

SQL> select LF_ROWS,DEL_LF_ROWS,DEL_LF_ROWS_LEN,USED_SPACE,BTREE_SPACE
     from index_stats;

     LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN USED_SPACE BTREE_SPACE
---------- ----------- --------------- ---------- -----------
       20          0              0      3269       5600

   从上面视图可以看到,当前索引共20条记录,没有被删除的记录,共使用了3269
个字节。然后我们删除位于索引块419里面的索引条目,包括10,12,14,16等4条记
录。


然后我们删除位于索引块419里的索引条目,包括10、12、14、16各4条记录。
SQL> delete index_test where substr(id,1,2) in('10','12','14','16');
SQL> commit;
SQL> alter system dump datafile 7 block 419;

  打开转储出来的文件可以发现如下的内容(我们节选了部分关键内容)。可以
发现kdxconro为9,说明该索引节点里还有9个索引条目。所以说,虽然表里的数
据被删除了,但是对应的索引条目并没有被删除,只是在各个索引条目上(row#
一行中的flag为D)做了一个'D'的标记,表示该索引条目被delete了。

kdxconro 9
row#0[443] flag: ---D-, lock: 2
row#1[604] flag: ---D-, lock: 2
row#2[765] flag: ---D-, lock: 2
row#3[926] flag: ---D-, lock: 2

然后我们再以树状结构转储索引,打开树状转储跟踪文件可以看到如下内容。块
419中还是包含9个索引索引条目(nrow为9),而有效索引条目只有5个(rrow为5),
那么被删除的索引条目就是4个(9减5);

SQL> alter session set events 'immediate trace name treedump level 7390';

----- begin tree dump
branch: 0x1c001a2 29360546 (0: nrow: 2, level: 1)
  leaf: 0x1c001a3 29360547 (-1: nrow: 9 rrow: 5)
  leaf: 0x1c001a4 29360548 (0: nrow: 11 rrow: 11)
----- end tree dump

再次分析索引,填充index_stats视图。
SQL> analyze index idx_test validate structure;
SQL> select LF_ROWS,DEL_LF_ROWS,DEL_LF_ROWS_LEN,USED_SPACE,BTREE_SPACE
     from index_stats;

     LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN USED_SPACE BTREE_SPACE
---------- ----------- --------------- ---------- -----------
       20          4            652      3269       5600

对照删除前视图里的信息,很明显看到,当前索引(条目)仍然是20条
记录,但是其中有4条是删除的,且索引使用的空间并没有释放被删除
记录所占用的空间(652Bytes), 仍然是删除前的3269个字节。

删除结束后,我们接下来测试一下插入一条记录,索引会发生什么变化。
分为3种情况:
A. 插入属于原来被删除键值范围内的值,比如13,观察如何进入包含设
置了删除标记的索引块 ;
B. 插入原来被删除的键值中的一个,比如16,观察是否能重新使用原来
的索引条目;
C. 插入一个完全不属于该表中已有记录的范围的值,比如rpad('M',150,'M'),
观察其对块419以及420会产生什么影响。


我们测试第一种情况(插入属于原来被删除键值范围内的值13):

SQL> insert into index_test values (rpad(to_char(13),150,'a'));
SQL> alter system dump datafile 7 block 419;

      打开跟踪文件以后会发现419块里的内容发生了变化,如下所示。我们
可以发现一个很有趣的现象,从kdxconro为6说明插入了键值13以后,导致原来
四个被标记为删除的索引条目都被清除出了索引块。同时,我们也确实发现原来
标记为D的四个索引条目都消失了。

……
kdxconro 6
……
kdxlende 0
……
row#0[121] flag: -----, lock: 2   被插入13
col 0; len 150; (150):
 31 33 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
……

我们分析索引,看看index_stats视图会如何变化。

SQL> analyze index idx_test validate structure;
SQL> select LF_ROWS,DEL_LF_ROWS,DEL_LF_ROWS_LEN,USED_SPACE,BTREE_SPACE
     from index_stats;

  LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN USED_SPACE BTREE_SPACE
---------- ----------- --------------- ---------- -----------
       17          0              0      2780       5600

      很明显,原来的del_lf_rows从4变为了0,同时used_space也从原来的3269
变成了2780。表示原来被删除的索引条目所占用的空间已经释放了。


我们继续测试第二种情况(插入删除的键值'16'):

SQL> insert into index_test values (rpad(to_char(8*2),150,'a'));
SQL> alter system dump datafile 7 block 419;

   打开跟踪文件以后,发现对于插入已经被标记为删除的记录来说,其过程
与插入属于该索引块索引范围的键值的过程没有区别。甚至你会发现,被插入
的16的键值所处的位置与插入的13的键值所在的位置完全一样(row#0[121]里
的121表示在索引块中的位置)。也就是说,oracle并没有重用原来为16的键值,
而是直接将所有标记为D的索引条目清除出索引块,然后插入新的键值为16的索
引条目。

    对于第三种情况,我们已经可以根据前面有关第一、第二种情况做出预测,
由于420块已经被充满,同时所插入的键值是整个表里的最大值,因此也不会因
此420号块发生分裂,而是直接获取一个新的索引块来存放该键值。但是419号
块里标记为D的索引条目是否能被清除出索引块呢?

SQL> insert into index_test values (rpad('M',150,'M'));
SQL> alter system dump datafile 7 block 419;
SQL> alter system dump datafile 7 block 420;
SQL> alter system dump datafile 7 block 421;

      打开跟踪文件,可以清楚的看到,419号块里的标记为D的4各索引条目仍然
保留在索引块里,同时420号块里的内容没有任何变化,而421号块里则存放了新
的键值:rpad('M',150,'M')。

我们看看index_stats视图会如何变化。其结果也符合我们从转储文件中所看到的内容。

SQL> analyze index idx_test validate structure;
SQL> select LF_ROWS,DEL_LF_ROWS,DEL_LF_ROWS_LEN,USED_SPACE,BTREE_SPACE
     from index_stats;

  LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN USED_SPACE BTREE_SPACE
---------- ----------- --------------- ---------- -----------
       21          4            652      3441       7456

      既然当插入rpad('M',150,'M')时对419号块没有任何影响,不会将标记为D
的索引条目移出索引块。那么如果我们事先将419号索引块中所有的索引条目都标
记为D,也就是说删除419号索引块中索引条目所对应的记录,然后再次插入rpad
('M',150,'M')时会发生什么? 
     通过测试,我们可以发现,再次插入一个最大值以后,该最大值会进入块421里,
但是块419里的索引条目则会被全部清除,变成了一个空的索引数据块。这也就是我们
通常所说的,当索引块里的索引条目全部被设置为D(删除)标记时,再次插入任何一个
索引键值都会引起该索引块里的内容被清除。


      最后,我们来测试一下,当索引块里的索引条目全部被设置为D(删除)标记
以后,再次插入新的键值时会如何重用这些索引块。我们先创建一个测试表,并插入
10000条记录。

SQL> create table delete_test(id number);

SQL> begin
 2    for i in 1..10000 loop
 3        insert into delete_test values (i);
 4    end loop;
 5    commit;
 6 end;
 7 /

SQL> create index idx_delete_test on delete_test(id);

SQL> analyze index idx_delete_test validate structure;

SQL> select LF_ROWS,LF_BLKS,DEL_LF_ROWS,USED_SPACE,BTREE_SPACE from index_stats;

  LF_ROWS   LF_BLKS DEL_LF_ROWS USED_SPACE BTREE_SPACE
---------- ---------- ----------- ---------- -----------
    10000        21          0    150021     176032

   可以看到,该索引具有21个叶子节点。然后我们删除前9990条记录。从而使得
21个叶子节点中只有最后一个叶子节点具有有效索引条目,前20个叶子节点里的索
引条目全都标记为D(删除)标记。

SQL> delete delete_test where id >= 1 and id <= 9990;
SQL> commit;

SQL> analyze index idx_delete_test validate structure;
SQL> select LF_ROWS,LF_BLKS,DEL_LF_ROWS,USED_SPACE,BTREE_SPACE from index_stats;

  LF_ROWS   LF_BLKS DEL_LF_ROWS USED_SPACE BTREE_SPACE
---------- ---------- ----------- ---------- -----------
    10000        21       9990   150021     176032

最后,我们插入从20000开始到30000结束,共10000条与被删除记录完全不重叠的记录。

SQL> begin
 2    for i in 20000..30000 loop
 3        insert into delete_test values (i);
 4    end loop;
 5    commit;
 6 end;
 7 /

SQL> analyze index idx_delete_test validate structure;

SQL> select LF_ROWS,LF_BLKS,DEL_LF_ROWS,USED_SPACE,BTREE_SPACE from index_stats;

  LF_ROWS   LF_BLKS DEL_LF_ROWS USED_SPACE BTREE_SPACE
---------- ---------- ----------- ---------- -----------
    10011        21          0    160302     176032

  很明显的看到,尽管被插入的记录不属于被删除的记录范围,但是只要索引块中所有
的索引条目都被删除了(标记为D),该索引就变成可用索引块而能够被新的键值重新利用了。

 

   因此,根据上面我们所做的试验,可以对索引的删除情况总结如下:

1). 当删除表里的一条记录时,其对应于索引里的索引条目并不会被物理的删除(空间
还是被占用),只是做了一个删除标记('D')。

2). 当一个新的索引条目进入一个索引叶子节点的时候,oracle会检查该叶子节点里是
否存在被标记为删除的索引条目,如果存在,则会将所有具有删除标记的索引条目从该
叶子节点里物理的删除。

3). 当一个新的索引条目(键值)插入索引时,这个值不属于该表中有记录的范围的值,
比如插入的是整表的最大值,那么不会将标记为'D'的索引条目移出索引块(特例见第
4条)。

4). 如果有所有索引条目都标记为'D'的索引块的话,再次插入任何一个索引键值都会
引起这些索引块(所有索引条目都标记为'D')里的内容被清除。

5). 当一个新的索引条目进入索引时,oracle会将当前所有被清空的叶子节点(该叶子
节点中所有的索引条目都被设置为删除'D'标记)收回,从而再次成为可用索引块,能
被重新使用了。

 

索引碎片问题 --

尽管被删除的索引条目所占用的空间大部分情况下都能够被重用,但仍然存在一些情况
可能导致索引空间被浪费,并造成索引数据块很多但是索引条目很少的后果,这时该索
引可以认为出现碎片。而导致索引出现碎片的情况主要包括:

1). 不合理的、较高的PCTFREE。很明显,这将导致索引块的可用空间减少。

2). 索引键值持续增加(比如采用sequence生成序列号的键值),同时对索引键值按
照顺序连续删除,这时可能导致索引碎片的发生。因为前面我们知道,某个索引块中
删除了部分的索引条目,只有当有键值进入该索引块时才能将空间收回。而持续增加
的索引键值永远只会插入排在前面的索引块中,因此这种索引里的空间几乎不能收回,
而只有其所含的索引条目全部删除时,该索引块才能被重新利用。

3). 经常被删除或更新的键值,以后几乎不再会被插入时,这种情况与上面的情况类似。
对于如何判断索引是否出现碎片,方法非常简单:直接运行ANALYZE INDEX … VALIDATE
STRUCTURE命令,然后检查index_stats视图的pct_used字段,如果该字段过低(低于50%),
则说明存在碎片。

 

9.2.2.3  B树索引对于更新(UPDATE)的管理

而对于值被更新对于索引条目的影响,则可以认为是删除和插入的组合。也就是
将被更新的旧值对应的索引条目设置为D(删除)标记,同时将更新后的值按照顺
序插入合适的索引块中。这里就不重复讨论了。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-687455/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13474143