索引碎片整理--重建索引、合并索引、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：

1. SQL> create index emp_idx1 on emp(empno) parallel (degree 8);
2. Index created.
3. SQL> select index_name,degree from user_indexes where table_name='EMP';
4. INDEX_NAME DEGREE
5. -------------------- ----------------------------------------
6. 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;对索引结构分析之后将会填充相应的数据，一般该视图可以提供给我们足够的信息去引导我们是否需要对索引进行重建。
查看相关字段信息：

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. 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：

1. SQL> create table test as select rownum id,'Amy' text from dual connect by level<=10000;
2. Table created.
3. SQL> select count(*) from test;
4. COUNT(*)
5. ----------
6. 10000
7. SQL> create index test_idx1 on test(id);
8. Index created.
9. SQL> select * from index_stats;
10. no rows selected
11. SQL> analyze index test_idx1 validate structure;
12. Index analyzed.
13. SQL> r
14. 1 select height,
15. 2 lf_rows,
16. 3 lf_blks,
17. 4 del_lf_rows,
18. 5 btree_space,
19. 6 used_space,
20. 7 pct_used,
21. 8 ((used_space - del_lf_rows_len) / btree_space) pct_unused,
22. 9 ((lf_rows - del_lf_rows) / lf_rows) pct_undel_rows
23. 10 from index_stats
24. 11 where name = 'TEST_IDX1'
25. 12*
26. HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE PCT_USED PCT_UNUSED PCT_UNDEL_ROWS
27. ---------- ---------- ---------- ----------- ----------- ---------- ---------- ---------- --------------
28. 2 10000 21 0 175944 150021 86 .852663347 1
29. SQL>
30. SQL> analyze index test_idx1 validate structure;
31. Index analyzed.
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. 7 pct_used,
39. 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. HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE PCT_USED PCT_UNUSED PCT_UNDEL_ROWS
44. ---------- ---------- ---------- ----------- ----------- ---------- ---------- ---------- --------------
45. 2 10000 21 9999 175944 150021 86 .001329969 .0001
46. SQL>
47. SQL> alter index test_idx1 deallocate unused;
48. Index altered.
49. SQL> analyze index test_idx1 validate structure;
50. Index analyzed.
51. SQL> select height,
52. 2 lf_rows,
53. 3 lf_blks,
54. 4 del_lf_rows,
55. 5 btree_space,
56. 6 used_space,
57. 7 pct_used,
58. 8 ((used_space - del_lf_rows_len) / btree_space) pct_unused,
59. 9 ((lf_rows - del_lf_rows) / lf_rows) pct_undel_rows
60. 10 from index_stats
61. 11 where name = 'TEST_IDX1';
62. HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE PCT_USED PCT_UNUSED PCT_UNDEL_ROWS
63. ---------- ---------- ---------- ----------- ----------- ---------- ---------- ---------- --------------
64. 2 10000 21 9999 175944 150021 86 .001329969 .0001
65. SQL>

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

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

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

1. SQL> select * from test;
2. ID TEX
3. ---------- ---
4. 10000 Amy
5. SQL> insert into test values(10001,'Rhys');
6. insert into test values(10001,'Rhys')
7. *
8. ERROR at line 1:
9. ORA-12899: value too large for column "SYS"."TEST"."TEXT" (actual: 4, maximum: 3)
10. SQL> desc test
11. Name Null? Type
12. ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
13. ID NUMBER
14. TEXT CHAR(3)
15. SQL> alter table test modify text char(15);
16. Table altered.
17. SQL> insert into test values(10001,'Rhys');
18. 1 row created.
19. SQL> commit;
20. Commit complete.
21. SQL> select height,
22. 2 lf_rows,
23. 3 lf_blks,
24. 4 del_lf_rows,
25. 5 btree_space,
26. 6 used_space,
27. pct_used,
28. 7 8 ((used_space - del_lf_rows_len) / btree_space) pct_unused,
29. 9 ((lf_rows - del_lf_rows) / lf_rows) pct_undel_rows
30. 10 from index_stats
31. 11 where name = 'TEST_IDX1';
32. HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE PCT_USED PCT_UNUSED PCT_UNDEL_ROWS
33. ---------- ---------- ---------- ----------- ----------- ---------- ---------- ---------- --------------
34. 2 10000 21 9999 175944 150021 86 .001329969 .0001
35. SQL> analyze index test_idx1 validate structure;
36. Index analyzed.
37. SQL> select height,
38. 2 lf_rows,
39. 3 lf_blks,
40. del_lf_rows,
41. 4 5 btree_space,
42. 6 used_space,
43. pct_used,
44. 7 8 ((used_space - del_lf_rows_len) / btree_space) pct_unused,
45. 9 ((lf_rows - del_lf_rows) / lf_rows) pct_undel_rows
46. 10 from index_stats
47. where name = 'TEST_IDX1';
48. 11
49. HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE PCT_USED PCT_UNUSED PCT_UNDEL_ROWS
50. ---------- ---------- ---------- ----------- ----------- ---------- ---------- ---------- --------------
51. 2 9584 21 9582 175944 143786 82 .001420907 .000208681
52. 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%就说明索引碎片比率很高了。需要整理碎片。