Oracle中的索引

Oracle中的索引

索引概述

在关系数据库中，索引是一种与表有关的数据库结构，它是除表以外的另一个重要模式对象。索引是建立在表的一列或多个列上的辅助对象，目的是提高表中数据的访问速度。

索引时表示数据的另一种方式，它提供的数据顺序不同于数据在磁盘上的物理存储顺序。它重新排列数据的物理位置，使其值为有序键值列表，每个键值是指向表行的指针，故其排列方式使其搜索变得更加有效。

Oracle中常用的索引类型有：B树索引、反向键索引、位图索引、基于函数的索引、簇索引、全局索引和局部索引。

创建索引的语法如下:

    CREATE UNIQUE|BTIMAP INDEX <schema>.<index_name>
    ON <schema>.<table_name>
    (<column_name>|<expression> ASC|DESC,
    <column_name>|<expression> ASC|DESC,...
    )
    TABLESPACE <tablespace_name>
    STORAGE <storage_settings>
    LOGGING|NOLOGGING
    COMPUTE STATISTICS
    NOCOMPRESS|COMPRESS <nn>
    NOSORT|REVERSE
    PATITION|GLOBAL PATITION <patition_setting>;

B树索引

B树索引是Oracle中默认并且最常用的索引，B树索引的组织结构类似一棵树，其中主要数据集中在叶子结点上，每个叶子结点中包括：索引列的值和记录行对应的物理地址ROWID。

创建一个B数索引，需要使用CREATE INDEX语句，如果用户要在自己的模式中创建索引，则必须具有CREATE INDEX的系统权限：如果用户想要在其他用户模式中创建索引，则必须具有CREATE ANY INDEX的系统权限。

创建普通索引

创建索引时，在ON关键字后面指定索引引用的表名和列名，使用TABLESPACE指定存储索引的表空间。
默认情况下，当用户为表定义一个主键时 系统将自动为该列创建一个B树索引，另外，当一个列已经包含索引时，则无法再在该列上创建索引。

例1:

CREATE UNIQUE INDEX sname_index ON siege.student sname)TABLESPACE learning;

创建唯一索引

索引可以是唯一的，也可以是不唯一的，唯一的B树索引可以保证索引列上不会有重复的值。创建唯一索引需要使用关键字UNIQUE。

例2：

DROP INDEX sname_index; 
CREATE UNIQUE INDEX sname_index ON siege.student (sname)TABLESPACE learning;

注：每列只能创建一个索引，索引先删除之前的索引再来创建唯一索引。

创建复合索引

复合索引，是指基于表中多个字段的索引。

例3：

DROP INDEX sname_index ;
CREATE  INDEX sname_index ON siege.student (sname,sage)TABLESPACE learning;

位图索引

位图索引不同于B树索引，它不存储ROWID值，也不存储键值，主要用于在比较特殊的列上创建索引。

当列的基数很低时（指在索引列中，所有列值的数量比表中行的数量少，例如’性别‘列只有2个值）。Oracle建议，当一个列的所有取值数量与行的总数比小于1%时，对该列就不再适合建立B树索引，而适用位图索引。

创建位图索引

位图索引适用于在表中基数比较小的列上创建,在表上放置单独的位图索引没有意义，只有对多个列建立位图索引，系统才可以有效地利用它们来提高查询的速度。

位图所以不是能使唯一索引，也不能进行键压缩，位图索引的作用来源于与其他位图索引的结合，当在多个列上进行查询，Oracle对这些列上的位图进行布尔AND和OR运算，最终找到需要的结果。

先修改student表结构，增加ssex字段，并赋值：

ALTER TABLE student  ADD (ssex Varchar2(1));
UPDATE student SET ssex='M'

然后对ssex列创建位图索引：

例4：

CREATE BITMAP INDEX ssex_bitmap_index on siege.student(ssex) TABLESPACE learning;

注：由于本机器安装的是XE版的Oracle，在执行下列语句时Bit-mapped indexes=FALSE，说明未安装此功能，故上面的语句执行会报00439错误，不过正常情况下应是正确的。

select * from v$option   Where  PARAMETER='Bit-mapped indexes'

反向键索引

反向键索引时一种特殊的B树索引，适用于在含有序列数的列上创建索引，在常规的B树索引中，如果主键是递增的，那么在向表中添加新的数据时，B数索引将直接访问最后一个数据，而不是一个结点一个结点的访问，这种情况造成的结果是：随着数据行的增加，以及原有数据行的删除，B树索引将变得越来越不均匀。

此时，可以创建反向键索引，其原理是：如果用户使用序列编号在表中添加新的记录，则反向键索引首先反向转化每个列键值的字节，然后在反向后的新数据上进行索引。

