5.6 索引

索引具有两个功能：一是强制实施主键约束和唯一约束，二是提高性能。应用程序的索引策略对于性能至关重要。索引管理所属的范围没有明确界限。当业务分析师指定将要实施为约束的业务规则时，他们实际在指定索引。数据库管理员将监视在数据库中运行的代码的执行，并将提出有关索引的建议。开发人员最了解代码内容和数据特点，也将参与到索引策略的开发中。

5.6.1 为什么索引是必需的

索引是约束机制的一部分。如果将某列(或一组列)标记为表的主键，那么，每次在表中插入行时，Oracle必须检查是否已经存在具有同一主键值的行。如果表的列上不具有索引，那么唯一的办法是扫描整个表，检查每一行。如果表仅有数行，这种做法可以接受，但如果表有数千行或数百万行(或数十亿行)，则不可行。通过索引，可以立即(或近乎立即)访问键值，因此，检查存在性几乎可以一挥而就。如果定义了主键约束，而主键列上尚不存在索引，Oracle将自动创建一个。

唯一约束也需要索引。与主键约束的区别在于，唯一约束的列可以留空。这不影响索引的创建和使用。外键约束通过索引来实施，但索引必须存在于父表中，而并非一定在为其定义约束的表中。外键约束将子表中的列与父表中的主键或唯一键关联起来。在子表中插入行时， Oracle将在父表中查找索引，在确认存在匹配的行后才允许执行插入。但是，为了提高性能，始终应在子表的外键列上创建索引：如果Oracle可以使用索引来确定子表中是否存在引用被删除行的行，那么在父表上执行DELETE操作的速度将大大加快。

索引对于性能至关重要。在执行包含WHERE子句的任何SQL语句时，Oracle必须确定要选择或修改的行。如果WHERE子句中引用的列上没有任何索引，唯一的途径是扫描整个表。全表扫描将读取表中的每一行，以便找到相关行。如果表有许多行，那么将耗用很长时间。如果相关的列上存在索引，Oracle将改为搜索索引。索引是有序键值列表，其排列方式可使搜索变得更有效。每个键值是指向表行的指针。如果表超过一定的大小，而且要检索的行的比例低于特定的值，那么，与扫描全表相比，通过索引查找来定位相关行将快得多。如果表较小，或WHERE子句将检索表行的大部分，则全表扫描来得更快：通常可以选择信任Oracle，因为Oracle可以根据数据库收集的有关表和表行的统计信息，做出是否使用索引的正确决策。

第二种可以使用索引的情况是排序。如果SELECT语句包括ORDER BY、GROUP BY、UNION或其他一些关键词，则必须按顺序排列行。如果有了索引，则可以按正确顺序返回行，而无须首先对它们进行排序。

索引有助于提高性能的第三种情况是在联接表时。不过此时，Oracle仍会根据表的大小和可用的内存资源做出选择：将表扫描到内存中并在那里将它们联接在一起可能比使用索引的速度更快。嵌套循环联接(nested loop join)技术使用另一个表上的索引遍历一个表来定位匹配行，这通常是一个磁盘密集型操作。哈希联接(hash join)技术将整个表读入内存，将其转换为哈希表，然后使用哈希算法定位匹配的行，这是一个内存和CPU更为密集型的操作。排序合并联接(sort merge join)在联接列上排序表，然后将它们合并在一起：这通常是磁盘、内存和CPU资源之间的折中。如果没有索引，Oracle将在可用的联接技术方面受到严重限制。

5.6.2 索引类型

Oracle 支持多类索引，这些索引有多个变体。此处描述的两类索引是B*树索引(默认索引类型)和位图索引。一般而言，索引将提高检索数据的性能，但会降低DML操作的性能(原因是必须维护索引)。每次在表中插入一行时，必须在表的每个索引中插入一个新键，这会给数据库造成更大负担。为此，在事务处理系统中，通常会尽量减少索引数量(可能不超过约束需要的数量)，而在查询密集系统(如数据仓库)中，创建足够多的索引会起到帮助作用。

1．B*树索引

B*树索引(B代表“平衡(balanced)”)是一个树结构。树的根节点指向第二级别的多个节点，第二级别的节点又指向第三级别的多个节点，以此类推。树的所需深度主要取决于表中的行数，以及索引键值的长度。

索引树的叶节点按顺序存储行键，每个键有一个指针，用来确定行的物理位置。因此，如果要使用索引查找检索行，而WHERE子句在索引列上使用了相等(equality)谓词，那么Oracle将沿着树向下导航，直至找到包含期望键值的叶节点，然后使用指针查找行。如果WHERE子句使用了不等(nonequality)谓词(如LIKE、BETWEEN、>或<等任何操作符)，则Oracle 可以在树中向下导航，找到第一个匹配的值键，然后导航索引的叶子节点来查找其他所有匹配的值。此时，它将按顺序从表中检索行。

行的指针是rowid。rowid是Oracle专用的虚拟列，每个表的每一行都有此虚拟列。其中的加密项是行的物理地址。因为rowid不是SQL标准的一部分，所以普通的SQL语句永远都看不到它们，但可以根据需要查看和使用它们。如图5-9所示。

每一行的 rowid 在全局上是唯一的。整个数据库的每个表的每一行都有不同的 rowid。rowid 加密项给出了行的物理地址，Oracle 可以基于此地址计算行在哪个操作系统文件的哪个位置，并直接找到它。

