第三章：索引与数据完整性_oracle 索引排序-优快云博客

②位图索引主要用来节省空间，减少Oracle对数据块的访问。它采用位图偏移方式来与表的行ID号对应。采用位图索引一般是在重复值太多的表字段情况下。位图索引在实际密集型OLTP（数据事务处理）中用得比较少，因为OLTP会对表进行大量的删除、修改和新建操作，Oracle每次进行操作都会对要操作的数据块加锁，所以多人操作时很容易产生数据块锁、等待、甚至死锁现象。在OLAP（数据分析处理）中应用位图索引有优势，因为OLAP中大部分是对数据库的查询操作，而且一般采用数据仓库技术，所以大量数据采用位图索引时节省空间比较明显。当创建表的命令中包含有唯一性关键字时，不能创建位图索引。创建全局分区索引时也不能选用位图索引。

（2）索引按功能和索引对象可分为以下六种类型。

唯一索引。唯一索引意味着不会有两行记录相同的索引键值。唯一索引表中的记录没有RowID，所以不能再对其建立其他索引。在Oracle 11g中，要建立唯一索引，必须在表中设置主关键字，建立了唯一索引的表只按照该唯一索引结构排序。
非唯一索引。不对索引列的值进行唯一性限制的称为非唯一索引。
分区索引。所谓分区索引是指索引可以分散地存在于多个不同的表空间中，其优点是可以提高数据查询的效率。
未排序索引。未排序索引也称为正向索引。由于Oracle 11g数据库中的行是按升序排序的，因此创建索引时不必指定对其排序而使用默认的顺序。
逆序索引。逆序索引也称为反向索引。该索引同样保持索引列按顺序排列，但是颠倒已索引的每列的字节。
基于函数的索引。基于函数的索引是指索引中的一列或者多列是一个函数或者表达式，索引根据函数或者表达式计算索引列的值。可以将基于函数的索引创建成为位图索引。

（二）使用索引的原则

1、在表中插入数据后创建索引。在表中插入数据后，创建索引效率将更高。因为如果在装载数据之前创建索引，那么插入每行时Oracle都必须更改索引。

2、索引正确的表和列。如果经常检索的内容仅为包含大量数据的表中少于15%的行，就需要创建索引。为了改善多个表的相互关系，常常使用索引列进行关系连接。

3、合理安排索引列。在CREATE INDEX语句中，列的排序会影响查询的性能，通常将最常用的列放在前面。创建一个索引来提高多列查询时，应该清楚地了解这个多列索引对什么列的存取有效、对什么列的存取无效。

注意：主键和唯一关键字所在的列可以自动建立索引，但应该在与之关联的外部关键字所在的列上创建索引

例如，当在A、B、C三列上创建索引时，实际得到的顺序如下：

A、B

A、B、C

所以，可以获得A列的索引、A和B列结合的索引以及A、B、C三列结合的索引。不能得到的顺序如下：

B、C

4、限制表中索引的数量。尽管表可以有任意数量的索引，但是索引越多，在修改表中的数据时对索引做出相应更改的工作量也越大，效率也就越低。因此，应及时删除目前不用的索引。

5、指定索引数据块空间。创建索引时，索引的数据块是用表中现存的值填充的，直到达到PCTREE为止。因此，如果打算将许多行插入到被索引的表中，PCTREE就应设置得大一点。不能给索引指定PCTUSED。

6、根据索引大小设置存储参数。创建索引之前应先估计索引的大小，以便更好地规划和管理磁盘空间。单个索引项的最大值大约是数据块大小的一半。

（三）创建索引

1、以界面方式创建索引

以在XSB表的“姓名”列创建索引为例，使用SQL Developer创建索引的操作过程如下。

（1）启动SQL Developer，展开连接myorcl，右击要创建索引的XSB表，选择“索引”菜单下的“创建索引”子菜单项，如图所示。

（2）在弹出的“创建索引”窗口中创建索引，如图所示。

（3）所有选项设置完后单击“确定”按钮完成索引的创建，索引创建完后单击XSB表，在“索引”选项页可以看到新创建的索引XSB_NAME_INDEX，如图所示。

2、以命令方式创建索引

使用SQL命令可以灵活方便地创建索引。在使用SQL命令创建索引时，必须满足下列条件之一。

