在数据库系统中应用索引重建技术

在数据库系统中应用索引重建技术

金喜波杨明远刁树民张晓勇

关键词:数据库应用系统索引碎片重建

中图分类号：TP311.132文献标识码：A

0引言

当系统运行一段时间后，系统的数据越来越多，查询速度开始下降，输出、统计速度也都随数据量的增大而变得缓慢；有时，由于空间的需要而删除一些数据，表空间就会产生碎片。这时，数据库应用系统的索引的维护就变得越来越重要。索引的重建和维护将极大的影响系统的性能。这里，针对数据库应用系统出现的实际情况，研究并提出索引的重建技术、维护及注意事项。

1索引的检查

1.1检查是否有失效的索引数据库应用系统中，原则上是不应该有失效的索引的。如果特殊情况下出现，就一定要基于分区表重建。目前数据库应用系统中，常出现的情况是如果索引是全局的，那么在表分区删除后，索引所占空间不能释放，并且索引将失效。在实际使用中，很多数据库应用系统发生过类似的问题。此时若进行汇总操作，不会成功，需要重建索引。

可利用语句:selectindex_namefromuser_indexeswherestatus='INVALID'

1.2检查是否缺少索引许多索引是在开始或升级时建立的。由于开始或升级时遇到一些复杂问题，常导致应该建立的索引没有建立。时间浪费在如下语句：selectcount(*)fromdepositfrom97wherereferenceno=:B1

1.3Oracle9i中的索引监控随着oracle9i在数据库应用系统的全面升级，越来越多的数据库应用系统将采用racle9i。在oracle9i中，可以监控索引的使用，如果没有使用到的索引，完全可以删除掉，减少DML操作时的时间。

2频繁插入删除数据的空表的处理

索引可能产生碎片，因为记录从表中删除时，相应也从表的索引中删除，而索引释放的空间却不能再用。频繁进行删除操作的被索引的表，应当阶段性地重建索引，以避免在索引中造成空间碎片，影响运行效率。在许可的条件下，也可以阶段性地truncate表，truncate命令删除表中所有记录，也删除索引碎片。

在系统中，有些表频繁插入数据，又频繁删除数据。表中经常保持空记录，但删除的记录包括其索引占用了大量的空间，这些表可以定期truncate表。

为保证truncate表时，表中数据为0记录数，可以使用如下语句进行锁表:Locktabletable_nameinexclusivemode

并检查此时记录数:selectcount(*)fromtable_name

如果此时表中有记录，请使用:DeletefromTSEVENTwhererownum<1

Commit

来释放锁，并重新加锁，直到表中数据为0。做完Truncate表后，表的锁会自动释放。

3索引的重建

根据以下几个原则进行检查，确定需要重建的索引。

3.1查看SYSTEM表空间中的用户索引为了避免数据字典的碎片出现，要尽量避免在SYSTEM表空间出现用户的表和索引。

可利用语句：selectindex_namefromdba_indexes;wheretablespace_name="SYSTEM";andownernotin("SYS"，"SYSTEM")

3.2确保用户的表和索引不在同一表空间内表和索引对象的第一个规则是把表和索引分离。把表和相应的索引建立在不同的表空间中，最好在不同的磁盘上。这样可以避免在数据管理和查询时出现的许多I/O冲突。

可利用语句：

setlinesize120

col"OWNER"formata20

col"INDEX"formata30

col"TABLE"formata30

col"TABLESPACE"formata30

select

i.owner"OWNER"，

i.index_name"INDEX"，

t.table_name"TABLE"，

i.tablespace_name"TABLESPACE"

from

dba_indexesi，

dba_tablest

wherei.owner=t.owner

andi.table_name=t.table_name

andi.tablespace_name=t.tablespace_name

andi.ownernotin("SYS"，"SYSTEM")

3.3确定有碎片的索引随着数据库的使用，不可避免地对基本表进行插入，更新和删除，这样导致叶子行在索引中被删除，使该索引产生碎片。插入删除越频繁的表，索引碎片的程度也越高。碎片的产生使访问和使用该索引的I/O成本增加。碎片较高的索引必须重建以保持最佳性能。如果索引的叶子行的碎片超过10%，此时必须考虑对索引进行重建。

3.4查看哪个索引被扩展了超过10次随着表记录的增加，相应的索引也要增加。如果一个索引的nextextent值设置不合理（太小），索引段的扩展变得很频繁。索引的extent太多，检索时的速度和效率就会降低。

可利用语句：

setlinesize100

colownerformata10

colsegment_nameformata30

coltablespace_nameformata30

select

count(*)，

owner，

segment_name，

tablespace_name

fromdba_extents

wheresegment_type="INDEX"

andownernotin("SYS"，"SYSTEM")

groupbyowner，segment_name，tablespace_name

havingcount(*)>10

orderbycount(*)desc

4重建索引注意事项

4.1重建命令

可利用语句：

alterindex索引名

rebuild

tablespace索引表空间名

storage(initial初始值next扩展值)

nologging；

NOLOGGING选项：NOLOGGING选项因为不写日志，所以大大提高了性能，比不使用NOLOGGING选项大约会节省70%的时间。使用NOLOGGING创建索引的唯一风险就是如果数据库需要做前滚恢复，你将不得不重建索引。

ONLINE选项：如果不选择ONLINE参数，重建索引时将使用原来的索引；选用ONLINE参数后，系统将采用全表扫描重建索引。

4.2提交如果出现资源忙，表明有进程正在使用该索引，等待一会再提交。

4.3确定剩余空间确定索引表空间还有足够的剩余空间。确定要把索引重建到哪个索引表空间中。要保证相应的索引表空间有足够的剩余空间。

coltablespaceformata20

selectb.file_id文件ID,

b.tablespace_name表空间,

b.file_name物理文件名,

b.bytes总字节数,

(b.bytes-sum(nvl(a.bytes,0)))已使用,

sum(nvl(a.bytes,0))剩余,

sum(nvl(a.bytes,0))/(b.bytes)*100剩余百分比

fromdba_free_spacea,dba_data_filesb;

wherea.file_id=b.file_id

groupbyb.tablespace_name,b.file_name,b.file_id,b.bytes;

orderbyb.tablespace_name

/

dba_free_space——表空间剩余空间状况

dba_data_files——数据文件空间占用情况

/

4.4整理表空间重建索引后，原有的索引被删除，这样会造成表空间的碎片，请使用如下命令整理表空间:

select"altertablespace"||tablespace_name||"coalesce;"

fromdba_free_space_coalesced;

wherepercent_blocks_coalesced!=100

/

整理表空间的碎片。

altertablespace表空间名coalesce

4.5整理索引

如果出于空间或其他的考虑，不能重建索引，可以整理索引。

alterindex用户名.索引名coalesce

5总结

通过上述分析，索引重建后的效果(测试略)可以归纳两方面，一是能释放浪费的大量的空间，二是性能明显提高。当系统运行一段时间后，我们都应该主动及时进行索引的重建。

参考文献：

[1]萨师煊，王珊.数据库系统概论[M].第二版.高等教育出版社1991.

[2]卢朝霞.ORACLE数据库系统应用实例集锦与编程技巧[M].清华大学出版社1992.

[3]孟小锋.关系数据库管理系统ORACLE原理与应用[M].电子工业出版社1993.

[4]卢朝霞，高志远，王剑.ORACLE关系数据库管理系统实用教程[M].东北工学院出版社1992.

[5]刘大昕，张健沛.ORACLE数据库系统[M].哈尔滨工程大学出版社1995.