如果需要的行数只占表的总行数的一小部分，而且表很大，则B*树是十分有效的检索行的方式。考虑以下语句：

        select count(*) from employees
        where last_name between 'A%' and 'Z%';

此WHERE子句涉及广泛的范围，将包括表中的每一行。如果通过搜索索引来查找rowid，然后使用rowid来查找行，那么，与扫描全表相比，速度将慢得多。毕竟需要的是整个表。另外，如果表非常小，一次磁盘读取就可以对其进行完整扫描，则没必要首先读取索引。

一般认为，如果查询要检索超过2%～4%的行，则全表扫描速度更快。如果在WHERE子句中指定的值是NULL，则会是一个例外情况。NULL不会参与到B*树索引中，因此，如下查询：

        select * from employees
        where last_name is null;

将始终导致全表扫描。如果一个列中包含的唯一值很少，那么，在此列上创建B*树索引就没有太大价值，因为其选择性不充分：为了找到每个不同键值而检索的表的部分过多。一般而言，如果遇到以下情况，应该使用B*树索引：

● 列的基数(不同值的个数)很大

● 表的行数多

● 列用在WHERE子句或JOIN条件中

2．位图索引

在很多业务应用程序中，数据和查询的特点致使B*树索引用处不大。考虑连锁超市的销售表，它存储一年的历史数据，可从几个维度进行分析。图5-10显示了一个仅有4个维度的简单实体-关系图。

每个维度的基数可能很少。做如表5-1的假设。

假设数据分布均匀，则只有两个维度(PRODUCT和DATE)具有的选择性优于2%～4%的常用标准，这使索引变得很有价值。但是，如果查询使用范围谓词(如计算一个月的销售额， 10个或更多产品类别的销售额)，那么，这些也都不符合条件。这是一个简单事实：B*树索引在数据仓库环境中通常无用。一个典型的查询是针对一个月内自购客户对某类产品的购买额来比较两个商店。虽然可以在相关列上建立B*树索引，但Oracle 将会忽略它们，因为其选择性不充分。正因为如此，设计了位图索引。

位图索引将与每个键值关联的rowid存储为位图。CHANNEL索引的位图可能如下：

        WALK-IN     11010111000101011100010101.....
        DELIVERY    00101000111010100010100010.....

这指示前两行销售给自购客户，第三次销售是递送，第四次销售是自购，以此类推。

SHOP索引的位图可能为：

        LONDON     11001001001001101000010000.....
        OXFORD     00100010010000010001001000.....
        READING    00010000000100000100100010.....
        GLASGOW    00000100100010000010000101.....

这表示前两次销售发生在London的商店，第三次在Oxford，第四次在Reading，以此类推。如果接收到此查询：

        select count(*) from sales
        where channel='WALK-IN'
        and shop='OXFORD';

Oracle可以检索两个相关位图，并使用布尔“与”操作将它们结合在一起：

        WALK-IN             11010111000101011100010101.....
        OXFORD              00100010010000010001001000.....
        WALK-IN & OXFORD    00000010000000010000000000.....

按位“与”操作的结果显示，只有第7行和第16行符合选择性标准。位图的合并速度极快，可使用AND、OR和NOT操作符的任意组合，基于很多列上的很多条件，实现复杂的布尔操作。与B*树索引相比，位图索引的一个特别好处在于它们包含NULL。就位图索引而言，NULL只不过是另一个具有自己的位图的不同值而已。

一般而言，在具备以下条件时使用位图索引：

● 列的基数(不同值的个数)小

● 表中的行数多

● 列用于布尔代数运算

3．索引类型选项

在创建索引时，可以应用6个常用选项：

● 唯一或非唯一(Unique or non-unique)

● 反向键(Reverse key)

● 压缩(Compressed)

● 复合(Composite)

● 基于函数(Function based)

● 升序或降序(Ascending or descending)

所有这6个选项都可应用于B*树索引，只有后三个选项可用于位图索引。

● 唯一索引不允许重复值。非唯一索引是默认设置。索引的唯一特性独立于唯一约束或主键约束：唯一索引不允许插入重复值，即使没有定义此类约束，也同样如此。唯一约束或主键约束可以使用非唯一索引；它正好没有重复值。实际上，这是可延迟约束的一项要求，因为在某个阶段(提交事务之前)可能存在重复值。下一节将讨论约束。

● 反向键索引基于将字节倒置的键列版本，例如，将针对“nhoJ”而非“John”来设置索引。在完成SELECT时，Oracle将自动反转搜索字符串的值。这是在多用户系统中避免争用的功能强大的技术。例如，如果多位用户正在并发地插入具有主键(基于连续增长的编号)的行，那么，所有索引插入将聚集在索引的高端。通过反转键，连贯的索引键插入将分布在整个索引范围内。即使“John”和“Jules”很是接近，“nhoJ”和“seluJ”也相距甚远。

● 压缩索引仅将重复键值存储一次。默认方式是不压缩，这意味着，如果键值不唯一，将为每个出现的键值存储一次，每次存储都有一个rowid 指针。压缩索引仅将键存储一次，后跟所有匹配rowid的字符串。

