1.概念及作用
在oracle中,索引是一种供服务器在表中快速查找一个行的数据库结构。在数据库中建立索引主要有以下作用。
(1) 快速存取数据。
(2) 既可以改善数据库性能,又可以保证列值的唯一性。
(3) 实现表与表之间的参照完整性
(4) 在使用orderby、groupby子句进行数据检索时,利用索引可以减少排序和分组的时间。
2.索引的分类
在关系数据库中,每一行都由一个行唯一标识RowID。RowID包括该行所在的文件、在文件中的块数和块中的行号。索引中包含一个索引条目,每一个索引条目都有一个键值和一个RowID,其中键值可以是一列或者多列的组合。
(1) 索引按存储方法分类,可以分为2类:B*树索引 和 位图索引。
① B*树索引 的存储结构类似书的索引结构,有分支和叶两种类型的存储数据块,分支块相当于书的大目录,叶块相当于索引到的具体的书页。Oracle用B*树机制存储索引条目,以保证用最短路径访问键值。默认情况下大多使用B*树索引,该索引就是通常所见的
唯一索引
、
逆序索引
。
② 位图索引 存储主要用于节省空间,减少oracle对数据块的访问。它采用位图偏移方式来与表的行ID号对应,采用位图索引一般是重复值太多的表字段。位图索引之所以在实际密集型OLTP(联机事物处理)中用的比较少,是因为OLTP会对表进行大量的删除、修改、新建操作。Oracle每次进行操作都会对要操作的数据块加锁。以防止多人操作容易产生的数据库锁等待甚至死锁现象。在OLAP(联机分析处理)中应用位图有优势,因为OLAP中大部分是对数据库的查询操作,而且一般采用数据仓库技术,所以大量数据采用位图索引节省空间比较明显。当创建表的命令中包含有唯一性关键字时,不能创建位图索引,创建全局分区索引时也不能用位图索引。
(2) 索引按功能和索引对象分还有以下类型。
① 唯一索引意味着不会有两行记录相同的索引键值。唯一索引表中的记录没有RowID,不能再对其建立其他索引。在oracle10g中,要建立唯一索引,必须在表中设置主关键字,建立了唯一索引的表只按照该唯一索引结构排序。
② 非唯一索引不对索引列的值进行唯一性限制。
③ 分区索引是指索引可以分散地存在于多个不同的表空间中,其优点是可以提高数据查询的效率。
④ 未排序索引也称为正向索引。Oracle10g数据库中的行是按升序排序的,创建索引时不必指定对其排序而使用默认的顺序。
⑤ 逆序索引也称反向索引。该索引同样保持列按顺序排列,但是颠倒已索引的每列的字节。
⑥ 基于函数的索引是指索引中的一列或者多列是一个函数或者表达式,索引根据函数或表达式计算索引列的值。可以将基于函数的索引建立创建成位图索引。
另外,按照索引所包含的列数可以把索引分为单列索引和复合索引。索引列只有一列的索引为单列索引,对多列同时索引称为复合索引。
3.索引使用原则
在正确使用索引的前提下,索引可以提高检索相应的表的速度。当用户考虑在表中使用索引时,应遵循下列一些基本原则。
(1) 在表中插入数据后创建索引。在表中插入数据后,创建索引效率将更高。如果在装载数据之前创建索引,那么插入每行时oracle都必须更改索引。
(2) 索引正确的表和列。如果经常检索包含大量数据的表中小于15%的行,就需要创建索引。为了改善多个表的相互关系,常常使用索引列进行关系连接。
(3) 主键和唯一关键字所在的列自动具有索引,但应该在与之关联的表中的外部关键字所在的列上创建索引。
(4) 合理安排索引列。在createindex语句中,列的排序会影响查询的性能,通常将最常用的列放在前面。创建一个索引来提高多列的查询效率时,应该清楚地了解这个多列的索引对什么列的存取有效,对什么列的存取无效。
例如:在A,B,C三列上创建索引
A有效
AB有效
ABC有效
(5) 限制表中索引的数量。尽管表可以有任意数量的索引,可是索引越多,在修改表中的数据时对索引做出相应更改的工作量也越大,效率也就越低。同样,目前不用的索引应该及时删除。
(6) 指定索引数据块空间的使用。创建索引时,索引的数据块是用表中现存的值填充的,直到达到PCTFREE为止。如果打算将许多行插入到被索引的表中,PCTFREE就应设置得大一点,不能给索引指定PCTUSED。
(7) 根据索引大小设置存储参数。创建索引之前应先估计索引的大小,以便更好地促进规划和管理磁盘空间。单个索引项的最大值大约是数据块大小的一半。
4.
语法结构:
创建索引
CREATE [UNIQUE] INDEX index_name ON table_name(column_name[,column_name…])
语法解析:
1. UNIQUE:指定索引列上的值必须是唯一的。称为
唯一索引
。
2. index_name:指定索引名。
3. tabl_name:指定要为哪个表创建索引。
4. column_name:指定要对哪个列创建索引。我们也可以对多列创建索引;这种索引称为组合索引。
5.
案例4:
为EMP表的ENAME列创建创建唯一索引,为EMP表的工资列创建普通索引,把JOB列先变为小写再创建索引。
代码演示:创建索引
SQL> CREATE UNIQUE INDEX UQ_ENAME_IDX ON EMP(ENAME); ①
Index created
SQL> CREATE INDEX IDX_SAL ON EMP(SAL); ②
Index created
SQL> CREATE INDEX IDX_JOB_LOWER ON EMP(LOWER(JOB)); ③
Index created
代码解析:
① 为SCOTT.EMP表的ENAME列创建唯一索引。
② 为SCOTT.EMP表的SAL列创建索引。
③ 在查询中可能经常使用job的小写作为条件的表达式,因此创建索引时,可以先对JOB列中的所有值转换为小写后创建索引,而这时需要使用lower函数,这种索引称为基于函数的索引。
在select语句查询时,Oracle系统会自动为查询条件上的列应用索引。索引就是对某一列进行排序,因此在索引列上,重复值越少,索引的效果越明显。
Oracle可以为一些列值重复非常多且值有限的列(比如性别列)上创建位图索引。关于Oracle更多的索引类型(比如反向键索引等),请参考Oracle官方文档。