ORACLE索引，索引的建立、修改、删除[通俗易懂]

大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE使用 1年只要46元 售后保障 童叟无欺

一、简介

      索引是关系数据库中用于存放每一条记录的一种对象，主要目的是加快数据的读取速度和完整性检查。建立索引是一项技术性要求高的工作。一般在数据库设计阶段的与数据库结构一道考虑。应用系统的性能直接与索引的合理直接有关

二、 语法

2.1 创建索引

CREATE INDEX CREATE [unique] INDEX [user.]index ON [user.]table (column [ASC | DESC] [,column [ASC | DESC] ] ... ) [CLUSTER [scheam.]cluster] [INITRANS n] [MAXTRANS n] [PCTFREE n] [STORAGE storage] [TABLESPACE tablespace] [NO SORT]

其中：
schema ORACLE模式，缺省即为当前帐户
index 索引名
table 创建索引的基表名
column 基表中的列名，一个索引最多有16列，long列、long raw 列不能建索引列
DESC、ASC 缺省为ASC即升序排序
CLUSTER 指定一个聚簇（Hash cluster不能建索引）
INITRANS、MAXTRANS 指定初始和最大事务入口数
Tablespace 表空间名
STORAGE 存储参数，同create table 中的storage.
PCTFREE 索引数据块空闲空间的百分比(不能指定pctused)
NOSORT 不（能）排序（存储时就已按升序，所以指出不再排序）

2.1修改索引

      对于较早的Oracle版本，修改索引的主要任务是修改已存在索引的存储参数适应增长的需要或者重新建立索引。而Oracle8I及以后的版本，可以对无用的空间进行合并。这些的工作主要是由管理员来完成。

ALTER [UNIQUE] INDEX [user.]index [INITRANS n] [MAXTRANS n] REBUILD [STORAGE n]

其中：
REBUILD 是 根据原来的索引结构重新建立索引，实际是删除原来的索引后再重新建立。

提示：DBA经常用 REBUILD 来重建索引可以减少硬盘碎片和提高应用系统的性能。

2.3 删除索引

DROP INDEX [schema.]indexname

三、各类索引介绍

3.1 基于函数的索引

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

示例：

为EMP表的ename 列建立大写转换函数的索引idx ：

CREATE INDEX idx ON emp ( UPPER(ename));

这样就可以在查询语句来使用：
SELECT * FROM EMP WHERE UPPER(ename) LIKE ‘JOH%’;

3.2 反向键索引

      反向键索引通过反向键保持索引的所有叶子键上的插入分布。有时，可用反向键索引来避免不平衡的索引。对于反向键索引可以进行下面操作：
1. 通过在ALTER INDEX命令后加REBUILD NOREVERSE或REBUILD REVERSE子句来使索引边为反向键索引或普通索引；
2. 采用范围扫描的查询不能使用反向键索引；
3. 位图索引不能反向；
4. 索引编排表不能反向。

示例1：创建一个反向键索引

CREATE INDEX i ON t (a,b,c) REVERSE;

示例2：使一个索引变为反向键索引

ALTER INDEX i REBUILD NOREVERSE

3.3 索引组织表

      与普通的索引不一样，索引组织表（Index_Organized Table）是根据表来存储数据，即将索引和表存储在一起。这样的索引结构表（Index_organized table—IOT）的特点是：对表数据的改变，如插入一新行、删除某行都引起索引的更新。
      索引组织表就象带一个或多个列所有的普通表一样，但索引组织表在B-树索引结构的叶节点上存储行数据。通过在索引结构中存储数据，索引组织表减少了总的存储量，此外，索引组织表也改善访问性能。
由于表中的行与B_树索引存放在一起，每个行都没有ROWID，而是用主键来标识。但是Oracle会“猜”这些行的位置并为每个行分配逻辑的ROWID。此外，你可以为这样的表建立第二个索引。

创建索引结构表也是用CREATE TABLE 命令加ORGANIZATION INDEX关键字来实现。但是，这样的表在创建完后，你还必须为该表建立一个主键。

例子：

CREATE TABLE IOT_EXPAMPLE ( Pk_col1 number(4), Pk_col2 varchar2(10), Non_pk_col1 varchar2(40), Non_pk_col2 date, CONSTRAINT pk_iot PRIMARY KEY( pk_col1, pk_col2) ) ORGANIZATION INDEX TABLESPACE INDEX STORAGE( INITIAL 1M NEXT 512K PCTINCREASE 0 );

索引组织表有些限制：

1. 不能使用唯一约束；
2. 必须具有一个主键；
3. 不能建立簇；
4. 不能包含LONG类型列；
5. 不支持分布和复制。

提示：如果建立了索引组织表，则会在DBA_TABLES中的IOT_TYPE和IOT_NAME列上记录有索引组织表的信息。

四、总结

适当的使用索引可以提高数据检索速度，可以给经常需要进行查询的字段创建索引。

oracle的索引分为5种:唯一索引，组合索引，反向键索引，位图索引，基于函数的索引

创建索引的标准语法:
CREATE INDEX 索引名 ON 表名 (列名)
TABLESPACE 表空间名;

创建唯一索引:
CREATE unique INDEX 索引名 ON 表名 (列名)
TABLESPACE 表空间名;

创建组合索引:
CREATE INDEX 索引名 ON 表名 (列名1,列名2)
TABLESPACE 表空间名;

创建反向键索引:
CREATE INDEX 索引名 ON 表名 (列名) reverse
TABLESPACE 表空间名;

“TABLESPACE 表空间名” 可以省略

原文地址：http://www.cnblogs.com/djcsch2001/articles/1823459.html