Oracle - 索引命令

前言：介绍Oracle索引操作的一些常用语句：

创建索引的一些规则：

1、权衡索引个数与DML之间关系，DML也就是插入、删除数据操作。

这里需要权衡一个问题，建立索引的目的是为了提高查询效率的，但建立的索引过多，会影响插入、删除数据的速度，因为我们修改的表数据，索引也要跟着修改。这里需要权衡我们的操作是查询多还是修改多。

2、把索引与对应的表放在不同的表空间。

当读取一个表时表与索引是同时进行的。如果表与索引和在一个表空间里就会产生资源竞争，放在两个表这空就可并行执行。

重新创建索引：

所引是由oracle自动完成，当我们对数据库频繁的操作时，索引也会跟着进行修改，当我们在数据库中删除一条记录时，对应的索引中并没有把相应的索引只是做一个删除标记，但它依然占据着空间。除非一个块中所有的标记全被删除的时，整个块的空间才会被释放。这样时间久了，索引的性能就会下降。这个时候可以重新建立一个干净的索引来提高效率。

SQL> alter index orders_region_id_idx rebuild tablespace index02;
通过上面的命令就可以重现建立一个索引，oracle重建立索引的过程：

1、锁表，锁表之后其他人就不能对表做任何操作。
2、创建新的（干净的）临时索引。
3、把老的索引删除掉
4、把新的索引重新命名为老索引的名字
5、对表进行解锁。

移动所引：

其实，我们移动索引到其它表空间也同样使用上面的命令，在指定表空间时指定不同的表空间。新的索引创建在别位置，把老的干掉，就相当于移动了。

SQL> alter index orders_region_id_idx rebuild tablespace index03;

在线重新创建索引：

上面介绍，在创建索引的时候，表是被锁定，不能被使用。对于一个大表，重新创建索引所需要的时间较长，为了满足用户对表操作的需求，就产生的这种在线重新创建索引。

SQL> alter index orders_id_idx rebuild online;
创建过程：

1、锁住表
2、创建立临时的和空的索引和IOT表用来存在on-going DML。普通表存放的键值，IOT所引表直接存放的表中数据；on-gong DML也就是用户所做的一些增删改的操作。
3、对表进行解锁
4、从老的索引创建一个新的索引。
5、IOT表里存放的是on-going DML信息，IOT表的内容与新创建的索引合并。
6、锁住表
7、再次将IOT表的内容更新到新索引中，把老的索引干掉。
8、把新的索引重新命名为老索引的名字
9、对表进行解锁

整合索引碎片：

如上图，在很多索引中有剩余的空间，可以通过一个命令把剩余空间整合到一起。

SQL> alter index orders_id_idx  coalesce;

删除索引：

SQL> drop  index  hr.departments_name_idx;

分析索引  

可以通过一条命令来完成分析索引，分析的结果会存放在在index_stats表中。

查看存放分析数据的表：
SQL> select count(*) from index_stats;

  COUNT(*)
----------
执行分析索引命令：
SQL> analyze index my_bit_idx validate structure;

Index analyzed.

再次查看 index_stats 已经有了一条数据
SQL> select count(*) from index_stats;

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

把数据查询出来：
SQL> select height,name,lf_rows,lf_blks,del_lf_rows from index_stats;

    HEIGHT   NAME              LF_ROWS   LF_BLKS   DEL_LF_ROWS
---------- ---------------------------------------------------------------------- ---------- -----------
  MY_BIT_IDX            1000          3            100

分析数据分析：
（HEIGHT）这个所引高度是2 ，（NAME）索引名为MY_BIT_IDX ，（LF_ROWS）所引表有1000行数据，（LF_BLKS）占用3个块，（DEL_LF_ROWS）删除100条记录。
这里也验证了前面所说的一个问题，删除的100条数据只是标记为删除，因为总的数据条数依然为1000条，占用3个块，那么每个块大于333条记录，只有删除的数据大于333条记录，这时一个块被清空，总的数据条数才会减少。