索引与数据完整性

1第5章索引与数据完整性201

索引02

数据的完整性和约束性31

索引4主要内容

索引的分类

建立索引的注意事项

创建索引

维护索引

维护索引01020304055什么是索引如果一个数据表中存有海量的数据记录，当对表执行指定条件的查询时。常规的查询方法会将所有的记录都读取出来，然后再把读取的每一条记录与查询条件进行比对，最后返回满足条件的记录。这样进行操作的时间开销和I/O开销都十分巨大的。对于这种情况，就可以考虑通过建立索引来减小系统开销。ROWID目录页码索引6索引的分类按照索引数据的存储方式可以将索引分为B树索引、位图索引、反向键索引和基于函数的索引；按照索引列的唯一性又可以分为唯一索引和非唯一索引；按照索引列的个数又可以分为单列索引和复合索引。7建立索引的注意事项（1）索引应该建立在WHERE子句频繁引用表列上，如果在大表上频繁使用某列或某几个列作为条件执行索引操作，并且检索行数低于总行数15%，那么应该考虑在这些列上建立索引。（2）限制表中索引的个数。索引主要用于加快查询速度，但会降低DML操作的速度。索引越多，DML操作速度越慢，尤其会极大地影响INSERT和DELETE操作的速度。因此，规划索引时，必须仔细权衡查询和DML的需求。（3）指定索引块空间的使用参数。基于表建立索引时，Oracle会将相应表列数据添加到索引块。为索引块添加数据时，Oracle会按照PCTFREE参数在索引块上预留部分空间，该预留空间时为将来的INSERT操作准备的。如果将来在表上执行大量INSERT操作，那么应该在建立索引时设置较大的PCTFREE。（4）将表和索引部署到相同的表空间，可以简化表空间的管理；将表和索引部署到不同的表空间，可以提高访问性能。（5）当在大表上建立索引时，使用NOLOGGING选项可以最小化重做记录。使用NOLOGGING选项可以节省重做日志空间、降低索引建立时间、提高索引并行建立的性能。不要在小表上建立索引。（6）为了提高多表连接的性能，应该在连接列上建立索引。8创建索引在SQLDeveloper中创建索引9创建索引使用SQL命令创建索引1.创建B树索引10创建索引CREATEINDEX索引名ON

表名(列名[ASC|DESC],…);创建B树索引的语法SQL>createindexemp_sal_indexonemp(sal);为emp表的sal列创建索引11创建索引2.创建位图索引12创建索引数据73697499752175667654769877827788783978447876790079027934deptno=1000000010100001deptno=2010010001001010deptno=300110110001010013创建索引CREATEBITMAPINDEX索引名ON

表名(列名[ASC|DESC],…);创建位图索引的语法SQL>createbitmapindexemp_deptno_indexonemp(deptno);在deptno字段上设置位图索引14维护索引在SQLDeveloper中维护索引右击表emp选择“编辑”菜单项，在表编辑窗口左边选择“索引”选项，在右边“所有”框中选中要维护的索引，在“索引属性”栏中修改索引的信息，单击“确认”按钮后完成修改。15维护索引使用SQL命令维护索引SQL>alterindexXSB_NAME_INDEX

renametoXSB_INDEX;重命名索引XSB_NAME_INDEX16删除索引界面方式删除索引17删除索引使用SQL命令删除索引SQL>DROPINDEXXSB_INDEX;删除XSB表中的一个索引名为XSB_INDEX的索引DROPINDEX[schema.]index_name语法182

数据的完整性和约束性19主要内容

非空约束

主键约束

唯一性约束

外键约束

禁用约束0102030405

激活约束

删除约束060720非空约束

非空约束就是限制必须为某个列提供值。空值（NULL）是不存在值，它既不是数字0，也不是空字符串，而是不存在、未知的情况。SQL>createtableBooks(BookNonumber(4)notnull, --图书编号，不为空BookNamevarchar2(20), --图书名称Authorvarchar2(10), --作者SalePricenumber(9,2), --定价PublisherNovarchar2(4)notnull, --出版社编号，不为空PublishDatedate, --出版日期ISBNvarchar2(20)notnull --ISBN，不为空);创建Books表，要求BookNo（图书编号），ISBN和PublisherNo（出版社编号）不能为空值。21主键约束主键约束=非空约束+唯一性约束22主键约束SQL>createtableBooks_1(BookNonumber(4)notnull, --图书编号BookNamevarchar2(20), --图书名称Authorvarchar2(10), --作者SalePricenumber(9,2), --定价PublisherNovarchar2(4)notnull, --出版社编号PublishDatedate, --出版日期ISBNvarchar2(20)notnull,- --ISBNconstraintBOOK_PKprimarykey(BookNo) --创建主键和主键约束);创建表Books_1，并为该表定义行级主键约束BOOK_PK（主键列为BookNo）。23唯一性约束

