oracle 索引的定义及使用

一、索引的概念：

索引是建立在表上的可选数据库对象，是一组排序后的的索引键。主要用于加快数据的检索，类似于书籍的目录，快速定位到目标数据。索引在物理上和逻辑上都是独立的，创建或删除索引对基表不会有影响。当对基表进行DML操作时Oracle会自动管理索引，无需手动处理。

二、索引的原理：

•   若没有索引，搜索某个记录时（例如查找name='wish'）需要搜索所有的记录，因为不能保证只有一个wish，必须全部搜索一遍，
• 若在name上建立索引，oracle会对全表进行一次搜索，将每条记录的name值哪找升序排列，然后构建索引条目（name和rowid），存储到索引段中，查询name为wish时即可直接查找对应地方
• 创建了索引并不一定就会使用，oracle自动统计表的信息后，决定是否使用索引，表中数据很少时使用全表扫描速度已经很快，没有必要使用索引

三、索引的特点：

1. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
2. 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
3. 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
4. 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
5. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

四、索引的分类：

1. 普通索引
2. 唯一索引
3. 反向键索引
4. 位图索引
5. 基于函数的索引

常用的是普通索引和唯一索引

五、索引的使用：

先创建一个学生表：

create table Student
(
  id        char(4)         not null,   -- 编号
  name      varchar2(30)    not null,   -- 姓名
  weight    number(4,1)     not null,   -- 体重
  height    number(3)       not null,   -- 身高
  birthday  date            not null,   -- 出生时间
  memo      varchar2(1000)      null,   -- 备注
  keyid     number(8)       not null   -- 记录编号,从序列SEQ_GIRL中获取。
);
alter table STUDENT add constraint ID_KEY primary key(id);

1、创建索引：

(1)普通索引语法：：

create index 索引名 on 表名(字段名1,字段名2,......,字段名n);

在实际开发中，通常以IDX_打头，加表名和索引编号。例如为学生表表T_GIRL的姓名字段创建索引

create index ID_KEY_1 on STUDENT(name);

（2)唯一索引语法：

create unique index 索引名 on 表名(字段名1,字段名2,......,字段名n);

如果表中的列没有重复的值，是唯一的，就可以创建唯一索引，唯一索引的效率比普通索引要高很多。例如学生表表T_GIRL的keyid字段，其值是从序列SEQ_GIRL中获取的，肯定是个唯一的值，可以创建唯一索引。

create unique index ID_KEY_1 on STUDENT(keyid);

（3)主键是唯一索引语法：

不要为表的主建创建索引，因为Oracle自动为表的主建字段创建唯一索引。

2、删除索引：

删除普通索引语法：

drop index 索引名;

删除主键索引语法：

alter table 表名 drop constraint 主键名;

示例：

drop index ID_KEY_1;--删除普通索引
alter table student drop constraint PK_GIRL;--删除主键或唯一索引

3、修改索引

(1)重建索引：重建索引可以减少硬盘碎片和提高数据库系统的性能

语法：

alter index 索引名 rebuild;

示例：

alter index ID_KEY_1 rebuild;

(2)整理碎片:对索引的无用空间进行合并，减少硬盘碎片和提高数据库系统的性能。

语法：

alter index 索引名 coalesce;

示例：

alter index KEY_ID_1 coalesce;

(3)修改索引名

语法：

alter index 索引名 rename to 新索引名;

示例：

alter index KEY_ID_1 rename to KEY_ID_2;

(4)禁用索引：索引被禁用后，如果要启用，需要用rebuild重建。

语法：

alter index 索引名 unusable;

示例：

alter index KEY_ID_1 unusable;