Oracle索引优化

什么是索引　　
　　索引是建立在表的一列或多个列上的辅助对象，目的是加快访问表中的数据；　　
　　Oracle存储索引的数据结构是B*树，位图索引也是如此，只不过是叶子节点不同B*数索引；　　
　　索引由根节点、分支节点和叶子节点组成，上级索引块包含下级索引块的索引数据，叶节点包含索引数据和确定行实际位置的rowid。

索引的种类　　
　　非唯一索引（最常用）
　　唯一索引
　　位图索引
　　局部有前缀分区索引
　　局部无前缀分区索引
　　全局有前缀分区索引
　　散列分区索引
　　函数的索引

索引正确的表和列
　　经常检索排序大表中40%或非排序表7%的行，建议建索引；
　　为了改善多表关联，索引列用于联结；
　　列中的值相对比较唯一；
　　取值范围（大：B*树索引，小：位图索引）；
　　Date型列一般适合基于函数的索引；
　　列中有许多空值，不适合建立索引
　　
为性能而安排索引列
　　经常一起使用多个字段检索记录，组合索引比单索引更有效；
　　把最常用的列放在最前面，例：dx_groupid_serv_id(groupid,serv_id)，在where条件中使用groupid或groupid,serv_id，查询将使用索引，若仅用到serv_id字段，则索引无效；
　　合并/拆分不必要的索引

限制每个表索引的数量　　
　　一个表可以有几百个索引（你会这样做吗？），但是对于频繁插入和更新表，索引越多系统CPU，I/O负担就越重；
　　建议每张表不超过5个索引。

考虑用nologging创建索引　　
　　对大表创建索引可以使用nologging来减少重做日志；
　　节省重做日志文件的空间；
　　缩短创建索引的时间；
　　改善了并行创建大索引时的性能。

创建基于函数的索引
　　
　　。常用与UPPER、LOWER、TO_CHAR(date)等函数分类上，例：
　　create index idx_func on emp (UPPER(ename)) tablespace tablespace_name;
　　
创建位图索引
　　对基数较小，且基数相对稳定的列建立索引时，首先应该考虑位图索引，例：
　　create bitmap index idx_bitm on class (classno) tablespace tablespace_name;
　　
明确地创建唯一索引　　
　　可以用create unique index语句来创建唯一索引，例：
　　create unique index dept_unique_idx on dept(dept_no) tablespace idx_1;
　　
创建与约束相关的索引　　
　　可以用using index字句，为与unique和primary key约束相关的索引，例如：
　　alter table table_name
　　add constraint PK_primary_keyname primary key (field_name)
　　using index tablespace tablespace_name；
　　
如何创建局部分区索引　　
　　基础表必须是分区表；
　　分区数量与基础表相同；
　　每个索引分区的子分区数量与相应的基础表分区相同；

重建现存的索引　　
　　重建现存的索引的当前时刻不会影响查询；　　
　　重建索引可以删除额外的数据块；　　
　　提高索引查询效率；
　　alter index idx_name rebuild nologging;　
　　对于分区索引：
　　alter index idx_name rebuild partition partiton_name nologging;

建立索引的代价　　
　　基础表维护时，系统要同时维护索引，不合理的索引将严重影响系统资源，主要表现在CPU和I/O上；　　
　　插入、更新、删除数据产生大量db file sequential read锁等待；