oracle索引

1. b-tree索引
Oracle数据库中最常见的索引类型是b-tree索引，也就是B-树索引，以其同名的计算科学结构命名。CREATE INDEX语句时，默认就是在创建b-tree索引。没有特别规定可用于任何情况。

2. 位图索引(bitmap index)
位图索引特定于该列只有几个枚举值的情况，比如性别字段，标示字段比如只有0和1的情况。

3. 基于函数的索引
比如经常对某个字段做查询的时候是带函数操作的，那么此时建一个函数索引就有价值了。

4. 分区索引和全局索引
这2个是用于分区表的时候。前者是分区内索引，后者是全表索引

5. 反向索引（REVERSE）
这个索引不常见，但是特定情况特别有效，比如一个varchar(5)位字段(员工编号)含值（10001,10002,10033,10005,10016..）
这种情况默认索引分布过于密集，不能利用好服务器的并行
但是反向之后10001,20001,33001,50001,61001就有了一个很好的分布，能高效的利用好并行运算。

6.HASH索引
HASH索引可能是访问数据库中数据的最快方法，但它也有自身的缺点。集群键上不同值的数目必须在创建HASH集群之前就要知道。需要在创建HASH集群的时候指定这个值。使用HASH索引必须要使用HASH集群。

7、创建索引

CREATE [UNIQUE] | [BITMAP] INDEX index_name  --unique表示唯一索引
ON table_name([column1 [ASC|DESC],column2    --bitmap，创建位图索引
[ASC|DESC],…] | [express])
[TABLESPACE tablespace_name]
[PCTFREE n1]                                 --指定索引在数据块中空闲空间
[STORAGE (INITIAL n2)]
[NOLOGGING]                                  --表示创建和重建索引时允许对表做DML操作，默认情况下不应该使用
[NOLINE]
[NOSORT];                                    --表示创建索引时不进行排序，默认不适用，如果数据已经是按照该索引顺序排列的可以使用

唯一索引和非唯一索引区别：如果是唯一索引，当根据条件查找到一条记录的时候，然后还会继续匹配后面的数据，如果是唯一索引，则停止搜索。默认是非唯一索引。

8、索引使用一段时间会产生碎片

1）重命名索引

alter index index_sno rename to bitmap_index;

2)合并索引（表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者合并索引,合并索引方式更好些，无需额外存储空间，代价较低）

alter index index_sno coalesce;

3)重建索引

　　方式一：删除原来的索引（drop index myindex），重新建立索引

　　方式二：alter index index_sno rebuild;

9、查看索引

select index_name,index-type, tablespace_name, uniqueness from all_indexes where table_name ='tablename';

10、建索引的原则

　1） 如果有两个或者以上的索引，其中有一个唯一性索引，而其他是非唯一，这种情况下oracle将使用唯一性索引而完全忽略非唯一性索引

　2）至少要包含组合索引的第一列（即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引）

　　当创建(a,b,c)联合索引时，相当于创建了(a)单列索引，(a,b)联合索引以及(a,b,c)联合索引
　　想要索引生效的话,只能使用 a和a,b和a,b,c三种组合；当然，我们上面测试过，a,c组合也可以，但实际上只用到了a的索引，c并没有用到！

　3） 小表不要简历索引

　4）对于基数大的列适合建立B树索引，对于基数小的列适合简历位图索引

　5）列中有很多空值，但经常查询该列上非空记录时应该建立索引

　6） 经常进行连接查询的列应该创建索引

　7） 使用create index时要将最常查询的列放在最前面

　8）LONG（可变长字符串数据，最长2G）和LONG RAW（可变长二进制数据，最长2G）列不能创建索引

　9）限制表中索引的数量（创建索引耗费时间，并且随数据量的增大而增大；索引会占用物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度）

11、注意事项

1）不要使用！=，<>匹配，用dept_no<10  or dept_no>10代替。

2）不要使用is null或者is not null,给列设置非空有默认值。

3）不要对索引字段加函数，比如date类型的birth字段加了索引，不要使用to_char(birthdate,'YYYY-MM-DD') = '2015',而是使用birthdate=to_date('2015','YYYY-MM-DD')

4) 不要比较不匹配的类型比如id是varchar2类型的，然后id=10000;数据库是做了to_number(id)=1000的操作，导致索引失效，所以要规范写id='1000';

5)使用like语句，name  like ‘aaa%’ 是可以的 ,name   like ‘%aaa%’用不到

6)尽管In写法要比exists简单一些，exists一般来说性能要比In要高的多用In还是用Exists的时机当in的集合比较小的时候

,或者用Exists无法用到选择性高的索引的时候，用In要好，否则就要用Exists

7）如果能不用到排序，则尽量避免排序。

8）如果获取的数据量过大，全部扫描效率更高