资源描述：
《索引的优化和维护》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、索引的优化和维护目的：通过对数据库中的索引进行优化和维护，提交检索的速度和效率。原理：随着数据库的使用，不可避免地对基本表进行插入，更新和删除，这样导致叶子行在索引中被删除，使该索引产生碎片。插入删除越频繁的表，索引碎片的程度也越高。碎片的产生使访问和使用该索引的I/O成本增加。碎片较高的索引必须重建以保持最佳性能。当索引的层数增大时，I/O的成本增加，检索的效率开始降低，oracle建议当索引的层数大于3时，则应当对此索引进行重建以提交效率。随着表记录的增加，相应的索引也要增加。如果一个索引的next值设置不合理(太小)，索引段的
2、扩展变得很频繁。索引的extent太多，检索时的速度和效率就会降低。当然还有就是由于一些人为的原因或者系统表的迁移，有可能造成索引的失效，也会降低检索的效率和速度。通过对索引进行分析，找出碎片比例占索引20%以上的索引；通过查询和索引相关的数据字典和表找出失效的索引、层数大于3的索引和被扩展超过10次的索引；将这些问题索引数据统计到临时表。分别对这些索引进行重建或重新设置next值（尽量增大，到合理的数值），从而达到优化索引和提高数据库效率的目的。脚本：DECLAREv_index_namevarchar2(100);v_analy
3、ze_strvarchar2(300);heightnumber;lf_rowsnumber;del_lf_rowsnumber;v_ex_countnumber;v_idx_ownervarchar2(30);v_tab_namevarchar2(50);v_tabspa_namevarchar2(50);v_idx_statusvarchar2(10);CURSORanalyze_indexISSELECTindex_name,table_owner,table_name,tablespace_name,statusFROMus
4、er_indexes;BEGINOPENanalyze_index;FETCHanalyze_indexINTOv_index_name,v_idx_owner,v_tab_name,v_tabspa_name,v_idx_status;WHILEanalyze_index%FOUNDLOOPifv_idx_status='INVALID'theninsertintolifemenu.index_stats_probvalues(v_index_name,null,null,null,‘该索引已失效’,v_idx_owner,v_t
5、ab_name,v_tabspa_name,v_idx_status);endif;ifv_idx_status='VALID'thenv_analyze_str:='analyzeindex'
6、
7、v_index_name
8、
9、'validatestructure';EXECUTEIMMEDIATEv_analyze_str;SELECTHEIGHT,DECODE(LF_ROWS,0,1,LF_ROWS),DEL_LF_ROWSINTOheight,lf_rows,del_lf_rowsFROMindex_stats;if(del_l
10、f_rows/lf_rows)>0.2theninsertintolifemenu.index_stats_probvalues(v_index_name,del_lf_rows/lf_rows,null,v_ex_count,'该索引的碎片太多，建议重建',v_idx_owner,v_tab_name,v_tabspa_name,v_idx_status);endif;ifheight>=4and(del_lf_rows/lf_rows)<=0.2theninsertintolifemenu.index_stats_probval
11、ues(v_index_name,null,height,v_ex_count,'该索引的层数超过3，建议重建',v_idx_owner,v_tab_name,v_tabspa_name,v_idx_status);endif;selectcount(*)intov_ex_countfromuser_extentswheresegment_name=v_index_name;ifv_ex_count>=11and(del_lf_rows/lf_rows)<=0.2andheight<4theninsertintolifemenu.i
12、ndex_stats_probvalues(v_index_name,null,null,v_ex_count,'该索引扩展超过10次，建议重建时增大参数next',v_idx_owner,v_tab_name,v_tabspa_na
