ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 教你如何成为Oracle 10g OCP - 第九章 对象管理(9) - 重建索引对性能的影响

教你如何成为Oracle 10g OCP - 第九章 对象管理(9) - 重建索引对性能的影响

原创 Linux操作系统 作者:tolywang 时间:2011-02-17 17:47:18 0 删除 编辑


9.2.5.  重建B树索引对于查询性能的影响

      最后我们来看一下重建索引对于性能的提高到底会有什么作用。假设
我们有一个表,该表具有1百万条记录,占用了100000个数据块。而在该表上
存在一个索引,在重建之前的pct_used为50%,高度为3,分支节点块数为40个,
再加一个根节点块,叶子节点数为10000个;重建该索引以后,pct_used为90%,
高度为3,分支节点块数下降到20个,再加一个根节点块,而叶子节点数下降
到5000个。
那么从理论上说:

1)如果通过索引获取单独1条记录来说:
重建之前的成本:1个根+1个分支+1个叶子+1个表块=4个逻辑读
重建之后的成本:1个根+1个分支+1个叶子+1个表块=4个逻辑读
性能提高百分比:0

2)如果通过索引获取100条记录(占总记录数的0.01%)来说,分两种情况:
最差的clustering_factor(即该值等于表的数据行数):
重建之前的成本:1个根+1个分支+0.0001*10000(1个叶子)+100个表块=103个逻辑读
重建之后的成本:1个根+1个分支+0.0001*5000(1个叶子)+100个表块=102.5个逻辑读
性能提高百分比:0.5%(也就是减少了0.5个逻辑读)

最好clustering_factor(即该值等于表的数据块):
重建之前的成本:1个根+1个分支+0.0001*10000(1个叶子)+0.0001*100000(10个表块)=13个逻辑读
重建之后的成本:1个根+1个分支+0.0001*5000(1个叶子)+0.0001*100000(10个表块)=12.5个逻辑读
性能提高百分比:3.8%(也就是减少了0.5个逻辑读)

3)如果通过索引获取10000条记录(占总记录数的1%)来说,分两种情况:

最差的clustering_factor(即该值等于表的数据行数):
重建之前的成本:1个根+1个分支+0.01*10000(100个叶子)+10000个表块=10102个逻辑读
重建之后的成本:1个根+1个分支+0.01*5000(50个叶子)+10000个表块=10052个逻辑读
性能提高百分比:0.5%(也就是减少了50个逻辑读)

最好clustering_factor(即该值等于表的数据块):
重建之前的成本:1个根+1个分支+0.01*10000(100个叶子)+0.01*100000(1000个表块)=1102个逻辑读
重建之后的成本:1个根+1个分支+0.01*5000(50个叶子)+0.01*100000(1000个表块)=1052个逻辑读
性能提高百分比:4.5%(也就是减少了50个逻辑读)

4)如果通过索引获取100000条记录(占总记录数的10%)来说,分两种情况:

最差的clustering_factor(即该值等于表的数据行数):
重建之前的成本:1个根+1个分支+0.1*10000(1000个叶子)+100000个表块=101002个逻辑读
重建之后的成本:1个根+1个分支+0.1*5000(500个叶子)+100000个表块=100502个逻辑读
性能提高百分比:0.5%(也就是减少了500个逻辑读)

最好clustering_factor(即该值等于表的数据块):
重建之前的成本:1个根+1个分支+0.1*10000(1000个叶子)+0.1*100000(10000个表块)=11002个逻辑读
重建之后的成本:1个根+1个分支+0.1*5000(500个叶子)+0.1*100000(10000个表块)=10502个逻辑读
性能提高百分比:4.5%(也就是减少了500个逻辑读)

5)对于快速全索引扫描来说,假设每次获取8个数据块:

重建之前的成本:(1个根+40个分支+10000个叶子)/ 8=1256个逻辑读
重建之后的成本:(1个根+40个分支+5000个叶子)/ 8=631个逻辑读
性能提高百分比:49.8%(也就是减少了625个逻辑读)


      从上面有关性能提高的理论描述可以看出,对于通过索引获取的记录
行数不大的情况下,索引碎片对于性能的影响非常小;当通过索引获取较大
的记录行数时,索引碎片的增加可能导致对于索引逻辑读的增加,但是索引
读与表读的比例保持不变;同时,我们从中可以看到,clustering_factor对
于索引读取的性能有很大的影响,并且对于索引碎片所带来的影响具有很大的
作用;最后,看起来,索引碎片似乎对于快速全索引扫描具有最大的影响。

      我们来看两个实际的例子,分别是clustering_factor为最好和最差的
两个例子。测试环境为8KB的数据块,表空间采用ASSM的管理方式。先做一个
最好的clustering_factor的例子,创建测试表并填充1百万条数据。

SQL> create table rebuild_test(id number,name varchar2(10));

SQL> begin
 2    for i in 1..1000000 loop
 3        insert into rebuild_test values(i,to_char(i));
 4            if mod(i,10000)=0 then
 5                commit;
 6            end if;
 7    end loop;
 8 end;
 9 /

   该表具有1百万条记录,分布在2328个数据块中。同时由于我们的数
据都是按照顺序递增插入的,所以可以知道,在id列上创建的索引都是
具有最好的clustering_factor值的。我们运行以下查询测试语句,分别
返回1、100、1000、10000、50000、100000以及1000000条记录。

