索引(Index)

索引(Index)

什么是索引

在关系型数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序 的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的 数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所 需的内容。

索引提供对表中行的直接和快速访问，它的目的是用已索引的路径快速定位数据以减少 磁盘 I/O。索引由 Oracle 服务器自动使用和维护，索引逻辑地和物理地独立于他们索引的 表，这意味者索引可以在任何时候被创建或删除，并且不影响基表或其它的索引。当删除表 时，相应的索引也被删除。

索引的类型

• 唯一性索引：当你在一个表中定义一个列为主键，或者定义一个唯一键约束时 Oracle 服务器自动创建该索引，索引的名字习惯上是约束的名字。

• 非唯一索引：由用户创建，例如，可以创建一个 FOREIGN KEY 列索引用于一个查询 中的连接来改进数据取回的速度。

创建索引的方式

• 自动：在一个表的定义中，当定义一个 PRIMARYKEY 或 UNIQUE 约束时，一个 唯一索引被自动创建。
• 手动：用户能够在列上创建非唯一的索引来加速对行的访问。

使用索引

过多也件坏事 。

在表上建立更多的索引并不意味者更快地查询，在带索引的表上被提交的每个 DML 操 作意味者索引必须更新；与表联系的索引越多，对 Oracle 数据库的影响越大，Oracle 数据 库在每次 DML 操作之后必须更新所有的索引。

什么时候创建索引

• 一个列包含一个大范围的值
• 一个列包含很多的空值
• 一个或多个列经常同时在一个 WHERE 子句中或一个连接条件中被使用
• 表很大，并且经常的查询期望取回少于百分之 2 到 4 的行

什么时候不创建索引

• 表很小
• 不经常在查询中作为条件被使用的列
• 大多数查询期望取回多于表中百分之 2 到 4 的行
• 表经常被更新
• 被索引的列作为表达式的的一部分被引用

操作索引

非唯一性索引的类型

oracle 的非唯一性索引：单行索引，复合索引(组合索引)，函数索引。

创建索引语法

CREATE INDEX index
ON table(column [, column...])

为employees 表中的 last_name 创建一个索引并命名为 emp_index。

--为employees 表中的 last_name 创建一个索引并命名为 emp_index。 
CREATE INDEX emp_index
ON employees (last_name);

为departments表创建一个包括 manager_id 与 location_id 复合 索 引 并 命 名 为 dept_man_loc。

--为departments表创建一个包括 manager_id 与 location_id 复合 索 引 并 命 名 为 dept_man_loc。
CREATE INDEX dept_man_loc
ON departments(manager_id, location_id);

–为 departments 表中的 department_name 创建一个带有大写函数的索引 dept_upper2。

--为 departments 表中的 department_name 创建一个带有大写函数的索引 dept_upper2。 
CREATE INDEX dept_upper2
ON departments(UPPER(department_name));

查询索引

• USER_INDEXEX 数据字典视图包含索引和它唯一的名字。

• USER_IND_COLUMNS视图包含索引名、表名和列名。

SELECT ic.index_name, ic.column_name, ic.column_position col_pos, ix.uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND ic.table_name = 'EMPLOYEES';

删除索引

DROP INDEX index;

删除名称为 dept_upper 的索引。

drop index dept_upper;