当索引中碎片比较严重时,我们可以通过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的效率是比较好的