《第5章 索引与数据完整性.ppt》由会员分享，可在线阅读，更多相关《第5章 索引与数据完整性.ppt（36页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、第第5章章 索引与数据完整性索引与数据完整性01 索引索引02 数据的完整性和约束性数据的完整性和约束性1 索引索引主要内容 索引的分类索引的分类 建立索引的注意事项建立索引的注意事项 创建索引创建索引 维护索引维护索引 维护索引维护索引0102030405什么是索引 如果一个数据表中存有海量的数据记录，当对表执行指定条件的查询时。常规的查询方法会将所有的记录都读取出来，然后再把读取的每一条记录与查询条件进行比对，最后返回满足条件的记录。这样进行操作的时间开销和I/O开销都十分巨大的。对于这种情况，就可以考虑通过建立索引来减小系统开销。ROWID目录页码索引索引的分类 按照索引数据的存储方式索
2、引数据的存储方式可以将索引分为B树索引、位图索引、反向键索引和基于函数的索引；按照索引列的唯一性索引列的唯一性又可以分为唯一索引和非唯一索引；按照索引列的个数索引列的个数又可以分为单列索引和复合索引。建立索引的注意事项（1）索引应该建立在WHERE子句频繁引用表列上，如果在大表上频繁使用某列或某几个列作为条件执行索引操作，并且检索行数低于总行数15%，那么应该考虑在这些列上建立索引。（2）限制表中索引的个数。索引主要用于加快查询速度，但会降低DML操作的速度。索引越多，DML操作速度越慢，尤其会极大地影响INSERT和DELETE操作的速度。因此，规划索引时，必须仔细权衡查询和DML的需求。（
3、3）指定索引块空间的使用参数。基于表建立索引时，Oracle会将相应表列数据添加到索引块。为索引块添加数据时，Oracle会按照PCTFREE参数在索引块上预留部分空间，该预留空间时为将来的INSERT操作准备的。如果将来在表上执行大量INSERT操作，那么应该在建立索引时设置较大的PCTFREE。（4）将表和索引部署到相同的表空间，可以简化表空间的管理；将表和索引部署到不同的表空间，可以提高访问性能。（5）当在大表上建立索引时，使用NOLOGGING选项可以最小化重做记录。使用NOLOGGING选项可以节省重做日志空间、降低索引建立时间、提高索引并行建立的性能。不要在小表上建立索引。（6）为
4、了提高多表连接的性能，应该在连接列上建立索引。创建索引在SQL Developer中创建索引创建索引使用SQL命令创建索引1.创建B树索引创建索引CREATE INDEX 索引名 ON 表名(列名 ASC|DESC,);创建B树索引的语法SQL create index emp_sal_index on emp(sal);为emp表的sal列创建索引创建索引2.创建位图索引创建索引数据数据73697499752175667654769877827788783978447876790079027934deptno=1000000010100001deptno=2010010001001010de
5、ptno=3001101100010100创建索引CREATE BITMAP INDEX 索引名 ON 表名(列名 ASC|DESC,);创建位图索引的语法SQL create bitmap index emp_deptno_index on emp(deptno);在deptno字段上设置位图索引维护索引在SQL Developer中维护索引 右击表emp选择“编辑”菜单项，在表编辑窗口左边选择“索引”选项，在右边“所有”框中选中要维护的索引，在“索引属性”栏中修改索引的信息，单击“确认”按钮后完成修改。维护索引使用SQL命令维护索引SQL alter index XSB_NAME_INDE
6、X rename to XSB_INDEX;重命名索引XSB_NAME_INDEX删除索引界面方式删除索引删除索引使用SQL命令删除索引SQL DROP INDEX XSB_INDEX;删除XSB表中的一个索引名为XSB_INDEX的索引DROP INDEX schema.index_name语法2 数据的完整性和约束性数据的完整性和约束性主要内容 非空约束非空约束 主键约束主键约束 唯一性约束唯一性约束 外键约束外键约束 禁用约束禁用约束0102030405 激活约束激活约束 删除约束删除约束0607非空约束 非空约束就是限制必须为某个列提供值非空约束就是限制必须为某个列提供值。空值（NUL
7、L）是不存在值，它既不是数字0，也不是空字符串，而是不存在、未知的情况。SQL create table Books (BookNo number(4)not null,-图书编号，不为空 BookName varchar2(20),-图书名称 Author varchar2(10),-作者 SalePrice number(9,2),-定价 PublisherNo varchar2(4)not null,-出版社编号，不为空 PublishDate date,-出版日期 ISBN varchar2(20)not null-ISBN，不为空 );创建Books表，要求BookNo（图书编号），
8、ISBN和PublisherNo（出版社编号）不能为空值。主键约束主主键约束束=非空非空约束束+唯一性唯一性约束束主键约束SQL create table Books_1 (BookNo number(4)not null,-图书编号 BookName varchar2(20),-图书名称 Author varchar2(10),-作者 SalePrice number(9,2),-定价 PublisherNo varchar2(4)not null,-出版社编号 PublishDate date,-出版日期 ISBN varchar2(20)not null,-ISBN constraint
9、 BOOK_PK primary key(BookNo)-创建主键和主键约束 );创建表Books_1，并为该表定义行级主键约束BOOK_PK（主键列为BookNo）。唯一性约束唯一性（唯一性（UNIQUEUNIQUE）约束表示的是数据不允束表示的是数据不允许出出现重复的情重复的情况。况。唯一性约束SQL create table Members (MemNo number(4)not null,-会员编号 MemName varchar2(20)not null,-会员名称 Phone varchar2(20),-联系电话 Email varchar2(30),-电子邮件地址 QQ varc
10、har2(20)Constraint QQ_UK unique,-QQ号，并设置为UNIQUE约束 ProvCode varchar2(2)not null,-省份代码 OccuCode varchar2(2)not null,-职业代码 InDate date default sysdate，-入会日期 Constraint Mem_PK primary key(MemNo)-主键约束列为MemNo );创建一个会员表Members，并要求为该表的QQ列定义唯一性约束。外键约束成员成员成员编号 NUMBER 姓名 VARCHAR2(20)建议建议建议编号 NUMBER 内容 CLOB成员编号
11、 NUMBER 外外键约束指的就是子表的某一个字段的内容取束指的就是子表的某一个字段的内容取值范范围必必须由主表指定，通由主表指定，通过外外键将两将两张数据表数据表联系起来。通系起来。通过FOREIGN KEY（FK）来指定外）来指定外键约束。束。删除外键表中的数据级联删除指的是在建立外键约束时通过ON DELETE CASCADE子句设置，这样在删除父表数据时，由父表数据关联的所有子表数据都会被删除。级联删除通通过ON DELETE SET NULL选项将子表数据级联设置为null，当父表数据删除时，子表的数据可以不用删除，并且将父表关联字段的内容设置为null。级联设置禁用约束约束创建之后
12、，如果没有经过特殊处理，就一直起作用。但也可以根据实际需要，临时禁用某个约束。当某个约束被禁用后，该约束就不再起作用了，但它还存在于数据库中。那么，为什么要禁用约束呢？这是因为约束的存在会降低插入和更改数据的效率，系统必须确认这些数据是否满足定义的约束条件。当执行一些特殊操作时，比如，使用SQL*Loader从外部数据源向表中导入大量数据，并且事先知道这些数据是满足约束条件的，为提高运行效率，就可以禁用这些约束。禁用约束SQL create table Student (StuCode varchar2(4)not null,StuName varchar2(10)not null,Age i
13、nt constraint Age_CK check(age 0 and age alter table employees_temp disable constraint temp_departid_fk;禁用employees_temp表中的约束temp_departid_fk2 2禁用已经存在的约束禁用已经存在的约束对于已存在的约束，则可以使用ALTERTABLEDISABLECONSTRAINT语句禁止该约束。激活约束alter table table_nameenable novalidate|validate constraint con_name;语法：删除约束alter tab
14、le table_name drop constraint con_name;语法：小结小结 本章首先介绍了本章首先介绍了索引这种数据库对象索引这种数据库对象，了解到索引对象能够加快大容量数据，了解到索引对象能够加快大容量数据的查询速度；另外，本章还在最后讲解了的查询速度；另外，本章还在最后讲解了数据的完整性和约束性数据的完整性和约束性，它们能够保证，它们能够保证数据的准确性。数据的准确性。上机指导如何使用CASCADE关键字创建外键约束以及如何实现数据的级联删除操作。在HR模式中，创建一个新表DEPARTMENTS_TEMP（该表的结构拷贝自DEPARTMENTS），然后在该表与EMPLOY
15、EES_TEMP表之间建立外键约束，并指定外键约束的引用类型为ON DELETE CASDE，最后删除DEPARTMENTS_TEMP表与EMPLOYEES_TEMP中都存在的外键值（1）在HR模式下，创建一个被引用表（该表的结构拷贝自DEPARTMENTS），并为其设置主键约束：SQL connect hr/hr-在hr模式下已连接。SQL create table departments_temp as select*from departments where department_id=30;-创建departments_temp 表表已创建。SQL alter table depar
16、tments_temp add primary key(department_id);-设置departments_temp表的主键约束表已更改。上机指导（2）在EMPLOYEES_TEMP表和DEPARTMENTS_TEMP表之间创建外键约束，并指定外键约束的引用类型为ON DELETE CASDE，代码如下：SQL alter table employees_temp add constraint temp_departid_fk2 foreign key(department_id)references departments_temp on delete cascade;表已更改。（3
17、）查看外键表EMPLOYEES_TEMP表中部门编号为30的记录数，代码如下：SQL select count(*)from employees_temp where department_id=30;COUNT(*)-6上机指导（4）删除外键表departments_temp中department_id为30的记录，代码如下：SQL delete departments_temp where department_id=30;已删除 1 行。SQL select count(*)from employees_temp where department_id=30;COUNT(*)-0通过上面的查询结果可以看出，由于指定了外键约束的引用类型为通过上面的查询结果可以看出，由于指定了外键约束的引用类型为ON ON DELETE CASDEDELETE CASDE后，所以在删除被引用表后，所以在删除被引用表DEPARTMENTS_TEMPDEPARTMENTS_TEMP中编号为中编号为3030的记的记录时，系统也级联删除了录时，系统也级联删除了EMPLOYEES_TEMPEMPLOYEES_TEMP表中所有编号为表中所有编号为3030的记录。的记录。