一、索引的概念
索引是一种与表相关的可选数据对象。通过在表中的一个或多个列上创建索引,就能够为数据的检索提供快捷的存取路径,减少查询时所需的磁盘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))
授权语句: