index rebuild vs index coalesce vs index shrink

最新推荐文章于 2022-03-21 10:30:17 发布

ezbit

最新推荐文章于 2022-03-21 10:30:17 发布

阅读量2k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： Oracle Database ORACLE索引研究文章标签： oracle index shrink build coalesce

本文链接：https://blog.youkuaiyun.com/yidian815/article/details/16887465

Oracle Database 同时被 2 个专栏收录

50 篇文章

订阅专栏

ORACLE索引研究

9 篇文章

订阅专栏

本文通过实验对比了REBUILD、COALESCE与SHRINK三种索引优化方式的区别，包括它们在空间使用、锁使用及效率方面的表现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

当索引中碎片比较严重时，我们可以通过REBUILD COALESCE SHRINK来回收索引空间。下面我们通过实验来比较一下这三种方式具有哪些不同

SQL> create table t1(c1 number,c2 number,c3 number);

表已创建。

已用时间:  00: 00: 00.03
SQL> insert into t1  select rownum,rownum,rownum from dual connect by level < 1000000;

已创建 999999 行。

已用时间:  00: 00: 03.69
SQL> commit;

提交完成。

已用时间:  00: 00: 00.01
SQL> create index rebuild_ind on t1(c1);

索引已创建。

已用时间:  00: 00: 10.76
SQL> create index coalesce_ind on t1(c2);

索引已创建。

已用时间:  00: 00: 14.74
SQL> create index shrink_ind on t1(c3);

索引已创建。

已用时间:  00: 00: 11.09
SQL> analyze table t1 compute statistics for table for all indexes for all columns;

表已分析。

已用时间:  00: 00: 16.02
SQL> delete from t1 where mod(c1,10) <> 0;

已删除900000行。

已用时间:  00: 06: 04.51
SQL> commit;

提交完成。

已用时间:  00: 00: 00.01
SQL> analyze table t1 compute statistics for table for all indexes for all columns;

表已分析。

已用时间:  00: 00: 02.12

SQL> analyze index rebuild_ind validate structure;

索引已分析

已用时间:  00: 00: 00.15
SQL> select name,height,blocks,lf_rows,del_lf_rows,lf_blks,br_rows,br_blks,btree_space,used_space,pct_used from index_stats;

NAME				   HEIGHT     BLOCKS	LF_ROWS DEL_LF_ROWS    LF_BLKS	  BR_ROWS
------------------------------ ---------- ---------- ---------- ----------- ---------- ----------
   BR_BLKS BTREE_SPACE USED_SPACE   PCT_USED
---------- ----------- ---------- ----------
REBUILD_IND				3	2304	 312317      212318	  2226	     2225
	 5    17839236	  5003533	  29
--删除后，bf_blks为2226，说明叶子节点没有释放，但是如果以后有插入操作，空的叶子节点是可以重用的，即部分空块即在索引的双向链条上没有释放，但同时还在空块的链条上从而可以重用

已用时间:  00: 00: 00.02
SQL> select segment_name,blocks from user_segments where segment_name like '%IND';

SEGMENT_NAME									      BLOCKS
--------------------------------------------------------------------------------- ----------
REBUILD_IND										2304
COALESCE_IND										2304
SHRINK_IND										2304

已用时间:  00: 00: 00.08
SQL> analyze index coalesce_ind validate structure;

索引已分析

已用时间:  00: 00: 00.14
SQL> select name,height,blocks,lf_rows,del_lf_rows,lf_blks,br_rows,br_blks,btree_space,used_space,pct_used from index_stats;

NAME				   HEIGHT     BLOCKS	LF_ROWS DEL_LF_ROWS    LF_BLKS	  BR_ROWS
------------------------------ ---------- ---------- ---------- ----------- ---------- ----------
   BR_BLKS BTREE_SPACE USED_SPACE   PCT_USED
---------- ----------- ---------- ----------
COALESCE_IND				3	2304	 286859      186860	  2226	     2225
	 5    17839236	  4596205	  26


已用时间:  00: 00: 00.01
SQL> analyze index shrink_ind validate structure;

索引已分析

已用时间:  00: 00: 00.11
SQL> select name,height,blocks,lf_rows,del_lf_rows,lf_blks,br_rows,br_blks,btree_space,used_space,pct_used from index_stats;

NAME				   HEIGHT     BLOCKS	LF_ROWS DEL_LF_ROWS    LF_BLKS	  BR_ROWS
------------------------------ ---------- ---------- ---------- ----------- ---------- ----------
   BR_BLKS BTREE_SPACE USED_SPACE   PCT_USED
---------- ----------- ---------- ----------
SHRINK_IND				3	2304	 244024      144025	  2226	     2225
	 5    17839236	  3910845	  22


已用时间:  00: 00: 00.00
SQL> alter index rebuild_ind rebuild;

索引已更改。

已用时间:  00: 00: 01.58

SQL> alter index coalesce_ind coalesce;

索引已更改。

已用时间:  00: 00: 07.03
SQL> alter index shrink_ind shrink space;

索引已更改。

