索引碎片整理--重建索引、合并索引、shrink索引

标签： 索引 碎片 重建 | 发表时间：2014-02-24 03:27 | 作者：xiaohai20102010
出处：http://blog.csdn.net

   今天是2014-02-23，之前有个网友，曾问我关于索引空间碎片整理的问题。今天有时间顺便在学习一下这个问题内容。随着时间的推移，索引也可能会产生碎片，oracle在处理该问题的时候提供给予我们有三种方式：重建索引、合并索引、shrink 索引。每种都有自己的特点，今天我在此学习一下记录一下笔记。
第一：重建索引：
  重建索引其实语句很简单实用alter index index_name rebuild;参数即可对指定的索引进行重建，但是注意在重建索引的时候 会对相应的对象加锁，因此重建的时候一定要注意，如何避免在重建索引的时候不影响其他业务使用呢？那么可以指定online 参数，如：alter index index_name rebuild online;指定该参数之后就不会对其他业务访问对象产生任何影响。另外有时候我们还可以指定并行创建索引，但要注意在指定parallel（degree interger）参数的时候，那么并行度将存储于索引中，随着在基于硬件如cpu个数创建速度上确实提高了，但在在执行查询的时候将使用并行方式，有时候也会伴随着等待事件的出现如：PX Deq Credit: send blkd，因此创建索引是不是应该使用paralle应该斟酌一下。
eg：

SQL> create index emp_idx1 on emp(empno) parallel (degree 8);

Index created.

SQL> select index_name,degree from user_indexes where table_name='EMP';

INDEX_NAME           DEGREE
-------------------- ----------------------------------------
EMP_IDX1             8


  另外当我们需要重新创建反向键索引的时候需要指定reserver参数：alter index index_name rebuild reverse;回收未使用的空间，当使用
alter index index_name deallocate unused;
命令的时候，会将没有使用的空闲段返回给数据，但是曾经使用过的空块将不会返还给数据库空间（包含之前删除的索引或是移动条目导致段内没有使用的空间）对于分区索引和索引组织表的信息查看：
http://blog.csdn.net/rhys_oracle/article/details/18671897
http://blog.csdn.net/rhys_oracle/article/details/18409063
  另外如何确定是否需要重建索引呢？一般认为有两种情况：
  1、索引深度大于等于4
  2、已删除的索引条目占总索引条目的20%
  3、索引空间使用率小于50%
再次不得不提 一个视图index_stats该视图默认是没有任何数据的，当使用analyze index index_name validate structure;对索引结构分析之后将会填充相应的数据，一般该视图可以提供给我们足够的信息去引导我们是否需要对索引进行重建。
查看相关字段信息：

SQL> desc index_stats;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 HEIGHT                                             NUMBER  （代表索引高度）
 BLOCKS                                             NUMBER  （索引占用块数）
 NAME                                               VARCHAR2(30)（索引名字）
 PARTITION_NAME                                     VARCHAR2(30)（分区索引名字）
 LF_ROWS                                            NUMBER （叶子行数）
 LF_BLKS                                            NUMBER  （在b树索引中叶子的块数）
 LF_ROWS_LEN                                        NUMBER  （所有叶子行数的长度）
 LF_BLK_LEN                                         NUMBER  （在一片叶子中可用空间）
 BR_ROWS                                            NUMBER  （在B树索引中有多少个分支行）
 BR_BLKS                                            NUMBER  （在B树索引中有多少个分支块）
 BR_ROWS_LEN                                        NUMBER  （在B树索引中所有分支块的总长度）
 BR_BLK_LEN                                         NUMBER  （在分支快中可用的空间）
 DEL_LF_ROWS                                        NUMBER  （在索引中删除叶子行数）
 DEL_LF_ROWS_LEN                                    NUMBER  （在索引中删除叶子行数的总的长度）
 DISTINCT_KEYS                                      NUMBER  （唯一值数目包括删除的行）
 MOST_REPEATED_KEY                                  NUMBER
 BTREE_SPACE                                        NUMBER  （当前分给该 索引总的大小空间）
 USED_SPACE                                         NUMBER  （已经被索引使用的空间大小包含被删的行数空间）
 PCT_USED                                           NUMBER  （索引空间使用率）
 ROWS_PER_KEY                                       NUMBER  （每个不同键值的平均行数不包括删除行）
 BLKS_GETS_PER_ACCESS                               NUMBER  
 PRE_ROWS                                           NUMBER  （前缀行数）
 PRE_ROWS_LEN                                       NUMBER  （前缀行的总长度）
 OPT_CMPR_COUNT                                     NUMBER  （压缩长度）
 OPT_CMPR_PCTSAVE                                   NUMBER

