189 8069 5689

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

随着时间的推移，索引也可能会产生碎片，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：

创新互联公司主要为客户提供服务项目涵盖了网页视觉设计、VI标志设计、营销推广、网站程序开发、HTML5响应式成都网站建设、手机网站制作、微商城、网站托管及成都企业网站维护、WEB系统开发、域名注册、国内外服务器租用、视频、平面设计、SEO优化排名。设计、前端、后端三个建站步骤的完善服务体系。一人跟踪测试的建站服务标准。已经为成都报废汽车回收行业客户提供了网站改版服务。

[sql] view plain copy
  1. SQL> create index emp_idx1 on emp(empno) parallel (degree 8);  
  2.   
  3. Index created.  
  4.   
  5. SQL> select index_name,degree from user_indexes where table_name='EMP';  
  6.   
  7. INDEX_NAME           DEGREE  
  8. -------------------- ----------------------------------------  
  9. 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] view plain copy
  1. SQL> desc index_stats;  
  2.  Name                                      Null?    Type  
  3.  ----------------------------------------- -------- ----------------------------  
  4.  HEIGHT                                             NUMBER  （代表索引高度）  
  5.  BLOCKS                                             NUMBER  （索引占用块数）  
  6.  NAME                                               VARCHAR2(30)（索引名字）  
  7.  PARTITION_NAME                                     VARCHAR2(30)（分区索引名字）  
  8.  LF_ROWS                                            NUMBER （叶子行数）  
  9.  LF_BLKS                                            NUMBER  （在b树索引中叶子的块数）  
  10.  LF_ROWS_LEN                                        NUMBER  （所有叶子行数的长度）  
  11.  LF_BLK_LEN                                         NUMBER  （在一片叶子中可用空间）  
  12.  BR_ROWS                                            NUMBER  （在B树索引中有多少个分支行）  
  13.  BR_BLKS                                            NUMBER  （在B树索引中有多少个分支块）  
  14.  BR_ROWS_LEN                                        NUMBER  （在B树索引中所有分支块的总长度）  
  15.  BR_BLK_LEN                                         NUMBER  （在分支快中可用的空间）  
  16.  DEL_LF_ROWS                                        NUMBER  （在索引中删除叶子行数）  
  17.  DEL_LF_ROWS_LEN                                    NUMBER  （在索引中删除叶子行数的总的长度）  
  18.  DISTINCT_KEYS                                      NUMBER  （唯一值数目包括删除的行）  
  19.  MOST_REPEATED_KEY                                  NUMBER  
  20.  BTREE_SPACE                                        NUMBER  （当前分给该 索引总的大小空间）  
  21.  USED_SPACE                                         NUMBER  （已经被索引使用的空间大小包含被删的行数空间）  
  22.  PCT_USED                                           NUMBER  （索引空间使用率）  
  23.  ROWS_PER_KEY                                       NUMBER  （每个不同键值的平均行数不包括删除行）  
  24.  BLKS_GETS_PER_ACCESS                               NUMBER    
  25.  PRE_ROWS                                           NUMBER  （前缀行数）  
  26.  PRE_ROWS_LEN                                       NUMBER  （前缀行的总长度）  
  27.  OPT_CMPR_COUNT                                     NUMBER  （压缩长度）  
  28.  OPT_CMPR_PCTSAVE                                   NUMBER  
  29.   
  30. 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] view plain copy
  1. SQL> create table test as select rownum id,'Amy' text from dual connect by level<=10000;  
  2.   
  3. Table created.  
  4.   
  5. SQL> select count(*) from test;  
  6.   
  7.   COUNT(*)  
  8. ----------  
  9.      10000  
  10.   
  11. SQL> create index test_idx1 on test(id);  
  12.   
  13. Index created.  
  14.   
  15. SQL> select * from index_stats;  
  16.   
  17. no rows selected  
  18.   
  19. SQL> analyze index test_idx1 validate structure;  
  20.   
  21. Index analyzed.  
  22. SQL> r  
  23.   1  select height,  
  24.   2         lf_rows,  
  25.   3         lf_blks,  
  26.   4         del_lf_rows,  
  27.   5         btree_space,  
  28.   6         used_space,  
  29.   7         pct_used,  
  30.   8         ((used_space - del_lf_rows_len) / btree_space) pct_unused,  
  31.   9         ((lf_rows - del_lf_rows) / lf_rows) pct_undel_rows  
  32.  10    from index_stats  
  33.  11   where name = 'TEST_IDX1'  
  34.  12*  
  35.   
  36.     HEIGHT    LF_ROWS    LF_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE   PCT_USED PCT_UNUSED PCT_UNDEL_ROWS  
  37. ---------- ---------- ---------- ----------- ----------- ---------- ---------- ---------- --------------  
  38.          2      10000         21           0      175944     150021         86 .852663347              1  
  39.   
  40. SQL>   
  41. SQL> analyze index test_idx1 validate structure;  
  42.   
  43. Index analyzed.  
  44.   
  45. SQL> select height,  
  46.   2         lf_rows,  
  47.   3         lf_blks,  
  48.   4         del_lf_rows,  
  49.   5         btree_space,  
  50.   6         used_space,  
  51.   7         pct_used,  
  52.   8         ((used_space - del_lf_rows_len) / btree_space) pct_unused,  
  53.   9         ((lf_rows - del_lf_rows) / lf_rows) pct_undel_rows  
  54.  10    from index_stats  
  55.  11   where name = 'TEST_IDX1';  
  56.   
  57.     HEIGHT    LF_ROWS    LF_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE   PCT_USED PCT_UNUSED PCT_UNDEL_ROWS  
  58. ---------- ---------- ---------- ----------- ----------- ---------- ---------- ---------- --------------  
  59.          2      10000         21        9999      175944     150021         86 .001329969          .0001  
  60.   
  61. SQL>   
  62. SQL> alter index test_idx1 deallocate unused;  
  63.   
  64. Index altered.  
  65.   
  66. SQL> analyze index test_idx1 validate structure;  
  67.   
  68. Index analyzed.  
  69. SQL> select height,  
  70.   2         lf_rows,  
  71.   3         lf_blks,  
  72.   4         del_lf_rows,  
  73.   5         btree_space,  
  74.   6         used_space,  
  75.   7         pct_used,  
  76.   8         ((used_space - del_lf_rows_len) / btree_space) pct_unused,  
  77.   9         ((lf_rows - del_lf_rows) / lf_rows) pct_undel_rows  
  78.  10    from index_stats  
  79.  11   where name = 'TEST_IDX1';  
  80.   
  81.     HEIGHT    LF_ROWS    LF_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE   PCT_USED PCT_UNUSED PCT_UNDEL_ROWS  
  82. ---------- ---------- ---------- ----------- ----------- ---------- ---------- ---------- --------------  
  83.          2      10000         21        9999      175944     150021         86 .001329969          .0001  
  84.   
  85. SQL>   


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

