索引

索引

一.概念及作用

在oracle中，索引是一种供服务器在表中快速查找一个行的数据库结构。在数据库中建立索引主要有以下作用。
1.快速存取数据。
2.既可以改善数据库性能，又可以保证列值的唯一性。
3.实现表与表之间的参照完整性。
4.在使用orderby、groupby子句进行数据检索时，利用索引可以减少排序和分组的时间。
5.一种独立于表的模式对象, 可以存储在与表不同的磁盘或表空间中。
6.索引被删除或损坏, 不会对表产生影响, 其影响的只是查询的速度。
7.索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 Oracle 管理系统决定何时使用索引。用户不用在查询语句中指定使用哪个索引。
8.在删除一个表时,所有基于该表的索引会自动被删除。
9.通过指针加速 Oracle 服务器的查询速度。
10.通过快速定位数据的方法，减少磁盘 I/O。

二.索引分类

在关系数据库中，每一行都由一个行唯一标识RowID。RowID包括该行所在的文件、在文件中的块数和块中的行号。索引中包含一个索引条目，每一个索引条目都有一个键值和一个RowID，其中键值可以是一列或者多列的组合。

1. 索引按存储方法分类，可以分为2类：B树索引和位图索引。
   1.1、B树索引的存储结构类似书的索引结构，有分支和叶两种类型的存储数据块，分支块相当于书的大目录，叶块相当于索引到的具体的书页。Oracle用B树机制存储索引条目，以保证用最短路径访问键值。默认情况下大多使用B树索引，该索引就是通常所见的唯一索引、逆序索引。
   1.2、位图索引存储主要用于节省空间，减少oracle对数据块的访问。它采用位图偏移方式来与表的行ID号对应，采用位图索引一般是重复值太多的表字段。位图索引之所以在实际密集型OLTP（联机事物处理）中用的比较少，是因为OLTP会对表进行大量的删除、修改、新建操作。Oracle每次进行操作都会对要操作的数据块加锁。以防止多人操作容易产生的数据库锁等待甚至死锁现象。在OLAP（联机分析处理）中应用位图有优势，因为OLAP中大部分是对数据库的查询操作，而且一般采用数据仓库技术，所以大量数据采用位图索引节省空间比较明显。当创建表的命令中包含有唯一性关键字时，不能创建位图索引，创建全局分区索引时也不能用位图索引。
2. 索引按功能和索引对象分还有以下类型。
   2.1、唯一索引意味着不会有两行记录相同的索引键值。唯一索引表中的记录没有RowID，不能再对其建立其他索引。在oracle10g中，要建立唯一索引，必须在表中设置主关键字，建立了唯一索引的表只按照该唯一索引结构排序。
   2.2、非唯一索引不对索引列的值进行唯一性限制。
   2.3、分区索引是指索引可以分散地存在于多个不同的表空间中，其优点是可以提高数据查询的效率。
   2.4、未排序索引也称为正向索引。Oracle10g数据库中的行是按升序排序的，创建索引时不必指定对其排序而使用默认的顺序。
   2.5、逆序索引也称反向索引。该索引同样保持列按顺序排列，但是颠倒已索引的每列的字节。
   2.6、基于函数的索引是指索引中的一列或者多列是一个函数或者表达式，索引根据函数或表达式计算索引列的值。可以将基于函数的索引建立创建成位图索引。
   另外，按照索引所包含的列数可以把索引分为单列索引和复合索引。索引列只有一列的索引为单列索引，对多列同时索引称为复合索引。

三.索引使用原则

在正确使用索引的前提下，索引可以提高检索相应的表的速度。当用户考虑在表中使用索引时，应遵循下列一些基本原则。
1.在表中插入数据后创建索引。在表中插入数据后，创建索引效率将更高。如果在装载数据之前创建索引，那么插入每行时oracle都必须更改索引。
2.索引正确的表和列。如果经常检索包含大量数据的表中小于15%的行，就需要创建索引。为了改善多个表的相互关系，常常使用索引列进行关系连接。
3.主键和唯一关键字所在的列自动具有索引，但应该在与之关联的表中的外部关键字所在的列上创建索引。
4.合理安排索引列。在createindex语句中，列的排序会影响查询的性能，通常将最常用的列放在前面。创建一个索引来提高多列的查询效率时，应该清楚地了解这个多列的索引对什么列的存取有效，对什么列的存取无效。
例如：在A,B,C三列上创建索引
A有效
AB有效
ABC有效
5.限制表中索引的数量。尽管表可以有任意数量的索引，可是索引越多，在修改表中的数据时对索引做出相应更改的工作量也越大，效率也就越低。同样，不用的索引应该及时删除。
6.指定索引数据块空间的使用。创建索引时，索引的数据块是用表中现存的值填充的，直到达到PCTFREE为止。如果打算将许多行插入到被索引的表中，PCTFREE就应设置得大一点，不能给索引指定PCTUSED。
7.根据索引大小设置存储参数。创建索引之前应先估计索引的大小，以便更好地促进规划和管理磁盘空间。单个索引项的最大值大约是数据块大小的一半。

四.创建索引

自动创建
在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引。
手动创建:用户可以在其它列上创建非唯一的索引，以加速查询。

在一个或多个列上创建索引
CREATE INDEX index ON table (column[, column]…);

在表 EMPLOYEES的列 LAST_NAME 上创建索引
CREATE INDEX emp_last_name_idx ON employees(last_name);

五.查询索引

可以使用数据字典视图 USER_INDEXES 和 USER_IND_COLUMNS 查看索引的信息
SELECT ic.index_name, ic.column_name,
ic.column_position col_pos,ix.uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND ic.table_name = ‘EMPLOYEES’;

六.删除索引

使用DROP INDEX:命令删除索引DROP INDEX index;
删除索引UPPER_LAST_NAME_IDX DROP INDEX upper_last_name_idx;
只有索引的拥有者或拥有DROP ANY INDEX 权限的用户才可以删除索引。
删除操作是不可回滚的。

七.索引的其他内容

什么时候创建索引—–以下情况可以创建索引:
列中数据值分布范围很广。
列经常在 WHERE 子句或连接条件中出现。
表经常被访问而且数据量很大 ，访问的数据大概占数据总量的2%到4%。

什么时候不要创建索引—–下列情况不要创建索引:
表很小
列不经常作为连接条件或出现在WHERE子句中
查询的数据大于2%到4%
表经常更新
Desc emp;
Create index name_index on emp(name);
索引不需要用，只是说我们在用name进行查询的时候，速度会更快。当然查的速度快了，插入的速度就会慢。因为插入数据的同时，还需要维护一个索引。