SQL>


查看未删除叶子行数占总行数的百分比公式为：(（lf_rows-del_lf_rows)/lf_rows)*100;
查看未删除行占用的空间百分比公式为：（（used_space-del_lf_rows_len)/btree_space)*100;
pct_used计算公式为：（used_space/btree_space)*100
eg：

SQL> create table test as select rownum id,'Amy' text from dual connect by level<=10000;

Table created.

SQL> select count(*) from test;

  COUNT(*)
----------
     10000

SQL> create index test_idx1 on test(id);

Index created.

SQL> select * from index_stats;

no rows selected

SQL> analyze index test_idx1 validate structure;

Index analyzed.
SQL> r
  1  select height,
  2         lf_rows,
  3         lf_blks,
  4         del_lf_rows,
  5         btree_space,
  6         used_space,
  7         pct_used,
  8         ((used_space - del_lf_rows_len) / btree_space) pct_unused,
  9         ((lf_rows - del_lf_rows) / lf_rows) pct_undel_rows
 10    from index_stats
 11   where name = 'TEST_IDX1'
 12*

    HEIGHT    LF_ROWS    LF_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE   PCT_USED PCT_UNUSED PCT_UNDEL_ROWS
---------- ---------- ---------- ----------- ----------- ---------- ---------- ---------- --------------
         2      10000         21           0      175944     150021         86 .852663347              1

SQL> 
SQL> analyze index test_idx1 validate structure;

Index analyzed.

SQL> select height,
  2         lf_rows,
  3         lf_blks,
  4         del_lf_rows,
  5         btree_space,
  6         used_space,
  7         pct_used,
  8         ((used_space - del_lf_rows_len) / btree_space) pct_unused,
  9         ((lf_rows - del_lf_rows) / lf_rows) pct_undel_rows
 10    from index_stats
 11   where name = 'TEST_IDX1';

    HEIGHT    LF_ROWS    LF_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE   PCT_USED PCT_UNUSED PCT_UNDEL_ROWS
---------- ---------- ---------- ----------- ----------- ---------- ---------- ---------- --------------
         2      10000         21        9999      175944     150021         86 .001329969          .0001

SQL> 
SQL> alter index test_idx1 deallocate unused;

Index altered.

SQL> analyze index test_idx1 validate structure;

Index analyzed.
SQL> select height,
  2         lf_rows,
  3         lf_blks,
  4         del_lf_rows,
  5         btree_space,
  6         used_space,
  7         pct_used,
  8         ((used_space - del_lf_rows_len) / btree_space) pct_unused,
  9         ((lf_rows - del_lf_rows) / lf_rows) pct_undel_rows
 10    from index_stats
 11   where name = 'TEST_IDX1';

    HEIGHT    LF_ROWS    LF_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE   PCT_USED PCT_UNUSED PCT_UNDEL_ROWS
---------- ---------- ---------- ----------- ----------- ---------- ---------- ---------- --------------
         2      10000         21        9999      175944     150021         86 .001329969          .0001

SQL>


收集统计信息，之后可以看到在dba_indexes中依然显示存在的索引叶块，优化器从而使用该索引。

SQL> exec dbms_stats.gather_table_stats('SYS','TEST',cascade=>true);

PL/SQL procedure successfully completed.

SQL>  select index_name,leaf_blocks,num_rows,degree from dba_indexes where index_name='TEST_IDX1';

INDEX_NAME                     LEAF_BLOCKS   NUM_ROWS DEGREE
------------------------------ ----------- ---------- ----------------------------------------
TEST_IDX1                                1          1 1

SQL> set autotrace trace exp
sSQL>   
SQL> select * from test where id<20;

Execution Plan
----------------------------------------------------------
Plan hash value: 2624864549

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |     7 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST      |     1 |     7 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX1 |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"<20)

SQL>


但是注意：使用analyze index index_name validate structure ;进行索引分析的时候会锁定相应的对象直到该命令执行完成，如果不加锁可以使用online参数，但使用online参数数据信息又不会记录到index_stats视图，且在重建索引的过程中会产生很多的redo日志，可以考虑使用nologging参数，另外当在分析完成后在执行插入操作，那么相应的del_lf_rows将会改变从而影响对索引的分析信息提取：
eg：

SQL> select * from test;         

        ID TEX
---------- ---
     10000 Amy

SQL> insert into test values(10001,'Rhys');
insert into test values(10001,'Rhys')
                              *
