在日常交流中经常听到一个错误的观点,数据库索引需要重建,理由是重建索引至少能有以下好处:
1、索引的重建,即使不能提高性能,也可以压缩索引的存储空间,使得单位索
引数据块所包含的索引更多,效率更高;
2、节省数据库的存储空间
但实际上,我们 不要盲目的重建索引,有可能重建索引造成索引的存储空间占用更大,效率更低。所以重建以前,最好对索引进行分析,查看索引的当前情况,以确定是否要重建。
以下是Richard Foote的三个具有代表性的实验:
实验一:创建一个普通表,表的数值是连续
SQL>create table test1(id number);
SQL>create index test1_idx on test1(id);
SQL>insert into test1 select rownum from dual connect by level<=1000000;
SQL>commit;
SQL>analyze index test1_idx validate structure;
SQL>select blocks, lf_blks, btree_space, pct_used from index_stats;
BLOCKS LF_BLKS BTREE_SPACE PCT_USED
------------------ ------------------- -------------------------- ---------------------
2048 1999 16016116 100
以上PCT_USED已经达到的最佳状态100%,如果对索引重建,会增大索引的存储空间:
SQL>alter index test1_idx rebuild;
SQL>analyze index test1_idx validate structure;
SQL>select blocks, lf_blks, btree_space, pct_used from index_stats;
BLOCKS LF_BLKS BTREE_SPACE PCT_USED
------------------ ------------------- -------------------------- ---------------------
2304 2226 17839236 90
实验二:创建小表,生成随机数
SQL>create table test2 (id number);
SQL>insert into test2 select ceil(dbms_random.value(0, 100000))
from dual
connect by level<=1000000;
SQL>commit;
SQL>create index test2_idx on test2(id);
SQL>insert into test2 select ceil(dbms_random.value(1,100000))
from dual connect by level<=50000;
SQL>commit;
SQL>analyze index test2_idx validate structure;
SQL>select blocks, lf_blks, btree_space, pct_used from index_stats;
BLOCKS LF_BLKS BTREE_SPACE PCT_USED
-------------------- -------------------- ----------------------- -----------------------
2304 2216 17767304 95
我们对索引重建,也将增加索引空间的使用:
SQL>alter index test2_idx rebuild;
SQL>analyze index test2_idx validate structure;
SQL>select blocks, lf_blks, btree_space, pct_used from index_stats;
BLOCKS LF_BLKS BTREE_SPACE PCT_USED
------------------ ------------------- -------------------------- ---------------------
2304 2226 17839236 90
实验三:与实验二相类似,但增加了一个批量的update,我们通常认为update后会造成索引空间的浪费,所以需要重建索引,能有效降低索引的存储空间,但实际上,并不是这么回事。
SQL>create table test3 (id number, value number);
SQL>insert into test3 select rownum, ceil(dbms_random.value(0, 100000))
from dual connect by level<=100000;
SQL>commit;
SQL>create index test3_idx on test3(value);
SQL>insert into test3 select rownum+100000,
ceil(dbms_random.value(0,100000)) from dual
connect by level <=5000;
SQL>commit;
运行以下存储过程,更新约为10%的数据
begin
for i in 1..10500 loop
update test3 set value =ceil(dbms_random.value(0,100000))
where id=i;
commit;
end loop;
end;
SQL>analyze index test3_idx validate structure;
SQL>select blocks, lf_blks, btree_space, pct_used from index_stats;
BLOCKS LF_BLKS BTREE_SPACE PCT_USED
------------------ ------------------- -------------------------- ---------------------
256 223 1791136 94
SQL>select del_lf_rows from index_stats;
DEL_LF_ROWS
-----------
110
我们更新了约10%的行(10500),但实际上索引存储空间标识为删除的110行,证明了大部分索引空间被重用。
重新创建索引,增加索引的存储空间:
SQL>alter index test3_idx rebuild;
SQL>analyze index test3_idx validate structure;
SQL>select blocks, lf_blks, btree_space, pct_used from index_stats;
BLOCKS LF_BLKS BTREE_SPACE PCT_USED
-------------------- ------------------- -------------------------- ---------------------
256 233 1871096 90
1、索引的重建,即使不能提高性能,也可以压缩索引的存储空间,使得单位索
引数据块所包含的索引更多,效率更高;
2、节省数据库的存储空间
但实际上,我们 不要盲目的重建索引,有可能重建索引造成索引的存储空间占用更大,效率更低。所以重建以前,最好对索引进行分析,查看索引的当前情况,以确定是否要重建。
以下是Richard Foote的三个具有代表性的实验:
实验一:创建一个普通表,表的数值是连续
SQL>create table test1(id number);
SQL>create index test1_idx on test1(id);
SQL>insert into test1 select rownum from dual connect by level<=1000000;
SQL>commit;
SQL>analyze index test1_idx validate structure;
SQL>select blocks, lf_blks, btree_space, pct_used from index_stats;
BLOCKS LF_BLKS BTREE_SPACE PCT_USED
------------------ ------------------- -------------------------- ---------------------
2048 1999 16016116 100
以上PCT_USED已经达到的最佳状态100%,如果对索引重建,会增大索引的存储空间:
SQL>alter index test1_idx rebuild;
SQL>analyze index test1_idx validate structure;
SQL>select blocks, lf_blks, btree_space, pct_used from index_stats;
BLOCKS LF_BLKS BTREE_SPACE PCT_USED
------------------ ------------------- -------------------------- ---------------------
2304 2226 17839236 90
实验二:创建小表,生成随机数
SQL>create table test2 (id number);
SQL>insert into test2 select ceil(dbms_random.value(0, 100000))
from dual
connect by level<=1000000;
SQL>commit;
SQL>create index test2_idx on test2(id);
SQL>insert into test2 select ceil(dbms_random.value(1,100000))
from dual connect by level<=50000;
SQL>commit;
SQL>analyze index test2_idx validate structure;
SQL>select blocks, lf_blks, btree_space, pct_used from index_stats;
BLOCKS LF_BLKS BTREE_SPACE PCT_USED
-------------------- -------------------- ----------------------- -----------------------
2304 2216 17767304 95
我们对索引重建,也将增加索引空间的使用:
SQL>alter index test2_idx rebuild;
SQL>analyze index test2_idx validate structure;
SQL>select blocks, lf_blks, btree_space, pct_used from index_stats;
BLOCKS LF_BLKS BTREE_SPACE PCT_USED
------------------ ------------------- -------------------------- ---------------------
2304 2226 17839236 90
实验三:与实验二相类似,但增加了一个批量的update,我们通常认为update后会造成索引空间的浪费,所以需要重建索引,能有效降低索引的存储空间,但实际上,并不是这么回事。
SQL>create table test3 (id number, value number);
SQL>insert into test3 select rownum, ceil(dbms_random.value(0, 100000))
from dual connect by level<=100000;
SQL>commit;
SQL>create index test3_idx on test3(value);
SQL>insert into test3 select rownum+100000,
ceil(dbms_random.value(0,100000)) from dual
connect by level <=5000;
SQL>commit;
运行以下存储过程,更新约为10%的数据
begin
for i in 1..10500 loop
update test3 set value =ceil(dbms_random.value(0,100000))
where id=i;
commit;
end loop;
end;
SQL>analyze index test3_idx validate structure;
SQL>select blocks, lf_blks, btree_space, pct_used from index_stats;
BLOCKS LF_BLKS BTREE_SPACE PCT_USED
------------------ ------------------- -------------------------- ---------------------
256 223 1791136 94
SQL>select del_lf_rows from index_stats;
DEL_LF_ROWS
-----------
110
我们更新了约10%的行(10500),但实际上索引存储空间标识为删除的110行,证明了大部分索引空间被重用。
重新创建索引,增加索引的存储空间:
SQL>alter index test3_idx rebuild;
SQL>analyze index test3_idx validate structure;
SQL>select blocks, lf_blks, btree_space, pct_used from index_stats;
BLOCKS LF_BLKS BTREE_SPACE PCT_USED
-------------------- ------------------- -------------------------- ---------------------
256 233 1871096 90
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/354732/viewspace-605540/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/354732/viewspace-605540/