MY LIFE

数据库运行一段时间后，由于大量的数据变化，会导致一些表的索引实效，造成SQL语句执行速度过慢，使数据库出现大量的锁，日常维护中应该对数据库定时重新编译索引。

编译索引代码如下

set feedback off;

column rebuild_index HEADING "";

spool temp_rebuild_indexes_epm.sql;

--重新编译非分区索引
SELECT 'ALTER INDEX '|| owner || '.' || index_name ||' REBUILD;' rebuild_index 
FROM dba_indexes 
WHERE  index_name not like 'PB%' and owner in ('TESTUSER') and index_type not like 'IOT%' and index_type<>'LOB' 
and partitioned='NO' and TEMPORARY='N'
ORDER BY owner,table_name,index_name;	

--重新编译分区索引，分区索引只能用rebuild partition partition_name来编译
SELECT  'ALTER INDEX ' || owner || '.' || index_name ||
         ' REBUILD PARTITION ' || p.partition_name ||';' rebuild_index
FROM dba_indexes i, dba_tab_partitions p
WHERE i.owner in ('TESTUSER') and p.table_owner in ('TESTUSER') and index_type <> 'LOB' and
         i.partitioned = 'YES' and i.table_name = p.table_name
order by rebuild_index;

spool off;

set feedback on;

@temp_rebuild_indexes_epm.sql;

exit	

完后将该段代码保存成.sql文件，通过sqlplus执行该.sql文件，注意要用system用户登录数据库。

执行方法

sql>start test.sql