Ora Ora Oracle电子杂志：关于索引的检验 之 5－8

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

上次对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: 0x20000a3 33554595 (-1: nrow: 120, level: 1) leaf: 0x2000004 33554436 (-1: nrow: 81) leaf: 0x2001b2c 33561388 (0: nrow: 78) leaf: 0x2000d98 33557912 (1: nrow: 72) leaf: 0x2002c34 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: 0x2001b2f 33561391 (8: nrow: 76) leaf: 0x2000d9a 33557914 (9: nrow: 79) leaf: 0x2001b30 33561392 (10: nrow: 68) : : : leaf: 0x2002c31 33565745 (116: nrow: 81) leaf: 0x2000a39 33557049 (117: nrow: 81) leaf: 0x2002c32 33565746 (118: nrow: 78) leaf: 0x200151d 33559837 (119: nrow: 77) leaf: 0x2002c33 33565747 (120: nrow: 73) leaf: 0x2000a38 33557048 (121: nrow: 70) leaf: 0x200344b 33567819 (122: nrow: 62) leaf: 0x2001b2a 33561386 (123: nrow: 70) leaf: 0x200344c 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: 0x200001a 33554458 (21: nrow: 116) leaf: 0x200001b 33554459 (22: nrow: 116) leaf: 0x200001c 33554460 (23: nrow: 116) : : Leaf: 0x2000058 33554520 (83: nrow: 116) Leaf: 0x2000059 33554521 (84: nrow: 116) Leaf: 0x200005a 33554522 (85: nrow: 24) ↑ ↑ Leaf No. 键的数量 ----- end tree dump ************************************************************* 比较扩张前和扩张后就知道， 每一个叶数据块所存的索引键变少了。 扩张前的1万笔是由小到大插入，每一个叶数据块存放了116个索引键。扩张后的100万笔不是由小到大、而是跳着插入（最后是0 → 1 → 2 ・・・ → 8 → 9的顺序），所以发生多次叶分割，导致每个叶数据块里存放的索引键变少，大约是62～81。 下次我们会对这个100万笔数据的表进行大量删除，检验结果是索引检索比全面检索更慢的奇怪现象。

　

读者问题1 我要问的是关于现在连载的索引主题，假如有索引是把日期当成键，系统会定期删除某个时点之前的数据﹝例如每个月一次把经过一年以上的数据删除﹞，索引中空数据块会增加，即使删除数据，索引空间还是会一直增加吗？要是发生上面的情况，是不是也会导致参照的索引数据块增大，使得处理时间逐渐增加？ 《回答》 您说的没错，因为空数据块增加，所以索引的空间也会扩大。对这样的索引构造进行单一检索，从根、枝、叶的顺序抵达目的键之后对索引的I/O就已经结束了。因此，已经空掉的空间除了只是“资源的浪费”之外，其实不会影响性能。问题在于范围检索。叶数据块里的索引一定是由小到大存放，如果对这样的索引构造使用“<2000年7月”之类的条件式，1年前的数据，也就是1999年7月之前，已经被删除清空的数据块也会成为access的对象，影响性能。 另外，删除的时候当然也一样，如果用了“日期<1999年7月”之类的条件式，检索的时候一样会连空的数据块都access。像这种删除大量数据的情况，建议大家每次都对索引进行REBUILD。详细回答请参考以后介绍的内容。

读者问题2 把定义为INDEX的项目以同样的值UPDATE，INDEX会变成什么样子？什么也不变吗？还是内部会发生更新，在某个地方出现影响？ 《回答》 我们根据您的问题进行检验，发现很有趣的结果。详细结果会在索引主题的最后一次向大家报告。请读者们一起想想这个问题的结果会是什么，并请期待我们的检验结果。

　