唯一性（UNIQUE）约束表示的是数据不允许出现重复的情况。24唯一性约束SQL>createtableMembers(MemNonumber(4)notnull, --会员编号MemNamevarchar2(20)notnull, --会员名称Phonevarchar2(20), --联系电话Emailvarchar2(30), --电子邮件地址QQvarchar2(20)ConstraintQQ_UKunique, --QQ号，并设置为UNIQUE约束ProvCodevarchar2(2)notnull, --省份代码OccuCodevarchar2(2)notnull,--职业代码InDatedatedefaultsysdate， --入会日期ConstraintMem_PKprimarykey(MemNo) --主键约束列为MemNo);创建一个会员表Members，并要求为该表的QQ列定义唯一性约束。25外键约束成员成员编号NUMBER <pk>姓名VARCHAR2(20)建议建议编号NUMBER <pk>内容CLOB成员编号NUMBER <fk>

外键约束指的就是子表的某一个字段的内容取值范围必须由主表指定，通过外键将两张数据表联系起来。通过FOREIGNKEY（FK）来指定外键约束。26删除外键表中的数据

级联删除指的是在建立外键约束时通过ONDELETECASCADE子句设置，这样在删除父表数据时，由父表数据关联的所有子表数据都会被删除。级联删除通过ONDELETESETNULL选项将子表数据级联设置为null，当父表数据删除时，子表的数据可以不用删除，并且将父表关联字段的内容设置为null。级联设置27禁用约束约束创建之后，如果没有经过特殊处理，就一直起作用。但也可以根据实际需要，临时禁用某个约束。当某个约束被禁用后，该约束就不再起作用了，但它还存在于数据库中。那么，为什么要禁用约束呢？这是因为约束的存在会降低插入和更改数据的效率，系统必须确认这些数据是否满足定义的约束条件。当执行一些特殊操作时，比如，使用SQL*Loader从外部数据源向表中导入大量数据，并且事先知道这些数据是满足约束条件的，为提高运行效率，就可以禁用这些约束。28禁用约束SQL>createtableStudent(StuCodevarchar2(4)notnull,StuNamevarchar2(10)notnull,AgeintconstraintAge_CKcheck(age>0andage<120)disable,Provincevarchar2(20),SchoolNamevarchar2(50));创建一个学生信息表（Student），并为年龄列（Age）定义一个disable状态的Check约束（要求年龄值在0到120之间）。1．在定义约束时禁用29禁用约束SQL>altertableemployees_temp

disableconstrainttemp_departid_fk;禁用employees_temp表中的约束temp_departid_fk2．禁用已经存在的约束对于已存在的约束，则可以使用ALTERTABLE…DISABLECONSTRAINT语句禁止该约束。30激活约束altertabletable_nameenable[novalidate|validate]constraintcon_name;语法：31删除约束altertabletable_name

dropconstraintcon_name;语法：32小结本章首先介绍了索引这种数据库对象，了解到索引对象能够加快大容量数据的查询速度；另外，本章还在最后讲解了数据的完整性和约束性，它们能够保证数据的准确性。33上机指导如何使用CASCADE关键字创建外键约束以及如何实现数据的级联删除操作。在HR模式中，创建一个新表DEPARTMENTS_TEMP（该表的结构拷贝自DEPARTMENTS），然后在该表与EMPLOYEES_TEMP表之间建立外键约束，并指定外键约束的引用类型为ONDELETECASDE，最后删除DEPARTMENTS_TEMP表与EMPLOYEES_TEMP中都存在的外键值（1）在HR模式下，创建一个被引用表（该表的结构拷贝自DEPARTMENTS），并为其设置主键约束：SQL>connecthr/hr --在hr模式下已连接。SQL>createtabledepartments_tempasselect*fromdepartmentswheredepartment_id=30; --创建departments_temp

表表已创建。SQL>altertabledepartments_tempaddprimarykey(department_id); --设置departments_temp表的主键约束表已更改。34上机指导（2）在EMPLOYEES_TEMP表和DEPARTMENTS_TEMP表之间创建外键约束，并指定外键约束的引用类型为ONDELETECASDE，代码如下：SQL>altertableemployees_tempaddconstrainttemp_depar