数据库索引规则Word下载.docx

数据库索引规则Word下载.docx

《数据库索引规则Word下载.docx》由会员分享，可在线阅读，更多相关《数据库索引规则Word下载.docx（12页珍藏版）》请在冰豆网上搜索。

另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；

相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。

复合索引优化和适用范围

索引可以包含一个、两个或更多个列。

两个或更多个列上的索引被称作复合索引。

例如，以下语句创建一个具有两列的复合索引：

CREATEINDEXname

ONemployee（emp_lname,emp_fname）

如果第一列不能单独提供较高的选择性，复合索引将会非常有用。

例如，当许多雇员具有相同的姓氏时，emp_lname和emp_fname上的复合索引非常有用。

因为每个雇员都有唯一的ID，所以emp_id和emp_lname上的复合索引可能没有用处，因此列emp_lname不会提供任何附加选择性。

利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引不同于使用两个单独的索引。

复合索引的结构与电话簿类似，它首先按姓氏对雇员进行排序，然后按名字对所有姓氏相同的雇员进行排序。

如果您知道姓氏，电话簿将非常有用，如果您知道名字和姓氏，电话簿则更为有用，但如果您只知道名字而不知道姓氏，电话簿将没有用处。

压缩的B树索引方法可显著提高复合索引的性能。

列顺序

在创建复合索引时，应该仔细考虑列的顺序。

对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；

仅对后面的任意列执行搜索时，复合索引则没有用处。

如果您很可能仅对一个列多次执行搜索，则该列应该是复合索引中的第一列。

如果您很可能对一个两列索引中的两个列执行单独的搜索，则应该创建另一个仅包含第二列的索引。

包含多个列的主键始终会自动以复合索引的形式创建索引，其列的顺序是它们在表定义中出现的顺序，而不是在主键定义中指定的顺序。

您应该考虑将通过主键来执行的搜索，以确定哪一列应该排在最前面。

在后面的任何被频繁搜索的主键列上，应该考虑添加额外的索引。

例如，假设您在两个列上创建一个复合索引。

一个列包含雇员的名字，另一个列包含雇员的姓氏。

您可以创建一个先包含名字后包含姓氏的索引。

或者，您也可以创建一个先包含姓氏后包含名字的索引。

虽然这两个索引以两个列组织信息，但它们具有不同的功能。

CREATEINDEXfname_lname

ONemployeeemp_fname,emp_lname;

CREATEINDEXlname_fname

ONemployeeemp_lname,emp_fname;

假设您需要搜索名字John。

唯一有用的索引是在索引的第一列包含名字的索引。

由于名字为John的雇员会出现在索引中的任意位置，因此先按姓氏再按名字组织的索引没有用处。

如果您认为很可能需要仅按名字或仅按姓氏查找雇员，则应该创建这两个索引。

或者，您也可以创建两个索引，一个索引仅包含一个列。

但是，请注意，AdaptiveServerAnywhere在处理单个查询时只使用一个索引来对任何一个表进行访问。

即使您知道名字和姓氏，AdaptiveServerAnywhere也可能需要读取额外的行，以查找包含正确姓氏的行。

当您使用CREATEINDEX命令创建索引时（如上例所示），列会按命令中所示的顺序创建。

主键索引和列顺序

列在主键索引中的顺序被强制为与列在表定义中出现的顺序相同，这与PRIMARYKEY约束中指定的列顺序无关。

此外，AdaptiveServerAnywhere还将强制一个附加约束：

表的主键列必须位于每个行的开头。

因此，如果主键被添加到现有表中，服务器就可以重写整个表以确保键列位于每个行的开头。

在多个列出现在主键中的情况下，您应该考虑所需的搜索类型。

如果合适，应切换列在表定义中的顺序，使最常搜索的列排在最前面，或者根据需要为其它列创建单独的索引。

复合索引和ORDERBY

缺省情况下，索引的列按升序排列，但您可以选择通过在CREATEINDEX语句中指定DESC来将这些列按降序排列。

只要ORDERBY子句仅包含索引中的列，AdaptiveServerAnywhere就可以选择使用索引来优化ORDERBY查询。