● 复合索引建立在连接两个或多个列的基础之上。对于混合的数据类型没有限制。如果搜索字符串不包含所有列，则仍然可以使用索引。但是，如果它不包括最左边的列，Oracle 将必须使用跳过-扫描(skip-scanning)方法，与包括最左边的列相比，这种方法的效率会低得多。

● 基于函数的索引则基于应用于一列或多列的函数(如UPPER(last_name)或TO_CHAR(startdate, 'ccyy-mm-dd'))的结果。查询必须将同一函数应用于搜索字符串，否则Oracle不能使用索引。

默认情况下，索引是升序索引，即键是按从最低值到最高值的顺序排列的。而降序索引与此相反。事实上，区别通常并不重要：索引中的条目以双链表的形式存储，因此，可按相同的速度向上或向下导航，但是，如果使用索引范围扫描进行检索，那么这将影响行的返回顺序。

5.6.3 创建和使用索引

在定义主键约束和唯一约束时，如果相关列上的索引并不存在，则将隐式创建索引。显式创建索引的基本语法如下：

        CREATE [UNIQUE | BITMAP] INDEX [ schema.]indexname
        ON [schema.]tablename (column [, column...] ) ;

索引的默认类型是非唯一、非压缩、非反向键B*树索引。无法创建唯一的位图索引(即便允许，也不会这么做，因为这会导致基数问题)。索引是模式对象，可能在一个模式中创建另一个模式的表上的索引，但大多数人认为这会造成混淆。复合索引(composite index)是多个列上的索引。复合索引可以在不同数据类型的列上，而且列不必在表中相互邻近。

下面的例子创建了表和索引，然后定义了约束：

        create table dept(deptno number,  dname varchar2(10));
        create table emp (empno number, surname varchar2 (10),
         forename varchar2(10), dob date, deptno number);
        create unique index dept_i1 on dept(deptno);
        create unique index emp_i1 on emp(empno);
        create index emp_i2 on emp(surname, forename);
        create bitmap index emp_i3 on emp(deptno);
        alter table dept add constraint dept_pk primary key (deptno);
        alter table emp add constraint emp_pk primary key (empno);
        alter table emp add constraint emp_fk
        foreign key (deptno) references dept(deptno);

创建的前两个索引被标记为UNIQUE，意即不允许插入重复值。此处未将其定义为约束，但实际上就是这样。第三个索引未定义为UNIQUE，即可以接受重复值；这是两个列上的复合索引。第四个索引定义为位图索引，因为与表中的行数相比，列的基数占的比例可能很小。

在定义两个主键约束时，Oracle将检测预先创建的索引，并使用它们来实施约束。注意， DEPT.DEPTNO上的索引并非用于提高性能，因为此表可能很小，以至于从不会使用索引来检索行(扫描速度更快)，但使用索引来实施主键约束依然至关重要。

在创建索引后，索引的使用完全透明化和自动化。在执行SQL语句之前，Oracle服务器将评估所有可能的执行方式。其中一些方法涉及使用可用的任何索引，其他方法与此无关。Oracle将使用在表和环境上收集的信息，来智能化地确定要使用哪些索引(如果有)。

5.6.4 修改和删除索引

ALTER INDEX命令不能用于更改本章描述的任何特性：索引类型(B*树或位图)，列，以及索引是唯一索引还是非唯一索引。ALTER INDEX命令属于数据库管理范畴，通常用于调整索引的物理属性，不用于调整开发人员关注的逻辑属性。如果有必要更改其中一些属性，则必须删除和重新创建索引。接着前一节的例子，将EMP_I2更改为包含员工的生日：

        drop index emp_i2;
        create index emp_i2 on emp(surname, forename, dob);

此复合索引现在包含具有不同数据类型的列。列的出现顺序正好与表中的定义顺序相同，但是，这毫无必要。

在删除表时，也将删除为表定义的所有索引和约束。如果通过创建约束隐式创建了索引，那么，在删除约束时也将删除索引。如果显式创建了索引，并在后来创建了约束，那么，如果删除约束，索引将留存下来。

练习5-5 创建索引

本练习将为CUSTOMERS表创建、填充、添加一些索引。

(1) 连接到HR模式，创建CUSTOMERS、ORDERS、ORDER_ ITEMS和PRODUCTS表，如下：

        create table customers(customer_id number(8,0) not null,
          join_date date not null, customer_status varchar2(8) not null,
          customer_name varchar2(20) not null, creditrating varchar2(10));

       create table orders (order_id number(8), order_date date, order_status
        varchar2(8), order_amount number(10,2),
        customer_id number(8));

       create table order_items (order_item_id number(8),
        order_id number(8), product_id number(8));

       create table products(product_id number(8),
        product_description varchar2(20), product_status varchar2(8),
        price number (10,2), price_date date, stock_count number(8));

(2) 在客户名称和客户状态上创建复合B*树索引：

        create index cust_name_i on customers (customer_name, customer_status);

(3) 在一些低基数列上创建位图索引：

        create bitmap index creditrating_i on customers(creditrating);

(4) 运行下列查询确定刚创建的索引的名称及其他一些特性。

        select index_name, column_name, index_type, uniqueness
        from user_indexes natural join user_ind_columns
        where table_name='CUSTOMERS';

5.7 约束