ERROR at line 1:
ORA-12899: value too large for column "SYS"."TEST"."TEXT" (actual: 4, maximum: 3)


SQL> desc test             
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 ID                                                                                                                         NUMBER
 TEXT                                                                                                                       CHAR(3)

SQL> alter table test modify text char(15);

Table altered.

SQL> insert into test values(10001,'Rhys');

1 row created.

SQL> commit;

Commit complete.

SQL> select height,
  2         lf_rows,
  3         lf_blks,
  4         del_lf_rows,
  5         btree_space,
  6         used_space,
       pct_used,
  7    8         ((used_space - del_lf_rows_len) / btree_space) pct_unused,
  9         ((lf_rows - del_lf_rows) / lf_rows) pct_undel_rows
 10    from index_stats
 11   where name = 'TEST_IDX1';

    HEIGHT    LF_ROWS    LF_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE   PCT_USED PCT_UNUSED PCT_UNDEL_ROWS
---------- ---------- ---------- ----------- ----------- ---------- ---------- ---------- --------------
         2      10000         21        9999      175944     150021         86 .001329969          .0001

SQL> analyze index test_idx1 validate structure;

Index analyzed.

SQL> select height,
  2         lf_rows,
  3         lf_blks,
       del_lf_rows,
  4    5         btree_space,
  6         used_space,
       pct_used,
  7    8         ((used_space - del_lf_rows_len) / btree_space) pct_unused,
  9         ((lf_rows - del_lf_rows) / lf_rows) pct_undel_rows
 10    from index_stats
 where name = 'TEST_IDX1';
 11  
    HEIGHT    LF_ROWS    LF_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE   PCT_USED PCT_UNUSED PCT_UNDEL_ROWS
---------- ---------- ---------- ----------- ----------- ---------- ---------- ---------- --------------
         2       9584         21        9582      175944     143786         82 .001420907     .000208681

SQL>


 

从以上可以看出两点内容，产生索引数据之后剩余的空间不会返还给数据库，但是当插入新数据的时候将有可能重新利用之前被删除数据的空间，另外一点可以看del_lf_row已经评估出现错误，到目前为止刚刚开始删除9999条数据，然后插入一条数据在进行分析，那么现在既然是9582，因此不能仅仅依靠del_lf_rows进行索引重建评估。以前记得有个朋友曾经提过这么一个问题，说是测试环境库执行一条sql会非常的块，但是导到正式环境却很慢，但是执行计划都是一样的，我的怀疑就是需要重建正式环境库的索引。因此，如果确定对 索引相同部分执行了大量删除操作，产生了大量的索引碎片，并且查询每次读取了大量的索引行，索引被频繁使用，这时候重建索引是有价值的。
第二种：合并索引
  合并索引就是将索引段中相邻的索引块其中空闲空间进行整合重组，从而释放索引块空间，这比较类似于我们windows的磁盘碎片整理，但是注意该过程不会将腾出的空间返回与数据库，而是加入到空闲空间列表中，以便下次在进行使用。这种操作对于那种以序列或是时间日志为字段的表是有非常重要价值的，因为当我们对这些表删除了大部分数据，那么其中很多空间是无法在进行使用的，那么在我们制定谓词查询的时候通常会扫描索引中很多空快，那么合并索引就将空的索引块进行释放与索引块的空闲列表中。
语句非常简单：
alter index index_name coalesce;
合并索引与重建索引不同事，合并索引不会降低索引的高度，而是对其数据条目进行重组整合，但是重建可能会降低索引高度，另外重建索引需要2倍的磁盘空间，首先需要存储原先的索引条目数据，还需要额外的空间存储新调整 的索引数据直到重建完成才可。

注：合并索引是一种在线操作。
第三种：shrink 索引：
 因为shrink是一个耗资源相对严重的过程，因此两个过程，一个是compact参数，另一个是直接shrink space，第一种类似于coalesce但是相比会产生更多的redo日志，执行完后不会释放空间，但是shrink space 除了整理碎片还可以将空间释放给表空间，但是shrink space虽然是在线可以做的，依然会产生过打的redo日志。除此之外shrink space还要启动行移动。
eg：
alter index index_name shrink space compact;
alter index index_name shrink space;
注：Shrink operations can be performed only on segments in locally managed tablespaces with automatic segment space management (ASSM).
 

 

作者：xiaohai20102010 发表于2014-2-23 19:27:23 原文链接
阅读：83 评论：0 查看评论

相关 [索引 碎片 重建] 推荐：

索引碎片整理--重建索引、合并索引、shrink索引