此外，索引列的排序方式必须与ORDERBY子句完全相同或完全相反。

对于单列索引，这种排序方式始终会使查询可以得到优化，但复合索引则需要稍微多考虑一些问题。

下表显示了一个两列索引的可能性。

索引列可优化的ORDERBY查询不可优化的ORDERBY查询

ASC、ASCASC、ASC或DESC、DESCASC、DESC或DESC、ASC

ASC、DESCASC、DESC或DESC、ASCASC、ASC或DESC、DESC

DESC、ASCDESC、ASC或ASC、DESCASC、ASC或DESC、DESC

DESC、DESCDESC、DESC或ASC、ASCASC、DESC或DESC、ASC

含有两个以上的列的索引遵循与上述规则相同的一般规则。

例如，假设您具有以下索引：

CREATEINDEXidx_example

ONtable1（col1ASC,col2DESC,col3ASC）

在这种情况下，以下查询可以得到优化：

SELECTcol1,col2,col3fromtable1

ORDERBYcol1ASC,col2DESC,col3ASC

和

SELECTcol1,col2,col3fromexample

ORDERBYcol1DESC,col2ASC,col3DESC

索引不用于优化在ORDERBY子句中具有ASC和DESC的其它任何模式的查询。

例如：

ORDERBYcol1ASC,col2ASC,col3ASC

不会得到优化。

Oracle数据库的完整性约束规则详解

添加时间:

2007-4-22　

完整性约束用于增强数据的完整性，Oracle提供了5种完整性约束：

　　　Check

　　　NOTNULL

　　　Unique

　　　Primary

　　　Foreignkey

完整性约束是一种规则，不占用任何数据库空间。

完整性约束存在数据字典中，在执行SQL或PL/SQL期间使用。

用户可以指明约束是启用的还是禁用的，当约束启用时，他增强了数据的完整性，否则，则反之，但约束始终存在于数据字典中。

禁用约束，使用ALTER语句:

ALTERTABLEtable_nameDISABLECONSTRAINTconstraint_name;

或

ALTERTABLEpoliciesDISABLECONSTRAINTchk_gender

如果要重新启用约束：

ALTERTABLEpoliciesENABLECONSTRAINTchk_gender

删除约束:

ALTERTABLEtable_nameDROPCONSTRAINTconstraint_name

ALTERTABLEpoliciesDROPCONSTRAINTchk_gender;

Check约束

在数据列上Check约束需要一个特殊的布尔条件或者将数据列设置成TRUE，至少一个数据列的值是NULL，Check约束用于增强表中数据内容的简单的商业规则。

用户使用Check约束保证数据规则的一致性。

Check约束可以涉及该行同属Check约束的其他数据列但不能涉及其他行或其他表，或调用函数SYSDATE,UID,USER,USERENV。

如果用户的商业规则需要这类的数据检查，那么可以使用触发器。

Check约束不保护LOB数据类型的数据列和对象、嵌套表、VARRY、ref等。

单一数据列可以有多个Check约束保护，一个Check约束可以保护多个数据列。

创建表的Check约束使用CREATETABLE语句，更改表的约束使用ALTERTABLE语句。

语法：

CONSTRAINT[constraint_name]CHECK（condition）;

Check约束可以被创建或增加为一个表约束，当Check约束保护多个数据列时，必须使用表约束语法。

约束名是可选的并且如果这个名字不存在，那么oracle将产生一个以SYS_开始的唯一的名字。

例：

CREATETABLEpolicies

（policy_idNUMBER,

holder_nameVARCHAR2（40）,

genderVARCHAR2

（1）constraintchk_genderCHECK（genderin（'

M'

'

F'

）,

marital_statusVARCHAR2

（1）,

date_of_birthDATE,

constraintchk_maritalCHECK（marital_statusin（'

S'

D'

W'

））

）;

NOTNULL约束

NOTNULL约束应用在单一的数据列上，并且他保护的数据列必须要有数据值。

缺省状况下，ORACLE允许任何列都可以有NULL值。

某些商业规则要求某数据列必须要有值，NOTNULL约束将确保该列的所有数据行都有值。

