索引碎片整理--重建索引、合并索引、shrink索引 - 数据库编程

今天是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      1759

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