索引与约束的使用

13.1 为什么引入索引

设想一下：在大型的商业数据库中有一个包含了数百万个记录的表。如果在这个表上没有索引的话，那么查询该表中的任何记录都只能通过顺序地逐行扫描得到。这会产生大量的磁盘输入/输出（I/O），因此也就会大大地降低系统的效率。

其实索引的概念很简单。许多读者可能有到图书馆借书的经历。当您到了图书馆之后，没有必要一个书架一个书架地看。一般您会先查看一下图书馆的图书目录，可以按书名、作者名或出版日期等查询。由于图书目录是按一定的顺序排放的，会很快地找到所需要的书的记录，在该记录中标有此书存放的准确位子，这时您就可直奔存书的地方了。

Oracle为了提高查询的效率也引入了索引。Oracle 索引也是按索引关键字的顺序存放记录，也叫数据结构。在索引记录中存有索引关键字和指向表中真正数据的指针（地址）。Oracle系统利用算法在索引上可以很快地查找到所需的记录并利用指针找到所需的数据。由于Oracle索引中只存索引关键字和指向表中真正数据的指针（地址），因此它的规模要比真正存有数据的表的规模小得多。这样对索引进行操作的I/O量要比对真正的表进行操作少很多。读者可能知道在计算机的所有操作当中，I/O 操作应该是最慢的，因此减少了I/O操作就等于加快了查询的速度。

引入索引目的就是为了加快查询的速度。Oracle 索引是一个独立于表的对象，它可以存放在与表不同的磁盘上。即使索引崩溃，甚至索引被删除掉都不会影响真正存有数据的表。在Oracle数据库中，一个索引一旦被建立就由Oracle系统自动维护，而且由Oracle系统决定什么时候使用这个索引。您不用在查询语句中指定使用哪个索引，其实您所使用的查询语句与没建索引时几乎完全一样，只是查询速度可能快多了。虽然 Oracle索引是一个独立于表的对象，但是当一个表被删除时所有基于该表的索引都被自动地删除掉。想想看为什么？

谈了这么多Oracle索引的内容，读者可能会问在Oracle数据库中怎样建立索引呢？

如何建立索引

在Oracle数据库中有两种方法来建立索引：

（1）Oracle系统自动建立——当用户在一个表上建立主键（PRIMARY KEY）或惟一（UNIQUE）约束时，Oracle系统会自动创建惟一索引（UNIQUEINDEX）。

（2）手工建立——用户在一个表中的一列或多列上用CREATE INDEX语句来创建非惟一索引（NONUNIQUE INDEX）。

这一节主要介绍手工建立索引，自动创建索引将在后面有关约束的章节中详细介绍。创建索引的命令格式如下：CREATE INDEX 索引名ON 表名（列名【，列名】…）；

例13-3

SQL> CREATE INDEX empcon_ename_idx

2 ON empcon(ename);例13-3结果索引已创建。

注意：|在例13-3中索引名的命名方式。这是Oracle公司推荐的方式，其索引名包括了三部分，即表名、列名和对象的类型。以这种方式命名的索引将来维护起来很方便。您可以不遵守这一约定，使用像aa、x1等作为索引名，但将来维护起来很困难。

如何查看索引

例13-6

SQL> SELECT INDEX_NAME,INDEX_TYPE,TABLE_NAME,UNIQUENESS

2 FROM user indexes;

例13-6的结果并未告诉我们这些索引都是基于哪些列。要想得到这方面的信息，您可以使用数据字典 user_ind_columns。为了使显示的信息清楚易懂，您可以先使用如例13-7 的SQL*PLUS命令来格式化SELECT语句的输出。

例13-8

SQL> SELECT index_name,table_name,column_name,column_position

2 FROM user_ind_columns;

例13-9

SQL>CREATE INDEX empcon_job_sal_idx

2 ON empcon(job,sal);

例13-10

SQL> SELECT index_name,table_name,column_name,column_position

2 FROM user_ind_columns;

例13-10的结果表明：在组合索引EMPCON JOB SAL IDX 中最左边的索引关键字（列）JOB的COLUMN POSITION 的值为1，而组合索引 EMPCON JOB_SAL_IDX中左边第二位的索引关键字（列）SAL的COLUMN_POSITION的值为2。现在您应该理解了COLUMN POSITION 的含义了吧？

使用索引时应注意的问题

尽管在一个表上建立索引可能会加快查询的速度，但这可能会降低DML操作的速度。因为每一条DML语句只要涉及到索引关键字，Oracle系统就得调整索引。另外索引作为一个独立的对象需要消耗磁盘空间。如果表很大的话，其索引消耗磁盘空间量也会很大

