解析索引段字段不多但比表段大的原因剖析和示例演示

2021-07-31 00:00:00 索引 专区 生产 空间 重建

为什么在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,重新插入索引,因此在updateinsert大量记录后

索引段会碎片化,进而需要更多的数据块用于索引段。减小索引段和解决碎片的有效方式就是重建索引。



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_ROWS51683


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_ROWS0 通过重建这部分空间释放了。继续查索引段的块使用情况


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操作频繁的表段,很可能索引段比表段要大,占据额外的磁盘空间,此时可以通过选择业务窗口重建的方式来释放索引段空间。

相关文章