◎索引的速度失常？ 目前为止的检索结果（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 ; 索引被分析了。 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: 0x20000a3 33554595 (-1: nrow: 120, level: 1) leaf: 0x2000004 33554436 (-1: nrow: 0) leaf: 0x2001b2c 33561388 (0: nrow: 0) leaf: 0x2000d98 33557912 (1: nrow: 0) leaf: 0x2002c34 33565748 (2: nrow: 0) leaf: 0x200036b 33555307 (3: nrow: 0) leaf: 0x2001b2d 33561389 (4: nrow: 0) leaf: 0x2000d99 33557913 (5: nrow: 0) : : branch: 0x200145f 33559647 (98: nrow: 107, level: 1) leaf: 0x2000988 33556872 (-1: nrow: 0) leaf: 0x2002a7a 33565306 (0: nrow: 0) : : leaf: 0x2002a81 33565313 (16: nrow: 0) leaf: 0x200145d 33559645 (17: nrow: 0) leaf: 0x2002a82 33565314 (18: nrow: 0) leaf: 0x2000d41 33557825 (19: nrow: 0) leaf: 0x200337b 33567611 (20: nrow: 3) leaf: 0x2001a8f 33561231 (21: nrow: 7) leaf: 0x200337c 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 ************************************************************* 这就是这一次的检验，下次还会有其他检验，敬请期待。

　

读者问题1 索引的extent缩小的情况，可能是“REBUILD索引”，DELETE之后、执行数据的EXPORT－IMPORT的时候索引应该也会缩小。以前有家公司的技术人员告诉我，光是EXPORT－IMPORT不会缩小(空间不会开放)，实际情况是如何？ 《回答》 EXPORT表的时候的索引处理，不是EXPORT附在那个表上的索引，而是EXPORT有关索引的定义。IMPORT的时候会根据那些定义，进行CREATE INDEX的处理。所以结果是会释放未使用的空间。

　

上次看了对索引执行大量删除之后，索引检索反而比全面检索慢很多。这次要根据这个奇怪的检验结果，看看Oracle对空数据块的访问情况。为了说明这个奇怪结果的原因，请查看下面的INDEX_STATS的内容与TREEDUMP。 ◎TEST01（1万笔（删除99万笔））的INDEX_STATS的内容 ************************************************************* 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: 0x20000a3 33554595 (-1: nrow: 120, level: 1) leaf: 0x2000004 33554436 (-1: nrow: 0) leaf: 0x2001b2c 33561388 (0: nrow: 0) leaf: 0x2000d98 33557912 (1: nrow: 0) leaf: 0x2002c34 33565748 (2: nrow: 0) leaf: 0x200036b 33555307 (3: nrow: 0) leaf: 0x2001b2d 33561389 (4: nrow: 0) leaf: 0x2000d99 33557913 (5: nrow: 0) : : branch: 0x200145f 33559647 (98: nrow: 107, level: 1) leaf: 0x2000988 33556872 (-1: nrow: 0) leaf: 0x2002a7a 33565306 (0: nrow: 0) : : leaf: 0x2002a81 33565313 (16: nrow: 0) leaf: 0x200145d 33559645 (17: nrow: 0) leaf: 0x2002a82 33565314 (18: nrow: 0) leaf: 0x2000d41 33557825 (19: nrow: 0) leaf: 0x200337b 33567611 (20: nrow: 3) leaf: 0x2001a8f 33561231 (21: nrow: 7) leaf: 0x200337c 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 ************************************************************* 首先请大家注意LF_BLKS、BR_ROWS和BR_BLKS。虽然删除了99万笔数据，但这些值还是和有100万笔数据的时候一模一样。 ◎TEST01（100万笔）的INDEX_STATS的内容 ************************************************************* HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS --------- --------- --------- --------- --------- --------- 3 51200 1000000 13793 13792 112 ************************************************************* LF_BLKS表示实际存在的叶数据块的数量，请大家先留着印象，13793这个值和索引的I/O次数13106很接近。 对TEST01（1万笔（删除99万笔））的检索结果请看 ANALYZE之后 只要看看ANALYZE之后的检索结果的【检索1】应该就能了解，虽然条件式可以使用索引，但是对索引的I/O次数是0。这说明CBO是根据ANALYZE的讯息来判断检索的方式，分析显示检索1万笔之中的5000笔（50%），全表扫描会比索引检索的cost低。换句话说，数据库的判断是，与其一一访问存放于索引键的叶数据块，不如利用Oracle的初始设定参数「db_file_multiblock_read_count」，以几个数据块为单位(预设是8个)直接访问实际存放数据的Oracle数据块。 上面的结果是对1万笔的表50%的数据（5000笔数据）执行检索，那么如果我们把检索的范围缩得很小很小，比如说使用范围条件式，执行只返回一笔数据的SELECT语句，会发生什么情况？ 检索结果： 在上面的条件式使用索引的检索结果，会发生对索引的I/O，这是理所当然的结果。前一次检索是全体的50%，所以CBO判断全表扫描的成本比索引检索低。这次的检索不到全体的0.01%， 所以CBO判断索引检索的成本更低。 不过，大家是否注意到这次空的叶数据块也发生I/O呢？键在叶数据块里一定会由小到大存放，如果只读最前面的叶数据块的最前面的键，应该只有根（1）、枝（1）、叶（1）这3次I/O，结果竟然还是和ANALYZE之前一样对空的叶数据块发生了12413次I/O。 Access空的叶数据库的情况 从这些结果可知，CBO会根据ANALYZE的讯息，按照CBO本身的标准﹝要读取哪个数据块﹞判断要不要对照索引。可是因为判断的时候没有考虑到索引的构造，所以导致出现偏差﹝Skew﹞，或是在遇到密度低的索引时未必能得到最好的结果。以后我们会找机会深入检验这个CBO判断标准，并向大家说明。各位读者有时间的话不妨自己试着检验看看。 我们都是热爱Oracle技术的研发人员，希望能让更多人认识Oracle。这份电子杂志不刊登外界广告，但是读者的数量是我们判断电子杂志存在价值的依据。如果您也认同电子杂志的内容，请介绍给您的朋友。感谢大家的支持！

　

