任务4 创建员工表索引

关键步骤如下。

在薪水级别（salgrade）表中，为级别编号（grade）列创建唯一索引。

在员工（employee）表中，为员工编号（empno）列创建反向键索引。

在员工（employee）表中，为工种（job）列创建位图索引。

在员工（employee）表中，为员工名称（ename）列创建大写函数索引。

2.4.1 认识索引

索引是与表关联的可选结构，是一种快速访问数据的途径，可以提高数据库性能。数据库可以明确地创建索引，以加快对表执行SQL语句的速度。当将索引键作为查询条件时，该索引将直接指向包含这些值的行的位置。即便删除索引，也无须修改任何SQL语句的定义。

2.4.2 创建合适的索引

在Oracle中，索引的分类如表2-3所示。

1.B树索引

B树索引通常也称为标准索引。索引的顶部为根，其中包含指向索引中下一级的项。下一级为分支块，分支块又指向索引中下一级的块。最低一级为叶节点，其中包含指向表行的索引项。叶块为双向链接，有助于按关键字值的升序和降序扫描索引。

创建普通索引的语法如下。

CREATE [UNIQUE] INDEX index_name ON table_name（column_list）

[TABLESPACE tablespace_name];

在语法中：

UNIQUE：用于指定唯一索引，默认情况下为非唯一索引。

index_name：所创建索引的名称。

table_name：表示为之创建索引的表名。

column_list：在其上创建索引的列名的列表，可以基于多列创建索引，列之间用逗号分隔。

tablespace_name：为索引指定表空间。

2.唯一索引和非唯一索引

唯一索引：定义索引的列中任何两行都没有重复值。唯一索引中的索引关键字只能指向表中的一行。在创建主键约束和唯一约束时都会创建一个与之对应的唯一索引。

非唯一索引：单个关键字可以有多个与其关联的行。

示例10

在薪水级别（salgrade）表中，为级别编号（grade）列创建唯一索引，代码如下。

SQL>CREATE UNIQUE INDEX index_unique_grade ON salgrade(grade);

3.反向键索引

与常规B树索引相反，反向键索引在保持列顺序的同时反转索引列的字节。反向键索引通过反转索引键的数据值来实现。其优点是对于连续增长的索引列，反转索引列可以将索引数据分散在多个索引块间，减少I/O瓶颈的发生。

反向键索引通常建立在一些值连续增长的列上，如系统生成的员工编号，但不能执行范围搜索。

示例11

在员工（employee）表中，为员工编号（empno）列创建反向键索引，代码如下。

SQL>CREATE INDEX index_reverse_empno ON employee(empno) REVERSE;

4.位图索引

位图索引的优点在于，它最适用于低基数数列（即该列的值是有限的，理论上不会是无穷大）。例如，员工表中的工种（job）列，即便是有几百万条员工记录，工种也是可计算的。工种列可以作为位图索引，类似的还有图书表中的图书类别列等。

位图索引具有以下优点。

（1）对于大批即时查询，可以减少响应时间。

（2）相比其他索引技术，占用空间明显减少。

（3）即使是在配置很低的终端硬件上，也能获得显著的性能。

位图索引不应当用在频繁发生INSERT、UPDATE和DELETE操作的表上，因为这些DML操作在性能方面的代价很高。位图索引最适合于数据仓库和决策支持系统。

示例12

在员工（employee）表中，为工种（job）列创建位图索引，代码如下。

SQL>CREATE BITMAP INDEX index_bit_job ON employee(job);

5.其他索引

组合索引：在表内多列上创建。索引中的列不必与表中的列顺序一致，也不必相互邻接，类似于MySQL中的复合索引，如员工表中部门列和职务列上的索引。组合索引最多包含32列。

基于函数的索引：若使用的函数或表达式涉及正在建立索引的表中的一列或多列，则创建基于函数的索引。可以将基于函数的索引创建为B树索引或位图索引。

示例13

在员工（employee）表中，为员工名称（ename）列创建大写函数索引，代码如下。

SQL>CREATE INDEX index_ename ON employee(UPPER(ename));

经验

创建组合索引时请将唯一性高（该列上存储的大部分数据是唯一的）的列放在第一位。

2.4.3 创建索引注意事项

创建索引时需遵循的原则如下。

（1）频繁搜索的列可作为索引。

（2）经常排序、分组的列可作为索引。

（3）经常用作连接的列（主键/外键）可作为索引。

（4）将索引放在一个单独的表空间中，不要放在有回退段、临时段和表的表空间中。

（5）对大型索引而言，可考虑使用NOLOGGING 子句创建大型索引。

（6）根据业务数据发生的频率，定期重新生成或重新组织索引，并进行碎片整理。

（7）仅包含几个不同值的列不可以创建为B树索引，但可根据需要创建位图索引。

（8）不要在仅包含几行的表中创建索引。

2.4.4 使用命令删除索引

1.DROP INDEX语句用于删除索引

例如，删除员工（employee）表中的index_bit_job位图索引，代码如下。

SQL>DROP INDEX index_bit_job;

2.何时应删除索引

（1）应用程序不再需要索引。

（2）执行批量加载前。大量加载数据前应先删除索引，加载后再重建索引有以下好处：①提高加载性能；②更有效地使用索引空间。

（3）索引已损坏。

2.4.5 重建索引应用场景

1.ALTER INDEX…REBUILD语句用于重建索引

例如，将反向键索引更改为正常的B树索引，代码如下。

SQL>ALTER INDEX index_reverse_empno REBUILD NOREVERSE;

2.何时应重建索引

（1）用户表被移动到新的表空间后，表上的索引不会自动转移，此时需将索引移到指定表空间。

ALTER INDEX index_name REBUILD TABLESPACE tablespace_name;

（2）索引中包含很多已删除的项。当对表进行频繁删除，造成索引空间浪费时，可以重建索引。

（3）需将现有的正常索引转换成反向键索引。

技能训练

上机练习4——为客户表创建合适的索引

需求说明

为了提高客户（customers）表的数据检索效率，需要为该表创建索引。请针对客户编号、名、姓氏、地域列创建合适的索引。

提示

建议步骤如下。

（1）根据反向键索引的特点为客户编号列创建反向键索引或者唯一索引。

（2）根据位图索引的特点为地域列创建位图索引。

（3）根据组合索引的特点为名和姓氏列创建组合索引。