索引集结号 索引知识全知晓

这一篇文章的目的 是本着让大家从头到尾的了解 什么是索引 索引的结构是怎样的 我们日常涉及到的索引调优都有哪些部分，可能有遗漏 欢迎大家补充。

1.什么是CLUSTERING_FACTOR?

         越是跟row 数接近 代表 每一个索引条目中的记录 越是不在同一个block。无法通过index rebuild 来重建 需要重新组织表数据。

 

2.重建索引一定优秀吗？

考虑一下如下场景 2个有7000个block的table  random value 。2个有700个block的索引。数据完全相同

那么好  假设对这个table做select * from table 的话 那么会得到7000来个逻辑读。

如果做索引全扫描的话 那么有700个逻辑读。

接下来重整T1 索引 rebuild。T1的block变为了500个。

如果做table full scan 的话 逻辑读还是不变  如果做索引扫描的话 就是一个500 一个700~

但是 如果接下来对索引进行insert 测试的话  下面是测试的结果。

 

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        0      0.00       0.00          0          0          0           0

Execute 100000     30.23      40.23          0       1945     517923      100000

Fetch        0      0.00       0.00          0          0          0           0

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

total   100000     30.23      40.23          0       1945     517923      100000

                                                                                         

         

********************************************************************************

                                                                                         

          INSERT INTO T2 VALUES ( :B1 )                                                  

                                                

                                                                                         

         

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        0      0.00       0.00          0          0          0           0

Execute 100000     22.15      26.20          0       2209     416377      100000

Fetch        0      0.00       0.00          0          0          0           0

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

total   100000     22.15      26.20          0       2209     416377      100000

 

 

首先对整理后的索引进行插入的时候消耗了更多的CPU 。当然由于block的减少 query 列也减少了。但是 current lock 却大大增加 ，而current lock 却是最消耗资源的部分。因为跟着你的再次随机数据的插入 索引需要进行分裂 于是索引再度变得不是那么平衡了  将来你可能需要再度rebuild。

 

那么 请考虑你的应用吧  到底是只根据索引访问的多 还是插入的比较多？

 

索引分裂的中度研究：

create table t(x char(1024));

 create index ti on t(x)

  insert into t values(2);

  BEGIN

         FOR c IN 1..7

         LOOP

            insert into t values(c);

        

         END LOOP;

  END ;

 

 

 

SELECT object_id  FROM dba_objects WHERE object_name='TI'

alter session set events 'IMMEDIATE trace name treedump level 62569’; 可以以tree的方式dump 索引的tree 结构。

 

 

*** 2011-02-23 19:57:03.897

----- begin tree dump

leaf: 0x345f20a 54915594 (0: nrow: 7 rrow: 7) 类型 和RBA   -1：代表父节点，root是0  nrow 是all index entries  rrow 是current entries

----- end tree dump

 

Select to_number('0x345f20a ','xxxxxxxxx') from dual;

 

select dbms_utility.data_block_address_file(54915594) from dual;

select dbms_utility.data_block_address_block(54915594) from dual;

alter system dump datafile 13 block 389642;  

 

此时只有一个leaf。即是root 又是leaf

row#0[6997] flag: ------, lock: 2, len=1035  高亮部分是 偏移量 开始位置是6997 bytes  长度是1024 data+data length 标记(1 byte)+ row header(3Byte) + row header length标记(1 Byte)+ RowID(6 Byte)

col 0; len 1024; (1024):

 31 20 20 20 20 20 20 20…….

row#1[5962] flag: ------, lock: 2, len=1035

col 0; len 1024; (1024):

32 20 20 20 20 20 20 20 20 20 20 20 20

…

row#6[787] flag: ------, lock: 2, len=1035

col 0; len 1024; (1024):

 37 20 20 20….

至此  1个block已经装不下了 如果我们再尝试插入新的数字的话 会造成索引的分裂了 哈哈哈哈哈！要的就是这个。

  insert into t VALUES(9);

 

alter session set events 'IMMEDIATE trace name treedump level 62565';

*** 2011-02-23 19:57:31.825

----- begin tree dump

branch: 0x345f20a 54915594 (0: nrow: 2, level: 1)  这里已经产生分裂了 原来的root leaf 变成了brach  他里面所有的数据都一动到了 下一个block(0x345f20b)中去了 因为 如果root节点变动 是需要修改数据字典的。

   leaf: 0x345f20b 54915595 (-1: nrow: 7 rrow: 7)  这里应该记录数不变 还是1-7

   leaf: 0x345f20c 54915596 (0: nrow: 1 rrow: 1) 这里应该是9  我们来验证一下

----- end tree dump

 