读者问题1 我想问关于REBUILD索引的问题。这个过程是不是根据现有的索引内容，以新的名称创建新的索引，删除旧的索引之后再把新索引的名称改为旧的名称？这种情况下，表空间的空闲空间是不是要包含容纳两个索引的大小才行？ 《回答》 您所说的没错，REBUILD不会像CREATE INDEX那样对表进行全表检索。它是根据现有的索引重建索引，所以可以快速进行处理。 不过因为含有索引的表空间上某段时间会同时存在两个索引，所以需要能容纳两个索引的空间。换句话说，需要容纳REBUILD之前的旧的索引和后来新做的索引。执行REBUILD的时候必须要有足够的空间才行。 但是，如果指定TABLESPACE，改变REBUILD的表空间的话，因为会在指定的TABLESPACE上建立新的索引，所以原来的表空间不需要容纳两个索引的空间。另外，要REBUILD的时候，既然是重新构建，最好也考虑一下INITIAL和NEXT存储参数。 另外，REBUILD执行的过程中，索引检索还是会针对旧的索引执行，所以这时候并不是全面检索。下面是REBUILD的时候，会以什么样的临时名称建立索引的检验结果。 【检验结果】 ************************************************************* ALTER INDEX TEST_IDX REBUILD ; （TEST_IDX存在于USERS表空间） ************************************************************* ----- 检索对索引执行REBUILD处理时的临时段信息 ----- ************************************************************* SELECT SEGMENT_NAME,TABLESPACE_NAME,SEGMENT_TYPE FROM USER_SEGMENTS ; SEGMENT_NAME TABLESPACE_NAME SEGMENT_TYPE ------------ --------------- ------------ 2.29198 USERS TEMPORARY ************************************************************* ---- 下面的检索显示已经将上面刚创建的索引RENAME为正式的索引名称「TEST_IDX」----- ************************************************************* SELECT HEADER_FILE,HEADER_BLOCK FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'TEST_IDX' ; HEADER_FILE HEADER_BLOCK ----------- ------------ 2 29198 ************************************************************* 由此可知，REBUILD过程中，段的头文件ID与段的Header的数据块ID会用点号组合在一起，做为REBUILD处理过程中的段名称（临时名称）。

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

转载于:http://blog.itpub.net/3898/viewspace-803894/