[sql] view plain copy
  1. SQL> exec dbms_stats.gather_table_stats('SYS','TEST',cascade=>true);  
  2.   
  3. PL/SQL procedure successfully completed.  
  4.   
  5. SQL>  select index_name,leaf_blocks,num_rows,degree from dba_indexes where index_name='TEST_IDX1';  
  6.   
  7. INDEX_NAME                     LEAF_BLOCKS   NUM_ROWS DEGREE  
  8. ------------------------------ ----------- ---------- ----------------------------------------  
  9. TEST_IDX1                                1          1 1  
  10.   
  11. SQL> set autotrace trace exp  
  12. sSQL>     
  13. SQL> select * from test where id<20;  
  14.   
  15. Execution Plan  
  16. ----------------------------------------------------------  
  17. Plan hash value: 2624864549  
  18.   
  19. -----------------------------------------------------------------------------------------  
  20. | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |  
  21. -----------------------------------------------------------------------------------------  
  22. |   0 | SELECT STATEMENT            |           |     1 |     7 |     3   (0)| 00:00:01 |  
  23. |   1 |  TABLE ACCESS BY INDEX ROWID| TEST      |     1 |     7 |     3   (0)| 00:00:01 |  
  24. |*  2 |   INDEX RANGE SCAN          | TEST_IDX1 |     1 |       |     2   (0)| 00:00:01 |  
  25. -----------------------------------------------------------------------------------------  
  26.   
  27. Predicate Information (identified by operation id):  
  28. ---------------------------------------------------  
  29.   
  30.    2 - access("ID"<20)  
  31.   
  32. SQL>   


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

