`

Alter index coalesce VS shrink space

 
阅读更多

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

分享到：
| SQL Plan Management Creating SQL plan b ...
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    ORACLE重建索引总结

    Oracle数据库中的索引是提升查询性能...2. 重建索引有删除重建（不推荐）、`ALTER INDEX REBUILD`（推荐，尤其是在线模式）和`ALTER INDEX COALESCE`（节省空间，但不能移动索引）等方式，根据实际需求选择合适的方法。

    oracle优化资料.docx

    - 删除大量数据后，对应的索引也需要收缩，使用`ALTER INDEX idxname SHRINK SPACE;`可以实现。 6. **注意点** - `SHRINK TABLE`只适用于使用ASSM（自动段空间管理）的表，否则会报错`ORA-10635`。 以上方法有助...

    Oracle碎片整理

    本文将深入探讨Oracle中的碎片整理，主要关注`ALTER TABLE T MOVE`和`ALTER TABLE T SHRINK SPACE`两个命令。 首先，我们来理解什么是Oracle数据库中的碎片。碎片分为两种类型：内部碎片和外部碎片。内部碎片是指...

    ALTER 命令收集

    ALTER INDEX table_index REBUILD ONLINE; ``` 与常规的重建索引不同，`ONLINE`选项允许在不锁定表的情况下重建索引，从而在索引维护期间保持表的可用性。 ### 三、ALTER DATABASE命令详解 `ALTER DATABASE`命令...

    alter sql 语句实例

    例如：ALTER TABLE table_name CLUSTER ON index_name; 11. SET WITHOUT CLUSTER：从表中删除最新使用的 CLUSTER 索引。 例如：ALTER TABLE table_name SET WITHOUT CLUSTER; 12. SET WITHOUT OIDS：从表中删除 ...

    TVTools+AlterID+2.0.rar

    TVTools AlterID 是一款解决TeamViewer被检测为商业用途的无限换ID工具，使用TeamViewer远程控制软件的朋友都知道，TeamViewer不管是什么所谓的破解版还是TeamViewer免费版使用过程都会出现一些TeamViewer被检测为...

    AlterID（认证工具）

    AlterID是一款专为TeamViewer14设计的认证工具，它主要用于修改Windows系统中的TeamViewer配置，以实现特定的认证功能。在深入探讨AlterID的工作原理和用途之前，我们需要先了解TeamViewer的基础知识。 TeamViewer...

    sql中的alter命令

    ALTER TABLE tablename ADD INDEX emp_name(name); ``` 这将为`tablename`表中的`name`列添加一个索引。 ### 添加主键 (Add Primary Key) 主键是表中的唯一标识符，通常用于确保数据的唯一性。例如： ```sql ALTER...

    alter 的使用方法

    在数据库管理领域，`ALTER`命令是一个非常实用且强大的工具，它允许用户修改现有表的结构，而无需重建整个表。这对于维护数据库的灵活性和适应性至关重要。根据提供的标题、描述以及部分代码示例，我们可以深入探讨`...

    SQL Server中alter table一些用法.doc

    SQL Server 中 Alter Table 的一些用法 SQL Server 中 Alter Table 语句的主要作用是对已经创建的表进行修改、添加、删除约束、修改表结构等操作。下面是 Alter Table 语句的一些用法： 添加约束 Alter Table ...

    ALTER JTAG 下载线USB 原理图

    ALTER JTAG下载线USB原理图相关的知识主要集中在嵌入式系统、硬件设计和编程接口上。ALTER JTAG，全称为ALTERA Joint Test Action Group，是ALTERA公司提供的一种硬件调试和编程接口，它允许开发者对FPGA（Field-...

    alter语句关于sql数据库

    `ALTER`语句是SQL语言中的一个重要组成部分，用于在数据库中对已有对象进行修改，如表、视图、索引等。在SQL Server中，`ALTER`语句尤其常用，以下将详细介绍在修改表结构时涉及到的一些关键操作。 1. **添加字段**...

    AlterID2.zip

    标题“AlterID2.zip”和描述“AlterID2 zip”暗示了我们正在处理一个与“AlterID2”相关的软件或程序的压缩包。这个“AlterID”可能是一个软件的名称，或者是某种特定功能或者服务的标识。在IT行业中，"AlterID"可以...

    SQL中的ALTER TABLE语句详解.pdf

    - `ALTER INDEX index_name VISIBLE | INVISIBLE`: 改变索引的可见性。 - 示例：`ALTER TABLE users ALTER INDEX pk_users INVISIBLE;` - `ALTER [COLUMN] col_name SET DEFAULT value`: 设置默认值。 - 示例：`...

    alter提升各种数据库脚本的方法

    ### Alter提升各种数据库脚本的方法 #### 概述 在数据库管理中，随着业务需求的变化和技术的发展，数据库表结构的调整变得越来越频繁。常见的表结构调整包括字段类型更改、添加或删除字段等操作。本文将详细介绍...

    AlterID.rar

    在描述中提到的"AlterID.rar"似乎是一个解决这个问题的方法。 "AlterID.exe"是压缩包中的文件，很可能是一个程序，用于修改TeamViewer的ID，这样每次试用期结束后，通过更改ID，用户可以再次获得7天的免费试用。...

    Oracle19c数据库高水位线(HWM)详解与操作指南

    最后，给出了几种降低 HWM 的方法，包括 alter table move、shrink space、数据复制、exp/imp 以及 deallocate unused 等方法。 适合人群：具备 Oracle 数据库基础的数据库管理员和技术人员。 使用场景及目标：帮助...

    AlterID.zip

    AlterID.exe 密码123456

Magicbox
Global site tag (gtag.js) - Google Analytics