holder_nameVARCHAR2（40）NOTNULL,

genderVARCHAR2

（1）,

date_of_birthDATENOTNULL

对于NOTNULL的ALTERTABLE语句与其他约束稍微有点不同。

ALTERTABLEpoliciesMODIFYholder_nameNOTNULL

唯一性约束（Uniqueconstraint）

唯一性约束可以保护表中多个数据列，保证在保护的数据列中任何两行的数据都不相同。

唯一性约束与表一起创建，在唯一性约束创建后，可以使用ALTERTABLE语句修改。

column_namedata_typeCONSTRAINTconstraint_nameUNIQUE

如果唯一性约束保护多个数据列，那么唯一性约束要作为表约束增加。

语法如下：

CONSTRAINTconstraint_name（column）

UNIQUEUSINGINDEXTABLESPACE

（tablespace_name）STORAGE（storedclause）

唯一性约束由一个B-tree索引增强，所以可以在USING子串中为索引使用特殊特征，比如表空间或存储参数。

CREATETABLE语句在创建唯一性约束的同时也给目标数据列建立了一个唯一的索引。

CREATETABLEinsured_autos

（policy_idNUMBERCONSTRAINTpk_policiesPRIMARYKEY,

vinVARCHAR2（10）,

coverage_beginDATE,

coverage_termNUMBER,

CONSTRAINunique_autoUNIQUE（policy_id,vin）USING

INDEXTABLESPACEindexSTORAGE（INITIAL1MNEXT10MPCTINCREASE0）

用户可以禁用未以性约束，但他仍然存在，禁用唯一性约束使用ALTERTABLE语句。

ALTERTABLEinsured_autosDISABLECONSTRAINunique_name;

删除唯一性约束，使用ALTERTABLE....DROPCONSTRAIN语句：

ALTERTABLEinsured_autosDROPCONSTRAINunique_name;

注意用户不能删除在有外部键指向的表的唯一性约束。

这种情况下用户必须首先禁用或删除外部键（foreignkey）。

删除或禁用唯一性约束通常同时删除相关联的唯一索引，因而降低了数据库性能。

经常删除或禁用唯一性约束有可能导致丢失索引带来的性能错误。

要避免这样错误，可以采取下面的步骤：

1、在唯一性约束保护的数据列上创建非唯一性索引。

2、添加唯一性约束。

主键（PrimaryKey）约束

表有唯一的主键约束。

表的主键可以保护一个或多个列，主键约束可与NOTNULL约束共同作用于每一数据列。

NOTNULL约束和唯一性约束的组合将保证主键唯一地标识每一行。

像唯一性约束一样，主键由B-tree索引增强。

创建主键约束使用CREATETABLE语句与表一起创建，如果表已经创建了，可以使用ALTERTABLE语句。

date_of_birthDATE

与唯一性约束一样，如果主键约束保护多个数据列，那么必须作为一个表约束创建。

vinVARCHAR2（40）,

CONSTRAINTpk_insured_autosPRIMARYKEY（policy_id,vin）

USINGINDEXTABLESPACEindex

STORAGE（INITIAL1MNEXT10MPCTINCREASE0）

禁用或删除主键必须与ALTERTABLE语句一起使用:

ALTERTABLEpoliciesDROPPRIMARYKEY;

ALTERTABLEpoliciesDISABLEPRIMARYKEY;

外部键约束（Foreignkeyconstraint）

外部键约束保护一个或多个数据列，保证每个数据行的数据包含一个或多个null值，或者在保护的数据列上同时拥有主键约束或唯一性约束。

引用（主键或唯一性约束）约束可以保护同一个表，也可以保护不同的表。

与主键和唯一性约束不同外部键不会隐式建立一个B-tree索引。

在处理外部键时，我们常常使用术语父表（parenttable）和子表（childtable）,父表表示被引用主键或唯一性约束的表，子表表示引用主键和唯一性约束的表。

创建外部键使用CREATETABLE语句，如果表已经建立了，那么使用ALTERTABLE语句。

（policy_idNUMBERCONSTRAINTpolicy_fk

REFERENCEpolicies（policy_id