语法格式为：

CREATE [UNIQUE∣BITMAP] INDEX     			/*索引类型*/
		[<用户方案名>.]<索引名> 
	ON  <表名>(<列名> | <列名表达式> [ASC∣DESC] [,…n])
[LOGGING | NOLOGGING]				/*指定是否创建相应的日志记录*/
[COMPUTE STATISTICS]				/*生成统计信息*/
[COMPAESS | NOCOMPRESS]				/*对复合索引进行压缩*/
[TABLESPACE <表空间名>]            			/*索引所属表空间*/
[SORT | NOSORT]					/*指定是否对表进行排序*/
[REVERSE]

说明：

UNIQUE：指定索引所基于的列（或多列）值必须唯一。默认的索引是非唯一索引。Oracle建议不要在表上显式定义UNIQUE索引。
BITMAP：指定创建位图索引而不是B*索引。位图索引保存的行标识符与作为位映射的键值有关。位映射中的每一位都对应于一个可能的行标识符，位设置意味着具有对应行标识符的行包含该键值。
<用户方案名>：表示包含索引的方案。若忽略则Oracle在自己的方案中创建索引。
ON子句：在指定表的列中创建索引，ASC和DESC分别表示升序索引和降序索引。
<列名表达式>：用指定表的列、常数、SQL函数和自定义函数创建的表达式，用于创建基于函数的索引。指定列名表达式以后用基于函数的索引查询时，必须保证查询该列名表达式不为空。
LOLOGGING | NOLOGGING：LOGGING选项规定在创建索引时，创建相应的日志记录，NOGGING选项则表示创建索引时不产生重做日志信息，默认为LOGGING。
COMPUTE STATISTICS：COMPUTE STATISTICS选项表示在创建索引时直接生成索引的统计信息，这样可以避免以后对索引进行分析操作。
COMPAESS | NOCOMPRESS：对于复合索引，如果指定了COMPRESS选项，则可以在创建索引时对重复的索引值进行压缩，以节省索引的存储空间，但对索引进行压缩后将会影响索引的使用效率，默认为NOCOMPRESS。
SORT | NOSORT：默认情况下，Oracle在创建索引时会对表中的记录进行排序，如果表中的记录已经按照顺序排序，可以指定NOSORT选项，这样可以省略创建索引时对表进行的排序操作，加快索引的创建速度。但若索引列或多列的行不按顺序保存，Oracle就会返回错误，默认为SORT。
REVERSE：指定以反序索引块的字节，不包含行标识符。NOSORT不能与该选项一起指定。

【例】为KCB表的课程名列创建索引。

CREATE INDEX kcb_name_idx
	ON KCB(课程名);

【例】根据XSB表的姓名列和出生时间列创建复合索引。

CREATE INDEX XSB_ind
	ON XSB(姓名, 出生时间);

（四）维护索引

创建索引之后，还需要经常进行修改维护。索引的修改维护操作包括改变索引的物理和存储特征值、为索引添加空间、收回索引所占的空间、重新创建索引等。

1、界面方式维护索引

右击XSB表选择“编辑”菜单项，在“编辑表”窗口左边选择“索引”选项，在中间“索引”框中选中要维护的索引，在右边“索引属性”组中修改索引的信息，单击“确定”按钮后完成修改，如图所示。

2、以命令方式创建索引

使用ALTER INDEX命令维护索引必须在操作者自己的模式中，或者操作者拥有ALTER ANY INDEX系统权限。ALTER INDEX语句的语法格式为：

ALTER INDEX [<用户方案名>.]<索引名>
[LOGGING | NOLOGGING]
[TABLESPACE <表空间名>]
[SORT | NOSORT]
[REVERSE]
[RENAME TO <新索引名>]

说明：RENAME TO子句用于修改索引的名称，其余选项与CREATE INDEX语句中相同。

【例】重命名索引kcb_name_idx。

ALTER INDEX kcb_name_idx
	RENAME TO kcb_idx;

（五）删除索引

1．以界面方式删除索引

在SQL Developer中，右击索引所在的表，选择“索引”菜单项的“删除”子菜单项，在弹出的“删除”窗口中选择要删除的索引，如图所示，单击“应用”按钮即可。

2．以命令方式删除索引

语法格式为：

