为什么在update或者insert大量数据后，索引占空间比表大以及解决方案。
测试
SQL> insert into tseg select object_id,object_name from dba_objects;
75845 rows created.
SQL>/
SQL> commit;
Commit complete.
SQL> select segment_name,segment_type,bytes/1024/1024 from user_segments where segment_name like ‘%SEG%’;
SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024
-------------------- ------------------------------------ ---------------
IDX_TSEG_NAME INDEX 11
TSEG TABLE 8
此时，表8M 而索引11M，索引已经比表大了。
下面尝试update数据
SQL> update tseg set object_name='dfjdlfsjdfldfjsdlfjdslfjdslfjdlfjdslfjsdlfjds'||rowid;
151696 rows updated.
SQL> commit;
Commit complete.
SQL> select segment_name,segment_type,bytes/1024/1024 from user_segments where segment_name like '%SEG%';
SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024
-------------------- ------------------------------------ ---------------
TSEG TABLE 15
IDX_TSEG_NAME INDEX 31
在update表后，此时我有意增大了object_name段的大小，我们看到索引比表大很多了，两倍多一点。
实际上对于索引，没有update的概念，更新索引列时，旧的索引标记为delete,重新插入索引，因此在update和insert大量记录后
索引段会碎片化，进而需要更多的数据块用于索引段。减小索引段和解决碎片的有效方式就是重建索引。
SQL> select owner ,segment_name,partition_name ,segment_type,tablespace_name,sum( bytes) bytes,count(*) extent_count from dba_extents where segment_name like '%SEG%' and owner='SCOTT' group by owner ,segment_name,partition_name ,segment_type,tablespace_name order by owner ,segment_name,partition_name ,segment_type,tablespace_name;
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES EXTENT_COUNT
---------- --------------- --------------- --------------- ------------------------- ---------- ------------
SCOTT IDX_TSEG_NAME INDEX USERS 32505856 46
SCOTT TSEG TABLE USERS 15728640 30
SQL> analyze index scott.idx_tseg_name validate structure;
Index analyzed.
SQL> col name for a20
SQL> col partition_name for a20
SQL>SELECT name, partition_name,HEIGHT,BLOCKS,LF_ROWS,LF_BLKS,DEL_LF_ROWS,BR_ROWS,BR_BLKS FROM INDEX_STATS WHERE NAME='IDX_TSEG_NAME'
NAME PARTITION_NAME HEIGHT BLOCKS LF_ROWS LF_BLKS DEL_LF_ROWS BR_ROWS BR_BLKS
-------------------- -------------------- ---------- ---------- ---------- ---------- ----------- ---------- ————————
IDX_TSEG_NAME 3 3968 303379 3743 51683 3742 41 <<<<<DEL_LF_ROWS为51683
SQL> declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage ('&owner', '&idx_name', 'INDEX', v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
dbms_output.put_line('FS1 0-25% free Blocks = '||v_fs1_blocks);
dbms_output.put_line('FS2 25-50% free Blocks = '||v_fs2_blocks);
dbms_output.put_line('FS3 50-75% free Blocks = '||v_fs3_blocks);
dbms_output.put_line('FS4 75- free Blocks = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
/
Enter value for owner: SCOTT
Enter value for idx_name: IDX_TSEG_NAME
old 15: dbms_space.space_usage ('&owner', '&idx_name', 'INDEX', v_unformatted_blocks,
new 15: dbms_space.space_usage ('SCOTT', 'IDX_TSEG_NAME', 'INDEX', v_unformatted_blocks,
Unformatted Blocks = 62
FS1 0-25% free Blocks = 0
FS2 25-50% free Blocks = 1348
FS3 50-75% free Blocks = 0
FS4 75- free Blocks = 0
Full Blocks = 2488
PL/SQL procedure successfully completed.
我们看到FS2 25-50% free Blocks = 1348是没有满的块，也就是25%到50%的块空间是空的。但是这些块是占据段空间的。
下面我们重建该索引
SQL> alter index scott.idx_tseg_name rebuild online;
Index altered.
SQL> SELECT name, partition_name,HEIGHT,BLOCKS,LF_ROWS,LF_BLKS,DEL_LF_ROWS,BR_ROWS,BR_BLKS FROM INDEX_STATS;
no rows selected
SQL> analyze index scott.idx_tseg_name validate structure;
Index analyzed.
SQL> SELECT name, partition_name,HEIGHT,BLOCKS,LF_ROWS,LF_BLKS,DEL_LF_ROWS,BR_ROWS,BR_BLKS FROM INDEX_STATS;
NAME PARTITION_NAME HEIGHT BLOCKS LF_ROWS LF_BLKS DEL_LF_ROWS BR_ROWS BR_BLKS
-------------------- -------------------- ---------- ---------- ---------- ---------- ----------- ---------- ----------
IDX_TSEG_NAME 3 1664 151696 1597 0 1596 16 <<<<<DEL_LF_ROWS为
此时DEL_LF_ROWS为0 通过重建这部分空间释放了。继续查索引段的块使用情况
SQL> /
Enter value for owner: SCOTT
Enter value for idx_name: IDX_TSEG_NAME
old 15: dbms_space.space_usage ('&owner', '&idx_name', 'INDEX', v_unformatted_blocks,
new 15: dbms_space.space_usage ('SCOTT', 'IDX_TSEG_NAME', 'INDEX', v_unformatted_blocks,
Unformatted Blocks = 0
FS1 0-25% free Blocks = 0
FS2 25-50% free Blocks = 1
FS3 50-75% free Blocks = 0
FS4 75- free Blocks = 0
Full Blocks = 1612 <<<<<<<<<此时 都是满块，空间被释放总块数为1613
PL/SQL procedure successfully completed.
下面查询索引段和表段大小，发现索引目前比表要小。
SQL> select segment_name,segment_type,bytes/1024/1024 from dba_segments where segment_name like '%SEG%' and owner='SCOTT';
SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024
--------------- --------------- ---------------
TSEG TABLE 15
IDX_TSEG_NAME INDEX 13
说明：在insert update操作频繁的表段，很可能索引段比表段要大，占据额外的磁盘空间，此时可以通过选择业务窗口重建的方式来释放索引段空间。