ONDELETECASCADE,

makeVARCHAR2（30）,

modelVARCHAR（30）,

yearNUMBER,

CONSTRAINauto_fkFROEIGNKEY（make,model,year）

REFERENCESautomobiles（make,model,year）

ONDELETESETNULL

ONDELETE子串告诉ORACLE如果父纪录（parentrecord）被删除后，子记录做什么。

缺省情况下禁止在子记录还存在的情况下删除父纪录。

外部键和NULL值

在外部键约束保护的数据列中NULL值的处理可能产生不可预料的结果。

ORACLE使用ISOstandarMatchNone规则增强外部键约束。

这个规则规定如果任何外部键作用的数据列包含有一个NULL值，那么任何保留该键的数据列在父表中没有匹配值。

比如，在父表AUTOMOBILES中，主键作用于数据列MAKE，MODEL，YEAR上，用户使用的表INSURED_AUTOS有一个外部约束指向AOTOMOBILES，注意在INSURES_AUTOS中有一数据行的MODEL列为NULL值，这一行数据已经通过约束检查，即使MAKE列也没有显示在父表AUTOMOBILES中，如下表：

表1AUTOMOBILES

表2INSURED_AUTOS

延迟约束检验（DeferredConstraintChecking）

约束检验分两种情况，一种是在每一条语句结束后检验数据是否满足约束条件，这种检验称为立即约束检验（immediatelychecking）,另一种是在事务处理完成之后对数据进行检验称之为延迟约束检验。

在缺省情况下Oracle约束检验是立即检验（immediatelychecking）,如果不满足约束将先是一条错误信息，但用户可以通过SETCONSTRAINT语句选择延迟约束检验。

SETCONSTRAINTconstraint_name|ALLDEFEERRED|IMMEDIATE--;

序列（Sequences）

Oracle序列是一个连续的数字生成器。

序列常用于人为的关键字，或给数据行排序否则数据行是无序的。

像约束一样，序列只存在于数据字典中。

序列号可以被设置为上升、下降，可以没有限制或重复使用直到一个限制值。

创建序列使用SETSEQUENCE语句。

CREATESEQUENCE[schema]sequenceKEYWORD

KEYWORD包括下面的值：

删除序列使用DROPSEQUENCE语句。

DROPSEQUENCEsequence_name

索引（INDEXES）

索引是一种可以提高查询性能的数据结构，在这一部分我们将讨论索引如何提高查询性能的。

ORACLE提供了以下几种索引：

B-Tree、哈希（hash）、位图（bitmap）等索引类型；

基于原始表的索引；

基于函数的索引；

域（Domain）索引。

实际应用中主要是B-Tree索引和位图索引，所以我们将集中讨论这两种索引类型。

B-Tree索引

B-Tree索引是最普通的索引，缺省条件下建立的索引就是这种类型的索引。

B-Tree索引可以是唯一或非唯一的，可以是单一的（基于一列）或连接的（多列）。

B-Tree索引在检索高基数数据列（高基数数据列是指该列有很多不同的值）时提供了最好的性能。

对于取出较小的数据B-Tree索引比全表检索提供了更有效的方法。

但当检查的范围超过表的10%时就不能提高取回数据的性能。

正如名字所暗示的那样，B-Tree索引是基于二元树的，由枝干块（branchblock）和树叶块（leafblock）组成,枝干块包含了索引列（关键字）和另一索引的地址。

树叶块包含了关键字和给表中每个匹配行的ROWID。

位图索引

位图索引主要用于决策支持系统或静态数据，不支持行级锁定。

位图索引可以是简单的（单列）也可以是连接的（多列），但在实践中绝大多数是简单的。

位图索引最好用于低到中群集（cardinality）列，在这些列上多位图索引可以与AND或OR操作符结合使用。

位图索引使用位图作为键值，对于表中的每一数据行位图包含了TRUE

（1）、FALSE（0）、或NULL值。

位图索引的位图存放在B-Tree结构的页节点中。

B-Tree结构使查找位图非常方便和快速。

另外，位图以一种压缩格式存放，因此占用的磁盘空间比B-Tree索引要小得多。