删除表记录后对应索引如何保持平衡树,索引段大小如何变化。

2021-08-01 00:00:00 索引 数据 删除 都是 大小

删除表记录后对应索引如何保持平衡树,索引段大小如何变化。

如果表由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频繁的表,定期重建索引可以释放段空间,提高索引维护的效率。


相关文章