表约束是数据库能够实施业务规则以及保证数据遵循实体-关系模型的一种手段，其中，实体-关系模型由定义应用程序数据结构的系统分析所确定。例如，机构中的商业分析师决定使用数字来唯一标识所有客户以及所有订单，在创建客户之后才能将订单发给客户，而且每张订单必须包含有效日期和一个大于零的值。要实现这个功能，需要进行下列操作：在CUSTOMERS表的CUSTOMER_ID列上和ORDERS表的ORDER_ID列上创建主键约束，在引用CUSTOMERS表的ORDERS表上创建外键约束，在ORDERS表的DATE列上创建一个NOT NULL约束(DATE数据类型本身能够自动确保所有日期有效，这种数据类型并不接受无效的日期)，在ORDERS表的ORDER_ AMOUNT列上创建一个CHECK约束。

在针对定义了约束的表执行任何DML时，如果DML违反了约束，则将自动回滚整个语句。注意，如果一个DML语句影响到多个行，那么，在特定行遇到约束问题前，此语句可能已经局部成功。如果此语句是多语句事务的一部分，那么，事务中已经成功的语句将保持完好，但不提交。

5.7.1 约束类型

Oracle数据库支持的约束类型如下：

● UNIQUE

● NOT NULL

● PRIMARY KEY

● FOREIGN KEY

● CHECK

约束具有名称。最好使用标准命名约定指定名称，如果未显式指定名称，Oracle将为其生成名称。一般用于对象-关系交互式操作的REF约束超出了本书的讨论范围。

1．UNIQUE约束

UNIQUE约束要求，对于列或列组合而言，表中每行的值必须是不同的。如果此约束针对单个列，则相应的列称为键(key)列。如果约束由多列组成(称为组合键唯一约束)，这些列并不必是相同的数据类型，也不必在表定义中相互邻近。

UNIQUE约束的怪异之处在于，可以在键列中输入NULL值。在键列中，可能有任意数量的包含NULL值的行。这是因为NULL不等于任何东西，甚至不等于另一个NULL。因此，如果不搜索NULL，则可以确保在键列上选择行时将仅返回一行；如果搜索NULL，那么，键列为NULL的所有行都将返回。

UNIQUE约束通过索引来实施。在定义UNIQUE约束时，Oracle将查看键列上的索引，如果不存在，就创建一个。此后，每次插入行时，Oracle都将查看索引，了解键列的值是否已经存在。如果已存在，则将拒绝插入。这些索引(称为B*树索引)的结构不包含NULL值，正因为如此，才允许出现多个包含NULL的行：索引中根本不存在NULL。虽然索引的第一要务是实施约束，但也有次生效应：如果在SQL语句的WHERE子句中使用键列，性能将提高。但是，选择WHERE key_column IS NULL则不使用索引(因为它不包括NULL)，因此总是导致扫描整个表。

2．NOT NULL约束

NOT NULL约束强制在键列中输入值。它针对每个列进行定义，有时被称为强制列(mandatory column)。如果业务要求一组列都具有值，则不能为整个组定义NOT NULL约束，而必须针对每列定义NOT NULL约束。

如果尝试插入没有为具有NOT NULL约束的列指定值的行，将导致错误。如3.1节所述，在创建表时通过在列上包含DEFAULT子句，可以不指定值。

3．PRIMARY KEY约束

主键(primary key)是定位表中单个行的方式。关系数据库范例要求每个表都必须有主键，主键是用于区分每行的列或列组合。Oracle数据库的定义与此范例(及其他一些RDBMS实现)有所不同，它允许存在不包含主键的表。

主键约束的实现实际上是UNIQUE和NOT NULL约束的组合。键列必须具有唯一值，而且不得为空。与UNIQUE约束一样，约束列上必须存在索引。如果不存在，将在定义约束时创建索引。一个表只能有一个主键，试着创建第二个，将出现错误。但是，表可以有任意数量的UNIQUE和NOT NULL约束列，因此，如果业务分析师认定多个列必须是唯一的而且必须填充，则可将其中一个指定为主键，将其他的列指定为UNIQUE和NOT NULL。例如EMPLOYEE表，其中电子邮件地址、社会保险号和员工编号都是必需的，而且是唯一的。

4．FOREIGN KEY约束

在父子关系的子表中定义FOREIGN KEY约束。此约束使子表中的列(或列组合)对应父表的主键列。这些列不必同名，但数据类型必须相同。FOREIGN KEY约束定义数据库的关系结构：连接第三范式的表的多对一关系。

如果父表具有UNIQUE和/或PRIMARY KEY约束，则这些列可用作FOREIGN KEY约束的基础，即使允许空值，也是如此。

UNIQUE约束允许约束列中出现NULL值，FOREIGN KEY约束也同样如此。即使父表的行中不存在NULL，也可将行插入包含NULL外键列的子表中。这会创建孤行，并产生令人不快的混乱。一般而言，UNIQUE约束中的所有列以及FOREIGN KEY约束中的所有列最好也定义NOT NULL约束，这往往是业务要求。

