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

第八章视图、序列、索引和同义词,建立视图,视图的使用与基表类似，要有视图名及相应的列。生成一个视图可使用CREATEVIEW命令。其语法格式如下：CREATEORREPLACEFORCEVIEWviewnameASWITHREADONLY|WITHCHECKOPITION,ORREPLACE:允许新视图替换已存在的一个同名视图。FORCE:即使在基本表不存在或对这些表无访问权限时，也可建立视图。不过，这个视图在表被建立和被授权访问它们之前一直处于无效状态。WITHREADONLY:只允许查询访问视图。WITHCHECKOPITION：如果关联视图使用此选项建立，将完全不能插入，但能删除和修改。,CREATEORREPLACEFORCEVIEWTMP_VIEWASSELECTa.empno,a.ename,b.dnameFROMempa,DEPTbwherea.deptno=b.deptno;,使用视图,一旦建立了一个视图，就可以把它当作一个表从中查询数据。CREATEORREPLACEFORCEVIEWTMP_VIEWASSELECTa.empno,a.ename,b.dnameFROMempa,DEPTbwherea.deptno=b.deptno;select*fromtmp_view;,Oraclec允许对视图执行UPDATE、INSERT和DELETE语句。如果一个视图包括了基本表中的所有列，并仅有一个WHERE子句来限制那些返回的行，那么Oracle将允许更新这个视图。对于没有保存至少一个基本表的主键的那些视图是不能被更新的。另外一些处理汇部数据、基于表达式的列、联合和某种关联的视图也不能被更新。,比如：带有INTERSECT、UNION或MINUS等集合操作符的视图带有GROUPBY、CONNECTBY或STARTWITH子句的视图带有AVG、SUM或MAX等组函数的视图带有DISTINCT的功能的视图；如果不清楚指定的视图是否可以更新，或可以更新哪些列，可以查询USER_UPDATABLE_COLUMNS数据字典视图。,例如查询上例中建立的视图TMP_VIEW是否可以更新可以执行如下操作：selectcolumn_name,updatablefromuser_updatable_columnswheretable_name=TMP_VIEW;,重新编译视图,任何时间改变一个视图的查询所引用的对象时，Oracle将标记该视图无效。再次使用视图前它必须被重新编译。从新编译一个视图语法如下：ALTERVIEWviewnameCOMPILE；alterviewbookauthor_viewcompile;alterviewbookonwebcompile;,重建视图,修改视图所能处理的操作只是重新编辑视图。若想改变视图的定义，必须重建视图。若想修改视图bookonweb的定义为所有上网的且出版日期在1995年1月1日之后的书目信息，需要重建视图bookonweb。createorreplaceviewbookonwebasselect*frombookinfowherestatus=yandpublishtime1-1月-95withreadonly;,删除视图,可以使用DROPVIEW命令从数据库中删除视图。其格式为：dropviewschema.viewname;dropviewbookauthor_view也可以使用OEM通过右击视图从弹出菜单中选择“删除”来删除一个视图。,查看视图信息,视图创建后，可以通过数据字典中的一些有关视图信息的视图来查看视图。,查询用户bookcreator所拥有的全部视图的名称、类型selectview_namefromall_viewswhereowner=BOOKCREATOR;,序列管理,Oracle序列是一个连续的数字生成器。比如希望表的主键是一个数字，并且希望每次向表中插入新行时那个数字能自动增加，或正在建立审计跟踪时，需要知道生成这些审计跟踪记录的确切的顺序，而靠一个时间戳通常不足以实现这些功能。序列只存在于数据字典中。序列号可以被设置为上升、下降，可以没有限制或重复使用直到一个限制值。,创建序列,创建序列使用CREATESEQUENCE语句。其语法格式如下：CREATESEQUENCEschema.sequencenameKEYWORD下面的例子将创建一个序列，初始值是1，并且每次访问自动增加1。SQLCREATESEQUENCEsqc_plan_key2STARTWITH13INCREMENTBY14ORDER5NOCYCLE;,STARTWITH1用于指定初始值是1。INCREMENTBY1用于指定每次访问自动增加1，如果INCREMENTBY后面跟一个负值，则序列将是降序的。ORDER用于保证每个序列的值都将比先前的值大。使用NOCYCLE设置序列值在达到限制值以后不能重复，这是缺省设置。当试图产生MAXVALUE+1的值时，将会产生一个异常。,其它关键字还有：MINVALUE：定义序列可以生成的最小值，这是降序序列中的限制值。缺省情况下该值为NOMINVALUE，对于升序为1，对于降序为-10E26。MAXVALUE：序列能生成的最大数字。这是升序序列中的限制值，缺省的MAXVALUE为NOMAXVALUE，对于升序为10E26，对于降序为-1。CYCLE：设置序列值在达到限制值以后可以重复。CACHE：定义序列值占据的内存块的大小，缺省值为20。NOCACHE：在每次序列号产生时强制数据字典更新，保证在序列值之间没有间隔当创建序列时，STARTWITH值必须等于或大于MINVALUE。,改变序列,改变序列的办法也有两种，一种是通过命令行ALTERSEQUENCE命令改变序列的选项。另一种是通过在OEM中右击序列名，从弹出菜单中选择“编辑”来进行改变。对于序列的当前值是无法更改的，如果应用中确有这种要求，那么只能删除此序列后，再使用STARTWITH子句重建此序列。,修改一个序列，使用ALTERSEQUENCE实现。如修改序列stuenno的cache属性为30，且不可循环使用ALTERSEQUENCEstuennonocyclecache30；,删除序列,使用DROPSEQUENCE命令来删除一个序列。其语法格式为：DROPSEQUENCEsequence_name也可以通过OEM来删除序列。简单地右击想删除的序列，并从弹出的菜单中选择“移去”。当一个序列被删除后，其相关的权限也理所当然的被删除了，但建立在其上的同义词或触发器将会被保留，只不过处于无效状态，不能被使用。,序列的应用,在Oracle中建立自动增加字段，需要执行下面的操作：定义需要作为数字增加的列希望表DEPT的主键字段DEPTNO，每次插入行时自动增加1。表DEPT的结构如下所示：名称是否为空?类型-DEPTNONOTNULLNUMBER(6)DNAMEVARCHAR2(30)LOCVARCHAR2(60),注意：DEPTNO是主键，其值类型是NUMBER型，且长度是6位。所以将要建立的序列的最大值不能超过999999。CREATESEQUENCEsqc_dept_keyNOCYCLEMAXVALUE999999STARTWITH1;,可通过nextval和currval伪列来引用序列，每引用一次序列的伪列nextval，就会按照序列的定义产生一个新的序列码；通过序列的伪列currval可以反复利用当前的序列码。,可以在如下场合使用序列的nextval和currval伪列：Insert语句的values子句中Select语句中的前面选择的表列名中Update语句中的set子句,在如下场合不能够使用nextval或currval伪列：子查询视图或快照的定义查询带有distinct操作符的select语句带有groupby或orderby子句的select语句通过union、intersect或minus与另一个select语句相结合的select语句中Select语句的where子句中Createtable或altertable语句中的一个表列的default值定义中检查约束的条件中,想了解一个已存在的序列信息，可以通过数据字典DBA_SEQUENCES、ALL_SEQUENCES和USER_SEQUENCES等几个视图来查看。,无论什么时候当插入一行时，从序列中定一个触发器来设置列的值在DEPT表中建立插入触发器。使得每次插入一行时，DEPTNO字段自动通过访问序列sqc_dept_key来获取值。,CREATEORREPLACETRIGGERtri_dept_setdeptnoBEFOREINSERTONdeptFOREACHROWDECLAREp_nextnodept.deptno%type;BEGINSELECTsqc_dept_key.NEXTVALINTOp_nextnoFROMdual;:new.deptno:=p_nextno;END;/,在建立上面的触发器后，DEPT表中的字段DEPTNO总是使用所建立的序列来设置。即使插入语句为DEPTNO指定了一些其它的值，也是这样。定义一个触发器来防止更新此列。有了前面3步已经实现了对DEPT表主键自动增加的功能，但如，有人更新了一条已经存在的记录，并将主键值改变为一个可能和将来插入相冲突的值该怎么办。所以这个值不能被更新，下面定义一个触发器来防止此列被更新。,CREATEORREPLACETRIGGERtri_dept_updeptnoBEFOREUPDATEOFdeptnoONdeptFOREACHROWBEGINRAISE_APPLICATION_ERROR(-20000,不能更新自动增加类型的主键值!);END;/,测试结果：首先向表DEPT中输入一行数据，查询其结果；然后再将此行数据字段DEPTNO更新。具体操作如下所示：SQLinsertintodeptvalues(203,203,203);SQLselect*fromdept;SQLupdatedeptsetdeptno=3;,索引管理,Oracle支持两种基本的索引类型：B*Tree索引和位映射索引。B*Tree索引也是最常用的索引，它使用一个倒置的有序的树形结构来加速访问表中行的速度。位映射索引常被用于那些包含较少唯一值的列。位映射索引总是对表中的每一行包含一条记录。记录的大小取决于索引列中唯一值的数量，因为位映射索引是由位的长字符串组成，如果列中的唯一值越多，需要位的长字符串就更长。,在表EMP中存在下面一些数据：SQLselectempno,ename,deptnofromemp;EMPNOENAMEDEPTNO-3926ChendeJun2027101Haoxin7033927Zhanzou3022911GehenCao7031923BoWei4105836Jiangqin5034926Luoqun7006627Litao8028312Guohua4109536ShunJian5038926Liuquan700,对ENAME字段建立B*Tree索引，则其倒置的树型结构如下所示：,B*Tree索引有以下几个特点：它们保持数据有序，使得查询某一范围的记录更容易。对于用复合列索引，可用前沿列来快速查询，即使那个查询并未引用索引中所有列。它们自动保持平衡，由于所有的叶子结点在同一层，所以检索一条记录所需要的时间对索引中的全部数据是一致的。性能保持相对连续，即使当被索引的表的大小增加时也如此。,雇员表EMPNO是否退休状态-3926YesC7101YesE3927NoU2911NoO1923YesE,如果要在是否退休列上建立位映射索引，则其概念视图如下所示：EMPNOYesNo-392610710110392701291101192310,如果在状态列上建立位映射索引，则其概念视图如下所示：EMPNOCEUO-3926100071010100392700102911000119230100,从上面看出，如果所在列在只有2个唯一值“Yes/No”时，位映射索引只需要求2位的字符串就能表示，而当其有4个唯一值时，就至少需要有4位的字符串才能表示。所以当列的唯一值很多时，会导致位映射索引很大。所以对于那些有多个唯一值的列，不适合使用它们。一个列中唯一值数量越多，位映射索引效率就越低。另外位映射索引仅在Oracle企业版中可用。,创建索引,使用CREATEINDEX命令创建索引的语法如下所示：CREATEUNIQUEINDEXindx_nameONtablename(colnames)TABLESPACEtablespacenameSTORAGE(storage_setting)NOLOGGINGNOSORT|REVERSE,UNIQUE：指定索引的每一条记录是唯一的。TABLESPACE：指定索引的表空间。STORAGE：指定存储参数，如果不指定将采用表空间默认的存储参数。NOLOGGING：指定建立索引而不向数据REDO日志文件记录索引的建立工作。使用此参数可以提高创建速度，缺点是由于没写入日志文件，所以如果通过日志文件恢复数据库以后，将无法恢复此索引，必须重建。,NOSORT：指定创建索引时不用对表中索引数据进行排序。如果表中的数据已经被排序，可以使用此选项，如果在建立索引期间，发现表中的数据实际并未排序则将产生异常。REVERSE：指定建立反向索引。所谓反向索引是在每个索引记录中反转字节，比如chendj的反向索引值是jdnehc。当有大量索引记录以相同的字母开始时，并且反向这些字母将删除那个集合时，可以考虑采用此选项；但如果本身正在查询一个范围内的数据时，就不适合使用REVERSE选项。,为表DEPT的DNAME字段建立一个索引的例子。CREATEUNIQUEINDEXINDX_DEPT_DNAMEONDEPT(DNAME)TABLESPACEINDXSTORAGE(INITIAL10KNEXT20KMAXEXTENTSUNLIMITED)NOLOGGINGREVERSE,建立B-树索引Createuniqueindex模式.索引名称on(模式.)表名称(列名asc|desc,列名asc|desc)Createindexbooknm_indexonbookinfo(bookname);Createindexauthorinfo_indexonauthorinfo(birthdate,hometown,briefhistory);Createindexbook_press_indexonbookinfo(pressnameasc);,建立位映射索引Createbitmapindex模式.索引名称on(模式.)表名称(列名asc|desc,列名asc|desc),设有一个汽车数据表carinfo，记录非常多，有1000多万条记录，其中包含了大量的低基数列，如color(汽车颜色)、make(汽车品牌)、model(汽车型号)和year(出厂日期)等。这些列各自包含的值数目不超过100。createbitmapindexcar_bmp_indexoncarinfo(color,make,model,year),查询索引信息,列出当前用户所拥有表中的所有索引的信息的命令：selectindex_name,index_type,uniquenessfromuser_indexes;查询某个索引对应的表名、表列、升降序等信息selectindex_name,table_name,column_name,column_position,descendfromuser_ind_columnswhereindex_name=BOOKNM_INDEX;,修改索引,在建立索引后可以改变它，但是，在大多数情况下，仅限于改变其物理特点。不能向索引中增加列。Oracle允许进行下列变化:将一个索引移动到另一个表空间使用不同的存储参数重建索引对索引更名重新分配不使用的空间或分配一个新的空间标志此索引不可被使用其实重新将一个索引定位到另一个表空间，实际上需要索引完全重建。只不过，Oracle使这个过程自动化了。开始或终止对一个索引的使用情况进行监控。,可以使用ALTERINDEX命令来改变索引。ALTERINDEXindx_dept_dnameRENAMETOindx_dept_dname_new;另外使用REBUILD子句可以进行许多物理变化。重建索引时，Oracle通过使用一套新的存储参数从临时本中重建索引。,通过重建indx_dept_dname索引来改变其参数：ALTERINDEXindx_dept_dnameREBUILDTABLESPACEusersNOLOGGINGSTORAGE(INITIAL5KNEXT5K);ALTERINDEXBOOK_PRESS_INDEXREBUILDTABLESPACEUSERSONLINESTORAGE(INITIAL5KNEXT5K);Tablespaceusers表明在users表空间里重建索引，Online指定在重建过程中希望用户访问表。,把索引BOOKNM_INDEX的名称改为BOOKNAME_INDEXALTERINDEXBOOKNM_INDEXRENAMETOBOOKNAME_INDEX;SELECTindex_name,table_name,column_name,descendFromuser_ind_columnswhereindex_name=BOOKNAME_INDEX;,也可以在OEM中，通过可视化的GUI界面来修改一个索引。其创建的步骤如下所示：启动OEM并注册到数据库。打开索引文件夹，选取准备更改的索引。右击此索引并从弹出菜单选择编辑。将弹出编辑窗口，其界面和新建索引类似。修改相关信息后点“确认”，OEM将执行索引的修改操作。,删除索引,可以使用DROPINDEX命令从数据库中删除索引。DROPINDEXINDX_DEPT_DNAME；DROPINDEXAUTHORINFO_INDEX;不能够直接删除与主键或唯一键约束相关的索引。必须先删除相应的主键或唯一键约束。在OEM中，可以通过在索引名字上右击并从弹出菜单中选择“移去”来删除一个索引。,聚簇,聚簇是(clusters)一种可选的数据库对象，它将经常在相同数据块中一起使用的表进行物理分组，使经常被同时访问的表在物理位置上可以存储在一起。聚簇分为表簇(tablecluster)和哈希簇(hashcluster)。,一个表簇由若干个共享着相同数据块的表构成，这些表具有一个或多个相同的表列并且常常一起使用。同一个表簇中的各个表中相关的列称为簇键(clusterkey)。簇键用一个簇索引(clusterindex)来进行索引。对于簇中的多个表，簇键值只存储一次。,创建表簇之前的准备：主要用于执行查询操作，而不是插入或更新操作。表经常一起被查询或连接。,创建表簇,CREATECLUSTER模式.表簇名称(列名1数据类型,表列名2数据类型)物理属性组句SIZE数目K|MTABLESPACE表空间名称存储属性组句;size用于预计簇键和其相关的表列所需要的平均存储空间字节数。估计能够装入一个成簇的数据块内的簇键个数。限制了成簇数据块内簇键的个数，最大化优化了簇内键值的存储效率。簇和簇索引可以创建在不同的表空间里。,在表bookinfo和authorinfo里都有表列authorname，把这两个表生成簇，oracle会把authorname表列放在同一个数据块里。建立表簇book_author，以authorname为簇键。Createclusterbook_author(authornamevarchar2(20)pctused80pctfree5size600tablespaceusersstorage(initial200knext300kminextents2maxextents20pctincrease33);,创建表簇内的表(clusteredtables),创建完表簇后，就可以在表簇内创建表了。Createtablebookinfo(booknonumber(38)primarykey,booknamevarchar2(40)notnull,isbnvarchar2(20),bookkindvarchar2(10),authornamevarchar2(20)notnullreferencesauthorinfo,)Clusterbook_author(authorname);,Createtableauthorinfo(authornamevarchar2(20)primarykey,birthdatedate,hometownvarchar2(40),briefhistoryvarchar2(200),contactvarchar2(40)Clusterbook_author(authorname);,创建簇索引(clusterindexes),在向簇内表插入数据之前，可以为簇建一个索引。Createindex索引名称oncluster表簇名称tablespace表空间名称存储属性组句;,为表簇book_author创建簇索引book_author_index。Createindexbook_author_indexonclusterbook_authortablespaceusersstorage(initial50knext50kminextents2maxextents10pctincrease33)pctfree5;,查看聚簇信息,列出当前用户所拥有的所有聚簇信息的命令selectcluster_name,tablespace_name,key_size,cluster_typeFromuser_clusters;,修改聚簇信息,可修改已创建的表簇的如下属性：物理属性(PCTFREE、PCTUSED、INITRANS、MAXTRANS和存储属性)存储簇键信息所需的平均空间(即SIZE)默认的并行度修改表簇book_author的size为900Alterclusterbook_authorSize900;Selectcluster_name,key_size,cluster_typeFromuser_clustersWherecluster_name=BOOK_AUTHOR;,删除聚簇,如果一个簇里的各个表不再需要了，就可以删除这个簇。当删掉一个簇时，簇里的表和相应的簇索引同时也被删掉。dropclusterstuenroll_cluster;如果删除的簇中含有一个或多个表，想同时删除。dropcluster聚簇名称includingtables;如果簇中的一个或多个表包含外键，参照了这个簇以外的表，想删除这个簇和内部的表。dropcluster聚簇名称includingtablecascadeconstraints;,哈希簇,在哈希簇中，表是基于哈希值组织的，在表的主键值上使用哈希函数可以得到这个哈希值。,适用于哈希簇：大多数查询均为等值查询大小稳定不适用于哈希簇对表的查询返回结果不是单个值，而是一组键值大小不稳定应用程序,创建哈希簇,Createcluster模式.哈希簇名称(列名1数据类型,表列名2数据类型)物理属性组句size数目k|mtablespace表空间名称存储属性组句hashishashkeys数目;Hashkeys数目用来指定哈希簇使用的哈希函数可以产生各不相同的哈希值的数量上限。Hashis用来指定一个用户的哈希函数，否则oracle使用内部定义哈希函数。,不能够在哈希簇里创建簇键，也没有必要在哈希簇里创建索引。,创建一个表stuenroll登记学生注册信息，表大小比较稳定，学生序号stuno，经常被等值查询。Createclusterstuenroll_cluster(stunonumber(5,0)tablespaceusersstorage(initial250knext50kminextents1maxextents3hashisstunohashkeys150;Hashisstuno表示直接采用stuno作为哈希值，无需任何哈希函数。如果簇键的取值各不相同，分布平均，可直接采用簇键值作为哈希值。,建完哈希簇后，就可以在簇里建表了Createtablestuenroll(stunonumber(5,0)primarykey,)Clusterstuenroll_cluster(stuno);,修改哈希簇与表簇基本相同，但对于哈希簇，size、hashis和hashkeys属性均不能在altercluster命令中修改。如果想修改必须重新创建哈希簇，然后把数据信息从原来的哈希簇复制到新建的哈希簇里。,同义词管理,同义词是一个对象(表、视图、序列、过程、函数、包、快照或其它同义词)的一个代替的名字。同义词能使多个用户使用同一个对象而不用将模式作为前缀在对象前面。通过使用CREATESYNONYM和DROPSYNONYM命令来管理同义词。对另一个数据对象而言同义词是一个别名。Oracle支持两种类型的同义词：公共的和专有的。其中公共同义词是针对所有用户的，相对而言专有同义词则只针对对象拥有者或被授予权限的用户。同义词的另一个用途是能使两个应用程序用两个不同名字指向同一个表。,创建同义词,采用命令方式建立同义词只要具有CREATESYNONYM权限，就可以建立同义词，如果建立的是公共同义词，则需要有CREATEPUBLICSYNONYM权限。建立了同义词，并不意味着就能访问此同义词代表的对象。必须要由目标对象的所有者授予你相应的访问权限，此同义词才能被正确访问。使用命令创建同义词的语法如下：CREATEPUBLICSYNONYMschema.synonym_nameFORschema.objectdb_link;,为当前数据库bookcreator的表bookinfo建立公有同义词book。createpublicsynonymbookforbookcreator.bookinfo;查看book结构，看看与bookcreator.bookinfo表是否相同。describebook;创建同义词时，oracle不会检查其有效性，用户应自己检查。,在用户USER1下建立指向SCOTT用户下EMP表的同义词SQLconnuser1/user1已连接。SQLcreatesynonymemployeeforscott.emp;,未授予权限时，测试结果SQLselectempno,enamefromemployee;selectempno,enamefromemployeeERROR位于第1行:ORA-00942:表或视图不存在虽然用户USER1已经建立了指向SCOTT用户下EMP表的同义词，但SCOTT用户并没有授予USER1访问表EMP的相关权限，所以在访问时出现上述错误。,授予权限SQLconnscott/tiger已连接。SQLgrantselectonemptouser1;授权成功。再次访问同义词SQLconnuser1/user1;已连接。SQLselectempno,enamefromemployee;EMPNOENAME-7369SMITH7499ALLEN7521WARD7566JONES,通过OEM建立同义词的步骤如下所示：启动OEM并注册到数据库右击同义词文件夹从弹出菜单中选择创建用建立同义词所需要的信息填充“一般信息”选项卡单击“创建”，OEM将执行创建同义词的操作。,删除同义词,DROPPUBLICSYNONYMsynonym_name;如果删除公共同义词一定要包括PUBLIC关键字在内。droppublicsynonymbook;如果要通过OEM企业管理器来删除同义词，只需要选中需要删除的同义词，右击它，从弹出的菜单中选择“移去”就能完成删除操作。,可以通过数据字典中的DBA_SYNONYMS、ALL_SYNONYMS和USER_SYNONYMS等视图查询有关同义词信息。select*fromall_synonymswheresynonym_name=BOOK;,小结,本章主要介绍了视图、序列、索引和同义词的管理。其中视图通过隐藏基本表的复杂性来有助于快速访问数据。作为一种安全措施，视图也可以帮助用户分离数据。视图以一条SELECT语句存储在数据字典中，它们并不存储数据。序列可以用来为主键生成唯一值，也可以用于审计跟踪。Oracle支持两种索引，B*Tree索引和位映射索引。在数据库中通过索引的使用可以提高数据访问的效率。同义词可以使开发者以短名称来引用表，从而提高开发效率，也可以简化应用程序从一种模式到另一模式的迁移。,