oracle索引详解

分类：

 逻辑上：1、单列索引 2、多行索引 3、唯一索引(意味着不会有两行记录相同的索引键值) 4、非唯一索引 5、函数索引 6、域索引 

7、函数索引

 物理上：1、分区索引(是指索引可以分散地存在于多个不同的表空间中，其优点是可以提高数据查询的效率。) 2、非分区索引  (全表索引)

数据结构：

B-tree：

1、适合与大量的增、删、改（OLTP）

2、不能用包含OR操作符的查询

3、适合高基数的列（唯一值多）

4、B*树索引的存储结构类似书的索引结构，有分支和叶两种类型的存储数据块，分支块相当于书的大目录，叶块相当于索引到的具体的书页。Oracle用B*树机制存储索引条目，以保证用最短路径访问键值。默认情况下大多使用B*树索引，该索引就是通常所见的唯一索引、逆序索引。

Bitmap: 

1、适合与决策支持系统
2、做UPDATE代价非常高
3、非常适合OR操作符的查询
4、位图索引存储主要用于节省空间，减少oracle对数据块的访问。它采用位图偏移方式来与表的行ID号对应，采用位图索引一般是重复值太多的表字段。

使用原则：

1、索引应该经常建在Where 子句经常用到的列上。如果某个大表经常使用某个字段进并且检索行数小于总表行数的15%。则应该考虑。

2、对于两表连接的字段，应该建立索引。如果经常在某表的一个字段进行Order By 则也经过进行索引。

3、主键和唯一关键字所在的列自动具有索引，但应该在与之关联的表中的外部关键字所在的列上创建索引。

4、索引主要进行提高数据的查询速度。 当进行DML时，会更新索引。因此索引越多，则DML越慢，其需要维护索引。 因此在创建索引及DML需要权衡。

创建索引:

1、单列索引

Create Index <自定义索引名Index_Name> On <Table_Name>(Column_Name)

2、多列索引（复合索引）

Create Index <自定义索引名Index_Name> On <Table_Name>(Column_Name1，Column_Name2)

索引失效情况：

1、索引列上不要使用is not null/is null 如果某列建立索引,当进行Select * from emp where depto is not null/is null。 则会是索引失效。

2、索引列上不要使用函数,SELECT Col FROM tbl WHERE substr(name ,1 ,3 ) = 'ABC' 注意：对于 MIN, MAX 函数，Oracle 仍然使用索引。

3、索引列上不要进行计算SELECT Col FROM tbl WHERE col / 10 > 10 则会使索引失效

4、索引列上不要使用（ != 、 <> ）如:SELECT Col FROM tbl WHERE col ! = 10 
应该 改成：SELECT Col FROM tbl WHERE col > 10 OR col < 10 。

5、索引列上不要使用 LIKE ‘%T’ 进行模糊查询会使索引失效

6、索引列上不要比较不匹配数据类型， 例如：

dept_id是一个varchar2型的字段，在这个字段上有索引，但是下面的语句会执行全表扫描。

select * from dept where dept_id = 900198;

这是因为 oracle 会自动把 where 子句转换成 to_number(dept_id)=900198，相当于使用函数，这样就限制了索引的使用。正确写法如下：

select * from dept where dept_id = '900198';

7、复合索引不要单独使用非第一索引列

如果在where 子句中有OR 操作符或单独引用Column_Name2列(索引列的后面列) 则将不会走索引，将会进行全表扫描。

重建索引：

重建索引的前提：
1、表上频繁发生update,delete操作；
2、表上发生了alter table ..move操作（move操作导致了rowid变化）

重建索引的标准:
1、索引重建是否有必要，一般看索引是否倾斜的严重，是否浪费了空间， 那应该如何才可以判断索引是否倾斜的严重，是否浪费了空间， 对索引进行结构分析（如下）：
SQL>Analyze index index_name validate structure;
2、在执行步骤1的session中查询index_stats表，不要到别的session去查询。
SQL>select height,DEL_LF_ROWS/LF_ROWS from index_stats;
说明：当 查询出来的 height>=4 或者 DEL_LF_ROWS/LF_ROWS>0.2 的场合 ， 该索引考虑重建 。

重建索引的方式:

alter index indexname rebuild; 或alter index indexname rebuild online;
说明：此方式比较快，可以在24*7环境中实现，建议使用此方式。

注意：rebuild 会阻塞 dml 操作 ，rebuild online 不会阻塞 dml 操作 

重建分区索引方法：

1、Alter index indexname rebuild partition paritionname tablespace tablespacename;
Alter index indexname rebuild subpartition partitioname tablespace tablespacename;

说明：Partition name 可以从user_ind_partitions查找
Tablepace 参数允许alter index操作更改索引的存储空间

索引状态描述：

在数据字典中查看索引状态，发现有三种：
1、valid:当前索引有效
2、N/A :分区索引有效
3、unusable:索引失效

其他：

1、truncate 分区操作和truncate 普通表的区别？
2、Truncate 分区操作会导致全局索引失效; truncate 普通表对索引没有影响；
3、Truncate 分区操作不会释放全局索引中的空间，而truncate 普通表会释放索引所占空间；
4、rename 表名操作对索引没有影响，因为rename操作只是更改了数据字典，表中数据行的rowid并没有发生变化