例如，如果用户输入索引键2009，则反向键索引将其反向转化为9002， 这样可以将索引键变成非递增的，从而使得数据在值的范围分布上比原来更均匀。

反向键索引适用于在表中严格排序的列上创建，在查询时，用户只需要像常规方式一样查询数据，而不需要关心键的反向处理，系统会自动完成该处理。

例5：

CREATE  INDEX sid_reserve_index on siege.student(sid)  REVERSE TABLESPACE learning;

基于函数的索引

基于函数的索引只是常规的B树索引，只不过它存放的数据是由表中的数据应用函数得到的，而不是直接存放表中的数据本身。

在Oracle中，经常遇到自负大小写或数据类型转换等问题，这时，就可以引用函数对这些数据进行转换。

例如对student表进行查询：

SELECT * FROM student WHERE upper(sname)=UPPER('sam');

使用这种查询方式，用户不是基于表中存储的记录进行搜索的。虽然只在某一列上建立了索引，但Oracle会被迫执行全表搜索。为遇到的各个行都计算UPPER()函数。

此时，应考虑创建基于函数的索引，其可以提高在查询条件中使用函数和表达式时查询的执行速度。如果用户要在自己的模式中创建基于函数的索引，则必须具有QUERY REWRITE系统权限：如果用户想要其他模式中创建基于函数的索引，则必须具有CREATE ANY INDEX和GLOBAL QUERY REWRITE权限。

创建基于函数的索引，Oracle会首先对包含索引列的函数值或表达式进行求值，然后对求值后的结果进行排序，最后存储到索引中。

下例针对UPPER函数创建了函数索引，如果在查询条件中包含相同的函数，则可以提高查询的执行速度。

例6：

CREATE INDEX sname_func_index ON student(UPPER(sname))  TABLESPACE learning;

管理索引

合并和重建索引

随着对表的不断更新，在表的索引中将会产生越来越多的存储碎片，这些碎片会影响索引的使用效率。合并索引和重建索引时两种消除索引存储碎片的方式。

合并索引是指将B树索引中叶子结点的存储碎片合并在一起，这种合并不会改变索引的物理组织结构。

合并上例的索引如下：

例7：

ALTER INDEX sname_func_index COALESCE DEALLOCATE UNUSED;

重建索引在消除存储碎片的同时，还可以改变索引的全部存储参数设置，以及改变索引的存储表空间。

重建索引，实际上是在指定的表空间中重新建立一个新的索引，然后删除原来的索引。

例8：

ALTER INDEX sname_func_index REBUILD ;

监视索引

监视索引时为了确保索引得到有效的利用。

打开索引监视：

ALTER INDEX sname_func_index MONITORING  USAGE;

关闭索引监视：

ALTER INDEX sname_func_index NOMONITORING  USAGE;

可以通过查看数据字典来观察索引的使用情况：

SELECT * FROM v$object_usage;

其结果如下：

INDEX_NAME	TABLE_NAME	MONITORING	USED	START_MONITORING	END_MONITORING
SNAME_FUNC_INDEX	STUDENT	YES	NO	02/28/2015 00:36:21

若查询时使用过该索引，则USED的值会变为YES。

删除索引

用户可以删除自己模式中的索引，如果要删除其他模式中的索引，则必须具有DROP ANY INDEX系统权限。

一个索引被删除后，它所占用的盘区会全部返回给它所在的表空间，并且可以被表空间中的其他对象使用。通常在如下情况下需要删除某个索引：

• 该索引不需要再使用
• 该索引很少被使用，索引的使用情况可以通过监视来查看
• 该索引包含较多的存储碎片，需要重建该索引

例9：

DROP  INDEX sname_func_index;

索引组织表

索引组织表（Index Organized Table，IOT），在存储时并不像普通表那样采用堆组织方式将记录无序地存储在数据段中，而是采用类是B树索引的索引组织方式将记录按照某个主键列排序后，在以B树索引的方式存在数据段中。

索引组织表为包含精确匹配和范围搜索的查询提供提供了对表中的数据的快速访问，这种访问时快速的、基于主键的，但是以牺牲插入和更新性能为代价的。

创建索引组织表

创建索引组织表，需要使用ORGANIZATION INDEX关键字，另外，必须为索引组织表指定主键。

例10：

CREATE TABLE  index_table (
       ID NUMBER PRIMARY KEY,
       NAME VARCHAR2(10)
)
 ORGANIZATION INDEX
 TABLESPACE learning;

如果向索引组织表中添加数据，Oracle会根据主键列对其进行排序，然后再将数据写入磁盘。所以在使用主键列查询时，相比标准表来说，索引组织表可以得到更好的读取性能。