Select to_number('345f20c','xxxxxxxxx') from dual;

select dbms_utility.data_block_address_file(54915596) from dual;

select dbms_utility.data_block_address_block(54915596) from dual;

alter system dump datafile 13 block 389644;  

 

~ row#0[6997] flag: ------, lock: 2, len=1035

col 0; len 1024; (1024):

39 20 ……

 

只有1条  39    这个就是9了。

 

那么接下来 我们再插入一条

    insert into t values(10);

alter session set events 'IMMEDIATE trace name treedump level 62569';

----- begin tree dump

branch: 0x345f20a 54915594 (0: nrow: 3, level: 1)

   leaf: 0x345f20b 54915595 (-1: nrow: 6 rrow: 6)

   leaf: 0x345f20d 54915597 (0: nrow: 2 rrow: 2)

   leaf: 0x345f20c 54915596 (1: nrow: 1 rrow: 1)

----- end tree dump

 

居然再度分裂了！！！原来  新插入的10 是大于1 小于2 的 （CHAR 的排序） 。 所以 再度分裂了索引。对比一下上面   得到0x345f20c block 未发生改变 而0x345f20b本来有7个 现在变成了6个 而0x345f20d这个新多出来的是2个 那么我们来看看这2个是什么?

 

Select to_number('345f20d','xxxxxxxxx') from dual;

select dbms_utility.data_block_address_block(54915597) from dual;

alter system dump datafile 13 block 389645;  

是7和6  。这两个家伙被分裂出来了。

row#0[5962] flag: ------, lock: 0, len=1035

col 0; len 1024; (1024): 

 36 20 20 20 …..

row#1[6997] flag: ------, lock: 0, len=1035

col 0; len 1024; (1024):

37 20 20 20 20 20 20 20 20…….

 

而原来的block里的结构则发生了重组。这个时候原block可以容纳一条新数据进去了 因为已经分裂出了2条记录（一条新 一条旧）。

 

分裂有2个原则 就是对一个block中的数据进行左右分裂 右侧分裂出去10%左右“leaf node 90-10 splits”或者直接分裂 “5-5 leaf node splits”

这一次很明显是直接分裂通过统计信息可以得到select * from v$sysstat where name like '%split%'；

 

说话的过程中我又做了2次插入。第一次11 未发生分裂，12的时候又发生分裂了。

新的block

----- begin tree dump

branch: 0x345f20a 54915594 (0: nrow: 4, level: 1)

   leaf: 0x345f20b 54915595 (-1: nrow: 6 rrow: 6)

   leaf: 0x345f20e 54915598 (0: nrow: 2 rrow: 2)

   leaf: 0x345f20d 54915597 (1: nrow: 2 rrow: 2)

   leaf: 0x345f20c 54915596 (2: nrow: 1 rrow: 1)

----- end tree dump                                                                                                                                                                                                                     

里面包含的是4 5.。

 

下面介绍分裂的过程：

         5-5  leaf节点分裂

首先分配一个new  index block 。

将需要分裂的block中低值的一半留在块中，（根据上面的实验应该是 如果下刀的部分在一条记录的中间 那么 这条记录也被保留下来了。索引我们7条记录最多分裂出去3条）

另一半则放入新的block中

将新的插入值放在合适的block中（不一定是在旧块哦，比如 1-7 的时候如果我插入61 的话 恐怕新block中 就是 61,和7 这两个值了）

更新旧block的next leaf block pointer(kdxlenxt) to这个新block;

把旧的原next leaf block的previous leaf block pointer 指向新的block

就像双向链表一样的原理

更新branch block  添加一条新的指针到这个新的block。

5-5 branch节点分裂

原理同leaf 分裂

         Root 5-5 分裂

这次首先是分配两个块 然后还是从中间切开 一边一半 分配给这两个新块  然后原来的root块清空  插入这两个新branch的位置 。

这样索引树就多了一层。

         90-10 分裂

如果最新插入的索引条目是将要插入的block中最大的一条的话 那么只会将这一条分裂到另外一块。

 

===================index空间的占用情况===========================

Index 删除的条目是否会得到重用？

答案是 YES。

CREATE TABLE t1 (a CHAR(1024));

CREATE INDEX t1_idx ON t1 (a);

 

 

INSERT INTO t1 values(1);

INSERT INTO t1 values(2);

INSERT INTO t1 values(3);

INSERT INTO t1 values(4);

INSERT INTO t1 values(5);

INSERT INTO t1 values(6);

INSERT INTO t1 values(7);

INSERT INTO t1 values(9);

 

 

SQL> analyze index T1_IDX validate STRUCTURE;

 

Index analyzed.

 

