4-实验四-模式对象 - 副本.doc

实验四 数据库模式对象管理实验报告姓名： 郭姗 班级： 计本122 学号： 201206080205 一、 实验目的1） 掌握表、索引、索引化表的概念及管理2） 掌握分区的概念及分区管理3） 掌握外部表的概念及其管理4） 了解簇、视图、序列、同义词、数据库链接等的管理二、 预习内容1） 什么是模式，模式有什么特点？答：是指一系列逻辑数据结构或对象的集合。1）模式与用户相对应，一个模式只能被一个数据库用户所拥有，并且模式的名称与这个用户的名称相同。2） 通常情况下，用户所创建数据库对象都保存在与自己同名的模式中。3） 同一模式中数据库对象的名称必须惟一，而在不同模式中的数据库对象可以同名。4） 默认情况下，用户引用的对象是与自己同名模式中的对象，如果要引用其他模式中的对象，需要在该对象名之前指明对象所属模式。 2） Oracle数据库中模式对象有哪些？表空间、用户、角色、目录、概要文件及上下文问等数据库对象属于某个模式吗？答：包含下列类型：聚集、数据库链、数据库触发器、维度、外部过程库、索引和索引类型、Java类、Java资源和Java源代码、实体化视图和实体化视图日志、对象表、对象类型和对象视图、操作符、序列、存储函数、过程和包、同义词、表和索引组织表、视图。不属于。3） 如何进行模式的选择与切换？答：如果用户以NORMAL身份登录，则进入同名模式；若以SYSDBA身份登录，则进入SYS模式；如果以SYSOPER身份登录，则进入PUBLIC模式。4） 说明索引的作用，以及Oracle数据库中索引的类型。索引作用答：a) 索引是为了加速对表中元组的检索而创建的一种独立的存储结构；b) 依赖于表建立，又独立于被索引的表；c) 通过使用索引加速行的检索，但减慢更新的速度；d) 快速定位数据，减少磁盘 I/O；e) Oracle自动使用、维护索引索引分类f) 惟一性索引与非惟一性索引 g) 平衡树索引与位图索引 h) 单列索引与复合索引i) 函数索引 j) 聚簇索引k) 全局索引与本地索引三、 实验环境32位Windows XP/Windows Server2000/Windows Server2003 +Oracle10g环境四、 实验内容1） 按照下列表结构利用SQL语句创建class、student两个表。class表列名数据类型约束备注CNONUMBER(2)主键班号CNAMEVARCHAR2(20)班名NUMNUMBER(3)人数student表列名数据类型约束备注SNONUMBER(4)主键学号SNAMEVARCHAR2(10)唯一姓名SAGENUMBER年龄SEXCHAR(2)性别CNONUMBER(2)班级号2） 为student表添加一个外键约束，其CNO列参照class表的CNO列。3） 为student表的SAGE列添加一个检查约束，保证该列取值在0100之间。4） 为student表的SEX列添加一个检查约束，保证该列取值为“M”或“F”，且默认值为“M”。5） 在class表的CNAME列上创建一个唯一性索引。6） 利用子查询分别创建一个事务级的临时表和会话级的临时表，其结构与student表的结构相同。7） 创建一个索引化表，其表结构与student表相同。8） 创建一个student_range表（列、类型与student表的列、类型相同），按学生年龄分为3个区，低于20岁的学生信息放入part1区，存储在EXAMPLE表空间中；20-30岁的学生信息放在part2区，存放在ORCLTBS1表空间中；其他数据放在part3区，存放在ORCLTBS2表空间中。9） 创建一个student_list表（列、类型与student表的列、类型相同），按学生性别分为两个区。10） 创建一个起始值为10000的序列，步长为2，最大值为100000，不可循环。11） 为scott模式下的emp表创建一个公共同义词，名称为employee。12） 创建一个视图，包含员工号、员工名和该员工领导的员工号、员工名。五、 实验步骤写出每一条实验内容对应的SQL语句。1) 按照下列表结构利用SQL语句创建class、student两个表。CREATE TABLE class(CNO NUMBER(3) PRIMARY KEY,CNAME VARCHAR2(20),NUM NUMBER(2);CREATE TABLE student(SNO NUMBER(4) PRIMARY KEY,SNAME VARCHAR2(10) UNIQUE,SAGE NUMBER(4);SEX CHAR(2),CNO NUMBER(2);2) 为student表添加一个外键约束，其CNO列参照class表的CNO列。ALTER TABLE studentADD CONSTRAINT P_FK FOREIGN KEY(CNO) REFERENCES class(CNO);3) 为student表的SAGE列添加一个检查约束，保证该列取值在0100之间ALTER TABLE studentADD CONSTRAINT P_CK CHECK(SAGE BETWEEN 0 AND 100);4) 为student表的SEX列添加一个检查约束，保证该列取值为“M”或“F”，且默认值为“M”。ALTER TABLE studentADD CONSTRAINT P_CK CHECK(SEX IN M, F);5) 在class表的CNAME列上创建一个唯一性索引。CREATE UNIQUE INDEX class_CNAME ON class(CNAME);6) 利用子查询分别创建一个事务级的临时表和会话级的临时表，其结构与student表的结构相同。创建事务级别的临时表CREATE GLOBAL TEMPORARY TABLE std-temp(SNO NUMBER(4) PRIMARY KEY,SNAME VARCHAR2(10) UNIQUE,SAGE NUMBER(4);SEX CHAR(2),CNO NUMBER(2)ON COMMIT DELETE ROWS;创建会话级别的临时表CREATE GLOBAL TEMPORARY TABLE sees_temp(SNO NUMBER(4) PRIMARY KEY,SNAME VARCHAR2(10) UNIQUE,SAGE NUMBER(4);SEX CHAR(2),CNO NUMBER(2)ON COMMIT PRESERVE ROWS;7）创建一个索引化表，其表结构与student表相同。CREATE TABLE new_student(SNO NUMBER(4) PRIMARY KEY,SNAME VARCHAR2(10) UNIQUE,SAGE NUMBER(4);SEX CHAR(2),CNO NUMBER(2)ORGANIZATION INDEX; 8)创建一个student_range表（列、类型与student表的列、类型相同），按学生年龄分为3个区，低于20岁的学生信息放入part1区，存储在EXAMPLE表空间中；20-30岁的学生信息放在part2区，存放在ORCLTBS1表空间中；其他数据放在part3区，存放在ORCLTBS2表空间中。CREATE TABLE student(SNO NUMBER(4) PRIMARY KEY,SNAME VARCHAR2(10) UNIQUE,SAGE NUMBER(4);SEX CHAR(2),CNO NUMBER(2)PARTITION BY RANGE(SAGE)(PARTITION part1 VALUES LESS THAN(20) TABLESPACE EXAMPLE,PARTITION part12 VALUES LESS THAN(20) TABLESPACE ORCLTBS1,PARTITION part1 VALUES LESS THAN(MAXVALUE)TABLESPACE ORCLTBS2 STORAGE(INITIAL 10M NEXT 20M),STORAGE(INITIAL 20M NEXT 10M MAXENTENTS 10);9）创建一个student_list表（列、类型与student表的列、类型相同），按学生性别分为两个区。CREATE TABLE student_list(SNO NUMBER(4) PRIMARY KEY,SNAME VARCHAR2(10) UNIQUE,SAGE NUMBER(4);SEX CHAR(2),CNO NUMBER(2)PARTITION BY LIST(SEX)(PARTITION student_male VALUES(M), PARTITION student_female VALUES(F);10）创建一个起始值为10000的序列，步长为2，最大值为100000，不可循环。CREATE SEQUENCE sequence INCREMENT BY 2START WITH 10000 MAXVALUE 100000;11) 为scott模式下的emp表创建一个公共同义词，名称为employee。CREATE PUBLIC SYNONYM scottemp FOR scott.emp;12）创建一个视图，包含员工号、员工名和该员工领导的员工号、员工名。CREATE TABLE emp(Empno NUMBER(4) PRIMARY KEY,Ename VARCHAR2(10),Depno NUMBER(4),Dename VARCHAR2(10);CREATE OR REPLACE VIEW emp_base_info_viewASSELECT empno,ename,depno,dename FROM emp;六、 思考题1） 思考数据库中使用索引的优点和缺点，索引是如何工作的？答：创建索引可以大大提高系统的性能。第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。第二，可以大大加快 数据的检索速度，这也是创建索引的最主要的原因。第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。第四，在使用分组和排序 子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。增加索引也有许多不利的一个方面。第一，创建索引和维护索引要耗费时间，这种时间随着数据 量的增加而增加。第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。 索引在逻辑上和物理上都与其基表（base table）是相互独立的。用户可以随时创建（create）或移除（drop）一个索引，而不会影响其基表或基表上的其他索引。当用户移除一个索引时， 所有的应用程序仍然能够继续工作，但是数据访问速度有可能会降低。作为一种独立的数据结构，索引需要占用存储空间。 当索引被创建后，对其的维护与使用都是 Oracle 自动完成的。当索引所依赖的数据发生插入，更新，删除等操作时，Oracle 会自动地将这些数据变化反映到相关的索引中，无需用户的额外操作。 即便索引的基表中插入新的数据，对被索引数据的查询性能基本上能够保持稳定不变。但是，如果在一个表上建立了过多的索引，将降低其插入，更新，及删除的性能。因为 Oracle 必须同时修改与此表相关的索引信息。 索引的使用原则：（1）导入数据后再创建索引每当向表中插入数据时，ocacle 都需要对索引内容进行更新。因此，如果导入大量的数据，oracle 就需要对索引进行大量的更新操作，会影响数据导入的效率。（2）在适当的表和列上创建适当的索引 a 经常查询的记录数目少于表中所有记录总数的5%时就应当创建索引。 b 经常进行连接查询表时。在连接列上建立索引能够显著提高查询的速度。 C 对于取值范围很大的列应创建B树索引。 d 对于取值范围很小的列应当创建位图索引。 e 不能在LONG,LONG RAW,LOB数据类型的列上创建索引； f oracle 会自动在PRIMARY KEY ,UNIQUE 约束的列上创建唯一性索引。（3）合理设置复合索引中列的顺序 复合索引中列的顺序会影响索引的使用效率，应将频繁使用的列放在其他列前面。当A,B,C三列创建了复合索引时，相当于创建了A,AB,ABC三个索引。（4）限制表中索引的数目（5）为索引设置合适的PCTFREE参数对索引来说，当向表中插入记录时，会将新增记录所对应的索引条目保留在相应数据块的保留空闲空间中。因此，如果经常要对某个表执行数据插入操作，就应该为这个表的索引设置一个较高的PCTFREE参数。（6）选择存储索引的表空间在默认情况下，索引与表存储在同一表空间中，这样有利于数据库维护操作，具有较高的可用性；反之，若将索引与表存储在不用的空间中，则可提高系统的存取性能，减少硬盘的I/O冲突，但是表与索引的可用状态可能会出现不一致。 2） Oracle数据库中对表和索引进行分区管理有何