尝试在子表中插入父表中没有匹配行的行，将生成错误。同样，如果父表中的某行在子表中已有引用它的行，则删除相应的行将引发错误。可以使用两种技术来更改此行为。首先，可将此约束创建为ON DELETE CASCADE。这意味着，如果删除父表中的行，那么Oracle将在子表中搜索所有匹配行，并删除它们。这将自动发生。一个作用较温和的技术是将约束创建为ON DELETE SET NULL。在此情况下，如果删除父表中的行，Oracle将在子表中搜索所有的匹配行，并将外键列设为空。这意味着，子行将成为孤行，但依然存在。如果子表中的列也有NOT NULL约束，则父表上的删除操作将失败。

即使子表中没有行，也不能删除或截断外键关系中的父表。如果使用ON DELETE SET NULL或ON DELETE CASCADE子句，这依然适用。

FOREIGN KEY约束的一个变体是自引用FOREIGN KEY约束。这将定义一个条件，其中的父行和子行存在于同一个表中。如EMPLOYEE表，其中包括员工经理的列。经理本身也是一名员工，必须存在于此表中。因此，如果主键是EMPLOYEE_ID列，并由MANAGER_ID列来确定经理，则FOREIGN KEY约束表明，MANAGER_ID列的值必须反过来引用有效的EMPLOYEE_ID。如果员工就是经理本人，则此行将引用自身。

5．CHECK约束

CHECK约束可用来实施简单规则，如列中输入的值必须在一个值域内。规则必须是一个结果为TRUE或FALSE的表达式。规则可以引用作为字面值输入的绝对值，也可以引用同一行中的其他列，也可以使用一些函数。可以根据需要为一个列应用足够多的CHECK约束，但无法使用子查询来计算值是否被允许，也无法使用诸如SYSDATE的函数。

5.7.2 定义约束

可在创建表时定义约束，也可在后期将约束添加到表中。如果在创建表时定义约束，则可以与引用的列一起定义约束，也可以在表定义结束阶段定义约束。使用后一种技术会获得更大的灵活性。例如，如果一起定义约束，将无法定义引用两列的FOREIGN KEY约束，或引用除约束列以外的任意列的CHECK约束；而如果在表定义结束阶段定义约束，则这些都可能完成。

如果在创建表时定义约束，而约束需要索引(UNIQUE或PRIMARY KEY约束)，则索引将与表一起创建。