DROP INDEX [<用户方案名>.]<索引名>

【例】删除XSCJ数据库中XSB表的复合索引XSB_ind。

DROP INDEX XSB_ind;

二、数据完整性

Oracle使用完整性约束防止不合法的数据进入到基表中。管理员和开发人员可以定义完整性规则，以增强商业规则，限制数据表中的数据。如果一个DML语句执行的任何结果破坏了完整性约束，Oracle就会回滚语句，返回错误信息。

例如，假设在KCB表的“开课学期”列上定义了完整性约束，要求该列的值只能是1～8。如果INSERT或者UPDATE语句向该列插入大于8的值，Oracle将回滚语句，并返回错误信息。使用完整性约束有以下几个好处。

在定义完整性约束时，一般使用SQL语句。当定义和修改完整性约束时，不需要额外编程。SQL语句很容易编写，可减少编程错误，Oracle能够控制其功能。为此，推荐使用应用代码和数据库触发器声明完整性约束，这比使用存储过程声明要好。

完整性规则定义在表上，存储在数据字典中。应用程序的任何数据都必须满足表的相同的完整性约束。通过将商业规则从应用代码中移到中心完整性约束，数据库的表能够保证存储合法的数据，并需要了解数据库应用是如何操纵信息的。存储过程不能提供表的中心规则。数据库触发器可以提供这些好处，但是实现的复杂性远远大于完整性约束。

如果通过完整性约束增强的商业规则改变了，管理员只需修改完整性约束，所有的应用就会自动与修改后的约束保持一致。相反，如果使用应用增强商业规则，开发人员则需要修改所有代码，并重新编译、调试和测试修改后的应用程序。
Oracle 系统将每个完整性约束的特定信息存储在数据字典中，可以使用这些信息设计数据库应用，为完整性约束提供当前的用户反馈。

（一）数据完整性的分类

数据完整性就是指数据库中的数据在逻辑上的一致性和准确性。一般情况下，可以把数据完整性分成三种类型：域完整性、实体完整性和参照完整性。

1．域完整性

域完整性又称为列完整性，指定一个数据集对某一个列是否有效和确定是否允许空值。域完整性通常是使用有效性检查来实现的，还可以通过限制数据类型、格式或者可能的取值范围来实现。例如，对于数据库XSCJ的KCB表，课程的学分应在0～10之间，为了对学分这一数据项输入的数据范围进行限制，可以在定义KCB表的同时定义学分列的约束条件以达到这一目的。

2．实体完整性

实体完整性也可以称为行完整性，要求表中的每一行有一个唯一的标识符，这个标识符就是主关键字。例如，居民身份证号是唯一的，这样才能唯一地确定某一个人。通过索引、UNIQUE 约束、PRIMARY KEY 约束可实现数据的实体完整性。例如，对于XSCJ数据库中的XSB表，“学号”作为主关键字，每一个学生的学号能唯一地标识该学生对应的行记录信息，那么在输入数据时，就不能有相同学号的行记录，通过对“学号”这一字段建立主键约束可实现XSB表的实体完整性。

3．参照完整性

参照完整性又称引用完整性，它保证主表与从表（被参照表）中数据的一致性。在Oracle中，参照完整性的实现是通过定义外键（FOREIGN KEY）与主键（PRIMARY KEY）之间的对应关系实现的。如果在被引用表中的一行被某外关键字引用，那么这一行既不能被删除，也不能修改主关键字。参照完整性确保键值在所有表中一致。主键是指在表中能唯一标识表的每个数据行的一个或多个表列。外键是指如果一个表中的一个或若干个字段的组合是另一个表的主键，则称该字段或字段组合为该表的外键。

例如，对于XSCJ数据库中XSB表的每一个学号，在CJB表中都有相关的课程成绩记录，将XSB作为主表，“学号”字段定义为主键，CJB作为从表，表中的“学号”字段定义为外键，从而建立主表和从表之间的联系实现参照完整性。XSB和CJB表的对应关系如表5.1和表5.2所示。

一旦定义了两个表之间的参照完整性，则有如下要求。

完整性约束是通过限制列数据、行数据和表之间数据来保证数据完整性的有效方法。约束是保证数据完整性的标准方法。每一种数据完整性类型都可以由不同的约束类型来保障。约束确保有效的数据输入到列中并维护表与表之间的关系。下表描述了不同类型的完整性约束。

