Oracle9i第9章视图、序列、索引和同义词.ppt

第八章 视图、序列、索引和同义词,建立视图,视图的使用与基表类似，要有视图名及相应的列。生成一个视图可使用CREATE VIEW命令。其语法格式如下： CREATE OR REPLACE FORCE VIEW viewname AS WITH READ ONLY|WITH CHECK OPITION,OR REPLACE:允许新视图替换已存在的一个同名视图。 FORCE:即使在基本表不存在或对这些表无访问权限时，也可建立视图。不过，这个视图在表被建立和被授权访问它们之前一直处于无效状态。 WITH READ ONLY:只允许查询访问视图。 WITH CHECK OPITION：如果关联视图使用此选项建立，将完全不能插入，但能删除和修改。,CREATE OR REPLACE FORCE VIEW TMP_VIEW AS SELECT a.empno, a.ename, b.dname FROM emp a, DEPT b where a.deptno=b.deptno;,使用视图,一旦建立了一个视图，就可以把它当作一个表从中查询数据。 CREATE OR REPLACE FORCE VIEW TMP_VIEW AS SELECT a.empno, a.ename, b.dname FROM emp a, DEPT b where a.deptno=b.deptno; select * from tmp_view;,Oraclec允许对视图执行UPDATE、INSERT和DELETE语句。 如果一个视图包括了基本表中的所有列，并仅有一个WHERE子句来限制那些返回的行，那么Oracle将允许更新这个视图。 对于没有保存至少一个基本表的主键的那些视图是不能被更新的。另外一些处理汇部数据、基于表达式的列、联合和某种关联的视图也不能被更新。,比如： 带有INTERSECT、UNION或MINUS等集合操作符的视图 带有GROUP BY、CONNECT BY或START WITH子句的视图 带有AVG、SUM或MAX等组函数的视图 带有DISTINCT的功能的视图； 如果不清楚指定的视图是否可以更新，或可以更新哪些列，可以查询USER_UPDATABLE_COLUMNS数据字典视图。,例如查询上例中建立的视图TMP_VIEW是否可以更新可以执行如下操作： select column_name, updatable from user_updatable_columns where table_name='TMP_VIEW',重新编译视图,任何时间改变一个视图的查询所引用的对象时，Oracle将标记该视图无效。再次使用视图前它必须被重新编译。从新编译一个视图语法如下： ALTER VIEW viewname COMPILE； alter view bookauthor_view compile; alter view bookonweb compile;,重建视图,修改视图所能处理的操作只是重新编辑视图。若想改变视图的定义，必须重建视图。 若想修改视图bookonweb的定义为所有上网的且出版日期在1995年1月1日之后的书目信息，需要重建视图bookonweb。 create or replace view bookonweb as select * from bookinfo where status=y and publishtime1-1月-95 with read only;,删除视图,可以使用DROP VIEW命令从数据库中删除视图。其格式为： drop view schema.viewname; drop view bookauthor_view 也可以使用OEM通过右击视图从弹出菜单中选择“删除”来删除一个视图。,查看视图信息,视图创建后，可以通过数据字典中的一些有关视图信息的视图来查看视图。,查询用户bookcreator所拥有的全部视图的名称、类型 select view_name from all_views where owner=BOOKCREATOR;,序列管理,Oracle序列是一个连续的数字生成器。 比如希望表的主键是一个数字，并且希望每次向表中插入新行时那个数字能自动增加，或正在建立审计跟踪时，需要知道生成这些审计跟踪记录的确切的顺序，而靠一个时间戳通常不足以实现这些功能。 序列只存在于数据字典中。序列号可以被设置为上升、下降，可以没有限制或重复使用直到一个限制值。,创建序列,创建序列使用CREATE SEQUENCE语句。其语法格式如下： CREATE SEQUENCE schema.sequencename KEYWORD 下面的例子将创建一个序列，初始值是1，并且每次访问自动增加1。 SQL CREATE SEQUENCE sqc_plan_key 2 START WITH 1 3 INCREMENT BY 1 4 ORDER 5 NOCYCLE;,START WITH 1用于指定初始值是1。 INCREMENT BY 1用于指定每次访问自动增加1，如果INCREMENT BY后面跟一个负值，则序列将是降序的。 ORDER用于保证每个序列的值都将比先前的值大。 使用NOCYCLE设置序列值在达到限制值以后不能重复，这是缺省设置。当试图产生MAXVALUE+1的值时，将会产生一个异常。,其它关键字还有： MINVALUE：定义序列可以生成的最小值，这是降序序列中的限制值。缺省情况下该值为NOMINVALUE，对于升序为1，对于降序为-10E26。 MAXVALUE：序列能生成的最大数字。这是升序序列中的限制值，缺省的MAXVALUE为NOMAXVALUE，对于升序为10E26，对于降序为-1。 CYCLE：设置序列值在达到限制值以后可以重复。 CACHE：定义序列值占据的内存块的大小，缺省值为20。 NOCACHE：在每次序列号产生时强制数据字典更新，保证在序列值之间没有间隔当创建序列时，START WITH值必须等于或大于MINVALUE。,改变序列,改变序列的办法也有两种，一种是通过命令行ALTER SEQUENCE命令改变序列的选项。 另一种是通过在OEM中右击序列名，从弹出菜单中选择“编辑”来进行改变。 对于序列的当前值是无法更改的，如果应用中确有这种要求，那么只能删除此序列后，再使用START WITH子句重建此序列。,修改一个序列，使用ALTER SEQUENCE实现。 如修改序列stuenno的cache属性为30，且不可循环使用 ALTER SEQUENCE stuenno nocycle cache 30；,删除序列,使用DROP SEQUENCE命令来删除一个序列。其语法格式为： DROP SEQUENCE sequence_name 也可以通过OEM来删除序列。简单地右击想删除的序列，并从弹出的菜单中选择“移去”。 当一个序列被删除后，其相关的权限也理所当然的被删除了，但建立在其上的同义词或触发器将会被保留，只不过处于无效状态，不能被使用。,序列的应用,在Oracle中建立自动增加字段，需要执行下面的操作： 定义需要作为数字增加的列 希望表DEPT的主键字段DEPTNO，每次插入行时自动增加1。 表DEPT的结构如下所示： 名称 是否为空? 类型 - - - DEPTNO NOT NULL NUMBER(6) DNAME VARCHAR2(30) LOC VARCHAR2(60),注意：DEPTNO是主键，其值类型是NUMBER型，且长度是6位。所以将要建立的序列的最大值不能超过999999。 CREATE SEQUENCE sqc_dept_key NOCYCLE MAXVALUE 999999 START WITH 1;,可通过nextval和currval伪列来引用序列，每引用一次序列的伪列nextval，就会按照序列的定义产生一个新的序列码；通过序列的伪列currval可以反复利用当前的序列码。,可以在如下场合使用序列的nextval和currval伪列： Insert语句的values子句中 Select语句中的前面选择的表列名中 Update语句中的set子句,在如下场合不能够使用nextval或currval伪列： 子查询 视图或快照的定义查询 带有distinct操作符的select语句 带有group by或order by子句的select语句 通过union、intersect或minus与另一个select语句相结合的select语句中 Select语句的where子句中 Create table或alter table语句中的一个表列的default值定义中 检查约束的条件中,想了解一个已存在的序列信息，可以通过数据字典DBA_SEQUENCES、ALL_SEQUENCES和USER_SEQUENCES等几个视图来查看。,无论什么时候当插入一行时，从序列中定一个触发器来设置列的值 在DEPT表中建立插入触发器。使得每次插入一行时，DEPTNO字段自动通过访问序列sqc_dept_key来获取值。,CREATE OR REPLACE TRIGGER tri_dept_setdeptno BEFORE INSERT ON dept FOR EACH ROW DECLARE p_nextno dept.deptno%type; BEGIN SELECT sqc_dept_key.NEXTVAL INTO p_nextno FROM dual; :new.deptno := p_nextno; END; /,在建立上面的触发器后，DEPT表中的字段DEPTNO总是使用所建立的序列来设置。即使插入语句为DEPTNO指定了一些其它的值，也是这样。 定义一个触发器来防止更新此列。 有了前面3步已经实现了对DEPT表主键自动增加的功能，但如，有人更新了一条已经存在的记录，并将主键值改变为一个可能和将来插入相冲突的值该怎么办。所以这个值不能被更新，下面定义一个触发器来防止此列被更新。,CREATE OR REPLACE TRIGGER tri_dept_updeptno BEFORE UPDATE OF deptno ON dept FOR EACH ROW BEGIN RAISE_APPLICATION_ERROR(-20000, '不能更新自动增加类型的主键值!'); END; /,测试结果： 首先向表DEPT中输入一行数据，查询其结果；然后再将此行数据字段DEPTNO更新。具体操作如下所示： SQL insert into dept values(203, '203','203'); SQL select * from dept; SQL update dept set deptno=3;,索引管理,Oracle支持两种基本的索引类型：B*Tree索引和位映射索引。 B*Tree索引也是最常用的索引，它使用一个倒置的有序的树形结构来加速访问表中行的速度。 位映射索引常被用于那些包含较少唯一值的列。位映射索引总是对表中的每一行包含一条记录。记录的大小取决于索引列中唯一值的数量，因为位映射索引是由位的长字符串组成，如果列中的唯一值越多，需要位的长字符串就更长。,在表EMP中存在下面一些数据： SQL select empno, ename, deptno from emp; EMPNO ENAME DEPTNO - - - 3926 Chen de Jun 202 7101 Hao xin 703 3927 Zhan zou 302 2911 Ge hen Cao 703 1923 Bo Wei 410 5836 Jiang qin 503 4926 Luo qun 700 6627 Li tao 802 8312 Guo hua 410 9536 Shun Jian 503 8926 Liu quan 700,对ENAME字段建立B*Tree索引，则其倒置的树型结构如下所示：,B*Tree索引有以下几个特点： 它们保持数据有序，使得查询某一范围的记录更容易。 对于用复合列索引，可用前沿列来快速查询，即使那个查询并未引用索引中所有列。 它们自动保持平衡，由于所有的叶子结点在同一层，所以检索一条记录所需要的时间对索引中的全部数据是一致的。 性能保持相对连续，即使当被索引的表的大小增加时也如此。,雇员表 EMPNO 是否退休 状态 - - - 3926 Yes C 7101 Yes E 3927 No U 2911 No O 1923 Yes E,如果要在是否退休列上建立位映射索引，则其概念视图如下所示： EMPNO Yes No - - 3926 1 0 7101 1 0 3927 0 1 2911 0 1 1923 1 0,如果在状态列上建立位映射索引，则其概念视图如下所示： EMPNO C E U O - - 3926 1 0 0 0 7101 0 1 0 0 3927 0 0 1 0 2911 0 0 0 1 1923 0 1 0 0,从上面看出，如果所在列在只有2个唯一值“Yes/No”时，位映射索引只需要求2位的字符串就能表示，而当其有4个唯一值时，就至少需要有4位的字符串才能表示。 所以当列的唯一值很多时，会导致位映射索引很大。 所以对于那些有多个唯一值的列，不适合使用它们。 一个列中唯一值数量越多，位映射索引效率就越低。 另外位映射索引仅在Oracle企业版中可用。,创建索引,使用CREATE INDEX命令创建索引的语法如下所示： CREATE UNIQUE INDEX indx_name ON tablename(colnames) TABLESPACE tablespacename STORAGE (storage_setting) NOLOGGING NOSORT|REVERSE,UNIQUE：指定索引的每一条记录是唯一的。 TABLESPACE：指定索引的表空间。 STORAGE：指定存储参数，如果不指定将采用表空间默认的存储参数。 NOLOGGING：指定建立索引而不向数据REDO日志文件记录索引的建立工作。使用此参数可以提高创建速度，缺点是由于没写入日志文件，所以如果通过日志文件恢复数据库以后，将无法恢复此索引，必须重建。,NOSORT：指定创建索引时不用对表中索引数据进行排序。如果表中的数据已经被排序，可以使用此选项，如果在建立索引期间，发现表中的数据实际并未排序则将产生异常。 REVERSE：指定建立反向索引。所谓反向索引是在每个索引记录中反转字节，比如chendj的反向索引值是jdnehc。当有大量索引记录以相同的字母开始时，并且反向这些字母将删除那个集合时，可以考虑采用此选项；但如果本身正在查询一个范围内的数据时，就不适合使用REVERSE选项。,为表DEPT的DNAME字段建立一个索引的例子。 CREATE UNIQUE INDEX INDX_DEPT_DNAME ON DEPT (DNAME) TABLESPACE INDX STORAGE ( INITIAL 10K NEXT 20K MAXEXTENTS UNLIMITED) NOLOGGING REVERSE,建立B-树索引 Create unique index 模式.索引名称 on (模式.)表名称 (列名asc|desc ,列名asc|desc) Create index booknm_index on bookinfo(bookname); Create index authorinfo_index on authorinfo(birthdate,hometown,briefhistory); Create index book_press_index on bookinfo(pressname asc);,建立位映射索引 Create bitmap index 模式.索引名称 on (模式.)表名称 (列名asc|desc ,列名asc|desc),设有一个汽车数据表carinfo，记录非常多，有1000多万条记录，其中包含了大量的低基数列，如color(汽车颜色)、make(汽车品牌)、model(汽车型号)和year(出厂日期)等。这些列各自包含的值数目不超过100。 create bitmap index car_bmp_index on carinfo(color,make,model,year),查询索引信息,列出当前用户所拥有表中的所有索引的信息的命令： select index_name,index_type,uniqueness from user_indexes; 查询某个索引对应的表名、表列、升降序等信息 select index_name,table_name,column_name,column_position,descend from user_ind_columns where index_name=BOOKNM_INDEX;,修改索引,在建立索引后可以改变它，但是，在大多数情况下，仅限于改变其物理特点。不能向索引中增加列。 Oracle允许进行下列变化: 将一个索引移动到另一个表空间 使用不同的存储参数重建索引 对索引更名 重新分配不使用的空间或分配一个新的空间 标志此索引不可被使用 其实重新将一个索引定位到另一个表空间，实际上需要索引完全重建。只不过，Oracle使这个过程自动化了。 开始或终止对一个索引的使用情况进行监控。,可以使用ALTER INDEX命令来改变索引。 ALTER INDEX indx_dept_dname RENAME TO indx_dept_dname_new; 另外使用REBUILD子句可以进行许多物理变化。重建索引时，Oracle通过使用一套新的存储参数从临时本中重建索引。,通过重建indx_dept_dname索引来改变其参数： ALTER INDEX indx_dept_dname REBUILD TABLESPACE users NOLOGGING STORAGE(INITIAL 5K NEXT 5K); ALTER INDEX BOOK_PRESS_INDEX REBUILD TABLESPACE USERS ONLINE STORAGE(INITIAL 5K NEXT 5K); Tablespace users表明在users表空间里重建索引， Online指定在重建过程中希望用户访问表。,把索引BOOKNM_INDEX的名称改为BOOKNAME_INDEX ALTER INDEX BOOKNM_INDEX RENAME TO BOOKNAME_INDEX; SELECT index_name,table_name,column_name,descend From user_ind_columns where index_name=BOOKNAME_INDEX;,也可以在OEM中，通过可视化的GUI界面来修改一个索引。其创建的步骤如下所示： 启动OEM并注册到数据库。 打开索引文件夹，选取准备更改的索引。 右击此索引并从弹出菜单选择编辑。将弹出编辑窗口，其界面和新建索引类似。 修改相关信息后点“确认”，OEM将执行索引的修改操作。,删除索引,可以使用DROP INDEX命令从数据库中删除索引。 DROP INDEX INDX_DEPT_DNAME； DROP INDEX AUTHORINFO_INDEX; 不能够直接删除与主键或唯一键约束相关的索引。必须先删除相应的主键或唯一键约束。 在OEM中，可以通过在索引名字上右击并从弹出菜单中选择“移去”来删除一个索引。,聚簇,聚簇是(clusters)一种可选的数据库对象，它将经常在相同数据块中一起使用的表进行物理分组，使经常被同时访问的表在物理位置上可以存储在一起。 聚簇分为表簇(table cluster)和哈希簇(hash cluster)。,一个表簇由若干个共享着相同数据块的表构成，这些表具有一个或多个相同的表列并且常常一起使用。 同一个表簇中的各个表中相关的列称为簇键(cluster key)。簇键用一个簇索引(cluster index)来进行索引。对于簇中的多个表，簇键值只存储一次。,创建表簇之前的准备： 主要用于执行查询操作，而不是插入或更新操作。 表经常一起被查询或连接。,创建表簇,CREATE CLUSTER 模式.表簇名称(列名1 数据类型,表列名2 数据类型) 物理属性组句 SIZE 数目 K|M TABLESPACE 表空间名称 存储属性组句; size用于预计簇键和其相关的表列所需要的平均存储空间字节数。 估计能够装入一个成簇的数据块内的簇键个数。 限制了成簇数据块内簇键的个数，最大化优化了簇内键值的存储效率。 簇和簇索引可以创建在不同的表空间里。,在表bookinfo和authorinfo里都有表列authorname，把这两个表生成簇，oracle会把authorname表列放在同一个数据块里。建立表簇book_author，以authorname为簇键。 Create cluster book_author(authorname varchar2(20) pctused 80 pctfree 5 size 600 tablespace users storage(initial 200k next 300k minextents 2 maxextents 20 pctincrease 33);,创建表簇内的表(clustered tables),创建完表簇后，就可以在表簇内创建表了。 Create table bookinfo (bookno number(38) primary key, bookname varchar2(40) not null, isbn varchar2(20), bookkind varchar2(10), authorname varchar2(20) not null references authorinfo, ) Cluster book_author(authorname);,Create table authorinfo (authorname varchar2(20) primary key, birthdate date, hometown varchar2(40), briefhistory varchar2(200), contact varchar2(40) ) Cluster book_author(authorname);,创建簇索引(cluster indexes),在向簇内表插入数据之前，可以为簇建一个索引。 Create index 索引名称 on cluster 表簇名称 tablespace 表空间名称 存储属性组句;,为表簇book_author创建簇索引book_author_index。 Create index book_author_index on cluster book_author tablespace users storage (initial 50k next 50k minextents 2 maxextents 10 pctincrease 33) pctfree 5;,查看聚簇信息,列出当前用户所拥有的所有聚簇信息的命令 select cluster_name,tablespace_name,key_size, cluster_type From user_clusters;,修改聚簇信息,可修改已创建的表簇的如下属性： 物理属性(PCTFREE、PCTUSED、INITRANS、MAXTRANS和存储属性) 存储簇键信息所需的平均空间(即SIZE) 默认的并行度 修改表簇book_author的size为900 Alter cluster book_author Size 900; Select cluster_name,key_size,cluster_type From user_clusters Where cluster_name=BOOK_AUTHOR;,删除聚簇,如果一个簇里的各个表不再需要了，就可以删除这个簇。当删掉一个簇时，簇里的表和相应的簇索引同时也被删掉。 drop cluster stuenroll_cluster; 如果删除的簇中含有一个或多个表，想同时删除。 drop cluster 聚簇名称 including tables; 如果簇中的一个或多个表包含外键，参照了这个簇以外的表，想删除这个簇和内部的表。 drop cluster 聚簇名称 including table cascade constraints;,哈希簇,在哈希簇中，表是基于哈希值组织的，在表的主键值上使用哈希函数可以得到这个哈希值。,适用于哈希簇： 大多数查询均为等值查询 大小稳定 不适用于哈希簇 对表的查询返回结果不是单个值，而是一组键值 大小不稳定 应用程序,创建哈希簇,Create cluster 模式. 哈希簇名称 (列名1 数据类型 ,表列名2 数据类型) 物理属性组句 size 数目 k|m tablespace 表空间名称 存储属性组句 hash is hashkeys 数目; Hashkeys 数目用来指定哈希簇使用的哈希函数可以产生各不相同的哈希值的数量上限。 Hash is用来指定一个用户的哈希函数，否则oracle使用内部定义哈希函数。,不能够在哈希簇里创建簇键，也没有必要在哈希簇里创建索引。,创建一个表stuenroll登记学生注册信息，表大小比较稳定，学生序号stuno，经常被等值查询。 Create cluster stuenroll_cluster (stuno number(5,0) tablespace users storage (initial 250k next 50k minextents 1 maxextents 3 hash is stuno hashkeys 150; Hash is stuno表示直接采用stuno作为哈希值，无需任何哈希函数。如果簇键的取值各不相同，分布平均，可直接采用簇键值作为哈希值。,建完哈希簇后，就可以在簇里建表了 Create table stuenroll (stuno number(5,0) primary key, ) Cluster stuenroll_cluster(stuno);,修改哈希簇与表簇基本相同，但对于哈希簇，size、hash is和hashkeys属性均不能在alter cluster命令中修改。 如果想修改必须重新创建哈希簇，然后把数据信息从原来的哈希簇复制到新建的哈希簇里。,同义词管理,同义词是一个对象(表、视图、序列、过程、函数、包、快照或其它同义词)的一个代替的名字。 同义词能使多个用户使用同一个对象而不用将模式作为前缀在对象前面。通过使用CREATE SYNONYM和DROP SYNONYM命令来管理同义词。 对另一个数据对象而言同义词是一个别名。Oracle支持两种类型的同义词：公共的和专有的。其中公共同义词是针对所有用户的，相对而言专有同义词则只针对对象拥有者或被授予权限的用户。 同义词的另一个用途是能使两个应用程序用两个不同名字指向同一个表。,创建同义词,采用命令方式建立同义词 只要具有CREATE SYNONYM权限，就可以建立同义词，如果建立的是公共同义词，则需要有CREATE PUBLIC SYNONYM权限。 建立了同义词，并不意味着就能访问此同义词代表的对象。必须要由目标对象的所有者授予你相应的访问权限，此同义词才能被正确访问。 使用命令创建同义词的语法如下： CREATE PUBLIC SYNONYM schema.synonym_name FOR schema. objectdb_link;,为当前数据库bookcreator的表bookinfo建立公有同义词book。 create public synonym book for bookcreator.bookinfo; 查看book结构，看看与bookcreator.bookinfo表是否相同。 describe book; 创建同义词时，oracle不会检查其有效性，用户应自己检查。,在用户USER1下建立指向SCOTT用户下EMP表的同义词 SQL conn user1/user1 已连接。 SQL create synonym employee for scott.emp;,未授予权限时，测试结果 SQL select empno, ename from employee; select empno, ename from employee ERROR 位于第 1 行: ORA-00942: 表或视图不存在 虽然用户USER1已经建立了指向SCOTT用户下EMP表的同义词，但SCOTT用户并没有授予USER1访问表EMP的相关权限，所以在访问时出现上述错误。,授予权限 SQL conn scott/tiger 已连接。 SQL grant select on emp to user1; 授权成功。 再次访问同义词 SQL conn user1/user1; 已连接。 SQL select empno, ename from employee; EMPNO ENAME - - 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES,通过OEM建立同义词的步骤如下所示： 启动OEM并注册到数据库 右击同义词文件夹从弹出菜单中选择创建 用建立同义词所需要的信息填充“一般信息”选项卡 单击“创建”，OEM将执行创建同义词的操作。,删除同义词,DROP PUBLIC SYNONYM synonym_name; 如果删除公共同义词一定要包括PUBLIC关键字在内。 drop public synonym book; 如果要通过OEM企业管理器来删除同义词，只需要选中需要删除的同义词，右击它，从弹出的菜单中选择“移去”就能完成删除操作。,可以通过数据字典中的DBA_SYNONYMS、ALL_SYNONYMS和USER_SYNONYMS等视图查询有关同义词信息。 select * from all_synonyms where synonym_name=BOOK;,小结,本章主要介绍了视图、序列、索引和同义词的管理。其中视图通过隐藏基本表的复杂性来有助于快速访问数据。作为一种安全措施，视图也可以帮助用户分离数据。视图以一条SELECT语句存储在数据字典中，它们并不存储数据。 序列可以用来为主键生成唯一值，也可以用于审计跟踪。 Oracle支持两种索引，B*Tree索引和位映射索引。在数据库中通过索引的使用可以提高数据访问的效率。 同义词可以使开发者以短名称来引用表，从而提高开发效率，也可以简化应用程序从一种模式到另一模式的迁移。,