SQL> select HEIGHT,LF_BLKS,LF_ROWS,DEL_LF_ROWS from index_stats;

 

    HEIGHT    LF_BLKS    LF_ROWS DEL_LF_ROWS   现在有2个block

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

         2          2          8           0

 

SQL> DELETE FROM t1 WHERE a=4;  删除一个

 

1 row deleted.

 

SQL> commit;

 

Commit complete.

 

SQL> analyze index T1_IDX validate STRUCTURE;

 

Index analyzed.

 

SQL>  select HEIGHT,LF_BLKS,LF_ROWS,DEL_LF_ROWS from index_stats;

 

    HEIGHT    LF_BLKS    LF_ROWS DEL_LF_ROWS

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

         2          2          8           1    ---这里有一个被删除的

 

SQL> insert into t1 values (99);  插入一个99   应该插入第二个 block

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> analyze index T1_IDX validate STRUCTURE;  

 

Index analyzed.

 

SQL> select HEIGHT,LF_BLKS,LF_ROWS,DEL_LF_ROWS from index_stats;  发现删除的条目依然存在

 

    HEIGHT    LF_BLKS    LF_ROWS DEL_LF_ROWS

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

         2          2          9           1

 

SQL> insert into t1 values(11);  插入一条记录   这个会插入第一个block

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> analyze index T1_IDX validate STRUCTURE;

 

Index analyzed.

 

SQL> select HEIGHT,LF_BLKS,LF_ROWS,DEL_LF_ROWS from index_stats;

 

    HEIGHT    LF_BLKS    LF_ROWS DEL_LF_ROWS

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

         2          2          9           0   --这时  消失了。

 

结论：当插入的entry 目的地是有删除条目的block时  删除的条目会被全部清空。

 

##当发生延迟块清除时  删除的条目也会被清空。具体请参见TOM的延迟块清除 一个道理。

 

 

但是索引空间依旧会被某些因素狼浪费：

1.PCT FREE 过高

2.收缩表

3.单向的增删索引列数据

4.频繁的删除或更新索引

5.索引列长度太大

 

关于索引rebuild 的一些趣事：

1.Rebuild的要注意PCT_FREE 否则会造成索引分裂

2.如果PCT_USED 蛮高的话 rebuild 不会减少索引的level

重建索引的条件：

         1.free space 超过50%

         2.索引一定要小才可以的（大部分情况下 小不了  也不差多那一两块）

         3. Coalesce 也可以达到收缩空间的目的只是不会去减少高度。

 

Coalesce 和rebuild的区别

举两个例子：1.1-100000条记录 我删除了前 10000条 后面的90%都是不需要rebuild的 以后的插入 也都是越来越大 全部都是在树的右侧，此时 只需要收缩删除的 左侧的10%的空间即可  那么适合用coalesce

2.还是100000条记录 我每100条删除1条 还是删除1万条。这个时候coalesce 就傻了  他需要merge 一块 删除一块再merge  嘿嘿……

 

----- begin tree dump

branch: 0x18a66aa 25847466 (0: nrow: 112, level: 1)

   leaf: 0x18a66ab 25847467 (-1: nrow: 240 rrow: 240)

   leaf: 0x18a66b3 25847475 (0: nrow: 460 rrow: 460)

   leaf: 0x107df45 17293125 (1: nrow: 254 rrow: 254)

   leaf: 0x149f782 21624706 (2: nrow: 284 rrow: 284)

   leaf: 0x149f781 21624705 (3: nrow: 323 rrow: 323)

   leaf: 0x107df48 17293128 (4: nrow: 232 rrow: 232)

   leaf: 0x107df47 17293127 (5: nrow: 231 rrow: 231)

   leaf: 0x107df46 17293126 (6: nrow: 231 rrow: 231)

   leaf: 0x345ecf8 54914296 (7: nrow: 248 rrow: 248)

   leaf: 0x107df44 17293124 (8: nrow: 231 rrow: 231)

   leaf: 0x107df43 17293123 (9: nrow: 231 rrow: 231)

   leaf: 0x18a66c8 25847496 (10: nrow: 278 rrow: 278)

   leaf: 0x107df41 17293121 (11: nrow: 387 rrow: 387)

   leaf: 0x107df42 17293122 (12: nrow: 231 rrow: 231)

   leaf: 0x18a66c6 25847494 (13: nrow: 319 rrow: 319)

   leaf: 0x18a66c4 25847492 (14: nrow: 233 rrow: 233)

   leaf: 0x1c9d411 30004241 (15: nrow: 240 rrow: 240)

   leaf: 0x1c9d412 30004242 (16: nrow: 240 rrow: 240)

   leaf: 0x345ecf3 54914291 (17: nrow: 420 rrow: 420)

   leaf: 0x345ecf2 54914290 (18: nrow: 327 rrow: 327)

   leaf: 0x1c9d413 30004243 (19: nrow: 240 rrow: 240)

   leaf: 0x1c9d414 30004244 (20: nrow: 233 rrow: 233)

   leaf: 0x1c9d415 30004245 (21: nrow: 240 rrow: 240)

   leaf: 0x1c9d416 30004246 (22: nrow: 240 rrow: 240)

