--有一些时候发现某些表的索引大小会超出表的大小，而且索引字段比表字段少很多，

怀疑是对表的更新导致。

--测试索引比表大的情况

create table t_index(id,name,owner,type) as select object_id,object_name,object_type from dba_objects;

create index idx_t on t_index(id,name);


sql> create index idx_t on t_index(id,name);

Index created

sql>


SELECT SEGMENT_NAME,BYTES,BLOCKS FROM USER_SEGMENTS WHERE SEGMENT_NAME IN ('T_INDEX','IDX_T') ;


SEGMENT_NAME BYTES BLOCKS
-------------------------------------------------------------------------------- ---------- ----------
IDX_T 6291456 768
T_INDEX 8388608 1024




begin
for x in 1..100000
loop update t_index set id=id+0.1 where id=x;
end loop;
end;
/


PL/sql procedure successfully completed

sql> commit;



sql> ANALYZE INDEX IDX_T VALIDATE STRUCTURE ;

Index analyzed

sql> analyze table T_INDEX compute statistics;

Table analyzed

sql> analyze index idx_t compute statistics;

Index analyzed

sql>



sql> SELECT SEGMENT_NAME,'IDX_T') ;

SEGMENT_NAME BYTES BLOCKS
---------------------------------------- ---------- ----------
T_INDEX 8388608 1024
IDX_T 10485760 1280

sql>


sql> alter index idx_t rebuild;

Index altered

sql> SELECT SEGMENT_NAME,'IDX_T') ;

SEGMENT_NAME BYTES BLOCKS
---------------------------------------- ---------- ----------
T_INDEX 8388608 1024
IDX_T 6291456 768

sql>

--结果：当数据update操作频繁，对index的维护会导致其占用空间过大，通过rebuild可以恢复到原始状态。

原文链接：https://www.f2er.com/oracle/208977.html