当前位置:首页 > 开发 > 数据库 > 正文

Alter index coalesce VS shrink space

发表于: 2014-08-02   作者:czmmiao   来源:转载   浏览次数:
摘要: 10g中引入了对索引的shrink功能,索引shrink操作会扫描索引的页块,并且通过归并当前存在的数据将先前已删除记录的空间重新利用;很多书籍亦或者MOS的Note中都会提及SHRINK命令与早期版本中就存在的COALESCE(合并)命令具有完全相同的功能,或者说2者是完全等价的-" alter index shrink space is equivalent to coalesce&

10g中引入了对索引的shrink功能,索引shrink操作会扫描索引的页块,并且通过归并当前存在的数据将先前已删除记录的空间重新利用;很多书籍亦或者MOS的Note中都会提及SHRINK命令与早期版本中就存在的COALESCE(合并)命令具有完全相同的功能,或者说2者是完全等价的-" alter index shrink space is equivalent to coalesce",事实是这样的吗?

SQL> conn maclean/maclean
Connected.

/* 测试使用版本10.2.0.4 * /

SQL> select * from v$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

/* 建立测试用表YOUYUS,高度为3 */

SQL> drop table YOUYUS;
Table dropped.

SQL> create table YOUYUS as select rownum t1,rpad('A',20,'B') t2 from dual connect by level<=999999;
Table created.

SQL> create index ind_youyus on youyus(t1,t2) nologging;
Index created.

SQL> analyze  index IND_YOUYUS validate  structure;
Index analyzed.

/*
大家因该很熟悉 analyze index .. validate structure 命令 ,实际上该命令存在一个兄弟: 
analyze  index IND_YOUYUS validate  structure online,
加上online子句后validate structure可以在线操作,但该命令不会填充index_stats临时视图
*/

SQL> set linesize 200;
SQL> set linesize 200;
SQL> select height,
  2         blocks,
  3         lf_blks,
  4         lf_rows_len,
  5         lf_blk_len,
  6         br_blks,
  7         br_rows,
  8         br_rows_len,
  9         br_blk_len,
 10         btree_space,
 11         used_space,
 12         pct_used
 13    from index_stats;

    HEIGHT     BLOCKS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_BLKS    BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE USED_SPACE   PCT_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- ----------
         3       5376       5154    36979767       7996          9       5153       61784       8028    41283636   37041551         90

/*  可以看到IND_YOUYUS索引的基本结构,在初始状态下其block总数为5376,其中页块共5154  */

/*  我们在表上执行删除操作,均匀删除三分之一的数据 */

SQL> delete YOUYUS where mod(t1,3)=1;
333333 rows deleted.

SQL> commit;
Commit complete.

SQL> conn maclean/maclean
Connected.

SQL> select vs.name, ms.value
  2    from v$mystat ms, v$sysstat vs
  3   where vs.statistic# = ms.statistic#
  4     and vs.name in ('redo size','consistent gets');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
consistent gets                                                          45
redo size                                                                 0

SQL> alter index ind_youyus coalesce;

Index altered.

SQL> select vs.name, ms.value
  2    from v$mystat ms, v$sysstat vs
  3   where vs.statistic# = ms.statistic#
  4     and vs.name in ('redo size','consistent gets');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
consistent gets                                                         788
redo size                                                          70649500

/* coalesce 操作产生了大约67MB的redo数据  */

SQL> analyze  index IND_YOUYUS validate  structure;
Index analyzed.

SQL> set linesize 200;
SQL> select height,
  2         blocks,
  3         lf_blks,
  4         lf_rows_len,
  5         lf_blk_len,
  6         br_blks,
  7         br_rows,
  8         br_rows_len,
  9         br_blk_len,
 10         btree_space,
 11         used_space,
 12         pct_used
 13    from index_stats;

    HEIGHT     BLOCKS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_BLKS    BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE USED_SPACE   PCT_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- ----------
         3       5376       3439    24653178       7996          9       3438       41188       8028    27570496   24694366         90

/* 可以看到执行coalesce(合并)操作后页块数量下降到3439,同时coalesc命令并不释放索引上的多余空间,
但索引结构实际占用的空间BTREE_SPACE下降到了27570496 bytes */
/* 清理测试现场 */

SQL> drop table YOUYUS;
Table dropped.

SQL> create table YOUYUS as select rownum t1,rpad('A',20,'B') t2 from dual connect by level<=999999; 
Table created. 		  

SQL> create index ind_youyus on youyus(t1,t2) nologging;
Index created.

SQL> delete YOUYUS where mod(t1,3)=1;
333333 rows deleted.

SQL> commit;
Commit complete.

SQL> conn maclean/maclean
Connected.
SQL> select vs.name, ms.value
  2    from v$mystat ms, v$sysstat vs
  3   where vs.statistic# = ms.statistic#
  4     and vs.name in ('redo size','consistent gets');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
consistent gets                                                          45
redo size                                                                 0