类型 和RBA   -1：代表父节点，root是0  nrow 是all index entries  rrow 是current entries

 

 

Select to_number('18a66aa','xxxxxxxxxxxxxxxxxxxxxxx') from dual;

select dbms_utility.data_block_address_file(25847466) from dual;

select dbms_utility.data_block_address_block(25847466) from dual;

6 684162

 

alter system dump datafile 6 block 681462;  

Block header dump:  0x018a66aa

Object id on Block? Y

 seg/obj: 0xf45e  csc: 0x01.99eec7bf  itc: 1  flg: -  typ: 2 - INDEX

     fsl: 0  fnx: 0x0 ver: 0x01

 

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0009.009.0001bfa5  0x00813342.0b1f.03  C---    0  scn 0x0001.99eec7bf

 

Branch block dump

=================

header address 182924018244=0x2a971f0244

kdxcolev 1                                                   #index level

KDXCOLEV Flags = - - -

kdxcolok 0                                                       # denotes whether structural block transaction is occurring

kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y # internal operation code

kdxconco 2                                                     # index column count    why 2?

kdxcosdc 1                                                     # count of index structural changes involving block

kdxconro 111                                               #number of block entries (不包括kdxbrlmc指针)

kdxcofbo 250=0xfa                                   #offset to beginning of free space within block

kdxcofeo 7003=0x1b5b                              # offset to the end of free space （第一个开始有数据的地方）

kdxcoavs 6753                                              # available space in block

kdxbrlmc 25847467=0x18a66ab              # block address if index value is less than the first (row#0) value

kdxbrsno 2                                                     # last index entry to be modified

kdxbrbksz 8056                                        # size of usable block space

kdxbr2urrc 0

row#0[7695] dba: 25847475=0x18a66b3  Row number (starting at 0) [starting location in block] dba

col 0; len 4; (4):  3c 5f 51 25   column numbe ; column length :column value

col 1; TERM

row#1[7051] dba: 17293125=0x107df45

col 0; len 4; (4):  3c 60 14 1e

col 1; TERM

row#2[7003] dba: 21624706=0x149f782

col 0; len 3; (3):  3c 60 30

col 1; TERM

row#3[7012] dba: 21624705=0x149f781

col 0; len 4; (4):  3c 60 35 2e

col 1; TERM

 

///############### 由于有些属性branch 里没有 所以下面补充leaf 的一份

Block header dump:  0x018a65f6

Object id on Block? Y

seg/obj: 0x375d  csc: 0x00.b7fe6  itc: 2  flg: -  typ: 2 - INDEX

     fsl: 0  fnx: 0x0 ver: 0x01

    

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

0x02   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.000b7fe6

 

Leaf block dump

===============

header address 182924018268=0x2a971f025c

kdxcolev 0   #index level

KDXCOLEV Flags = - - -

kdxcolok 0  # denotes whether structural block transaction is occurring

kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y      # internal operation code

kdxconco 2       # index column count    why 2?

kdxcosdc 0     # count of index structural changes involving block

kdxconro 448    #number of block entries

kdxcofbo 932=0x3a4

kdxcofeo 1761=0x6e1

kdxcoavs 829

kdxlespl 0 #bytes of uncommitted data at time of block split that have been cleaned out

kdxlende 0 # number of deleted entries

kdxlenxt 25847287=0x18a65f7 # 下一个leaf 的RBA

kdxleprv 25847285=0x18a65f5 # 上一个leaf 的RBA （root里是没有的）

kdxledsz 0# usable block space (by default less than branch due to the additional ITL entry)

kdxlebksz 8032

row#0[8018] flag: ------, lock: 0, len=14    row number (starting at 0) followed by [starting location within block ] followed by various flags (locking information, deletion flag etc.)

col 0; len 4; (4):  c3 2c 2e 25

col 1; len 6; (6):  01 8a 24 fd 00 56

row#1[8004] flag: ------, lock: 0, len=14

col 0; len 4; (4):  c3 2c 2e 25

col 1; len 6; (6):  01 8a 24 fd 00 57

row#2[7990] flag: ------, lock: 0, len=14

//################################################

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

转载于:http://blog.itpub.net/21818314/viewspace-693204/