如果你管理的Oracle数据库下某些应用项目有大量的修改删除 *** 作 数据索引是需要周期性的重建的
它不仅可以提高查询性能 还能增加索引表空间空闲空间大小
在ORACLE里大量删除记录后 表和索引里占用的数据块空间并没有释放
重建索引可以释放已删除记录索引占用的数据块空间
转移数据 重命名的方法可以重新组织表里的数据
下面是可以按ORACLE用户名生成重建索引的SQL脚本
SET ECHO OFFSET FEEDBACK OFFSET VERIFY OFFSET PAGESIZE SET TERMOUT ONSET HEADING OFFACCEPT username CHAR PROMPT Enter the index username: spool /oracle/rebuild_&username sqlSELECT REM + + || chr( ) || REM | INDEX NAME : || owner || || segment_name || lpad( | (length(owner) + length(segment_name)) ) || chr( ) || REM | BYTES : || bytes || lpad ( | (length(bytes)) ) || chr( ) || REM | EXTENTS : || extents || lpad ( | (length(extents)) ) || chr( ) || REM + + || chr( ) || ALTER INDEX || owner || || segment_name || chr( ) || REBUILD || chr( ) || TABLESPACE || tablespace_name || chr( ) || STORAGE ( || chr( ) || INITIAL || initial_extent || chr( ) || NEXT || next_extent || chr( ) || MINEXTENTS || min_extents || chr( ) || MAXEXTENTS || max_extents || chr( ) || PCTINCREASE || pct_increase || chr( ) || )|| chr( ) || chr( ) FROM dba_segments WHERE segment_type = INDEX AND owner= &username ORDER BY owner bytes DESCspool off
如果你用的是WINDOWS系统 想改变输出文件的存放目录 修改spool后面的路径成
spool c oraclerebuild_&username sql
如果你只想对大于max_bytes的索引重建索引 可以修改上面的SQL语句
在AND owner= &username 后面加个限制条件 AND bytes>&max_bytes
如果你想修改索引的存储参数 在重建索引rebuild_&username sql里改也可以
比如把pctincrease不等于零的值改成是零
生成的rebuild_&username sql文件我们需要来分析一下 它们是否到了需要重建的程度
分析索引 看是否碎片严重 SQL>ANALYZE INDEX &index_name VALIDATE STRUCTUREcol name heading Index Name format a col del_lf_rows heading Deleted|Leaf Rows format col lf_rows_used heading Used|Leaf Rows format col ratio heading % Deleted|Leaf Rows format SELECT name del_lf_rows lf_rows del_lf_rows lf_rows_used to_char(del_lf_rows / (lf_rows)* ) ratio FROM index_stats where name = upper( &index_name )
当删除的比率大于 % 时 肯定是需要索引重建的
经过删改后的rebuild_&username sql文件我们可以放到ORACLE的定时作业里
比如一个月或者两个月在非繁忙时间运行
如果遇到ORA 错误 表示索引在的表上有锁信息 不能重建索引
那就忽略这个错误 看下次是否成功
对那些特别忙的表要区别对待 不能用这里介绍的方法
lishixinzhi/Article/program/Oracle/201311/19038连续索引页由从一个页到下一个页的指针链接在一起。当对数据的更改影响到索引时,索引中的信息可能会在数据库中分散开来。重建索引可以重新组织索引数据(对于聚集索引还包括表数据)的存储,清除碎片。这可通过减少获得请求数据所需的页读取数来提高磁盘性能。 在Microsoft�0�3 SQL Server�6�4 2000 中,如果要用一个步骤重新创建索引,而不想删除旧索引并重新创建同一索引,则使用 CREATE INDEX 语句的 DROP_EXISTING 子句可以提高效率。这一优点既适用于聚集索引也适用于非聚集索引。 以删除旧索引然后重新创建同一索引的方式重建聚集索引,是一种昂贵的方法,因为所有二级索引都使用聚集键指向数据行。如果只是删除聚集索引然后重新创建,则会使所有非聚集索引都被删除和重新创建两次。一旦删除聚集索引并再次重建该索引,就会发生这种情形。通过在一个步骤中重新创建索引,可以避免这一昂贵的做法。在一个步骤中重新创建索引时,会告诉 SQL Server 要重新组织现有索引,避免了删除和重新创建非聚集索引这些不必要的工作。该方法的另一个重要优点是可以使用现有索引中的数据排序次序,从而避免了对数据重新排序。这对于聚集索引和非聚集索引都十分有用,可以显著减少重建索引的成本。另外,通过使用 DBCC DBREINDEX 语句,SQL Server 还允许对一个表重建(在一个步骤中)一个或多个索引,而不必单独重建每个索引。 DBCC DBREINDEX 也可用于重建执行 PRIMARY KEY 或 UNIQUE 约束的索引,而不必删除并创建这些约束(因为对于为执行 PRIMARY KEY 或 UNIQUE 约束而创建的索引,必须先删除该约束,然后才能删除该索引)。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)