[索引的优化和维护.docVIP

索引的优化和维护 目的： 通过对数据库中的索引进行优化和维护，提交检索的速度和效率。 原理： 随着数据库的使用，不可避免地对基本表进行插入，更新和删除，这样导致叶子行在索引中被删除，使该索引产生碎片。插入删除越频繁的表，索引碎片的程度也越高。碎片的产生使访问和使用该索引的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_na