4．完整性约束的状态

在Oracle中，完整性约束有四种状态。

（二）域完整性的实现

Oracle 系统可以通过 CHECK约束实现域完整性。CHECK 约束实际上是字段输入内容的验证规则，表示这个字段的输入内容必须满足 CHECK 约束的条件；若不满足则数据无法正常输入。

1．以界面方式操作CHECK约束

在XSCJ数据库的CJB表中，学生每门功课的成绩在0～100的范围内，如果对用户的输入数据要施加这一限制，需要创建CHECK约束，操作过程如下。右击CJB表，选择“约束条件”菜单项下的“添加检查”子菜单项，如图5.9所示，弹出“添加检查”窗口，在“约束条件名称”栏中输入约束名CH_CJ，在“检查条件”栏中输入CHECK约束的条件“成绩>=0 AND 成绩<=100”，完成后单击“应用”按钮完成CHECK约束的创建。

如果要修改或删除已经创建的CHECK约束，右击CJB表选择“编辑”菜单项，进入“检查约束条件”选项页面中，如图所示。

2．以命令方式操作CHECK约束

（1）在创建表时创建约束

语法格式为：

CREATE TABLE <表名>
(	<列名> <数据类型> [DEFAULT <默认值>] [NOT NULL | NULL]
		[CONSTRAINT <CHECK约束名>] CHECK(<CHECK约束表达式>) 	/*定义为列的约束*/
	[,…n]
	[CONSTRAINT <CHECK约束名>] CHECK(<CHECK约束表达式>)	              /*定义为表的约束*/
)

说明：CONSTRAINT关键字用于为CHECK约束定义一个名称，如果省略则系统自动为其定义一个名称。CHECK表示定义CHECK约束，其后表达式为逻辑表达式，称为CHECK约束表达式。如果直接在某列的定义后面定义CHECK约束，则CHECK约束表达式中只能引用该列，不能引用其他列。如果需要引用不同的列，则必须在所有的列定义完之后再定义CHECK约束。

【例】定义KCB2表，同时定义其“学分”列的约束条件。

CREATE TABLE KCB2
( 	
	课程号 		char(3)	 	NOT NULL,
	课程名	 	char(16) 		NOT NULL,
	开课学期 		number(1) 	NULL,
	学时 		number(2) 	NULL, 
	学分 		number(1)		CHECK (学分>=0 AND 学分<=10) NOT NULL
								/*定义为列的约束*/
);

【例】在XSCJ数据库中创建books表，其中包含所有的约束定义。

CREATE TABLE books
( 	
	book_id 		number(10),
	book_name 	varchar2(50) 	NOT NULL,
	book_desc 	varchar2(50) 	DEFAULT 'New book',
	max_lvl 		number(3,2) 	NOT NULL,
	trade_price 	number(4,1) 	NOT NULL,
	CONSTRAINT ch_cost CHECK(max_lvl<=250)		/*定义为表的约束*/
);

（2）在修改表时创建约束

语法格式为：

ALTER TABLE <表名>
	ADD( CONSTRAINT <CHECK约束名> CHECK(<CHECK约束表达式>))

说明：ADD CONSTRAINT表示在已定义的表中增加一个约束定义。

【例】通过修改XSCJ数据库的books表，增加trade_price（批发价）字段的CHECK约束。

ALTER TABLE books
	ADD(CONSTRAINT ch_price CHECK(trade_price<=250));

（3）删除约束

语法格式为：

ALTER TABLE <表名>
	DROP CONSTRAINT <CHECK约束名>

说明：该语句在指定的表中，删除名为指定名称的CHECK约束。

【例】删除XSCJ数据库的books表中“批发价”字段的CHECK约束。

ALTER TABLE books
	DROP CONSTRAINT ch_price;

（三）实体完整性的实现

一个表只能有一个PRIMARY KEY（主键）约束，而且PRIMARY KEY约束中的列不能取空值。由于PRIMARY KEY约束能确保数据的唯一，所以经常用来定义标识列。当为表定义PRIMARY KEY约束时，Oracle 11g为主键列创建唯一索引，实现数据的唯一性，在查询中使用主键时，该索引可用来对数据进行快速访问。如果PRIMARY KEY约束是由多列组合定义的，则某一列的值可以重复，但PRIMARY KEY约束定义中所有列的组合值必须唯一。

