最后我们来看一下重建索引对于性能的提高到底会有什么作用。假设我们有一个表，该表具有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);
145
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
