索引的优化和维护

索引的优化和维护

目的：

通过对数据库中的索引进行优化和维护，提交检索的速度和效率。

原理：

随着数据库的使用，不可避免地对基本表进行插入，更新和删除，这样导致叶子行在索引中被删除，使该索引产生碎片。插入删除越频繁的表，索引碎片的程度也越高。碎片的产生使访问和使用该索引的I/O成本增加。碎片较高的索引必须重建以保持最佳性能。当索引的层数增大时，I/O的成本增加，检索的效率开始降低，oracle建议当索引的层数大于3时，则应当对此索引进行重建以提交效率。随着表记录的增加，相应的索引也要增加。如果一个索引的next值设置不合理(太小)，索引段的扩展变得很频繁。索引的extent太多，检索时的速度和效率就会降低。当然还有就是由于一些人为的原因或者系统表的迁移，有可能造成索引的失效，也会降低检索的效率和速度。

通过对索引进行分析，找出碎片比例占索引20%以上的索引；通过查询和索引相关的数据字典和表找出失效的索引、层数大于3的索引和被扩展超过10次的索引；将这些问题索引数据统计到临时表。分别对这些索引进行重建或重新设置 next值（尽量增大，到合理的数值），从而达到优化索引和提高数据库效率的目的。

脚本：

DECLARE

v_index_name varchar2(100);

v_analyze_str varchar2(300);

height number;

lf_rows number;

del_lf_rows number;

v_ex_count number;

v_idx_owner varchar2(30);

v_tab_name varchar2(50);

v_tabspa_name varchar2(50);

v_idx_status varchar2(10);

CURSOR analyze_index IS

SELECT index_name,table_owner,table_name,tablespace_name,status

FROM user_indexes;

BEGIN

OPEN analyze_index;

FETCH analyze_index INTO

v_index_name,v_idx_owner,v_tab_name,v_tabspa_name,v_idx_status;

WHILE analyze_index%FOUND LOOP

if v_idx_status ='INVALID' then

insert into lifemenu.index_stats_prob

values(v_index_name,null,null,null,

‘该索引已失效’,v_idx_owner,v_tab_name,v_tabspa_name,v_idx_status);

end if;

if v_idx_status ='VALID' then

v_analyze_str := 'analyze index '||v_index_name||' validate structure';

EXECUTE IMMEDIATE v_analyze_str;

SELECT HEIGHT,DECODE(LF_ROWS,0,1,LF_ROWS),DEL_LF_ROWS

INTO height,lf_rows,del_lf_rows

FROM index_stats;

if (del_lf_rows/lf_rows)>0.2 then

insert into lifemenu.index_stats_prob

values(v_index_name,del_lf_rows/lf_rows,null,v_ex_count,'该索引的碎片太多，建议重建',v_idx_owner,v_tab_name,v_tabspa_name,v_idx_status);

end if;

if height>=4 and (del_lf_rows/lf_rows) <=0.2 then

insert into lifemenu.index_stats_prob

values(v_index_name,null,height,v_ex_count,'该索引的层数超过3，建议重建',v_idx_owner,v_tab_name,v_tabspa_name,v_idx_status);

end if;

select count(*)

into v_ex_count

from user_extents

where segment_name =v_index_name;

if v_ex_count>=11 and (del_lf_rows/lf_rows)<=0.2 and height<4 then

insert into lifemenu.index_stats_prob

values(v_index_name,null,null,v_ex_count,'该索引扩展超过10次，建议重建时增大参数next',v_idx_owner,v_tab_name,v_tabspa_name,v_idx_status);

end if;

end if;

commit;

v_index_name :='';

v_analyze_str :='';

v_idx_status :='';

FETCH analyze_index INTO

v_index_name,v_idx_owner,v_tab_name,v_tabspa_name,v_idx_status;

END LOOP;

CLOSE analyze_index;

END;

执行结果：

执行结果存储在表index_stats_prob中，表的结构和说明如下：

create table INDEX_STATS_PROB

(

INDEX_NAME VARCHAR2(30) not null,//索引名称

FRAG_PCT NUMBER, //碎片比例

HEIGHT NUMBER, //索引层数

EXTENT_TIME NUMBER, //扩展次数

COM VARCHAR2(50), //建议处理方法

OWNER VARCHAR2(30) not null,//所属用户

TABLE_NAME VARCHAR2(50), //所属表

TABLESPACE_NAME VARCHAR2(50), //所在表空间