oracle index学习总结

oracle index

 

1.index需要储存空间和I/O操作。

2.index的目的是加快select的速度的。

3.insert,update,delete数据oracle会同时对索引进行相应的调整，因此会增加一定的消耗。

4.使用index一定能加快select速度吗？不是的，数据少和巨大时index会影响select的速度，因此如果查询速度可以满足，就不要建index。

5．Index 对null 无效。

 

分类：

一、从物理角度

1. partitioned or nonpartitioned : 分区或不分区索引。分区索引用于分区表。

2.B-tree(平衡树) : normal or reverse key 正常和倒序索引。

   oracle默认索引方式，平衡树形索引，在叶子节点上有双向链表，加快索引定位速度，oracle有一定的优化，可以根据链表直接定位记录，而不走树，综合使用提高速度。见图1和图2。

图1

 

 

图2

 

 

 

 

 

 

 

 

 

 

 

 

 

3.bitmap(位图)  ：用二进制的0、1来构建索引，在进行or操作时非常快, 但要注意bitmap对于并发操作时，改一条会锁了很多记录，因为所有的记录在一个索引条目上，所以修改或增加时会一起锁定，见图3.

图3

 

 

区别和使用场景

B-tree索引	Bitmap 索引
Suitable for high-cardinality columns（记录对应的列重复的值较少，如主键，姓名等 ）。	Suitable for low-cardinality columns(用在记录相同的值较多的列上，如果性别只有两种值：男和女)。
Updates on keys relatively inexpensive （在做updated时，b-tree只消耗很少的资源）。	Updates to key columns very expensive （在做updated时，bitmap的消耗是昂贵的）。
Inefficient for queries using OR predicates（where子句中 or条件较多时速度较慢）	Effcient for queries using OR predicates （where子句中 or条件较多时速度非常快）
Useful for OLTP（记录频繁的insert和update,查询相对较少的系统）。	Useful for data warehousing （OLIP）数据仓库，查询系统等较少做数据修改的系统。

 

 

二、逻辑角度：

1.single column or concatenated单索引和组合索引。

2.unique or nonunique: 唯一索引和非唯一索引。

3.function-based: 基于函数的索引，把一些where条件作为函数。

4.domain： 数据库以外的索引，如文件等。

 

 

三、创建index时的注意事项：

1.balance query and DML needs: 索引的目的是为了提高查询速度，但它会加重DML的负担。

2.place in separate tablespace: 索引和表应该放在不同的表空间，如果把索引和表放在同一个空间，会引起竞争，因为在读取一个表时，记录和索引是同时读取，修改也同步进行的。

3.use uniform extent sizes:Multipes of five blocks or minimum extent size for tablespace. 索引空间是extent是大小应该是5 blocks的倍数，因为oracle是一次读出5个blocks，如果你的extends是6，就会造成2次I/O操作。

4. consider nologging for large indexes： 在创建索引时可以关闭索引对应的redo 日志，提高速度，因为索引和数据不同，如果索引创建时出意外，数据还在，就再创建一次好了。

5.INITRANS should generally be higher on indexes than on the corresponding tables：INITRANS 参数比对应的表的值大些，因为索引也是已表记录的方式保存的，但索引大大小于表的记录，所以一个block中存储的索引记录就大大多于表在一个block中的记录，加大INITRANS可以增加在一个block中的事务的并发数，就提高了效率。

6.rebuilding indexes:如果删除一条记录，对应的索引仅仅是做了逻辑删除，只有一个block中的全部索引都被标识为逻辑删除，orcle才会真正的回收block, 这时这个block才能被再次利用，在表的记录做update时，index是先做了逻辑删除，然后再为该记录新建一个索引的，所以表在频繁的增删改后，就会造成index对应的block不完整，和系统碎片的情况是一致的，造成空间浪费，加大index的I/O，影响性能。而rebuilding indexes就可以回收原来的，重新构建一个高效的索引，但重构时会锁表。

              语法： alter index index_name rebuild;

7． coalescing indexes: 整理索引碎片，效率高，不锁表。

语法：Alter index  index_name coalesce;

 

四．管理索引

1.分析索引：

1) select * from user_objects where object_type='INDEX'

2）analyze index PK_T_TICKET validate structure;

3）select * from index_stats;

HEIGHT（b-tree的高度）	BLOCKS（索引有多少块）	NAME（索引名）	LF_ROWS（记录数）	DEL_LF_ROWS（删除记录数）
2	256	PK_T_TICKET	82775	792

当 DEL_LF_ROWS/ LF_ROWS>15%时应进行 索引重建或 索引碎片整理。

 

2.drop 索引：当屁量导入大量数据时，索引会影响导入速度。可以现在drop掉，导入后再重建索引。

 

3.监控索引：

    1）设置监控那个索引 alter index pk_t_ticket monitoring usage;

2）查看该索引用没有使用select * from v$object_usage

3）select count(1) from pk_t_ticket;

4) 查看该索引用没有使用select * from v$object_usage

5)关闭监控 alter index pk_t_ticket nomonitoring usage;

监控一个月就大概可以知道那些是无用的索引了。

6） 查询索引的详细信息 ： select * from all_ind_columns where index_name='PK_T_TICKET' .那个表的那个列上有索引及详细信息。