一些刚入门的数据库开发人员在他们刚刚学会建立索引时，往往趋向于在他们操作的表中的几乎每一列上都创建索引。这可能会消耗大量的磁盘空间，而且会大大地降低这些表的DML操作的速度。切记一定要避免在一个表上创建过多的索引（Over Indexes），尤其对DML操作频繁的表。在建立每一个索引时，您都要权衡由这个索引所带来的查询速度的改进和它对 DML 操作的冲击。您一定要问自己： “对您的系统来说是查询操作重要呢？还是DML操作重要呢？"没有一个完美的答案，最后是在这两种操作之间做出折衷。

另外一个值得注意的问题是：虽然您建立了索引，但Oracle系统并不保证一定使用它。为了让Oracle系统有可能使用索引，您应该把索引关键字放在SELECT语句的WHERE子句中。但这也不能保证Oracle系统百分之百地使用索引。例如把组合索引中的一个索引关键字放在了SELECT语句的WHERE子句中，而该索引关键字不是最左边的索引关键字，

此时Oracle系统将不使用该组合索引（Oracle9i在这方面做了一些改进）。再有索引（列）关键字为SELECT语句中某个表达式的一部分时，Oracle系统将可能不使用该索引。

还需要补充的一点是：尽管建立了索引，但Oracle9i之前的版本，对于这些索引是否被使用过，在 Oracle 系统中没有任何记录。Oracle9i 对这方面做出了一些改进。Oracle9i 可以记录某个索引是否被使用过，但Oracle9i只记录用过或没用过（YES/NO），即用过一次是用过，用过一万次也是用过。不管怎样Oracle9i还是向前迈进了一步。

那么什么时候应该建立索引，什么时候不应该建立索引呢？对这一问题Oracle并没有给出一个精确的答案，而是只给出了一些指导原则。

在下列条件之一成立时，您应该为这个表建立索引：

■ 表很大而且大多数查询的返回数据量很少（Oracle 推荐为小于总行数的百分之二

到百分之四）。因为如果返回数据量很大的话就不如顺序地扫描这个表了。

■ 此列的取值范围很广，一般为随机分布。如在大多数员工表中的年龄（age）列一

般为随机分布，即几乎从18岁到60岁（许多西方国家为65岁）所有年龄的员工都有（但在军队和夜总会员工的年龄可能就主要集中在18到20岁之间，这样的数据就不是随机分布）。

■ 一列或多列经常出现在WHERE子句或连接条件中。原因在前面已经解释了。

■ 表上的DML操作较少。

■ 此列中包含了大量的空值（NULL）。

■ 此列不经常作为 SELECT语句中某个表达式的一部分。将以上的陈述反过来说就是什么时候不应该建立索引。

基于函数的索引

如果在 SELECT 语句的WHERE 子句中使用了某一表达式，如何加快这样的查询的速度呢？在Oracle8i以前的版本上是没有办法做到的。但在Oracle8i或以后的版本上可以利用创建基于函数的索引的方法来解决这一难题。与一般的索引不同的是，基于函数的索引中的索引关键字是表达式而不是列。

假如在您的公司里，工资在2000元以下为低收入者。您或者经理们可能时常要查看一下，哪些员工属于低收入者，哪些员工不属于低收入者。为了加快这类查询的速度，您试着用例13-11的DDL语句来建立一个基于表达式 sal-2000的索引。

例13-11

SQL> CREATE INDEX empcon_salgt_idx

2 ON empcon(sal-2000);

怎样确认Oracle系统是否使用了索引

虽然在Oracle数据库中，由Oracle系统决定什么时候使用索引，不用在查询语句中指定使用哪个索引。但是Oracle系统究竟有没有使用索引一直是一个扑朔迷离的问题。可能想知道到底有没有办法确切地知道您所建立的索引是否真的使用过。您可以使用SQL*PLUS的EXPLAIN命令来得到有关索引使用情况的信息。但在使用这个命令之前，您需要运行类似例13-19的utlxplan.sql脚本文件来产生plan_table表。

例13-19

@d:\Oracle\ora90\rdbms\admin\utlxplan

在例13-19中，d：Oraclelora90 为 Oracle 的安装目录，也就是在有些书中称之为SOracle_HOME的目录。在您的Oracle系统上可能不同。如果您找不到这个脚本文件可用搜索器（在许多操作系统上为find命令）搜一下就行了。

之后可以使用例13-20的SQL*PLUS命令来解释您的查询语句。例13-20

SQL> EXPLAIN plan for

2        SELECT ename,job,sal,comm,deptno

