oracle 修索引改空间_ORACLE索引，索引的建立、修改、删除

一、简介

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

二、 语法

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]

其中：schemaORACLE模式，缺省即为当前帐户index索引名table创建索引的基表名column 基表中的列名，一个索引最多有16列，long列、long raw列不能建索引列DESC、ASC缺省为ASC即升序排序

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

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

Tablespace 表空间名

STORAGE 存储参数，同createtable中的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 TABLEIOT_EXPAMPLE

(

Pk_col1number(4),

Pk_col2varchar2(10),

Non_pk_col1varchar2(40),

Non_pk_col2 date,CONSTRAINT pk_iot PRIMARY KEY( pk_col1, pk_col2)

)

ORGANIZATIONINDEXTABLESPACEINDEXSTORAGE( INITIAL 1MNEXT 512K PCTINCREASE 0 );

索引组织表有些限制：

不能使用唯一约束；

必须具有一个主键；

不能建立簇；

不能包含LONG类型列；

不支持分布和复制。

提示：如果建立了索引组织表，则会在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 表空间名” 可以省略