Oracle用户名重建索引方法探究

每个DBA都应该知道数据索引的重要性。特别是管理Oracle的DBA，很多应用项目会涉及到大量的修改删除操作， 数据索引是需要周期性的重建的。这里介绍的根据Oracle用户名重建索引的方法可以帮助我们提高查询性能。

更重要的是，它不仅可以提高查询性能，还能增加索引表空间空闲空间大小。在Oracle里大量删除记录后，表和索引里占用的数据块空间并没有释放。重建索引可以释放已删除记录索引占用的数据块空间。

下面是可以按Oracle用户名生成重建索引的SQL脚本：

SETECHOOFF; SETFEEDBACKOFF; SETVERIFYOFF; SETPAGESIZE0; SETTERMOUTON; SETHEADINGOFF; ACCEPTusernameCHARPROMPT'Entertheindexusername:'; spool/oracle/rebuild_&username.sql; SELECT'REM+-----------------------------------------------+'||chr(10)|| 'REM|INDEXNAME:'||owner||'.'||segment_name ||lpad('|',33-(length(owner)+length(segment_name))) ||chr(10)|| 'REM|BYTES:'||bytes ||lpad('|',34-(length(bytes)))||chr(10)|| 'REM|EXTENTS:'||extents ||lpad('|',34-(length(extents)))||chr(10)|| 'REM+-----------------------------------------------+'||chr(10)|| 'ALTERINDEX'||owner||'.'||segment_name||chr(10)|| 'REBUILD'||chr(10)|| 'TABLESPACE'||tablespace_name||chr(10)|| 'STORAGE('||chr(10)|| 'INITIAL'||initial_extent||chr(10)|| 'NEXT'||next_extent||chr(10)|| 'MINEXTENTS'||min_extents||chr(10)|| 'MAXEXTENTS'||max_extents||chr(10)|| 'PCTINCREASE'||pct_increase||chr(10)|| ');'||chr(10)||chr(10) FROMdba_segments WHEREsegment_type='INDEX'ANDowner='&username'ORDERBYowner,bytesDESC; spooloff; 

如果你用的是Windows系统， 想改变输出文件的存放目录， 修改spool后面的路径成：

spool c：\oracle\rebuild_&username.sql；

如果你只想对大于max_bytes的索引重建索引， 可以修改上面的SQL语句：在AND owner=’&username’ 后面加个限制条件 AND bytes> &max_bytes

如果你想修改索引的存储参数，在重建索引rebuild_&username.sql里改也可以。比如把pctincrease不等于零的值改成是零。

生成的rebuild_&username.sql文件我们需要来分析一下， 它们是否到了需要重建的程度：分析索引，看是否碎片严重 。

SQL>ANALYZEINDEX&index_nameVALIDATESTRUCTURE; colnameheading'IndexName'formata30 coldel_lf_rowsheading'Deleted|LeafRows'format99999999 collf_rows_usedheading'Used|LeafRows'format99999999 colratioheading'%Deleted|LeafRows'format999.99999 SELECTname, del_lf_rows, lf_rows-del_lf_rowslf_rows_used, to_char(del_lf_rows/(lf_rows)*100,'999.99999')ratio FROMindex_statswherename=upper('&index_name'); 

当删除的比率大于15 – 20% 时，肯定是需要索引重建的。

经过删改后的rebuild_&username.sql文件我们可以放到Oracle的定时作业里：

比如一个月或者两个月在非繁忙时间运行。如果遇到ORA-00054错误， 表示索引在的表上有锁信息， 不能根据Oracle用户名来重建索引。

那就忽略这个错误， 看下次是否成功。对那些特别忙的表要区别对待， 不能用这里介绍的方法，还要把它们的索引从rebuild_&username.sql里删去。

【编辑推荐】

全面讲解Oracle查询用户表空间浅析Oracle用户权限表的管理方法Oracle数据库备份与恢复特性浅谈使用Oracle外部表的五个限制浅析Oracle中的表空间查询方法