SQL> alter index ind_youyus shrink space;

Index altered.

SQL> select vs.name, ms.value
  2    from v$mystat ms, v$sysstat vs
  3   where vs.statistic# = ms.statistic#
  4     and vs.name in ('redo size','consistent gets');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
consistent gets                                                        2951
redo size                                                          90963340

/* SHRINK SPACE操作产生了86MB的redo数据,多出coalesce时的28% */

SQL> analyze  index IND_YOUYUS validate  structure;

Index analyzed.

SQL> set linesize 200;
SQL> select height,
  2         blocks,
  3         lf_blks,
  4         lf_rows_len,
  5         lf_blk_len,
  6         br_blks,
  7         br_rows,
  8         br_rows_len,
  9         br_blk_len,
 10         btree_space,
 11         used_space,
 12         pct_used
 13    from index_stats;

    HEIGHT     BLOCKS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_BLKS    BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE USED_SPACE   PCT_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- ----------
         3       3520       3439    24653178       7996          9       3438       41188       8028    27570496   24694366         90

/* 索引结构与coalesce命令维护后相同,但shrink space操作释放了索引上的空闲空间 */

/* 再次清理测试现场 */

SQL> drop table YOUYUS;
Table dropped.

SQL> create table YOUYUS as select rownum t1,rpad('A',20,'B') t2 from dual connect by level<=999999; 
Table created. 

SQL> create index ind_youyus on youyus(t1,t2) nologging;
Index created.

SQL>  delete YOUYUS where mod(t1,3)=1;
333333 rows deleted.

SQL> commit;
Commit complete.

SQL> conn maclean/maclean
Connected.

SQL> select vs.name, ms.value
  2    from v$mystat ms, v$sysstat vs
  3   where vs.statistic# = ms.statistic#
  4     and vs.name in ('redo size','consistent gets');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
consistent gets                                                          45
redo size                                                                 0

SQL> alter index ind_youyus shrink space compact;

Index altered.

SQL> select vs.name, ms.value
  2    from v$mystat ms, v$sysstat vs
  3   where vs.statistic# = ms.statistic#
  4     and vs.name in ('redo size','consistent gets');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
consistent gets                                                        3208
redo size                                                          90915424

SQL> analyze  index IND_YOUYUS validate  structure;

Index analyzed.

SQL> set linesize 200;
SQL> select height,
  2         blocks,
  3         lf_blks,
  4         lf_rows_len,
  5         lf_blk_len,
  6         br_blks,
  7         br_rows,
  8         br_rows_len,
  9         br_blk_len,
 10         btree_space,
 11         used_space,
 12         pct_used
 13    from index_stats;

    HEIGHT     BLOCKS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_BLKS    BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE USED_SPACE   PCT_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- ----------
         3       5376       3439    24653178       7996          9       3438       41188       8028    27570496   24694366         90

/* shrink space compact 起到了和coalesce完全相同的作用,但其产生的redo仍要多于coalesce于28% */

coalesce与shrink space命令对比重建索引(rebuild index)有一个显著的优点:不会导致索引降级。从以上测试可以看到coalesce与shrink space compact功能完全相同;在OLTP环境中,大多数情况下我们并不希望回收索引上的空闲空间,那么coalesce或者shrink space compact(not shrink space)可以成为我们很好的选择,虽然实际操作过程中2者消耗的资源有不少差别。

并不是说coalesce就一定会消耗更少的资源,这需要在您的实际环境中具体测试,合适的才是最好的!

参考至:http://www.cnblogs.com/macleanoracle/archive/2010/09/12/2967532.html
如有错误,欢迎指正

邮箱: czmcj@163.com

Alter index coalesce VS shrink space

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
select Coalesce( '居住建筑->' || decode(JZLXFL,0,'传统民居',1,'花园别墅',2,'里弄住宅',3,'其
最近整理了一下系统内所有表的空间占用情况,发现其中一些表其实只有及万行,却占用了非常大的空间
Like it or not, JavaScript has become the heaviest asset on modern webpages. Long gone are th
Index B-Tree Index的叶节点指向RowID,然后根据RowID再找对应的Block 语法:CREATE INDEX NAME_SAL
ALTER TABLE简介 当表被创建后,在使用过程中可能会有一些新的需求,这时候可能需要修改表的结构。
今年4月份参与系统开发的时候遇到一个情况: 有这么一个存储过程 存储过程: 表结构为: 前提:这个
下面3条语句,旨在刷新oracle的缓存。这里总结一下。 1)alter system flush global context 下图说
1. Linux、FreeBSD、Solaris下Vmware Tools的调用命令: #/usr/bin/vmware-toolbox 2. 可能有的朋友
今天使用vs2008开发web项目时,发现按F5运行时,提示:无法启动程序"http://localhost:12346/Defaul
原文: 一个非常有用的函数——COALESCE 很多人知道ISNULL函数,但是很少人知道Coalesce函数,人们会
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号