ORACLE索引总结
一．B树索引
0. B树索引中不存在非唯一的条目。
1) 在非唯一索引中，ORACLE会把rowid作为一个额外的列追加到键上，使得键唯一。Exp：create index I on T( x , y ) ,从概念上讲就是Create unique index I on T(x , y , rowid)。ORACLE会首先按索引键值排序，然后再按照rowid升序排序。
2) 在唯一索引中，数据只按索引键值排序。
1． 大多数情况下，B树索引的高度都是2或者3，所以一般情况下，在索引中找到一个键只需要2或3次I/O。
2． B树索引所有叶子块都应该在同一层上，并且叶子节点实际上都是双向链表，这样在进行索引区间扫描(index range scan)的时候，只需通过叶子节点的向前或者向后就可以了，无需再对索引结构进行导航。
3． 适当对B树索引存在重复值的列进行压缩, 可以增加缓存命中率，使I/O数减少，因为
相关的条目可能都存在在一个块中。（Exp:create index I on T(userid , username) username=’steven’这个值可能就会对应于多个rowid放在同一个索引块中）；但是带来的负面作用是使索引结构复杂化，维护索引更耗时，查询索引占用CPU更多的时间。（压缩适合用于多列索引中）
4． B树索引的反向键索引主要用于缓解索引右侧缓冲区忙等待。适合用于类似于sequence产生的PK主键上，因为这些列不太会需要使用区间扫描，也就是不会用到max(PK),min(PK),between and或者where PK < 200等查询
5． 如果在查询中会有order by colum1 asc,column2 desc, 试着在创建索引时create index I on T(colum1asc,column2 desc) , 因为ORACLE INDEX默认是DESC排序，在索引中排序总比在磁盘中排序好得多。
6． 适合B树索引使用的2种情况：
1) 访问表中占很小比例的行
2) 根本不访问表，所需查询的数据全部在索引中
3) 一种特殊的用法，使用索引访问表的全部行，这样的做法是为了使查询最初的响应时间很短，不是针对总吞吐量进行的优化。
7． 索引是按索引键顺序存储，索引会按键的有序顺序进行访问。索引指向的块则随机存储在堆中。
8． 建议：在thin表中使用B树索引查询<2-3%的列，fat表中<20-25%的列。
二．函数索引
1. 函数索引的好处: Exp: function(column1) 如果表中有1000行，即便column1列上建有索引，索引在此时也不被使用，function会执行1000次。
2． 建立函数索引会使插入/更新性能稍有下降，但是查询速度有了极大的提高。另外如果更新的时候没有涉及到建立函数索引的这个列，那就不会产生额外的开销。
3． 部分行建立索引：
1) 某一列只有很少的基数，例如只有Y和N
2) 通常只会查询where column1 = ‘N’
3) 并且值为N的行占很小的比例
4) 在此列上建立索引，由于很大的一部分索引空间是浪费的(等于Y的)，不会使用到的，所以我们可以在值为’N’的那些行上面建立索引。
5) Create index I on T(decode(column1 , ‘N’, ‘N’))
6) 利用的是B树索引特性，如果列值为null,在索引中就没有相应的条目。
4． 使用函数索引来保证复杂的约束：(某个条件成立时，X,Y,Z必须唯一)
1) 如果项目表，项目有2种状态，ACTIVE和INACTIVE
2) 希望ACTIVE的项目必须有唯一的项目名，INACTIVE的项目没有此要求
3) 此时项目状态这列上我们不适合建立唯一键
4) 但是我们可以建立一个唯一索引
5) Create unique index I on T(decode(STATUS , ‘ACTIVE’ , NAME))
6) 利用的还是B树索引特性，如果列值为null,在索引中就没有相应的条目。
三．索引的一些常见问题
1．B树索引特性，如果列值为null,在索引中就没有相应的条目。
1) Create table T (x int , y int)
2) Create unique index I T(x , y)
3) Insert into T values(1 , 1)
4) Insert into T values(null , 1)
5) Insert into T values(1 , null)
6) Insert into T values(null , null)
7) 此时索引中只有三行.(null , null)不在索引行中
8) Insert into T values(null , null) --成功插入
9) Insert into T values(1 , null) --报错，违反index唯一性
10) 所以我们可以看到ORACLE中null <> null (null代表N/A)
11) Where x is null --这个查询无法使用索引，因为（null,null）不在索引中，如果ORACLE使用索引就会得到错误的答案
12) 同样能够得出结论，如果在一个允许null的列上面建立索引，x is null也不会使用索引
13) 可以使用索引的条件Create table T (x int , y int not null);
2. 外键建立索引是需要的
3. 索引跳跃式扫描
1) Create index I T(x , y)
2) Select * from T where x=5; --此时优化器可能不会使用索引
3) Select x , y from T where x=5; --可能使用索引，因为所需要的内容都在索引中
4) 索引跳跃式扫描—skip scan
5) 如果y只有2个基数，Y和N ,oracle会采用index
6) Select * from T where x=5; --会经过如下处理
7) Select * from T where x=5 and y=’Y’
8) Union all
9) Select * from T where x=5 and y=’N’;
4. 如果索引建立在一个允许null的列上面, select * from T就会使用全表扫描，不使用索引，因为null的行并不在索引行中，所以不会使用index统计数目。
5. select * from T where x=5 等价于select * from T where to_number(x)=’5’,由于存在隐式转换，所以x上的索引不会得到使用
6. where trunc(date) = trunk(sysdate) 可以转换成 date >=trunc(sysdate) and date<trunk(sysdate+1)
7. 定期分析表
如果发现Oracle 在有索引的情况下，没有
