Oracle热爱者的天地

　

　

昆鹏数据恢复中心——技术引导未来，服务创造价值！
关于索引的检验 　之三

 

上次对 1 万笔数据的表进行了 1 ～ 5000 笔（一半）的范围检索，结果对索引发生了 45 次 I/O 。这次我们要以 TREEDUMP 功能确认结果。

◎ 扩张到 100 万笔的过程说明
1. 现有的 10000010 ～ 10100000 的 1 万笔，进一步插入 10100010 ～ 11000000 的 9 万笔（间隔 10 ），扩张成总计 10 万笔的表（末尾都是 0 ）。
2. 插入 10000001 ～ 10999991 的 10 万笔（间隔 10 ），扩张为总计 20 万笔数据的表（末尾都是 1 ）。
3. 之后，依序插入末尾是 2 ～ 9 的各 10 万笔数据（间隔 10 ），扩张为 10000001 ～ 11000000 总计 100 万笔数据的表。

看起来好像很麻烦，总之为了让叶分割经常发生，必须在既有的键与键之间插入新的键，至于扩张的过程本身并没有什么重要意义。与其直接由小到大按照顺序创建有 100 万笔数据的表，不如多进行叶分割，让大家注意到叶数据块的数量很多。

简单说明检索结果：【检索 1 】和【检索 2 】都是在 100 万笔的正中央的第 50 万笔的值 10500000 进行单一检索 (unique search) 。【检索 3 】和【检索 4 】是和 1 万笔的时候一样从 1 ～ 5000 笔进行范围检索。

这个检索结果和 1 万笔的时候不同的是，【检索 1 】的索引的 I/O 次数从 2 增加到 3 。这是因为数据增加到 100 万笔所以 HEIGHT （枝节点的阶层数）增加 1 （请参考下面的 INDEX_STATS 的检索结果的 HEIGHT ），检索一万笔之中的第 5000 笔和检索 100 万笔中的 第 50 万笔，只是 access 的数据块多了一个，不会影响到性能。

此外，【检索 3 】的索引的 I/O 次数和 1 万笔的时候从 1 ～ 5000 笔的范围检索，却从 45 增加到 72 ，只要看下面的 TREEDUMP 就能了解，反复叶分割的结果是每 1 叶数据块的 ROWID 的密度变低。至于其他的值，因为从 1 万笔增加到 100 万笔，所以 I/O 次数增加是理所当然的。

◎TEST01 （ 100 万笔）的 INDEX_STATS 的内容

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

ANALYZE INDEX TEST01 VALIDATE STRUCTURE ;

 

索引被分析了。

 

SELECT HEIGHT,BLOCKS,LF_ROWS,LF_BLKS,BR_ROWS,BR_BLKS FROM INDEX_STATS ;

 

HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS

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

3 51200 1000000 13793 13792 112

└→ 扩张到 100 万笔所以枝节点的阶层数增加

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

◎TEST01 （ 100 万笔）的 TREEDUMP 的内容（扩张后）

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

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME TREEDUMP LEVEL 3539' ;

 

----- begin tree dump

branch: 0x2000003 33554435 (0: nrow: 111, level: 2)

branch: 0x 20000a 3 33554595 (-1: nrow: 120, level: 1)

leaf: 0x2000004 33554436 (-1: nrow: 81)

leaf: 0x2001b 2c 33561388 (0: nrow: 78)

leaf: 0x2000d98 33557912 (1: nrow: 72)

leaf: 0x 2002c 34 33565748 (2: nrow: 68)

leaf: 0x200036b 33555307 (3: nrow: 77)

leaf: 0x2001b2d 33561389 (4: nrow: 76)

leaf: 0x2000d99 33557913 (5: nrow: 79)

leaf: 0x2001b2e 33561390 (6: nrow: 68)

leaf: 0x2000369 33555305 (7: nrow: 77)

leaf: 0x2001b 2f 33561391 (8: nrow: 76)

leaf: 0x2000d 9a 33557914 (9: nrow: 79)

leaf: 0x2001b30 33561392 (10: nrow: 68)