[sql] view plain copy
  1. SQL> select * from test;           
  2.   
  3.         ID TEX  
  4. ---------- ---  
  5.      10000 Amy  
  6.   
  7. SQL> insert into test values(10001,'Rhys');  
  8. insert into test values(10001,'Rhys')  
  9.                               *  
  10. ERROR at line 1:  
  11. ORA-12899: value too large for column "SYS"."TEST"."TEXT" (actual: 4, maximum: 3)  
  12.   
  13.   
  14. SQL> desc test               
  15.  Name                                                                                                              Null?    Type  
  16.  ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------  
  17.  ID                                                                                                                         NUMBER  
  18.  TEXT                                                                                                                       CHAR(3)  
  19.   
  20. SQL> alter table test modify text char(15);  
  21.   
  22. Table altered.  
  23.   
  24. SQL> insert into test values(10001,'Rhys');  
  25.   
  26. 1 row created.  
  27.   
  28. SQL> commit;  
  29.   
  30. Commit complete.  
  31.   
  32. SQL> select height,  
  33.   2         lf_rows,  
  34.   3         lf_blks,  
  35.   4         del_lf_rows,  
  36.   5         btree_space,  
  37.   6         used_space,  
  38.        pct_used,  
  39.   7    8         ((used_space - del_lf_rows_len) / btree_space) pct_unused,  
  40.   9         ((lf_rows - del_lf_rows) / lf_rows) pct_undel_rows  
  41.  10    from index_stats  
  42.  11   where name = 'TEST_IDX1';  
  43.   
  44.     HEIGHT    LF_ROWS    LF_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE   PCT_USED PCT_UNUSED PCT_UNDEL_ROWS  
  45. ---------- ---------- ---------- ----------- ----------- ---------- ---------- ---------- --------------  
  46.          2      10000         21        9999      175944     150021         86 .001329969          .0001  
  47.   
  48. SQL> analyze index test_idx1 validate structure;  
  49.   
  50. Index analyzed.  
  51.   
  52. SQL> select height,  
  53.   2         lf_rows,  
  54.   3         lf_blks,  
  55.        del_lf_rows,  
  56.   4    5         btree_space,  
  57.   6         used_space,  
  58.        pct_used,  
  59.   7    8         ((used_space - del_lf_rows_len) / btree_space) pct_unused,  
  60.   9         ((lf_rows - del_lf_rows) / lf_rows) pct_undel_rows  
  61.  10    from index_stats  
  62.  where name = 'TEST_IDX1';  
  63.  11    
  64.     HEIGHT    LF_ROWS    LF_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE   PCT_USED PCT_UNUSED PCT_UNDEL_ROWS  
  65. ---------- ---------- ---------- ----------- ----------- ---------- ---------- ---------- --------------  
  66.          2       9584         21        9582      175944     143786         82 .001420907     .000208681  
  67.   
  68. 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).



select name,
         blocks,
         del_lf_rows_len,
         lf_rows_len,
         (del_lf_rows_len / lf_rows_len) * 100,
         (DEL_LF_ROWS / LF_ROWS) * 100
    from index_stats;
  
  NAME          BLOCKS     DEL_LF_ROWS_LEN      LF_ROWS_LEN      (DEL_LF_ROWS_LEN / LF_ROWS_LEN) * 100          (DEL_LF_ROWS / LF_ROWS) * 100
  ------------  ---------  -------------------  ---------------  ----------- ---------------------------------   -------------------------
  IND_OBJ_ID    384        766085               1906952          40.1732713                                      40.2394062
  
  索引碎片比率：(del_lf_rows_len / lf_rows_len) * 100，如果百分比超过20%就说明索引碎片比率很高了。需要整理碎片。


本文名称：索引碎片整理--重建索引、合并索引、shrink索引
当前地址：http://jkwzsj.com/article/pgjepd.html

其他资讯