如果要确保一个表中的非主键列不输入重复值，应在该列上定义唯一约束（UNIQUE约束）。例如，对于XSCJ数据库的XSB表中“学号”列是主键，若在XSB表中增加一列“身份证号码”，可以定义一个唯一约束来要求表中“身份证号码”列的取值也是唯一的。

PRIMARY KEY约束与UNIQUE约束的主要区别如下。

（1）一个表只能创建一个PRIMARY KEY约束，但可根据需要对不同的列创建若干个UNIQUE约束。

（2）PRIMARY KEY字段的值不允许为NULL，而UNIQUE字段的值可取NULL。

PRIMARY KEY约束与UNIQUE约束的相同点是：两者均不允许表中对应字段存在重复值；在创建PRIMARY KEY约束与UNIQUE约束时会自动产生索引。

对于PRIMARY KEY约束与UNIQUE约束来说，都是由索引强制实现的。在实现PRIMARY KEY约束与UNIQUE约束时，Oracle按照下面过程来实现。

1．以界面方式操作主键及唯一约束

使用SQL Developer创建和删除PRIMARY KEY约束方法为：右击要创建约束的表，选择“编辑”菜单项，在“编辑表”窗口中单击“主键”选项，在右边页面中选择要添加或删除的主键列，如图5.14所示。

2．命令方式操作主键及唯一约束

（1）创建表的同时创建约束

语法格式为：

CREATE TABLE <表名>             				/*指定表名*/
	(<列名> <数据类型> [NULL |NOT NULL] 	      	/*定义字段*/
		{[CONSTRAINT <约束名>]       		  /*定义约束名*/
		  PRIMARY KEY | UNIQUE  }            		/*定义约束类型*/
		[,…n]
	[, [CONSTRAINT <约束名>] {PRIMARY KEY | UNIQUE}(<列名>,[,…n]) ]  
]
)

在上面语法中，通过关键字PRIMARY KEY、UNIQUE指定所创建的约束类型。可以在某一列的后面定义该列为PRIMARY KEY或UNQUE约束，也可以在所有的列定义完以后定义PRIMARY KEY或UNIQUE约束，但需要提供要定义约束的列或组合。

【例】对XSCJ数据库的XSB2表中的“学号”字段创建PRIMARY KEY约束，对“身份证号”字段定义UNIQUE约束。

CREATE TABLE XSB2
(
	学号 	char(6) 		NOT NULL CONSTRAINT PK_XH PRIMARY KEY,
	姓名 	char(8) 		NOT NULL,
	身份证号 char(20) 	 NOT NULL CONSTRAINT UN_ID UNIQUE,
	性别 	char(2) 		DEFAULT '1' NOT NULL,
	出生时间 date 		NOT NULL,
	专业 	char(12) 		NULL,
	总学分 	number(2) 	NULL,
	备注 	varchar2(200) 	NULL
);

（2）通过修改表来创建约束

语法格式为：

