6.6 索引

  什么是索引？这个问题在大家没有接触索引之前，比较难用一个定义解释清楚。不过可以通过一个形象的例子让大家理解什么是索引。如果把书当作一个数据库的表，那么书前的目录就是该表的索引。通过目录可以快速地找到所需要的内容，同样，通过索引可以快速地查询到所需要的数据。

6.6.1 索引概述

  索引也是数据库对象，通过指针的形式提高查询的速度。可以显式地创建索引，索引也可以被自动创建。如果没有给表建立索引，那么查询表中的记录，将会发生全表扫描，速度会比较慢。

  索引提供了对表中行的直接和快速的访问，目的是通过已索引的路径快速定位数据，以减少磁盘输入/输出操作，提高速度。索引由Oracle数据库维护，在对一个表进行DML操作时，Oracle会自动维护索引。

  索引可以提高查询速度，但同时Oracle数据库要维护索引，也会带来系统开销。所以对于一个表而言，是否创建索引，创建多少个索引，在哪些字段创建索引，是数据库管理员根据对该表的常用操作而做的决策。简单来说，如果一个数据量比较大的表经常被查询而较少地被插入或删除，则可以建立索引；相反，小表或经常被插入、删除记录的表，则不建议建立多个索引。具体在哪些字段建立索引，主要的依据是，对表的常用查询操作WHERE子句中的字段或多表查询中连接条件的字段。

  数据库表中，伪列ROWID是一个十六进制的串，包含数据对象编号、文件编号、块编号和行编号，访问任何指定行的最快方法是引用它的ROWID。

  图6.12显示了雇员表employees中已有的索引，从图中可以看出，emp_name_ix是一个基于两个字段的索引。

6.6 索引 - 图1


图6.12 雇员表employees的索引

6.6.2 操作索引

  • 创建索引

  前面已经提到过，创建索引有两种形式，一种是自动创建，另一种是手动显式创建。在一个表中，如果一个字段被定义为主键（PRIMARY KEY）或者定义了唯一性约束（UNIQUE），一个唯一索引被自动创建。如果是用户想建立一个唯一索引，通常的做法是定义一个唯一约束，从而自动创建索引。用户也可以在字段上创建一个非唯一索引来加快对该字段的查询速度。

  创建索引的语法形式如下：

  1. CREATE  [UNIQUE]  INDEX index
  3. ON table(col1, col2…)

  其中，UNIQUE表示建立的是唯一索引还是非唯一索引，index表示索引的名字，table表示该索引建立在哪个表上，col1和col2表示表中被索引的字段名称。

  为了改善雇员表employees中电话号码字段PHONE_NUMBER的查询速度，可以给雇员表的电话号码字段建立一个索引，其SQL语句如下：

  1. CREATE INDEX emp_phone_number_ix
  3. ON employees(phone_number)

  执行该SQL语句，之后通过PL/SQL Dev查看雇员表employees的Indexes选项卡，如图6.13所示，显示索引已经创建成功。

6.6 索引 - 图2


图6.13 索引创建成功

  • 创建基于函数的索引

  上面创建的索引是针对字段的，下面创建一个基于函数的索引，其SQL语句如下：

  1. CREATE INDEX emp_last_name_ix
  3. ON employees(LOWER(last_name))

  执行该SQL语句，再次打开Indexes选项卡，如图6.14所示，该索引创建成功，且Columns列里显示的是带函数的内容“LOWER(LAST_NAME)”。

6.6 索引 - 图3


图6.14 基于函数的索引

  当用户针对雇员表执行下面的SQL语句时，该索引将起到提高查询速度的作用。

  1. SELECT * FROM employees WHERE LOWER(last_name) = 'grant'
  • 删除索引

  不能直接修改索引，所以要想修改原索引的话，必须先删除索引然后再创建它。使用DROP INDEX index语句可以从数据字典中删除索引。