ITPub博客

首页 > 数据库 > Oracle > Ora Ora Oracle电子杂志:关于索引的检验 之 5-8

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

原创 Oracle 作者:yaanzy 时间:2005-08-05 10:32:01 0 删除 编辑
上次对1万笔数据的表进行了1~5000笔(一半)的范围检索,结果对索引发生了45次I/O。这次我们要以TREEDUMP功能确认结果。[@more@]
<关于索引的检验 之5>
上次对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会变成什么样子?什么也不变吗?还是内部会发生更新,在某个地方出现影响?

《回答》
我们根据您的问题进行检验,发现很有趣的结果。详细结果会在索引主题的最后一次向大家报告。请读者们一起想想这个问题的结果会是什么,并请期待我们的检验结果。

 

<关于索引的检验 之6>
◎索引的速度失常?
目前为止的检索结果(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的处理。所以结果是会释放未使用的空间。

 

<关于索引的检验 之7>
上次看了对索引执行大量删除之后,索引检索反而比全面检索慢很多。这次要根据这个奇怪的检验结果,看看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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    108
  • 访问量
    759601