索引数据块的重用机制探究(1)

 

创建一个100万条记录的表，其中id 的值为1-1000000，并在id上创建索引：

 

sys@EBANK>create table test (id number(10),comm varchar2(10)) tablespace users;

 

Table created.

 

sys@EBANK>Begin

  2  For I in 1 .. 1000000 loop

  3  Insert into test values(I,to_char(i));

  4  End loop;

  5  Commit;

  6  End ;

  7

  8  /

 

PL/SQL procedure successfully completed.

 

 

sys@EBANK>create index idx_test on test(id) tablespace users;

 

Index created.

 

sys@EBANK>analyze index idx_test validate structure;

 

Index analyzed.

 

sys@EBANK>select name,height,blocks,lf_rows,lf_blks,br_rows,br_blks from index_stats;

 

NAME                     HEIGHT     BLOCKS    LF_ROWS    LF_BLKS    BR_ROWS    BR_BLKS

-------------------- ---------- ---------- ---------- ---------- ---------- ----------

IDX_TEST                      3       2304    1000000       2226       2225          5

 

sys@EBANK>select object_name,object_type,object_id from user_objects where object_name='IDX_TEST';

 

OBJECT_NAME                    OBJECT_TYPE          OBJECT_ID

------------------------------ ------------------- ----------

IDX_TEST                       INDEX                    11340

 

sys@EBANK>alter session set events 'immediate trace name treedump level  11340';

 

Session altered.

 

-----删除500条记录后，做treedump，dump出索引的结构：

 

sys@EBANK>delete from test where id <=500;

 

500 rows deleted.

 

sys@EBANK>commit;

 

Commit complete.

 

sys@EBANK>alter session set events 'immediate trace name treedump level  11340';

 

Session altered.

 

----插入1000001-1000500共500条记录后，再次做treedump：

 

sys@EBANK>Begin

  2    For I in 1000001 .. 1000500 loop

  3    Insert into test values(I,to_char(i));

  4    End loop;

  5    Commit;

  6     End ;

  7     /

 

PL/SQL procedure successfully completed.

 

sys@EBANK>alter session set events 'immediate trace name treedump level  11340';

 

Session altered.

 

查看索引的结构：

 

[ora10g@single udump]$ grep branch standby_ora_32152.trc

branch: 0x800064 8388708 (0: nrow: 4, level: 2)

   branch: 0x8018bf 8394943 (-1: nrow: 672, level: 1)

   branch: 0x801b68 8395624 (0: nrow: 670, level: 1)

   branch: 0x801e13 8396307 (1: nrow: 670, level: 1)

   branch: 0x801eeb 8396523 (2: nrow: 214, level: 1)

 

-----初始含1-1000000共100万条数据的索引结构:

 

[ora10g@single udump]$ head -30 standby_ora_32152.trc|tail -15

*** SERVICE NAME:(SYS$USERS) 2011-01-26 05:20:41.662

*** SESSION ID:(104.175) 2011-01-26 05:20:41.662

----- begin tree dump

branch: 0x800064 8388708 (0: nrow: 4, level: 2)

   branch: 0x8018bf 8394943 (-1: nrow: 672, level: 1)

      leaf: 0x800065 8388709 (-1: nrow: 485 rrow: 485)

      leaf: 0x800066 8388710 (0: nrow: 479 rrow: 479)

      leaf: 0x800067 8388711 (1: nrow: 479 rrow: 479)

      leaf: 0x800068 8388712 (2: nrow: 479 rrow: 479)

      leaf: 0x800069 8388713 (3: nrow: 479 rrow: 479)

      leaf: 0x80006a 8388714 (4: nrow: 478 rrow: 478)

      leaf: 0x80006b 8388715 (5: nrow: 479 rrow: 479)

      leaf: 0x80006c 8388716 (6: nrow: 479 rrow: 479)

      leaf: 0x80006d 8388717 (7: nrow: 479 rrow: 479)

      leaf: 0x80006e 8388718 (8: nrow: 478 rrow: 478)

 

