oracle索引，分析索引，索引碎片整理

概述

索引分为B树索引和位图索引。我们主要研究B树索引，B树索引如下图（图片源自网络）：

　　索引是与表相关的一个可选结构，在逻辑上和物理上都独立于表数据，索引能优化查询，不能优化DML，oracle自动维护索引，频繁的DML操作反而会引起大量的索引维护。

　　如果sql语句仅仅访问被索引的列，那么数据库只需从索引中读取数据，而不会读取表；如果该语句还要访问未被索引的列，那么数据库会使用rowid来查找表中的行，通常，为检索表数据，数据库以交换方式先读取索引块，然后读取对应的表。

　　索引的目的是减少IO，

1. 大表，返回的行数<5%
2. 经常使用where子句查询的列
3. 离散度高的列
4. 更新键值代价低
5. 逻辑AND、OR效率高
6. 查看索引建在哪表哪列
   select * from user_indexes;
   select * from user_ind_columns;

索引的使用

1. 唯一索引
   create unique index empno_idx on emp(empno);
2. 一般索引
   create index empno_idx on emp(empno);
3. 组合索引
   create index job_deptno_idx on emp(job,deptno);
4. 函数索引：查询时必须用到这个函数才会使用到。
   create index fun_idx on emp(lower(ename));
5. 。。。

索引问题

查看执行计划：set autotrace traceonly explain；

　　索引碎片问题：由于基表做DML操作，导致索引表块的自动更改操作，尤其是基表的delete操作会引起index表的index_entries的逻辑删除，注意只有当一个索引块中的全部index_entry都被删除了，才会把这个索引块删除，索引对基表的delete、insert操作都会产生索引碎片问题。

　　在oracle文档中并没有清晰的给出索引碎片的量化标准，oracle建议通过segment advisor（片段顾问）解决表和索引的碎片问题（后面的课程会提及），如果你想自行解决，可以通过查看 index_stats视图，当以下三种情形之一发生时，说明积累的碎片应该整理了（经验之谈）

1. height >= 4 （概述中图示索引树的高度是3）
2. pct_used < 50%
3. del_lf_rows/lf_row > 0.2

实操：

先建表，建索引

然后插入1000000条数据

 

分析索引：analyse index t_idx validate structure;

查看分析结果：select name,height,pct_used,del_lf_rows/lf_rows from index_stats;

 

然后我们来破坏一下索引，重新查看一下分析结果

可以看到最后一个指标变了。说明产生一些碎片了。那么需要进行整理：

可见最后一个指标正常了（低于0.2）。