索引空间三倍于表大小

最新推荐文章于 2025-06-18 13:49:58 发布

转载最新推荐文章于 2025-06-18 13:49:58 发布 · 200 阅读

本文记录了一个索引空间远大于表空间的实际案例，通过对INDEX_HISTORY表空间的检查发现，索引IND_SUBSCRIBER_LASTCHADATE、IDX_SUBSMODE_ID及PK_SUBSOTHERINFO_MID占用了大量空间。进一步分析表明，这些索引对应的表空间也被显著占用。

INDEX_HISTORY表空间空间占用很大，检查里面的segment使用情况

select owner, segment_name, partition_name, segment_type, (bytes)/1024/1024/1024 GB
from dba_segments where tablespace_name = 'INDEX_HISTORY' order by GB desc;

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE GB
MISC IND_SUBSCRIBER_LASTCHADATE  INDEX 99.3359375
MISC IDX_SUBSMODE_ID  INDEX 13.625
MISC PK_SUBSOTHERINFO_MID  INDEX 12.4375

发现有三个索引占用较多的空间
select owner, segment_name, partition_name, segment_type, (bytes)/1024/1024/1024 GB
from dba_segments
where (owner, segment_name) in (select owner, table_name from dba_indexes where wner = 'MISC' and index_name = 'IND_SUBSCRIBER_LASTCHADATE')
order by GB desc ;

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE GB
MISC SUBSCRIBER SUBSCRIBER_P1 TABLE PARTITION 8.4033203125
MISC SUBSCRIBER SUBSCRIBER_P2 TABLE PARTITION 8.4033203125
MISC SUBSCRIBER SUBSCRIBER_P3 TABLE PARTITION 8.4013671875
MISC SUBSCRIBER SUBSCRIBER_P4 TABLE PARTITION 8.3974609375

select owner, segment_name, partition_name, segment_type, (bytes)/1024/1024/1024 GB
from dba_segments
where (owner, segment_name) in (select owner, table_name from dba_indexes where wner = 'MISC' and index_name = 'IDX_SUBSMODE_ID')
order by GB desc ;

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE GB
MISC SUBSMODE TABLE 21.58984375

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE GB
MISC SUBSOTHERINFO TABLE 9.6630859375

索引空间三倍于表大小，这东西可不是一般生产系统能见到的。

运行6年的系统，加上缺乏有效的维护才可以形成这一奇特现象，纯天然啊。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13605188/viewspace-687242/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13605188/viewspace-687242/