3        FROM empcon

 4       WHERE  (sal-2000)<0;

现在就可以通过例13-25查询plan_table表，看看Oracle系统是否使用了您所创建的empcon_salgt_idx索引。

例13-25

SQL> SELECT id, operation, options, object_name, position

2 FROM plan_table;

当一个索引不再需要时，应该删除它以释放这个索引所占有的磁盘空间。另外在大规模输入之前，为了加快输入数据的速度有时也应该先删除索引，等数据输入之后再重建这些索引。删除索引的命令格式如下：

DROP INDEX 索引名；

该DDL命令将从数据字典中删除索引的定义，并释放这个索引所占用的磁盘空间。要

删除一个索引，您必须是索引的所有者或是具有DROP ANY INDEX的系统权限。

假设您发现组合索引empcon job sal idx 没多大的用处，可以使用例13-29的DDL语句删除这个索引。例13-29

SQL> DROP INDEX EMPCON_JOB_SAL_IDX;例13-29结果索引己丢弃。

尽管例13-29的结果已显示“索引已丢弃”，但为了慎重起见，您还想验证一下组合索引 empcon job sal idx 是否真的被删除掉了。为了使显示的信息清楚易懂，还应该先使用例13-30和例13-31的SQL*PLUS的命名来格式化SELECT语句的输出。

例13-32

SQL> SELECT INDEX_NAME,INDEX_TYPE,TABLE_NAME,UNIQUENESS

2 FROM user_indexes;

为什么要引入约束及如何定义约束

引入约束的目的就是防止那些无效或有问题的数据输入到表中，使用数据库的术语就是维护数据的一致性。约束是强加在表上的规则或条件。当对该表进行DML或DDL操作时，如果此操作会造成表中的数据违反约束条件或规则的话，Oracle系统就会拒绝执行这个操作。这样做的好处是当错误刚一出现时就能被Oracle系统自动地发现，从而使数据库的开发和维护都更加容易。

Oracle系统一共提供了以下5种约束：

非空（NOT NULL）约束———所定义的列决不能为空。

惟一（UNIQUE）约束——在表中每一行中所定义的这列或这些列的值都不能相同主键（PRIMARYKEY）约束惟一地标识表中的每一行。

■ 外键（FOREIGN KEY）约束——用来维护从表（Child Table）和主表（Parent Table）

之间的引用完整性（Referential Integrity）。

■ 条件（CHECK）约束——表中每行都要满足该约束条件。

约束是加在表上的，因为只有表中存有数据。可以在创建表时在CREATETABLE 语句中定义约束，也可以在已存在的表上利用ALTERTABLE 语句来定义约束。既可以在列一级，也可以在表一级定义约束。约束的定义存在Oracle的数据字典中，只能通过数据字典来浏览约束。可以给出约束的名字。如果您在定义约束时没有给出约束的名字，Oracle 系统将为该约束自动生成一个名字，其格式为SYS_Cn，其中n为大于零的自然数。

非空（NOT NULL）约束13.9

一般在一个公司或机构中，所有的部门都应该有一个部门名。也就是说如果公司或机构要成立一个部门，就必须给出这个部门名，即部门名不能为空。现在您可以通过使用例13-41的 CREATE TABLE 语句来定义非空（NOT NULL）约束，以这种方法来实现上面所说的商业规则。

例13-41

SQL> CREATE TABLE deptcon(

2       deptno  NUMBER(3),

3       dname  VARCHAR2(15) NOT NULL,

4        loc VARCHAR2(20));

查看有关约束的信息

例13-52

SQL> SELECT owner, constraint_name, constraint_type, table_name

2 FROM user constraints;

现在我们来解释例13-52的显示结果中第三列C（constraint_type）中每个字母所代表的含义：

C 代表CHECK（条件约束）和NOTNULL（非空约束）。

P代表PRIMARYKEY（主键约束）。

R 代表REFERENTIALINTEGRITY，即FOREIGN KEY（外键约束）

U代表UNIQUE（惟一约束）。

例13-54

SQL> SELECT owner, constraint name, table name, column name

2 FROM user_cons_columns;

惟一（UNIQUE）约束

如果您的公司要求所有的部门的名字都不能相同，那又该怎么办呢？您可以利用在DNAME上定义惟一（UNIOUE）约束来实现公司的这一商业规则。

为了操作方便，可以先使用例13-55的DDL语句将加在deptcon表的DNAME上的NOTNULL（非空约束）删除掉。

例13-55

SQL> ALTER TABLE deptcon

2 DROP CONSTRAINT SYS_C002719;

例13-60