已用时间:  00: 01: 01.65
SQL> analyze index rebuild_ind validate structure;

索引已分析

已用时间:  00: 00: 00.06
SQL> select name,height,blocks,lf_rows,del_lf_rows,lf_blks,br_rows,br_blks,btree_space,used_space,pct_used from index_stats;

NAME				   HEIGHT     BLOCKS	LF_ROWS DEL_LF_ROWS    LF_BLKS	  BR_ROWS
------------------------------ ---------- ---------- ---------- ----------- ---------- ----------
   BR_BLKS BTREE_SPACE USED_SPACE   PCT_USED
---------- ----------- ---------- ----------
REBUILD_IND				2	 256	  99999 	  0	   222	      221
	 1     1783140	  1591506	  90
--重建索引，索引的空间重新分配

已用时间:  00: 00: 00.01
SQL> analyze index coalesce_ind validate structure;

索引已分析

已用时间:  00: 00: 00.05
SQL> select name,height,blocks,lf_rows,del_lf_rows,lf_blks,br_rows,br_blks,btree_space,used_space,pct_used from index_stats;

NAME				   HEIGHT     BLOCKS	LF_ROWS DEL_LF_ROWS    LF_BLKS	  BR_ROWS
------------------------------ ---------- ---------- ---------- ----------- ---------- ----------
   BR_BLKS BTREE_SPACE USED_SPACE   PCT_USED
---------- ----------- ---------- ----------
COALESCE_IND				3	2304	  99999 	  0	   223	      222
	 5     1823248	  1591516	  88
--coalesce后，索引中的空块在双向链条上剔除，但是在空块链条上保留，因此索引占用的总空间并没有变化

已用时间:  00: 00: 00.01
SQL> analyze index shrink_ind validate structure;

索引已分析

已用时间:  00: 00: 00.06
SQL> select name,height,blocks,lf_rows,del_lf_rows,lf_blks,br_rows,br_blks,btree_space,used_space,pct_used from index_stats;

NAME				   HEIGHT     BLOCKS	LF_ROWS DEL_LF_ROWS    LF_BLKS	  BR_ROWS
------------------------------ ---------- ---------- ---------- ----------- ---------- ----------
   BR_BLKS BTREE_SPACE USED_SPACE   PCT_USED
---------- ----------- ---------- ----------
SHRINK_IND				3	 248	  99999 	  0	   223	      222
	 5     1823248	  1591516	  88
--shrink后，索引的空块在段空间上释放

已用时间:  00: 00: 00.00
SQL> select segment_name,blocks from user_segments where segment_name like '%IND';

SEGMENT_NAME									      BLOCKS
--------------------------------------------------------------------------------- ----------
REBUILD_IND										 256
COALESCE_IND										2304
SHRINK_IND										 248

已用时间:  00: 00: 00.05

在空间使用情况上：

coalesce会整合索引空间，将利用率不高的块整合为利用率高的块，会将整理出的空块在索引结构的双向链条上释放，但是仍然保留在段内的空闲空间内。

shrink会将空块释放出段空间

rebuil重建索引因此段空间也是重建的

下面我们再来看一下锁的使用情况：

session 1：操作后不提交

SQL> select distinct sid from v$mystat;

       SID
----------
       179

SQL> update t1 set c1=c1+2,c2=c2+1,c3=c3+1;

session 2：

SQL> alter index rebuild_ind rebuild;
alter index rebuild_ind rebuild
            *
第 1 行出现错误:
ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效


已用时间:  00: 00: 00.00

SQL> alter index coalesce_ind coalesce;

索引已更改。

已用时间:  00: 00: 19.65
SQL> alter index shrink_ind shrink;
alter index shrink_ind shrink
                            *
第 1 行出现错误:
ORA-10630: Illegal syntax specified with SHRINK clause


已用时间:  00: 00: 00.00
SQL> alter index shrink_ind shrink space;

shrink 操作被阻塞，查看锁的使用情况

SQL> l
  1* select sid,type,id1,id2,lmode,request,block  from v$lock where sid in (select sid from v$session where username='SCOTT')
SQL> /

       SID TY	     ID1	ID2	 LMODE	  REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
	19 TX	  524289       2423	     6		0	   0
	19 TO	    5124	  1	     3		0	   0
	19 AE	     100	  0	     4		0	   0
	19 TO	   68064	  1	     3		0	   0
	19 SK	       7   16784210	     6		0	   0
	19 TX	  786454       1823	     0		4	   0
	19 TM	   89669	  0	     2		0	   0
       179 AE	     100	  0	     4		0	   0
       179 TX	  786454       1823	     6		0	   1
       179 TM	   89669	  0	     3		0	   0

已选择10行。

提交session1，后

SQL> alter index shrink_ind shrink space;

索引已更改。

已用时间:  00: 02: 51.02

总结：rebuild和shrink 不是"LOCK FRIENDLY",COALESCE是”lock friendly“，shrink does take a lock during the space release time

另外：如果索引的碎片情况非常严重，则rebuil的效率是较高的，而如果只是小部分索引的空间利用率不高，则COALESCE的效率是比较好的