Oracle --- 索引

一、索引的概念

索引是一种与表相关的可选数据对象。通过在表中的一个或多个列上创建索引，就能够为数据的检索提供快捷的存取路径，减少查询时所需的磁盘I/O操作，加快数据的检索速度。

 

索引是将创建列的键值和对应记录的物理记录号(ROWID)排序后存储起来，需要占用额外的存储空间来存放。由于索引占用的控件远小于表所占用的实际控件，在系统通过索引进行数据检索时，可先将索引调入内存，通过索引对记录进行定位，大大减少了磁盘I/O操作次数，提高检索效率。一般而言，表中的记录数据越多，索引带来的效率提高就越明显，所以在数据库系统中，索引是必不可少的数据对象之一。

 

创建或删除索引的操作不会影响到数据库中的表、数据库应用程序或其他的索引。这是索引独立性的一个体现。如果用户删除一个索引或索引损坏了，任何应用程序任然还能够继续正常工作，唯一受影响的可能就是某些查询的速度会减慢。但是反过来，创建或删除表的操作，却会引起在该表上进一步创建或删除索引的操作。

 

建立在表上的索引是一个独立于表的数据对象，，它可以被存储在于表不同的磁盘或表空间中，有单独为其设立命名的存储结构，即索引段。索引一旦被创建，在表上执行DML操作时Oracle就会自动对索引进行维护，并且由Oracle决定何时使用索引，用户完全不需要在SQL语句中指定使用哪个索引、如何使用索引。无论在表上是否创建了索引，编写和使用SQL语句都是一样的。

 

提示：由于Oracle有时也会利用索引，如唯一索引来实现一些完整性约束，因此在创建主键约束时会自动创建主键索引。

 

二、索引的创建语法

创建语法：

CREATE [UNIQUE]|[BITMAP] INDEX [user.]index_name

ON [user.]table_name(column [ASC | DESC][,column] [ASC | DESC] ...)

[CLUSTER [scheam.]cluster]

[INITTRANS n]

[MAXTRANS n]

[PCTFREE n]

[STORAGE storage]

[TABLESPACE tablespace]

[NO SORT]

[REVERSE]

 

参数说明：

UNIQUE : 用于创建唯一索引

BITMAP : 位图索引，如果不指定BITMAP选项，则默认创建的是B树索引

index_name : 索引名称

table_name : 创建索引的基表名

column : 基表中的列名，一个索引最多有16列，long列、long raw列不能建索引列

DESC、ASC : 缺省为ASC，即升序排序

CLUSTER : 指定一个聚簇(Hash cluster不能建索引)

INITRANS、MAXTRANS : 指定初始和最大事务入口数

PCTFREE : 索引数据块空闲空间的百分比(不能指定pctused)

Tablespace : 表空间名

STORAGE : 存储参数，同create table中的storage

NO SORT : 不排序(存储时就已按升序，所以指出不再排序)

REVERSE : 反向键索引

 

 

三、创建索引

3.1、普通索引

 

create index INDEX_TESTINDEX on T_INDEXTEST(name);

 

 

 

 

 

3.2、唯一索引

如果我们需要在某个表某个列创建索引，而这个列的值是不会重复的。这时我们可以创建唯一索引。

 

create unique index INDEX_TESTINDEX_ID on T_INDEXTEST(id);

 

 

 

 

 

3.3、复合索引

我们经常要对某几列进行查询，我们可以建立复合索引，也就是基于两个以上的列建立索引。

 

create index owners_index_ah on T_OWNERS(addressid,housenumber);

 

 

3.4、反向键索引

 

当某个字段的值为连续增长的值，如果构建标准索引，会形成歪脖子树。这样会增加查询的层数，性能会下降。建立反向键索引，可以使索引的值变得不规则，从而使索引树能够均匀分布。

 

 

create index test_reverse_index on T_TESTREVERSEINDEX(avalue)

 

 

使一个索引变为反向键索引：

 

ALTER INDEX INDEX_TEST1 REBUILD NOREVERSE;

 

 

 

 

 

3.4、位图索引

用于重复率高的列创建索引，比如sex列只有男和女；类型列，这样的列适合创建位图索引

 

create bitmap index index_owners_typeid on T_OWNERS(ownertypeid)

 

 

 

 

 

3.5、函数索引

基于函数的索引就是存储预先计算好的函数或表达式的索引。这些表达式可以是算数运算表达式、SQL或PL/SQL函数、C调用等。值得注意的是，一般用户要创建函数索引，必须具有GLOBAL QUERY REWRITE和CREATE ANY INDEX权限。否则不能创建函数索引。

 

create index idx on T_EMP(UPPER(ename))

授权语句：