select * from rebuild_test where id = 10;
select * from rebuild_test where id between 100 and 199;
select * from rebuild_test where id between 1000 and 1999;
select * from rebuild_test where id between 10000 and 19999;
select /*+ index(rebuild_test) */ * from rebuild_test where id between 50000 and 99999;
select /*+ index(rebuild_test) */ * from rebuild_test where id between 100000 and 199999;
select /*+ index(rebuild_test) */ * from rebuild_test where id between 1 and 1000000;
select /*+ index_ffs(rebuild_test) */ id from rebuild_test where id between 1 and 1000000;

在运行这些测试语句前,先创建一个pctfree为50%的索引,来模拟索引碎
片,分析并记录索引信息。

SQL> create index idx_rebuild_test on rebuild_test(id) pctfree 50;
SQL> exec dbms_stats.gather_table_stats(user,'rebuild_test',cascade=>true);

然后运行测试语句,记录每条查询语句所需的时间;接下来以pctfree为10%
重建索引,来模拟修复索引碎片,分析并记录索引信息。

SQL> alter index idx_rebuild_test rebuild pctfree 10;
SQL> exec dbms_stats.gather_table_stats(user,'rebuild_test',cascade=>true);

接着再次运行这些测试语句,记录每条查询语句所需的时间。下表显示了两个索引信息的对比情况。

pctfree
 Height
 blocks
 br_blks
 lf_blks
 pct_used
 clustering_factor
 
50%
 3
 4224
 8
 4096
 49%
 2326
 
10%
 3
 2304
 5
 2226
 90%
 2326
 

下表显示了不同的索引下,运行测试语句所需的时间对比情况。

记录数
 占记录总数的百分比
 pctused(50%)
 pctused(90%)
 性能提高百分比
 
1条记录
 0.0001%
 0.01
 0.01
 0.00%
 
100条记录
 0.0100%
 0.01
 0.01
 0.00%
 
1000条记录
 0.1000%
 0.01
 0.01
 0.00%
 
10000条记录
 1.0000%
 0.02
 0.02
 0.00%
 
50000条记录
 5.0000%
 0.06
 0.06
 0.00%
 
100000条记录
 10.0000%
 1.01
 1.00
 0.99%
 
1000000条记录
 100.0000%
 13.05
 11.01
 15.63%
 
1000000条记录(FFS)
 100.0000%
 7.05
 7.02
 0.43%
 

   上面是对最好的clustering_factor所做的测试,那么对于最差的
clustering_factor会怎么样呢?我们将rebuild_test中的id值反过来排
列,也就是说,比如对于id为3478的记录,将id改为8743。这样的话,
就将把原来按顺序排列的id值彻底打乱,从而使得id上的索引的clustering_factor
变成最差的。为此,我写了一个函数用来反转id的值。

create or replace function get_reverse_value(id in number) return varchar2 is
 ls_id varchar2(10);
 ls_last_item varchar2(10);
 ls_curr_item varchar2(10);
 ls_zero varchar2(10);
 li_len integer;
 lb_stop boolean;
begin
 ls_id := to_char(id);
 li_len := length(ls_id);
 ls_last_item := '';
 ls_zero := '';
 lb_stop := false;
 while li_len>0 loop
       ls_curr_item := substr(ls_id,li_len,1);
       if ls_curr_item = '0' and lb_stop = false then
           ls_zero := ls_zero || ls_curr_item;
       else
           lb_stop := true;
           ls_last_item:=ls_last_item||ls_curr_item;
       end if;
       ls_id := substr(ls_id,1,li_len-1);
       li_len := length(ls_id);
 end loop;
 return(ls_last_item||ls_zero);
end get_reverse_value;

      接下来,我们创建我们第二个测试的测试表。并按照与第一个测
试案例相同的方式进行测试。注意,对于测试查询来说,要把表名(包
括提示里的)改为rebuild_test_cf。

SQL> create table rebuild_test_cf as select * from rebuild_test;
SQL> update rebuild_test_cf set name=get_reverse_value(id);

 

 

 

B-Tree索引结构参考:

http://www.cublog.cn/u3/112761/showart_2218897.html
http://space.itpub.net/?uid-9842-action-viewspace-itemid-324139
http://www.360doc.com/content/11/0120/09/5547270_87766334.shtml
http://www.itpub.net/thread-300772-1-1.html
http://www.examda.com/oracle/zhonghe/20080505/110913194.html
http://**/viewthread.php?tid=21520 

深入研究Oracle B树索引系列 -by hanson
 
深入研究B树索引(一):http://space.itpub.net/?uid-9842-action-viewspace-itemid-312607
深入研究B树索引(二):http://space.itpub.net/?uid-9842-action-viewspace-itemid-321866
深入研究B树索引(三、四)http://space.itpub.net/?uid-9842-action-viewspace-itemid-324139
深入研究B树索引(四)续:http://space.itpub.net/?uid-9842-action-viewspace-itemid-324586
深入研究B树索引(五):http://space.itpub.net/?uid-9842-action-viewspace-itemid-324587
深入研究B树索引(五)续:http://space.itpub.net/?uid-9842-action-viewspace-itemid-324588
 

 

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

请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13468385