请考虑下面两个创建表的语句(已为其添加了行号)：

        1    create table dept(
        2    deptno number(2,0) constraint dept_deptno_pk primary key
        3    constraint dept_deptno_ck check (deptno between 10 and 90),
        4    dname  varchar2(20) constraint dept_dname_nn not null
        5    create table emp  (
        6    empno    number(4,0) constraint emp_empno_pk primary key,
        7    ename    varchar2(20) constraint emp_ename_nn not null,
        8    mgr      number (4,0) constraint emp_mgr_fk references emp (empno),
        9    dob      date,
        10    hiredate date,
        11    deptno   number(2,0) constraint emp_deptno_fk references dept(deptno)
        12    on delete set null,
        13    email varchar2(30) constraint emp_email_uk unique,
        14    constraint emp_hiredate_ck check (hiredate >= dob + 365*16),
        15    constraint emp_email_ck
        16    check ((instr(email, '@') > 0) and (instr(email, '.') > 0)));

下面逐行分析这些语句：

(1) 创建的第一个表称为DEPT，每个部门占用一行。

(2) DEPTNO是数值，两位数字，没有小数点。这是表的主键，约束名为DEPT_DEPTNO_PK。

(3) 应用于DEPTNO的第二个约束是CHECK约束，将其数字限制为10～90。此约束名为DEPT_ DEPTNO_CK。

(4) DNAME列是变长字符，包含使其不为空的DEPT_DNAME_NN约束。

(5) 创建的第二个表是EMP，每个员工占用一行。

(6) EMPNO是数值，最多4位数字，没有小数点。EMP_EMPNO_PK约束将此标记为表的主键。

(7) ENAME是变长字符，包含使其不为空的EMP_ENAME_NN约束。

(8) MGR是经理，其本身必须是员工。此列的定义方式与表的主键列EMPNO的定义方式相同。EMP_MGR_FK约束将此列定义为自引用的外键，因此输入的任何值必须引用EMP中已经存在的行(但未使用NOT NULL约束，所以可以保留为空)。

(9) DOB是员工的生日，是日期，不受约束。

(10) HIREDATE是员工雇佣日期，不受约束。起码当前不受约束。

(11) DEPTNO是员工关联的部门。此列的定义方式与DEPT表的主键列DEPTNO的定义方式相同，而EMP_DEPTNO_FK约束实施外键关联。不能将员工分配给不存在的部门。但这可以为空。

(12) EMP_DEPTO_FK约束进一步定义为ON DELETE SET NULL，因此如果删除DEPT中的父行，EMPNO中所有匹配的子行将DEPTNO设置为NULL。

(13) EMAIL是变长字符数据，如果输入就必须是唯一的(但可以保留为空)。

(14) 这定义一个附加的表级约束EMP_HIREDATE_CK。此约束用于检查童工的使用情况，对于雇佣日期不超过出生日期16年的任何行，将予以拒绝。此约束不可以与HIREDATE一起定义，因为此语法不允许在那时引用其他列。

(15) 添加到EMAIL列的附加约束EMP_EMAIL_CK对电子邮件地址进行两个检查。INSTR函数查找“@”和“.”字符(这些总是出现在有效的电子邮件地址中)，如果找不到这两个字符，CHECK条件将返回FALSE，行将被拒绝。

上例显示了在创建表时定义约束的几种可能性。其他可能如下：

● 控制UNIQUE和PRIMARY KEY约束的索引创建。

● 定义在插入时(默认方式)还是在后面(在提交事务时)检查约束。

● 说明此约束真正实施(默认方式)还是被禁用。

可以创建一个无约束的表，此后使用ALTER TABLE命令添加约束。最终结果没有差别，但此技术的使用降低了代码的自我记录性，因为完整的表定义将分散在多个语句中(而并非在一个语句中)。

5.7.3 约束状态

任何时候，每个约束都处于启用或禁用状态，验证或非验证状态。从语法上讲，它们的任何组合都可能出现：

● ENABLE VALIDATE 无法输入违反约束的行，而且表中的所有行都符合约束。

● DISABLE NOVALIDATE 可以输入任何数据(无论是否符合要求)，表中可能已经存在不合乎要求的数据。

● ENABLE NOVALIDATE 表中可以已经存在不合乎要求的数据，但现在输入的所有数据必须符合要求。

● DISABLE VALIDATE 表中所有数据都符合约束，但新行未必如此。约束上的索引也会删除。

理想状况是ENABLE VALIDATE(定义约束时的默认设置)。这将确保所有数据都是有效的，而且不能输入无效数据。另一方面，将大量数据上传到表时，DISABLE NOVALIDATE可能非常有用。正在上传的数据完全有可能不符合业务规则，但为了避免因为几个错误行而导致大型上传的失败，将约束设置为此状态将使上传成功执行。在上传完毕后，立即将约束的状态转换为ENABLE NOVALIDATE。在将约束转换为理想状态之前，这将防止事态的进一步恶化，同时会检查数据的合规性。

例如，考虑下面的脚本，它将实时数据源表中的数据读入归档数据表。假设目标表的列存在NOT NULL约束，而源表上未实施此约束：

        alter table sales_archive modify constraint sa_nn1 disable novalidate;
        insert into sales_archive
        select * from sales_current;
        alter table sales_archive modify constraint sa_nn1 enable novalidate;
        update sales_archive set channel='NOT KNOWN' where channel is null;
        alter table sales_archive modify constraint sa_nn1 enable validate;

5.7.4 检查约束

可以在执行语句时检查约束(IMMEDIATE约束)或提交事务时检查约束(DEFERRED约束)。默认方式下，所有约束都是IMMEDIATE (立即)约束，不能延迟。上例的一个替代方法是将约束创建为DEFERRED(延迟)约束：

        set constraint sa_nn1 deferred;
        insert into sales_archive
        select * from sales_current;
        update sales_archive set channel='NOT KNOWN' where channel is null;
        commit;
        set constraint sa_nn1 immediate;

要使约束成为延迟约束，必须使用适当的方法予以创建：

        alter table sales_archive add constraint sa_nn1
        check (channel is not null) deferrable initially immediate;

如果创建时没这么做，则无法在后来使约束成为延迟约束。在插入或更新行时将默认实施SA_NN1约束，但检查可以延迟到提交事务之时。可延迟约束通常用于外键。如果进程在父表和子表中插入或更新行，而未按正确顺序处理行，那么，在外键约束不延迟的情况下，进程将失败。

在ENABLED/DISABLED和VALIDATE/NOVALIDATE之间更改约束状态的操作会影响所有会话。此状态更改是一个数据字典更新。在IMMEDIATE和DEFERRED之间切换可延迟约束与会话有关，但初始状态将应用于所有会话。

练习5-6 管理约束

在本练习中，定义和调整在练习5-3中创建的表上的一些约束。

(1) 在SQL Developer中，导航至HR模式，并单击CUSTOMERS表。

(2) 通过Constraints选项卡，查看为该表创建的4个NOT NULL约束。注意，它们的名称起不到什么帮助作用，将在步骤(8)中确定名称。

(3) 单击Actions按钮，选择Constraints: Add Primary Key。

(4) 在Add Primary Constraint窗口中，命名约束为PK_CUSTOMER_ID，选择CUSTOMER_ID列，然后单击Apply按钮。

(5) 选择Show SQL选项卡来查看创建约束的语句，然后单击Apply按钮运行该语句。

(6) 使用SQL*Plus，以用户HR的身份连接到数据库。

(7) 运行此查询来查找约束的名称：

        select constraint_name, constraint_type, column_name
        from user_constraints natural join user_cons_columns
        where table_name='CUSTOMERS';

(8) 使用ALTER TABLE命令，基于步骤(7)检索到的最初约束名，将约束重命名为更有意义的名称：

        ALTER TABLE CUSTOMERS RENAME CONSTRAINT old_name TO new_name ;

(9) 向HR模式中添加下列约束：

        alter table orders add constraint pk_order_id primary key(order_id);
        alter table products add constraint pk_product_id primary key(product_id);
        alter table order_items add constraint fk_product_id foreign key(product_id)
          references products(product_id);
        alter table order_items add constraint fk_order_id foreign key(order_id)
          references orders(order_id);
        alter table orders add constraint fk_customer_id foreign key(customer_id)
          references customers(customer_id);

5.8 视图

对于用户来说，视图看起来与表相似：两者都是二维行列结构，并且用户可以在视图上运行SELECT语句和DML语句。程序员知道视图的真相：视图只不过是命名的SELECT语句。任何SELECT语句都返回二维行集。如果将SELECT语句保存为视图，那么每当用户查询或更新视图(给人的印象就是表)中的行时，就会像对待表一样运行语句并向用户显示结果。视图可以基于任何SELECT语句。可以是联接表、执行聚合或进行排序的语句，可以对视图使用在SELECT命令中合法的任何内容。

5.8.1 使用视图的原因

使用视图可能的原因包括：安全性、简化用户SQL语句、防止错误、提高性能和使数据易于理解。表和列名往往太长，而且相当无意义。视图和其中的列则更为一目了然。

1．用来实施安全性的视图

有时可能只应当允许用户看到表的部分行或列。虽然有几种方式可以做到这一点，但是视图往往是最简单的方式。以HR.EMPLOYEES表为例，这个表中包括不应让除人事部门之外的员工看到的个人详细信息。但是财务工作人员需要能够看到成本核算信息。该视图将对数据做客观处理：

        create view hr.emp_fin as
        select hire_date, job_id, salary, commission_pct, department_id from
          hr.employees;

注意，上面对表使用了模式限定符作为数据(常常是指基表或明细表)和视图的来源：视图是模式对象，可以从相同模式或其他模式的表中取得所需的数据。如果没有指定模式，则就是当前模式。

然后可以授予财务工作人员查看视图而不是查看表的权限，并且可以执行如下语句：

        select * from emp_fin where department_id=50;

他们只能看到组成该视图的5列，而看不到EMPLOYEES中其余包含个人信息的列。可以像表一样将该视图与其他表联接或执行聚合操作：

        select department_name, sum(salary) dept_sal
        from departments natural join emp_fin
        group by department_name;

构造良好的视图集可以在数据库中实现完整的安全结构，给予用户访问他们需要查看的数据的权限，而隐藏他们不需要查看的数据。

2．用来简化用户SQL的视图

对用户来说，如果复杂的工作(如联接或聚合)都由定义视图的代码完成，那么用户查询数据时就会轻松很多。在上面的示例中，用户必须编写将EMP_FIN视图联接到DEPARTMENTS表的代码，并计算每个部门的薪水总和。可以在一个视图中完成全部这些工作：

        create view dept_sal as
        select d.department_name, sum(e.salary) dept_sal
        from departments d left outer join employees e on
          d.department_id=e.department_id
        group by department_name order by department_name;

然后用户可以从DEPT_SAL中选择，而不需要知道关于联接的任何情况，甚至不需要知道如何排序结果：

        select * from dept_sal;

特别地，用户不需要知道如何确保列出了所有部门，甚至是没有员工的部门。上一节中的示例未能实现这些功能。

3．用来防止错误的视图

虽然不可能防止用户犯错误，但是构造良好的视图可以防止一些由于不了解应如何解释数据而产生的错误。上一节已经通过构造一个列出所有部门(不管这些部门目前是否有员工)的视图介绍了此概念。

视图有助于按照没有歧义的方式提供数据。例如，很多应用程序永远不会真正删除行。分析下面这个表：

        create table emp  ( empno number constraint emp_empno_pk primary key,
        ename  varchar2(10), deptno number, active varchar2(1) default 'Y');

列ACTIVE是一个标志，表示该员工当前被雇用，当插入一行时，这个标志的默认值为'Y'。当用户通过用户界面“删除”员工时，底层SQL语句将更新为把ACTIVE设置为’N'。如果不了解这一点的用户对表进行查询时，他们可能会严重曲解结果。因此，授予他们对视图的访问权限往往会更好：

        create view current_staff as select * from emp where active='Y';

访问这个视图的查询不可能看到“已删除的”员工成员。

4．使数据易于理解的视图

数据库中的数据结构是规范化表。期望用户理解规范化结构是不合理的要求。以Oracle E-Business套件为例，Accounts Receivable模块中的customer实际上是一个整体，由分布在HZ_ PARTIES、HZ_PARTY_SITES及HZ_CUST_ACCTS_ALL等表中的信息组成。所有这些表用主键到外键的关系连接，但是这些关系没有在任何对用户可见的标识符(如客户编号)上定义：它们基于用户永远见不到的列，其中包含按照顺序在内部生成的值。用来检索客户信息的表单和报表永远不会直接访问这些表，它们都是通过视图工作的。

视图除了可以通过易于理解的形式向用户提供数据之外，也提供用户看到的对象和存储在数据库中的对象之间的抽象层，对于维护工作相当有价值。视图允许在不需要重新编码应用程序的情况下重新设计数据结构。如果修改了表，那么调整视图定义可能对SQL和PL/SQL代码造成一些不必要的改动。视图是使应用程序能够在不同数据库之间移植的重要技术。

5．用来提升性能的视图

程序员可以优化视图背后的SELECT语句，这样用户就不需要关心代码的调整。得到同一个结果可能有很多方式，但是有些技术也许比其他技术慢很多。例如，当联接两个表时，通常可以选择嵌套循环联接，也可以选择哈希联接。嵌套循环联接使用索引找到单个行；哈希联接将整个表读入内存中。在这两种方法之间如何选择取决于数据的状态和可用的硬件资源。

从理论上讲，人们总是能依靠Oracle优化器来找出运行SQL语句的最佳方式，但是有时也会出错。如果程序员知道哪种技术最好，可以通知优化器采用这种技术。本例强制使用哈希技术：

        create view dept_emp as
        select /*+USE_HASH (employees departments)*/ department_name, last_name
        from departments natural join employees;

每当用户查询DEPT_EMP视图时，都会通过将明细表扫描到内存中来进行联接。用户不需要知道强制使用这种联接方法的语法。也不需要知道这种语法：它不在OCP SQL的考试范围之内，但是应该知道使用视图设计进行调整的概念。

5.8.2 简单视图和复杂视图

出于实用的目的，简单视图和复杂视图的划分与是否能对视图执行DML语句有关：简单视图通常能接受DML语句，复杂视图则不能。严格的定义如下所示：

● 简单视图从明细表中取得数据，不使用函数，不进行聚合。

● 复杂视图可以联接明细表，使用函数，进行聚合。

对上一节中作为示例的4个视图应用这些定义可知，第一个和第三个视图是简单视图，第二个和第四个视图是复杂视图。

一般不能对复杂视图执行INSERT、UPDATE或DELETE命令。视图中的行对明细表中的行进行反向映射，但是这种映射并不总是能够一对一地建立，而这对于DML操作是必需的。一般来说可以对简单视图执行DML，但并非总是如此。例如，如果视图不包括具有NOT NULL约束的列，那么通过视图进行INSERT操作就不会成功(除非该列有默认值)。这样会产生令人不安的影响，因为错误消息会引用语句中没有提到的表和列，如图5-11中的第一个示例所示。

图5-10中的第一个视图RNAME_V确实符合简单视图的定义，但是不能通过该视图执行INSERT操作，因为这个视图缺少强制列。第二个视图RUPPERNAME_V是复杂视图，因为它包括一个函数。这使得INSERT无法执行，因为数据库无法计算出到底应插入什么数据：它不能以确定性的方式反向工程UPPER函数的效果。但是DELETE操作成功执行，因为这个操作不依赖于该函数。

5.8.3 创建、更改和删除视图

创建视图的语法如下：

        CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW
        [schema.]viewname [(alias [, alias]...)]
        AS subquery
        [WITH CHECK OPTION [CONSTRAINT constraintname]]
        [WITH READ ONLY [CONSTRAINT constraintname]] ;

注意，视图是模式对象。没有理由不允许一名用户拥有的视图引用另一名用户拥有的明细表。默认情况下，在当前模式中创建视图。可选关键字如下所示，到目前为止的示例中还没有用到这些关键字：

● OR REPLACE 如果视图已经存在，则在创建之前删除它。

● FORCE或NOFORCE 即使子查询中不存在明细表，FORCE关键字也会创建视图。NOFORCE是默认值，如果明细表不存在，则引发一个错误。

● WITH CHECK OPTION 这是用来处理DML的选项。如果子查询包括WHERE子句，那么这个选项会防止插入视图中不可见的行，或者防止导致行从视图中消失的更新。默认情况下不启用这个选项，它会产生令人不安的结果。

● WITH READ ONLY防止任何DML通过视图。

● CONSTRAINT constraintname 这个选项可以用来命名WITH CHECK OPTION和WITH READ ONLY限制，这样，当这些限制导致语句失败时，出现的错误消息更易于理解。

此外，可以为视图的列名提供一组别名。如果没有提供别名，列就会根据表的列命名，或者用子查询中指定的别名来命名。

ALTER VIEW命令的主要用途是编译视图，在成功地编译视图后才能使用该命令。在创建视图时，Oracle会检查该视图所基于的明细表和必要的列是否存在。如果明细表和必要的列不存在，编译就会失败，也不会创建视图——除非使用FORCE选项。如果使用了FORCE选项，则会创建视图，但是直到创建视图引用的表或列时才能成功地编译该视图。当查询无效视图时，Oracle会试图自动编译该视图。如果因为修复问题而成功地编译了视图，那么用户不会知道曾经存在问题，只是用户的查询会比平常花费的时间稍微长一些。一般而言，应当手动编译视图来确保成功编译，而不是让用户发现错误。

创建视图的列定义后，不能使用修改表列的方式调整视图的列。要调整视图的列，必须先删除视图再重新创建。DROP命令如下：

        DROP VIEW [schema.]viewname ;

在CREATE VIEW命令中使用OR REPLACE关键字，则会在创建视图前自动删除该视图(如果它已经存在)。

练习5-7 创建视图

在此练习中，使用处于HR模式的数据创建一些简单视图和复杂视图。可以使用SQL*Plus，也可以使用SQL Developer。

(1) 作为用户HR连接到数据库。

(2) 对EMPLOYEES和DEPARTMENT表创建视图，删除其中的所有个人信息：

        create view emp_anon_v as
        select hire_date, job_id, salary, commission_pct, department_id from employees;
        create view dept_anon_v as
        select department_id, department_name, location_id from departments;

(3) 创建联接与聚合这两个简单视图的复杂视图。注意，可以在视图的基础上再创建视图。

        create view dep_sum_v as
        select e.department_id, count(1) staff, sum(e.salary) salaries,
        d.department_name from emp_anon_v e join dept_anon_v d
        on e.department_id=d.department_id
        group by e.department_id, d.department_name;

(4) 通过查询确认视图有效。