SQL>ALTER TABLE deptcon

ADD CONSTRAINT deptcon_dname_uk UNIQUE(dname);2

其实在这个例子中，只要看到了约束名 DEPTCON DNAME UK，您就应该猜到该约束是定义在DEPTCON表的DNAME列上，并且是一个惟一（UNIQUE）约束。这种约束的命名法是Oracle推荐的，它由3部分组成：表名、列名和约束的类型，它们之间由下划线（）连接。其中约束的类型表示如下：

■ UK UNIQUE KEY（惟一）约束

■ PK PRIMARYKEY（主键）约束

■ FK FOREIGN KEY（外键）约束

■ CK CHECK（条件）约束

NN NOT NULL（非空）约束

条件（CHECK）约束

您可以利用条件（CHECK）约束来实现公司中一些比较复杂的商业规则。条件（CHECK）约束定义了表中每一行数据都必须满足的条件。条件（CHECK）约束中的条件与查询语句中的条件相同，但是不能包括以下的内容：

CURRVAL，NEXTVAL，LEVEL和ROWNUM这样的伪列（PSEUDOCOLUMNS）。

■ 引用其他行中值的查询语句。

■ SYSDATE，USER，USERENV和UID的函数调用。

条件（CHECK）约束既可以在表一级定义也可以在列一级定义。在一列上可以定义任意多个条件（CHECK）约束。

为了使读者容易理解，还是老办法用例子来说明。如果您留意过招工广告的话，您可能有印象，一般对前台或文秘的招工都要求应征者满足以下条件：

■ 女性

■ 年龄在18~35岁之间

■ 最好大学或以上学历

■ 相貌端庄

未婚

等等

SQL> CREATE TABLE person(

2         id        VARCHAR2(10),

3        name  VARCHAR2(20),

4        gender CHAR(1),       

5        age NUMBER,

6        CONSTRAINT person_gender_ck

7                                CHECK(gender='F'),

8        CONSTRAINT person_age_ck

9        CHECK(age BETWEEN 18 AND 35));

SQL> SELECT owner,constraint_name,constraint_type,table_name,

2        search_condition

3 FROM user constraints

4 WHERE table_name ='PERSON';

主键（PRIMARY KEY）约束

主键（PRIMARYKEY）是关系型数据库中一个非常重要的概念，您一旦在表中的某一列或某几列上定义了主键（PRIMARYKEY）约束，Oracle系统就会自动地维护实体完整性。

例13-80

SQL> ALTER TABLE deptcon

2  ADD CONSTRAINT deptcon_deptno_pk

3        PRIMARY KEY (deptno);

例13-90

SQL> SELECT INDEX_NAME,INDEX_TYPE,TABLE_NAME,UNIQUENESS

2 FROM user_indexes

3 WHERE table_name ='DEPTCON';

SQL> SELECT index_name, table_name, column_name, column_position

2 FROM user ind columns

3 wHERE table name = 'DEPTCON';

外键（FOREIGN KEY）约束

由于外键（FOREIGN KEY）约束是用来维护从表（Child Table）和主表（Parent Table）之间的引用完整性（Referential Integrity）的，所以外键（FOREIGN KEY）约束要涉及的不止一个表。正是由于这个原因使得外键（FOREIGN KEY）约束要比其他几种约束更难理解。因此本书要用较长的篇幅来讲解外键（FOREIGN KEY）约束。

SQL> ALTER TABLE empcon

2 ADD CONSTRAINT empcon_deptno_fk

3 FOREIGN KEY(deptno)REFERENCES deptcon(deptno);

SQL> SELECT owner,constraint_name,constraint_type,table_name,

2        r_constraint_name

3 FROM user_constraints

4 WHERE table_name='EMPCON';

SQL> SELECT owner,constraint_name,table_name,column_name,position

2 FROM user_cons_columns

3 WHERE table_name = 'EMPCON';

外键（FOREIGN KEY）约束对INSERT语句的影响

外键（FOREIGN KEY）约束对

DELETE语句的影响

结论：

在进行删除操作时，只有操作是在父表或主表（PARENT TABLE）这一端时才会产生违反引用完整性（Referential Integrity）的问题，而操作是在子表或从表（CHILD TABLE）端时不会产生。

外键（FOREIGN KEY）约束对UPDATE 语句的影响

SQL> UPDATE empcon

2        SET deptno = NULL

3        WHERE deptno = 88;

SQL> UPDATE deptcon

2        SET deptno=44

3        WHERE deptno = 88;

SQL> UPDATE empcon

2 SET deptno= 44 

3 WHERE deptno IS NULL;

结论：