:

:

:

leaf: 0x 2002c 31 33565745 (116: nrow: 81)

leaf: 0x 2000a 39 33557049 (117: nrow: 81)

leaf: 0x 2002c 32 33565746 (118: nrow: 78)

leaf: 0x200151d 33559837 (119: nrow: 77)

leaf: 0x 2002c 33 33565747 (120: nrow: 73)

leaf: 0x 2000a 38 33557048 (121: nrow: 70)

leaf: 0x200344b 33567819 (122: nrow: 62)

leaf: 0x2001b 2a 33561386 (123: nrow: 70)

leaf: 0x 200344c 33567820 (124: nrow: 68)

leaf: 0x2000d97 33557911 (125: nrow: 70)

leaf: 0x200344d 33567821 (126: nrow: 62)

leaf: 0x2001b2b 33561387 (127: nrow: 70)

leaf: 0x200344e 33567822 (128: nrow: 68)

leaf: 0x2000368 33555304 (129: nrow: 71)

----- end tree dump

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

◎TEST01 （ 100 万笔）的 TREEDUMP 的内容（扩张后）

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

----- begin tree dump

branch: 0x5800003 92274691 (0: nrow: 87, level: 1)

leaf: 0x2000004 33554436 (-1: nrow: 116)

leaf: 0x2000005 33554437 (0: nrow: 116)

leaf: 0x2000006 33554438 (1: nrow: 116)

leaf: 0x2000007 33554439 (2: nrow: 116)

leaf: 0x2000008 33554440 (3: nrow: 116)

:

:

leaf: 0x2000018 33554456 (19: nrow: 116)

leaf: 0x2000019 33554457 (20: nrow: 116)

leaf: 0x 200001a 33554458 (21: nrow: 116)

leaf: 0x200001b 33554459 (22: nrow: 116)

leaf: 0x 200001c 33554460 (23: nrow: 116)

:

:

Leaf: 0x2000058 33554520 (83: nrow: 116)

Leaf: 0x2000059 33554521 (84: nrow: 116)

Leaf: 0x 200005a 33554522 (85: nrow: 24)

↑ ↑

Leaf No. 键的数量

----- end tree dump

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

比较扩张前和扩张后就知道， 每一个叶数据块所存的索引键变少了。

扩张前的 1 万笔是由小到大插入，每一个叶数据块存放了 116 个索引键。扩张后的 100 万笔不是由小到大、而是跳着插入（最后是 0 → 1 → 2 ??? → 8 → 9 的顺序），所以发生多次叶分割，导致每个叶数据块里存放的索引键变少，大约是 62 ～ 81 。

下次我们会对这个 100 万笔数据的表进行大量删除，检验结果是索引检索比全面检索更慢的奇怪现象。

◎ 索引的速度失常？
目前为止的检索结果（ 1 万笔和 100 万笔）都是大家可以预料的结果。不过这次不是插入数据，而是进行大量数据删除，结果竟然出现 “ 全面检索比索引检索快很多 ” 的奇怪效果。

删除后的索引构造（ B-Tree ）的图

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

SELECT OBJECT_NAME,

OBJECT_ID

>FROM USER_OBJECTS WHERE OBJECT_TYPE = 'INDEX' ;

 

OBJECT_NAME OBJECT_ID

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

ID_EMP 3461

PK_DEPT 1924

PK_EMP 1926

TEST01 3539

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

接下来，指定 OBJECT_ID 取得 TREEDUMP 。

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

ALTER SESSION SET EVENTS

'IMMEDIATE TRACE NAME TREEDUMP LEVEL 3539' ;

└→ 分配给 TEST01 的 OBJECT_ID

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

此外， TREEDUMP 的输出目的地是数据库初始化参数 user_dump_dest 指定的目录位置，文件名为 ora_xxxx.trc ，其中的最新版本即是当前运行命令后生成的。

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

【 TREEDUMP 】

----- begin tree dump

branch: 0x5800003 92274691 (0: nrow: 87, level: 1) (1)