ALTER TABLE <表名>
	ADD([CONSTRAINT <约束名>] {PRIMARY KEY | UNIQUE} (<列名>[,…n])

说明：ADD CONSTRAINT表示对指定表增加一个约束，约束类型为PRIMARY KEY或UNIQE。索引字段可包含一列或多列。

【例】先在XSCJ数据库中创建XSB3表，然后通过修改表，对“学号”字段创建PRIMARY KEY约束，对“身份证号”字段创建UNIQUE约束。

CREATE TABLE XSB3
(
	 学号 	char(6) 	  NOT NULL,
	 姓名 	char(8) 	  NOT NULL,
	 身份证号 char(20) 	  NOT NULL,
	 性别 	char(2) 	  DEFAULT '1' NOT NULL,
	 出生时间 date 	  NOT NULL,
	 专业 	char(12) 	  NULL,
	 总学分 	number(2)   NULL,
	 备注 	varchar2(200) NULL
);
ALTER TABLE XSB3
	 ADD(PRIMARY KEY(学号));
ALTER TABLE XSB3
	ADD(CONSTRAINT UN_XS UNIQUE(身份证号));

（3）删除约束

删除PRIMARY KEY或UNIQUE约束主要通过ALTER TABLE语句的DROP子句进行。

语法格式为：

ALTER TABLE <表名>
	 DROP CONSTRAINT <约束名>[,…n];

【例5.11】删除XSB3的UN_XS约束。

ALTER TABLE XSB3
	 DROP CONSTRAINT UN_XS;

（四）参照完整性的实现

对两个相关联的表（主表与从表）进行数据插入和删除时，通过参照完整性保证它们之间数据的一致性。利用FOREIGN KEY定义从表的外键，PRIMARY KEY约束定义主表中的主键（不允许为空），可实现主表与从表之间的参照完整性。定义表间参照关系，可先定义主键，再对从表定义外键约束（根据查询的需要可先对从表的该列创建索引）。对于FOREIGN KEY约束来说，在创建时应该考虑以下因素。

（1）在删除主表之前，必须删除FOREIGN KEY约束。

（2）如果不删除或禁止FOREIGN KEY约束，则不能删除主表。

（3）在删除包含主表的表空间之前，必须先删除FOREIGN KEY约束。

1．以界面方式操作表间的参照关系

例如，要建立XSB表和CJB表之间的参照完整性，操作步骤如下。选择CJB表，右击鼠标，选择“编辑”菜单项，在“编辑表”窗口中选择“外键”选项，如图所示。

2．以命令方式操作表间的参照关系

前面已介绍了创建主键（PRMARY KEY约束）的方法，在此将介绍通过SQL命令创建外键的方法。

（1）在创建表的同时定义外键约束

语法格式为：

CREATE TABLE <从表名>
( 	 <列定义> [ CONSTRAINT <约束名> ] REFERENCES <主表名>[ ( <列名> [ ,...n ] ) ]
 	  [,…n]
	 [ [ CONSTRAINT <约束名> ] [ FOREIGN KEY ( <列名> [,...n ] ) [<参照表达式>]]
);

其中：

<参照表达式>::=
	 REFERENCES <主表名>[ ( <列名> [ ,...n ] ) ] 
	 [ ON DELETE { CASCADE | SET NULL } ]

定义外键时还可以指定如下两种参照动作：ON DELETE CASCADE表示从主表删除数据时自动删除从表中匹配的行；ON DELETE SET NULL表示当从主表删除数据时，设置从表中与之对应的外键列为NULL。如果没有指定动作，则删除主表数据时如果违反外键约束，操作将被禁止。

【例】创建stu表，要求表中所有的学生学号都必须出现在XSB表中。

CREATE TABLE stu
(
	 学号 	char(6)  	NOT NULL REFERENCES XSB (学号),
	 姓名 	char(8) 	NOT NULL,
	 出生时间 date 	NULL
);

【例】创建point表，要求表中所有的学号、课程号组合都必须出现在CJB表中，并且当删除CJB表中的记录时同时删除point表中与主键对应的记录。

CREATE TABLE point
(
	 学号 	char(6)  	 NOT NULL,
	 课程号 	char(3) 	 NOT NULL,
 	 成绩 	number(2)NULL,
	 CONSTRAINT FK_point FOREIGN KEY (学号,课程号) REFERENCES CJB (学号,课程号)
		ON DELETE CASCADE
);

2）通过修改表定义外键约束

语法格式为：

ALTER TABLE <表名>
	ADD CONSTRAINT <约束名>
		FOREIGN KEY( <列名>[,…n])
		REFERENCES <主表名>(<列名>[,…n]) <参照表达式>

该语句中的语法选项意义与之前使用CREATE TABLE语句定义外键约束的语法格式相同。

【例】假设XSCJ数据库中的KCB表为主表，KCB的“课程号”字段已定义为主键。CJB表为从表，如下示例用于将CJB的“课程号”字段定义为外键。

ALTER TABLE CJB
	ADD CONSTRAINT FK_KC FOREIGN KEY(课程号)
		REFERENCES KCB(课程号);

3）删除表间的参照关系

删除表间的参照关系，实际上删除从表的外键约束即可。语法格式与前面其他约束删除的格式相同。

【例】删除以上对“CJB.课程号”字段定义的FK_KC外键约束。

ALTER TABLE CJB
	DROP CONSTRAINT FK_KC;

最后，感谢大家的观看！Oracle网络数据库