在进行修改操作时，操作无论是在父表（PARENT TABLE）还是在子表（CHILD TABLE）端都可能会产生违反引用完整性（Referential Integrity）的问题.

约束对外键（FOREIGN KEY）DDL语句的影响

结论：

在进行删除整个表时，只有删除的是父表或主表（PARENT TABLE）时会产生违反引用完整性（Referential Integrity）的问题，而操作的是子表或从表（CHILD TABLE）时不会产生。

外键（FOREIGN KEY）的 ON DELETE SET 13.19

NULL和 ON DELETE CASCADE子句

ON DELETE SET NULL子句的作用是：当主表（Parent Table）中的一行数据被删除时，Oracle系统会自动地将所有从表（Child Table）中依赖于它的数据记录的外键（FOREIGN KEY）改为空（NULL）。真的像Oracle说的那样吗？做了以下的例子您就自然会明白了。

为了在empcon表中外键（FOREIGN KEY）约束上加入 ON DELETE SET NULL子句，您应该先使用例13-122的DDL语句来删除empcon表中deptno列上现有的外键（FOREIGN KEY）约束。

例13-122

SQL> ALTER TABLE empcon

2 DROP CONSTRAINT empcon_deptno_fk;

SQL> ALTER TABLE empcon

2 ADD CONSTRAINT empcon_deptno_fk

3 FOREIGN KEY(deptno)REFERENCES deptcon(deptno)

4 ON DELETE SET NULL;

SQL> ALTER TABLE empcon

2 ADD CONSTRAINT empcon_deptno_fk

3 FOREIGN KEY(deptno) REFERENCES deptcon (deptno)

4 ON DELETE CASCADE;

ON DELETE SET NULL子句的副作用比ON DELETE CASCADE子句要小，但是也要谨慎使用，只有在不得已时使用。结论：

如果在外键的定义中使用了 ON DELETE SET NULL 子句或 ON DELETE CASCADE 子句，无论删除操作是在父表（PARENT TABLE）这一端还是在子表或从表（CHILD TABLE）这一端都不会产生违反引用完整性（Referential Integrity）的问题。但是这却留下了安全隐患。

约束的维护

在前面我们讲过为了维护数据的一致性，您可能会在一个表上定义一个或多个约束。但Oracle系统进行过多的约束检查会大大地降低Oracle数据库系统的效率。在某些情况下，例如在数据库系统中大规模装入数据时，为了系统的效率您不得不牺牲数据的一致性来关闭一些约束，甚至删除一些约束。

关闭约束的命令格式如下：ALTER TABLE 表

DISABLE CONSTRAINT 约束名 【CASCADE】；

其中CASCADE子句用来关闭存在有完整性关系的约束。DISABLE子句既可以用在CREATE TABLE 语句中，也可以用在ALTER TABLE语句中。

SQL> ALTER TABLE deptcon

2 DISABLE CONSTRAINT deptcon_deptno_pk CASCADE;

打开约束的命令格式如下：ALTER TABLE 表

ENABLE CONSTRAINT 约束名；

ENABLE子句既可以用在CREATETABLE语句中，也可以用在ALTERTABLE语句中。如果您打开UNIQUE KEY或PRIMARY KEY约束，Oracle系统将自动地为UNIQUE KEY或PRIMARYKEY建立索引（INDEX）。

当多数人在休息或已进入梦乡时，为了那温馨的小家庭您不得不继续工作。您现在可以使用例13-142的DDL语句重新打开（ENABLED）主键约束DEPTCON_DEPTNO_PK，让Oracle系统来检测数据的一致性。

SQL> ALTER TABLE deptcon

2 DROP CONSTRAINT deptcon_deptno_pk CASCADE;

（1）不能在错误刚一出现时就发现。其后果在大型系统中可能是灾难性的，因为当一个表中的数据达到几十万行乃至几百万行之后再想找到那个错误简直成了大海里捞针。

（2）无论是过程，还是函数，都是由程序员（开发人员）写的。他们的水平高过Oracle 的设计者的可能性不大。因此这些程序出错的可能性明显增加，执行效率也很难和 Oracle 提供的约束相比。

（3）过程或函数存储次数明显高于Oracle的约束，这无疑也会拖累系统的效率。综上所述，您应该在创建表时就定义好所需的各种Oracle的约束。如果没有的话，应尽可能早地加上所需要的 Oracle 约束。这样会使系统更可靠，更容易维护。另外如果在Oracle提供的5种约束和自定义的程序（触发器、过程或函数）两者之间有选择的话，应尽可能使用Oracle的约束。这样可能会改进系统的效率和增加系统的稳定性。