对oracle索引的认识及相关操作

 

今天要对表做索引，大学基本没听过课，于是在网上搜了些文章，觉得很有用，总结一下：

对oracle索引的认识：

索引是用于加速数据存取数据对象，合理的使用索引可以大大降低I/O次数，从而提高数据访问性能。说白了，就是从表中取出相应的列，当作一个新表，查的时候先查这个表。

第一讲、索引并非总是最佳选择

　　如果发现Oracle 在有索引的情况下，没有使用索引，这并不是Oracle 的优化器出错。在有些情况下，Oracle 确实会选择全表扫描（Full Table Scan）,而非索引扫描（Index Scan）。这些情况通常有：

　　1. 表未做statistics, 或者 statistics 陈旧，导致 Oracle 判断失误。

　　2. 根据该表拥有的记录数和数据块数，实际上全表扫描要比索引扫描更快。

　　对第1种情况，最常见的例子，是以下这句sql 语句：

　　select count(*) from mytable;

　　在未作statistics 之前，它使用全表扫描，需要读取6000多个数据块（一个数据块是8k）, 做了statistics 之后，使用的是 INDEX (FAST FULL SCAN) ，只需要读取450个数据块。但是，statistics 做得不好，也会导致Oracle 不使用索引。

　　第2种情况就要复杂得多。一般概念上都认为索引比表快，比较难以理解什么情况下全表扫描要比索引扫描快。

第二讲、索引也有好坏

　　索引有 B tree 索引， Bitmap 索引， Reverse b tree 索引， 等。最常用的是 B tree 索引。 B 的全称是Balanced , 其意义是，从 tree 的 root 到任何一个leaf ，要经过同样多的 level. 索引可以只有一个字段（Single column）, 也可以有多个字段（Composite）,最多32个字段，8I 还支持 Function-based index. 许多developer 都倾向于使用单列B 树索引。

　　所谓索引的好坏是指：

　　1，索引不是越多越好。特别是大量从来或者几乎不用的索引，对系统只有损害。OLTP系统每表超过5个索引即会降低性能，而且在一个sql 中， Oracle 从不能使用超过 5个索引。

　　2，很多时候，单列索引不如复合索引有效率。

　　3，用于多表连结的字段，加上索引会很有作用。

　　那么，在什么情况下单列索引不如复合索引有效率呢？有一种情况是显而易见的，那就是，当sql 语句所查询的列，全部都出现在复合索引中时，此时由于 Oracle 只需要查询索引块即可获得所有数据，当然比使用多个单列索引要快得多。（此时，这种优化方式被称为 Index only access path）

　第三讲、索引再好，不用也是白搭

　　抛开前面所说的，假设你设置了一个非常好的索引，任何傻瓜都知道应该使用它，但是Oracle 却偏偏不用，那么，需要做的第一件事情，是审视你的 sql 语句。

　　Oracle 要使用一个索引，有一些最基本的条件：

　　1， where 子句中的这个字段，必须是复合索引的第一个字段；

　　2， where 子句中的这个字段，不应该参与任何形式的计算

　　具体来讲，假设一个索引是按 f1, f2, f3的次序建立的，现在有一个 sql 语句, where 子句是 f2 = : var2, 则因为 f2 不是索引的第1个字段，无法使用该索引。

索引的相关操作：

一、创建索引

1、单列索引

单列索引是基于单个列所建立的索引。

sql>create index 索引名 on 表名(列名);

 

2、复合索引

复合索引是基于两列或是多列的索引，在同一张表上可以有多个索引，但是要求列的组合必须不同。

sql>create index emp_index1 on emp(ename,job);

sql>create index emp_index2 on emp(job,ename);

二、使用索引的原则

使用原则：

1、在大表上建立索引才有意义。

2、在where子句或是连接条件上经常饮用的列上建立索引。

3、索引的层次不要超过4层。

 

三、索引缺点分析

索引有一些先天不足：

1、建立索引，系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引。

2、更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，一维持数据和索引的一致性。

实践表明，不恰当的索引不但于事无补，反而会降低系统性能。因为大量的索引在进行插入、修改和删除操作时比没有索引花费更多的系统时间。

比如在如下字段建立索引应该是不恰当的：

1、很少或从不引用的字段。

2、逻辑型的字段，如男或女（是或否）等。

综上所述，提高查询效率是以消耗一定的系统资源为代价的，索引不能盲目的建立，这是考验一个DBA是否优秀的很重要的指标。

 

四、其他索引

按照数据存储方式，可以分为B*树、反向索引、位图索引。

按照索引列的个数分类，可以分为单列索引、复合索引。

按照索引列值的唯一性，可以分为唯一索引和非唯一索引。

此外还有函数索引，全局索引，分区索引……

 

五、显示索引信息

1、显示表的所有索引

在同一张表上可以有多个索引，通过查询数据字典视图dba_indexs和user_indexs，可以显示索引信息。其中dba_indexs用于显示数据库所有的索引信息，而user_indexs用于显示当前用户的索引信息。

sql>select index_name,index_type from user_indexes where table_name='表名';

 

2、显示索引列

通过查询数据字典视图user_ind_columns，可以显示索引对应的列的信息。

sql>select table_name,column_name from user_ind_columns where index_name='IND_ENAME';