leaf: 0x2000004 33554436 (-1: nrow: 116) ↑

leaf: 0x2000005 33554437 (0: nrow: 116) ｜

leaf: 0x2000006 33554438 (1: nrow: 116) ｜

leaf: 0x2000007 33554439 (2: nrow: 116) ｜

leaf: 0x2000008 33554440 (3: nrow: 116) ｜

: ｜

: ｜

leaf: 0x2000018 33554456 (19: nrow: 116) ｜

leaf: 0x2000019 33554457 (20: nrow: 116) ｜

leaf: 0x 200001a 33554458 (21: nrow: 116) ｜

leaf: 0x200001b 33554459 (22: nrow: 116) ｜

leaf: 0x 200001c 33554460 (23: nrow: 116) ｜

leaf: 0x200001d 33554461 (24: nrow: 116) ｜ (3)

leaf: 0x200001e 33554462 (25: nrow: 116) ｜

leaf: 0x 200001f 33554463 (26: nrow: 116) ｜

leaf: 0x2000020 33554464 (27: nrow: 116) ｜

leaf: 0x2000021 33554465 (28: nrow: 116) ｜

leaf: 0x2000022 33554466 (29: nrow: 116) ｜

leaf: 0x2000023 33554467 (30: nrow: 116) ｜

leaf: 0x2000024 33554468 (31: nrow: 116) ｜

leaf: 0x2000025 33554469 (32: nrow: 116) ｜

leaf: 0x2000026 33554470 (33: nrow: 116) ｜

: ｜

: ｜

leaf: 0x200002d 33554477 (40: nrow: 116) ｜

leaf: 0x200002e 33554478 (41: nrow: 116) ｜

leaf: 0x 200002f 33554479 (42: nrow: 116) (2)

leaf: 0x2000030 33554480 (43: nrow: 116)

leaf: 0x2000031 33554481 (44: nrow: 116)

leaf: 0x2000032 33554482 (45: nrow: 116)

leaf: 0x2000033 33554483 (46: nrow: 116)

leaf: 0x2000034 33554484 (47: nrow: 116)

:

:

leaf: 0x2000058 33554520 (83: nrow: 116)

leaf: 0x2000059 33554521 (84: nrow: 116)

leaf: 0x 200005a 33554522 (85: nrow: 24)

↑ ↑

Leaf No. 键的数量

----- end tree dump

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

(1) 读取枝数据块，求出存放了第 5000 笔的值 10050000 的 DBA 。
(2) 读取存放了第 5000 笔的值 10050000 的叶数据块的号码 42 。
(3) 其后的数据块都符合条件式（ EMPNO01 <= 10050000 ），所以会读取叶资料块号码 42 ～ － 1 的数据块。

◎ 检索的过程如图 ：

下次我们会让表的数据增加到 100 万件，看看出现多次叶分割的情况。

◎ 索引的速度失常？
目前为止的检索结果（ 1 万笔和 100 万笔）都是大家可以预料的结果。不过这次不是插入数据，而是进行大量数据删除，结果竟然出现 “ 全面检索比索引检索快很多 ” 的奇怪效果。

删除后的索引构造（ B-Tree ）的图

这个图的表 TEST01 的记录是从 1 万笔记录增加到 100 万笔记录，再删除到剩下 1 万笔记录。请大家注意即使删除到剩下 1 万笔记录，而剩下的这 1 万笔数据和在没有增加到 100 万笔以前的 1 万笔数据并不相同。请回想从 1 万笔记录增加到 100 万笔记录的过程（按末尾为 0 → 1 → 2 ～ 8 → 9 的顺序插入）。这次的删除按照插入的顺序删除末尾 0 ～ 8 的数据，然后剩下结尾为 9 的 10 万笔资料，再删除以 9 为结尾的 9 万笔数据，剩下 1 万笔记录。

删除后的情况简单画成下面的图。

对于这个又缩回 1 万笔数据的表 TEST01 ，进行 1 ～ 5000 笔的范围检索，结果如下。

 

