第三，查看数据表空间里有哪些索引
用户的默认表空间应该不是SYSTEM表空间，而是数据表空间。在建立索引时，如果不指定相应的索引表空间名，那么，该索引就会建立在数据表空间中。这是程序员经常忽略的一个问题。应该在建索引时，明确的指明相应的索引表空间。
col segment_name format a30
select
owner,
segment_name,
sum(bytes)
from dba_segments
where tablespace_name=''数据表空间名''
and segment_type=''INDEX''
group by owner,segment_name |
第四，查看哪个索引被扩展了超过10次
随着表记录的增加，相应的索引也要增加。如果一个索引的next extent值设置不合理（太小），索引段的扩展变得很频繁。索引的extent太多，检索时的速度和效率就会降低。
set linesize 100
col owner format a10
col segment_name format a30
col tablespace_name format a30
select
count(*),
owner,
segment_name,
tablespace_name
from dba_extents
where segment_type=''INDEX''
and owner not in (''SYS'',''SYSTEM'')
group by owner,segment_name,tablespace_name
having count(*) >10
order by count(*) desc |
（2）找出需要重建的索引后，需要确定索引的大小，以设置合理的索引存储参数。
set linesize 120
col "INDEX" format a30
col "TABLESPACE" format a20
select
owner "OWNER",
segment_name "INDEX",
tablespace_name "TABLESPACE",
bytes "BYTES/COUNT",
sum(bytes) "TOTAL BYTES",
round(sum(bytes)/(1024*1024),0) "TOTAL M",
count(bytes) "TOTAL COUNT"
from dba_extents
where segment_type=''INDEX''
and segment_name in
(
''索引名1'',
''索引名2'',
......
)
group by owner,segment_name,segment_type,tablespace_name,bytes
order by owner,segment_name |
（3）确定索引表空间还有足够的剩余空间。
确定要把索引重建到哪个索引表空间中。要保证相应的索引表空间有足够的剩余空间。
select round(bytes/(1024*1024),2) free(M)
from sm$ts_free
where tablespace_name=''表空间名'' |
（4）重建索引。
重建索引时要注意以下几点：
a.如果不指定tablespace名，索引将建在用户的默认表空间。
b.如果不指定nologging，将会写日志，导致速度变慢。由于索引的重建没有恢复的必要，所以，可以不写日志。
c.如果出现资源忙，表明有进程正在使用该索引，等待一会再提交。
alter index 索引名
rebuild
tablespace 索引表空间名
storage(initial 初始值 next 扩展值)
nologging |
（5）检查索引。
对重建好的索引进行检查。
select * from dba_extents
where segment_name=''索引名'' |
（6）根据索引进行查询，检查索引是否有效
使用相应的where条件进行查询，确保使用该索引。看看使用索引后的效果如何。
select * from dba_ind_columns
where index_name like ''表名%'' |
然后，根据相应的索引项进行查询。
select * from ''表名%'' where ...... |
（6）找出有碎片的表空间，并收集其碎片。
重建索引后，原有的索引被删除，这样会造成表空间的碎片。
select ''alter tablespace ''||tablespace_name||'' coalesce;''
from dba_free_space_coalesced
where percent_blocks_coalesced!=100 |
整理表空间的碎片。
alter tablespace 表空间名 coalesce |