- - CSDN博客数据库推荐文章
   今天是2014-02-23，之前有个网友，曾问我关于索引空间碎片整理的问题. 今天有时间顺便在学习一下这个问题内容. 随着时间的推移，索引也可能会产生碎片，oracle在处理该问题的时候提供给予我们有三种方式：重建索引、合并索引、shrink 索引. 每种都有自己的特点，今天我在此学习一下记录一下笔记.

SQL Server 查询性能优化——堆表、碎片与索引（一）

- - 博客园_首页
      SQL Server在堆表中查询数据时，是不知道到底有多少数据行符合你所指定的查找条件，它将根据指定的查询条件把数据表的全部数据都查找一遍. 如果有可采用的索引，SQL Server只需要在索引层级查找每个索引分页的数据，再抓出所需要的少量数据分页即可. 访问数据表内数以万计的数据分页与只访问少数索引的分页两者间的差异，让索引变成效能调校的最佳工具.

深入理解重建索引(原创)

- - ITeye博客
索引在普遍意义上能够给数据库带来带来提升，但索引的额外开销也是不容小视的，而索引的重建也是维护索引的重要工作之一. 经过维护的索引可带来以下好处：. 1、CBO对于索引的使用可能会产生一个较小的成本值，从而在执行计划中选择使用索引. 2、使用索引扫描的查询扫描的物理索引块会减少，从而提高效率. 3、于需要缓存的索引块减少了，从而让出了内存以供其他组件使用.

Oracle表与索引的分析及索引重建(转载)

- - Oracle - 数据库 - ITeye博客
Oracle表与索引的分析及索引重建. 2010年12月31日 . 1.分析表与索引（analyze 不会重建索引）. 等同于 analyze table tablename compute statistics for table for all indexes for all columns. for table 的统计信息存在于视图：user_tables 、all_tables、dba_tables.

elasticsearch更改mapping(不停服务重建索引)

- - zzm
Elasticsearch的mapping一旦创建，只能增加字段，而不能修改已经mapping的字段. 但现实往往并非如此啊，有时增加一个字段，就好像打了一个补丁，一个可以，但是越补越多，最后自己都觉得惨不忍睹了. 这里有一个方法修改mapping，那就是重新建立一个index，然后创建一个新的mapping.

[转]Oracle 碎片

- - 小鸥的博客
　　当生成一个数据库时，它会 分成称为表空间( Tablespace )的多个逻辑段( Segment )，如系统( System )表空间 , 临时( Temporary )表空间等. 一个表空间可以包含多个数据范围( Extent )和一个或多个自由范围块，即自由空间( Free Space ).

碎片化生存

- - 槽边往事
我得实话告诉你：我已经没有办法读书了. 不是读不懂的那种不能，也不是因为厌倦而产生的那种抗拒. 它无关理解力和情绪，而是好像一种生理上的疾病，自己对自己的一部分无能为力. 我甚至连一个小节都无法读完，无法控制眼球转开去，似乎它在扭来扭去要找到一个停顿，否则就不肯继续工作. 同时，读完一段文字之后，头脑里突然会空白一下，然后那些字句和含义就突然消失掉了.

【fragmentation】ORACLE中碎片小结

- - CSDN博客数据库推荐文章
碎片的产生总的来讲是因为不断插入，修改使得产生一些小的无法利用的空闲空间. 碎片根据产生的位置不同分多种情况，其层级关系如下：. 我们这里针对tablespace-level fragmentation（table,index）进行介绍. 因此对于DMT 有必要定期做alter tablespace  coalesce的合并操作， 将小的extents合并成大的extents，减少碎片.

mysql碎片整理方法

- - 数据库 - ITeye博客
　　对于一个表如果经常插入数据和删除数据，则会产生很多不连续的碎片，这样久而久之，这个表就会占用很大空间，但实际上表里面的记录数却很少，这样不但会浪费空间，并且查询速度也更慢，因此为了解决这个问题，可以有心下解决方案. １、myisam存储引擎清理碎片方法. ２、innodb存储引擎清理碎片方法. 从上面的DATA_FREE字段可以看出碎片空间很大.

ElasticSearch 索引 VS MySQL 索引

- - crossoverJie's Blog
这段时间在维护产品的搜索功能，每次在管理台看到 elasticsearch 这么高效的查询效率我都很好奇他是如何做到的. 这甚至比在我本地使用 MySQL 通过主键的查询速度还快. 这类问题网上很多答案，大概意思呢如下：. Lucene 的全文检索引擎，它会对数据进行分词后保存索引，擅长管理大量的索引数据，相对于.