请大家特别注意这个检索执行结果，在不用索引情况下的检索花了 23 秒，有索引的情况下检索花费的时间是没有索引检索的 3 倍。不管之前发生多少次叶分割，刚开始的没有进行删除的情况下 1 万笔的索引的 I/O 次数是 45 次，现在是在增加到 100 万笔数据再删除到剩下的 1 万笔数据的情况下 I/O 是 13106 次，大约差 300 倍，实在很奇怪。下次我们会针对这个奇怪的现象，根据检验结果看看 Oracle 对空数据块 access 的情况。

下面是检验结果，有兴趣的读者可以先自己推测，下次再和我们的内容互相对照。

◎TEST01 （ 1 万笔（删除 99 万笔））的 INDEX_STATS 的内容

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

ANALYZE INDEX TEST01 VALIDATE STRUCTURE ;

 

索引被分析了。

 

ANALYZE INDEX TEST01 VALIDATE STRUCTURE ;

 

索引被分析了。

 

SELECT HEIGHT,BLOCKS,LF_ROWS,LF_BLKS,BR_ROWS,BR_BLKS FROM INDEX_STATS ;

 

HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS

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

3 51200 10000 13793 13792 112

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

◎TEST01 （ 1 万笔（删除 99 万笔））的 TREEDUMP 的内容（减少后）

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

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME TREEDUMP LEVEL 3539' ;

 

----- begin tree dump

branch: 0x2000003 33554435 (0: nrow: 111, level: 2)

branch: 0x 20000a 3 33554595 (-1: nrow: 120, level: 1)

leaf: 0x2000004 33554436 (-1: nrow: 0)

leaf: 0x2001b 2c 33561388 (0: nrow: 0)

leaf: 0x2000d98 33557912 (1: nrow: 0)

leaf: 0x 2002c 34 33565748 (2: nrow: 0)

leaf: 0x200036b 33555307 (3: nrow: 0)

leaf: 0x2001b2d 33561389 (4: nrow: 0)

leaf: 0x2000d99 33557913 (5: nrow: 0)

:

:

branch: 0x 200145f 33559647 (98: nrow: 107, level: 1)

leaf: 0x2000988 33556872 (-1: nrow: 0)

leaf: 0x 2002a 7a 33565306 (0: nrow: 0)

:

:

leaf: 0x 2002a 81 33565313 (16: nrow: 0)

leaf: 0x200145d 33559645 (17: nrow: 0)

leaf: 0x 2002a 82 33565314 (18: nrow: 0)

leaf: 0x2000d41 33557825 (19: nrow: 0)

leaf: 0x200337b 33567611 (20: nrow: 3)

leaf: 0x 2001a 8f 33561231 (21: nrow: 7)

leaf: 0x 200337c 33567612 (22: nrow: 7)

:

:

leaf: 0x2000d97 33557911 (125: nrow: 7)

leaf: 0x200344d 33567821 (126: nrow: 6)

leaf: 0x2001b2b 33561387 (127: nrow: 7)

leaf: 0x200344e 33567822 (128: nrow: 7)

leaf: 0x2000368 33555304 (129: nrow: 7)

----- end tree dump

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

这就是这一次的检验，下次还会有其他检验，敬请期待。

关于问题专区

我们很乐意回答大家的问题，如果您对于 Oracle 性能有任何疑问，只要我们能给出对您有价值的情报，一定会与您分享。欢迎大家尽量发问，我们会在今后的内容中回答您的问题！请您把问题发送到这里 。QQ:9417901

　

昆鹏数据恢复中心介绍

昆鹏数据恢复中心于 2004 年创立于沈阳浑河北岸的三好街高新技术产业区里，业务遍布中国。集项目研究、应用、实施、销售、服务于一体，为客户提供管理咨询、大型应用系统的性能优化、软件研发等一体化技术服务。昆鹏数据恢复中心由资深软件行业专家、优秀的技术开发队伍和具备先进理念的管理团队形成核心力量，具有丰富的项目管理经验和雄厚的技术实力。

昆鹏数据恢复中心服务内容                                         

磁盘阵列数据恢复

Oracle数据库恢复