---删除了前500条记录后的索引结构

 

[ora10g@single udump]$ head -30 standby_ora_32205.trc|tail -15

*** SESSION ID:(90.38) 2011-01-26 05:23:04.996

----- begin tree dump

branch: 0x800064 8388708 (0: nrow: 4, level: 2)

   branch: 0x8018bf 8394943 (-1: nrow: 672, level: 1)

      leaf: 0x800065 8388709 (-1: nrow: 485 rrow: 0)   ----删除500条记录后，此索引的leaf block记录数变为0，此时该leaf block已经变为可用

      leaf: 0x800066 8388710 (0: nrow: 479 rrow: 464) ----注意此leaf block的记录也被删除了15条

      leaf: 0x800067 8388711 (1: nrow: 479 rrow: 479)

      leaf: 0x800068 8388712 (2: nrow: 479 rrow: 479)

      leaf: 0x800069 8388713 (3: nrow: 479 rrow: 479)

      leaf: 0x80006a 8388714 (4: nrow: 478 rrow: 478)

      leaf: 0x80006b 8388715 (5: nrow: 479 rrow: 479)

      leaf: 0x80006c 8388716 (6: nrow: 479 rrow: 479)

      leaf: 0x80006d 8388717 (7: nrow: 479 rrow: 479)

      leaf: 0x80006e 8388718 (8: nrow: 478 rrow: 478)

      leaf: 0x80006f 8388719 (9: nrow: 479 rrow: 479)

 

插入了1000001到1000500共500条数据后的索引结构

[ora10g@single udump]$ head -30 standby_ora_32246.trc|tail -15

*** SESSION ID:(110.43) 2011-01-26 05:24:28.188

----- begin tree dump

branch: 0x800064 8388708 (0: nrow: 4, level: 2)

   branch: 0x8018bf 8394943 (-1: nrow: 671, level: 1)     ---插入了500条数据后，最初的地址为8388709的leaf  block已经从此枝节点上被去除

      leaf: 0x800066 8388710 (-1: nrow: 479 rrow: 464)   ---插入了500条数据后，此leaf block没有被重用，仍然是464条记录

      leaf: 0x800067 8388711 (0: nrow: 479 rrow: 479)

      leaf: 0x800068 8388712 (1: nrow: 479 rrow: 479)

      leaf: 0x800069 8388713 (2: nrow: 479 rrow: 479)

      leaf: 0x80006a 8388714 (3: nrow: 478 rrow: 478)

      leaf: 0x80006b 8388715 (4: nrow: 479 rrow: 479)

      leaf: 0x80006c 8388716 (5: nrow: 479 rrow: 479)

      leaf: 0x80006d 8388717 (6: nrow: 479 rrow: 479)

      leaf: 0x80006e 8388718 (7: nrow: 478 rrow: 478)

      leaf: 0x80006f 8388719 (8: nrow: 479 rrow: 479)

      leaf: 0x800070 8388720 (9: nrow: 479 rrow: 479)

……………….

……………...

branch: 0x801eeb 8396523 (2: nrow: 215, level: 1)

      leaf: 0x801e12 8396306 (-1: nrow: 449 rrow: 449)

      leaf: 0x801e14 8396308 (0: nrow: 449 rrow: 449)

              …………

              …………

      leaf: 0x801eea 8396522 (212: nrow: 491 rrow: 491)

      leaf: 0x800065 8388709 (213: nrow: 356 rrow: 356)  ---地址为8388709的leaf  block 已经被重用，被放置到最后一个枝节点上。

----- end tree dump

 

可见，当leaf block的数据被删空后，虽然其还在枝节点上，但当有新的数据插入时，其可以得到重用，并被放置到相应的枝节点上。

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

转载于:http://blog.itpub.net/10972173/viewspace-692906/