删除表记录后对应索引如何保持平衡树，索引段大小如何变化。
删除表记录后对应索引如何保持平衡树，索引段大小如何变化。
如果表由10000行，删除了9999行，索引相应记录会被删除。那么对于Oracle B*tree索引如何保持平衡。下面我们测试表数据删除后
索引段的变化，后续分析原因。
原始段和索引大小
SQL> SELECT segment_name,bytes/1024/1024 from dba_segments where segment_name like '%SEG%' AND owner='SCOTT';
SEGMENT_NAME BYTES/1024/1024
-------------------- ---------------
IDX_TSEG_NAME 13
TSEG 15
表段TSEG15M，索引IDX_TSEG_NAME段大小13M
下面删除表段TSEG的全部数据
SQL> delete from scott.tseg;
151696 rows deleted.
SQL> commit;
Commit complete.
下面查表段和索引段的大小
SQL> SELECT segment_name,bytes/1024/1024 from dba_segments where segment_name like '%SEG%' AND owner='SCOTT';
SEGMENT_NAME BYTES/1024/1024
-------------------- ---------------
IDX_TSEG_NAME 13
TSEG 15
显然delete不会降低表段的高水位线，空间没有释放。下面通过shrink space降低高水位。
SQL> alter table scott.tseg enable row movement;
Table altered.
SQL> alter table scott.tseg shrink space;
Table altered.
再次查询，这次表段的空间释放了，但是索引段的大小依然没有变化。
SQL> SELECT segment_name,bytes/1024/1024 from dba_segments where segment_name like '%SEG%' AND owner='SCOTT';
SEGMENT_NAME BYTES/1024/1024
-------------------- ---------------
IDX_TSEG_NAME 13
TSEG .0625
我们知道索引是自动维护的，但是索引段的大小确没有释放，我们看下索引块的使用情况。
SQL> declare
2 v_unformatted_blocks number;
3 v_unformatted_bytes number;
4 v_fs1_blocks number;
5 v_fs1_bytes number;
6 v_fs2_blocks number;
7 v_fs2_bytes number;
8 v_fs3_blocks number;
9 v_fs3_bytes number;
10 v_fs4_blocks number;
11 v_fs4_bytes number;
12 v_full_blocks number;
13 v_full_bytes number;
14 begin
15 dbms_space.space_usage ('&owner', '&idx_name', 'INDEX', v_unformatted_blocks,
16 v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
17 v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
18 dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
19 dbms_output.put_line('FS1 0-25% free Blocks = '||v_fs1_blocks);
20 dbms_output.put_line('FS2 25-50% free Blocks = '||v_fs2_blocks);
21 dbms_output.put_line('FS3 50-75% free Blocks = '||v_fs3_blocks);
22 dbms_output.put_line('FS4 75- free Blocks = '||v_fs4_blocks);
23 dbms_output.put_line('Full Blocks = '||v_full_blocks);
24 end;
25 /
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 = 1598
FS3 50-75% free Blocks = 0
FS4 75- free Blocks = 0
Full Blocks = 15
PL/SQL procedure successfully completed.
通过索引状态查看块分布
SQL> analyze index scott.idx_tseg_name validate structure;
Index analyzed.
SQL> set line 200
SQL> SELECT name, partition_name,HEIGHT,BLOCKS,LF_ROWS,LF_BLKS,DEL_LF_ROWS,BR_ROWS,BR_BLKS FROM INDEX_STATS
Deleted
Index Name PARTITION_NAME HEIGHT BLOCKS LF_ROWS LF_BLKS Leaf Rows BR_ROWS BR_BLKS
------------------------------ -------------------- ---------- ---------- ---------- ---------- --------- ---------- ----------
IDX_TSEG_NAME 3 1664 151696 1597 151696 1596 16
索引树的高度是3，DeletedBLOCKS是1664
下面重建索引
SQL> alter index scott.idx_tseg_name rebuild;
Index altered.
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 1 8 0 1
此时段空间回收，HEIGHT为1，BLOCKS 为8 也就是被段分配的小大小，此时索引段被全部回收了。
SQL> SELECT segment_name,bytes/1024/1024 from dba_segments where segment_name like '%SEG%' AND owner='SCOTT';
SEGMENT_NAME BYTES/1024/1024
-------------------- ---------------
IDX_TSEG_NAME .0625
TSEG .0625
索引段都是空块，除了一个根块。
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 = 0
PL/SQL procedure successfully completed.
表数据删除后，索引中对应的索引记录也会删除，但是索引树中的对应数据块依然属于该索引，只是此时都是空块，此时这个B*tree依然是平衡树
在后续插入数据时刻，Oracle就避免了索引分裂的发生，提高了索引更新的效率。但是这种策略不会影响索引性能，因为从Root到页块的搜索路径
长度没有变化。
结论：对于DML频繁的表，定期重建索引可以释放段空间，提高索引维护的效率。
相关文章