《Oracle9i第9章视图、序列、索引和同义词.ppt》由会员分享，可在线阅读，更多相关《Oracle9i第9章视图、序列、索引和同义词.ppt（87页珍藏版）》请在金锄头文库上搜索。
1、第八章第八章 视图、序列、索引和同义词视图、序列、索引和同义词建立视图建立视图n视图的使用与基表类似，要有视图名及相应的列。生成一个视视图的使用与基表类似，要有视图名及相应的列。生成一个视图可使用图可使用CREATE VIEWCREATE VIEW命令。其语法格式如下：命令。其语法格式如下：CREATE OR REPLACE FORCE VIEW viewname AS CREATE OR REPLACE FORCE VIEW viewname AS SELECT WITH READ ONLY|WITH CHECK OPITIONWITH READ ONLY|WITH CHECK OPITIO
2、NnOR REPLACE:OR REPLACE:允许新视图替换已存在的一个同名视图。允许新视图替换已存在的一个同名视图。nFORCE:FORCE:即使在基本表不存在或对这些表无访问权限时，也可建即使在基本表不存在或对这些表无访问权限时，也可建立视图。不过，这个视图在表被建立和被授权访问它们之前一立视图。不过，这个视图在表被建立和被授权访问它们之前一直处于无效状态。直处于无效状态。nWITH READ ONLY:WITH READ ONLY:只允许查询访问视图。只允许查询访问视图。nWITH CHECK OPITIONWITH CHECK OPITION：如果关联视图使用此选项建立，将完全：如果
3、关联视图使用此选项建立，将完全不能插入，但能删除和修改。不能插入，但能删除和修改。CREATE OR REPLACE FORCE VIEW TMP_VIEW CREATE OR REPLACE FORCE VIEW TMP_VIEW AS AS SELECT a.empno, a.ename, b.dname SELECT a.empno, a.ename, b.dname FROM emp a, DEPT b FROM emp a, DEPT b where a.deptno=b.deptno;where a.deptno=b.deptno;使用视图使用视图n一旦建立了一个视图，就可以把它当
4、作一个表从中查询数据。一旦建立了一个视图，就可以把它当作一个表从中查询数据。CREATE OR REPLACE FORCE VIEW TMP_VIEW CREATE OR REPLACE FORCE VIEW TMP_VIEW AS AS SELECT a.empno, a.ename, b.dname SELECT a.empno, a.ename, b.dname FROM emp a, DEPT b FROM emp a, DEPT b where a.deptno=b.deptno;where a.deptno=b.deptno;select * from tmp_view;selec
5、t * from tmp_view;nOraclecOraclec允许对视图执行允许对视图执行UPDATEUPDATE、INSERTINSERT和和DELETEDELETE语句。语句。n如果一个视图包括了基本表中的所有列，并仅有一个如果一个视图包括了基本表中的所有列，并仅有一个WHEREWHERE子句子句来限制那些返回的行，那么来限制那些返回的行，那么OracleOracle将允许更新这个视图。将允许更新这个视图。n对于没有保存至少一个基本表的主键的那些视图是不能被更新对于没有保存至少一个基本表的主键的那些视图是不能被更新的。另外一些处理汇部数据、基于表达式的列、联合和某种关的。另外一些处理汇
6、部数据、基于表达式的列、联合和某种关联的视图也不能被更新。联的视图也不能被更新。 比如：比如：n带有带有INTERSECTINTERSECT、UNIONUNION或或MINUSMINUS等集合操作符的视图等集合操作符的视图n带有带有GROUP BYGROUP BY、CONNECT BYCONNECT BY或或START WITHSTART WITH子句的视图子句的视图n带有带有AVGAVG、SUMSUM或或MAXMAX等组函数的视图等组函数的视图n带有带有DISTINCTDISTINCT的功能的视图；的功能的视图；n如果不清楚指定的视图是否可以更新，或可以更新哪些列，可如果不清楚指定的视图是否
7、可以更新，或可以更新哪些列，可以查询以查询USER_UPDATABLE_COLUMNSUSER_UPDATABLE_COLUMNS数据字典视图。数据字典视图。 n例如查询上例中建立的视图例如查询上例中建立的视图TMP_VIEWTMP_VIEW是否可以更新可是否可以更新可以执行如下操作：以执行如下操作： select column_name, updatable select column_name, updatable from user_updatable_columns from user_updatable_columns where table_name=TMP_VIEW;where
8、table_name=TMP_VIEW;重新编译视图重新编译视图n任何时间改变一个视图的查询所引用的对象时，任何时间改变一个视图的查询所引用的对象时，OracleOracle将标记将标记该视图无效。再次使用视图前它必须被重新编译。从新编译一该视图无效。再次使用视图前它必须被重新编译。从新编译一个视图语法如下：个视图语法如下：ALTER VIEW viewname COMPILEALTER VIEW viewname COMPILE；alter view bookauthor_view compile;alter view bookauthor_view compile;alter view b
9、ookonweb compile;alter view bookonweb compile;重建视图重建视图n修改视图所能处理的操作只是重新编辑视图。若想改变视图修改视图所能处理的操作只是重新编辑视图。若想改变视图的定义，必须重建视图。的定义，必须重建视图。n若想修改视图若想修改视图bookonwebbookonweb的定义为所有上网的且出版日期在的定义为所有上网的且出版日期在19951995年年1 1月月1 1日之后的书目信息，需要重建视图日之后的书目信息，需要重建视图bookonwebbookonweb。create or replace view bookonwebcreate or r
10、eplace view bookonwebas select * from bookinfoas select * from bookinfowhere status=where status=y y and publishtime and publishtime1-11-1月月-95-95with read only;with read only;删除视图删除视图n可以使用可以使用DROP VIEWDROP VIEW命令从数据库中删除视图。其格式为：命令从数据库中删除视图。其格式为：drop view schema.viewname;drop view schema.viewname;dro
11、p view bookauthor_viewdrop view bookauthor_viewn也可以使用也可以使用OEMOEM通过右击视图从弹出菜单中选择通过右击视图从弹出菜单中选择“删除删除”来删除一来删除一个视图。个视图。 n查询用户查询用户bookcreatorbookcreator所拥有的全部视图的名称、类型所拥有的全部视图的名称、类型select view_nameselect view_namefrom all_viewsfrom all_viewswhere owner=where owner=BOOKCREATORBOOKCREATOR; ;序列管理序列管理nOracleOr
12、acle序列是一个连续的数字生成器。序列是一个连续的数字生成器。n比如希望表的主键是一个数字，并且希望每次向表中插入新行比如希望表的主键是一个数字，并且希望每次向表中插入新行时那个数字能自动增加，或正在建立审计跟踪时，需要知道生时那个数字能自动增加，或正在建立审计跟踪时，需要知道生成这些审计跟踪记录的确切的顺序，而靠一个时间戳通常不足成这些审计跟踪记录的确切的顺序，而靠一个时间戳通常不足以实现这些功能。以实现这些功能。n序列只存在于数据字典中。序列号可以被设置为上升、下降，序列只存在于数据字典中。序列号可以被设置为上升、下降，可以没有限制或重复使用直到一个限制值。可以没有限制或重复使用直到一个
13、限制值。创建序列创建序列创建序列使用创建序列使用CREATE SEQUENCECREATE SEQUENCE语句。其语法格式如下：语句。其语法格式如下：CREATE SEQUENCE schema.sequencename KEYWORDCREATE SEQUENCE schema.sequencename KEYWORD下面的例子将创建一个序列，初始值是下面的例子将创建一个序列，初始值是1 1，并且每次访问自动增加，并且每次访问自动增加1 1。SQL CREATE SEQUENCE sqc_plan_keySQL CREATE SEQUENCE sqc_plan_key 2 START WI
14、TH 1 2 START WITH 1 3 INCREMENT BY 1 3 INCREMENT BY 1 4 ORDER 4 ORDER 5 NOCYCLE; 5 NOCYCLE;nSTART WITH 1START WITH 1用于指定初始值是用于指定初始值是1 1。nINCREMENT BY 1INCREMENT BY 1用于指定每次访问自动增加用于指定每次访问自动增加1 1，如果，如果INCREMENT INCREMENT BYBY后面跟一个负值，则序列将是降序的。后面跟一个负值，则序列将是降序的。nORDERORDER用于保证每个序列的值都将比先前的值大。用于保证每个序列的值都将比先
15、前的值大。n使用使用NOCYCLENOCYCLE设置序列值在达到限制值以后不能重复，这是缺省设置序列值在达到限制值以后不能重复，这是缺省设置。当试图产生设置。当试图产生MAXVALUE+1MAXVALUE+1的值时，将会产生一个异常。的值时，将会产生一个异常。 n其它关键字还有：其它关键字还有：MINVALUEMINVALUE：定义序列可以生成的最小值，这是降序序列中的：定义序列可以生成的最小值，这是降序序列中的限制值。缺省情况下该值为限制值。缺省情况下该值为NOMINVALUENOMINVALUE，对于升序为，对于升序为1 1，对于，对于降序为降序为-10E26-10E26。MAXVALUE
16、MAXVALUE：序列能生成的最大数字。这是升序序列中的限制：序列能生成的最大数字。这是升序序列中的限制值，缺省的值，缺省的MAXVALUEMAXVALUE为为NOMAXVALUENOMAXVALUE，对于升序为，对于升序为10E2610E26，对于，对于降序为降序为-1-1。CYCLECYCLE：设置序列值在达到限制值以后可以重复。：设置序列值在达到限制值以后可以重复。CACHECACHE：定义序列值占据的内存块的大小，缺省值为：定义序列值占据的内存块的大小，缺省值为2020。NOCACHENOCACHE：在每次序列号产生时强制数据字典更新，保证在序：在每次序列号产生时强制数据字典更新，保证
17、在序列值之间没有间隔当创建序列时，列值之间没有间隔当创建序列时，START WITHSTART WITH值必须等于或值必须等于或大于大于MINVALUEMINVALUE。改变序列改变序列n改变序列的办法也有两种，一种是通过命令行改变序列的办法也有两种，一种是通过命令行ALTER SEQUENCEALTER SEQUENCE命令改变序列的选项。命令改变序列的选项。n另一种是通过在另一种是通过在OEMOEM中右击序列名，从弹出菜单中选择中右击序列名，从弹出菜单中选择“编辑编辑”来来进行改变。进行改变。n对于序列的当前值是无法更改的，如果应用中确有这种要求，对于序列的当前值是无法更改的，如果应用中确
18、有这种要求，那么只能删除此序列后，再使用那么只能删除此序列后，再使用START WITHSTART WITH子句重建此序列。子句重建此序列。 n修改一个序列，使用修改一个序列，使用ALTER SEQUENCEALTER SEQUENCE实现。实现。n如修改序列如修改序列stuennostuenno的的cachecache属性为属性为3030，且不可循环使用，且不可循环使用ALTER SEQUENCE stuennoALTER SEQUENCE stuennonocyclenocyclecache 30cache 30；删除序列删除序列n使用使用DROP SEQUENCEDROP SEQUENC
19、E命令来删除一个序列。其语法格式为：命令来删除一个序列。其语法格式为：DROP SEQUENCE sequence_nameDROP SEQUENCE sequence_namen也可以通过也可以通过OEMOEM来删除序列。简单地右击想删除的序列，并从弹来删除序列。简单地右击想删除的序列，并从弹出的菜单中选择出的菜单中选择“移去移去”。n当一个序列被删除后，其相关的权限也理所当然的被删除了，当一个序列被删除后，其相关的权限也理所当然的被删除了，但建立在其上的同义词或触发器将会被保留，只不过处于无效但建立在其上的同义词或触发器将会被保留，只不过处于无效状态，不能被使用。状态，不能被使用。序列的应
20、用序列的应用n在在OracleOracle中建立自动增加字段，需要执行下面的操作：中建立自动增加字段，需要执行下面的操作：定义需要作为数字增加的列定义需要作为数字增加的列n希望表希望表DEPTDEPT的主键字段的主键字段DEPTNODEPTNO，每次插入行时自动增加，每次插入行时自动增加1 1。表表DEPTDEPT的结构如下所示：的结构如下所示：名称名称 是否为空是否为空? ? 类型类型- - - - -DEPTNO NOT NULL NUMBER(6)DEPTNO NOT NULL NUMBER(6)DNAME VARCHAR2(30)DNAME VARCHAR2(30)LOC VARCHA
21、R2(60)LOC VARCHAR2(60)n注意：注意：DEPTNODEPTNO是主键，其值类型是是主键，其值类型是NUMBERNUMBER型，且长度是型，且长度是6 6位。所位。所以将要建立的序列的最大值不能超过以将要建立的序列的最大值不能超过999999999999。 CREATE SEQUENCE sqc_dept_keyCREATE SEQUENCE sqc_dept_keyNOCYCLENOCYCLEMAXVALUE 999999MAXVALUE 999999START WITH 1;START WITH 1;n可通过可通过nextvalnextval和和currvalcurrva
22、l伪列来引用序列，每引用一次序伪列来引用序列，每引用一次序列的伪列列的伪列nextvalnextval，就会按照序列的定义产生一个新的序列，就会按照序列的定义产生一个新的序列码；通过序列的伪列码；通过序列的伪列currvalcurrval可以反复利用当前的序列码。可以反复利用当前的序列码。可以在如下场合使用序列的可以在如下场合使用序列的nextvalnextval和和currvalcurrval伪列：伪列：nInsertInsert语句的语句的valuesvalues子句中子句中nSelectSelect语句中的前面选择的表列名中语句中的前面选择的表列名中nUpdateUpdate语句中的语句
23、中的setset子句子句在如下场合不能够使用在如下场合不能够使用nextvalnextval或或currvalcurrval伪列：伪列：n子查询子查询n视图或快照的定义查询视图或快照的定义查询n带有带有distinctdistinct操作符的操作符的selectselect语句语句n带有带有group bygroup by或或order byorder by子句的子句的selectselect语句语句n通过通过unionunion、intersectintersect或或minusminus与另一个与另一个selectselect语句相结语句相结合的合的selectselect语句中语句中nS
24、electSelect语句的语句的wherewhere子句中子句中nCreate tableCreate table或或alter tablealter table语句中的一个表列的语句中的一个表列的defaultdefault值定义中值定义中n检查约束的条件中检查约束的条件中n想了解一个已存在的序列信息，可以通过数据字典想了解一个已存在的序列信息，可以通过数据字典DBA_SEQUENCESDBA_SEQUENCES、ALL_SEQUENCESALL_SEQUENCES和和USER_SEQUENCESUSER_SEQUENCES等几个等几个视图来查看。视图来查看。n无论什么时候当插入一行时，从
25、序列中定一个触发器来设置列无论什么时候当插入一行时，从序列中定一个触发器来设置列的值的值n在在DEPTDEPT表中建立插入触发器。使得每次插入一行时，表中建立插入触发器。使得每次插入一行时，DEPTNODEPTNO字字段自动通过访问序列段自动通过访问序列sqc_dept_keysqc_dept_key来获取值。来获取值。CREATE OR REPLACE TRIGGER tri_dept_setdeptnoCREATE OR REPLACE TRIGGER tri_dept_setdeptnoBEFORE INSERT ON deptBEFORE INSERT ON deptFOR EACH
26、ROWFOR EACH ROWDECLAREDECLAREp_nextno dept.deptno%type;p_nextno dept.deptno%type;BEGINBEGINSELECT sqc_dept_key.NEXTVAL INTO p_nextno FROM dual;SELECT sqc_dept_key.NEXTVAL INTO p_nextno FROM dual;:new.deptno := p_nextno;:new.deptno := p_nextno;END;END;/ /n在建立上面的触发器后，在建立上面的触发器后，DEPTDEPT表中的字段表中的字段DEPTN
27、ODEPTNO总是使用所建总是使用所建立的序列来设置。即使插入语句为立的序列来设置。即使插入语句为DEPTNODEPTNO指定了一些其它的值，指定了一些其它的值，也是这样。也是这样。n定义一个触发器来防止更新此列。定义一个触发器来防止更新此列。n有了前面有了前面3 3步已经实现了对步已经实现了对DEPTDEPT表主键自动增加的功能，但如，表主键自动增加的功能，但如，有人更新了一条已经存在的记录，并将主键值改变为一个可能有人更新了一条已经存在的记录，并将主键值改变为一个可能和将来插入相冲突的值该怎么办。所以这个值不能被更新，下和将来插入相冲突的值该怎么办。所以这个值不能被更新，下面定义一个触发器
28、来防止此列被更新。面定义一个触发器来防止此列被更新。 CREATE OR REPLACE TRIGGER tri_dept_updeptnoCREATE OR REPLACE TRIGGER tri_dept_updeptnoBEFORE UPDATE OF deptno ON deptBEFORE UPDATE OF deptno ON deptFOR EACH ROWFOR EACH ROWBEGINBEGINRAISE_APPLICATION_ERROR(-20000, RAISE_APPLICATION_ERROR(-20000, 不能更新自动增加类型的不能更新自动增加类型的主键值主键
29、值!);!);END;END;/ /测试结果：测试结果：首先向表首先向表DEPTDEPT中输入一行数据，查询其结果；然后再将此行数中输入一行数据，查询其结果；然后再将此行数据字段据字段DEPTNODEPTNO更新。具体操作如下所示：更新。具体操作如下所示：SQL insert into dept values(203, 203,203);SQL insert into dept values(203, 203,203);SQL select * from dept;SQL select * from dept;SQL update dept set deptno=3;SQL update de
30、pt set deptno=3;索引管理索引管理nOracleOracle支持两种基本的索引类型：支持两种基本的索引类型：B*TreeB*Tree索引和位映射索引。索引和位映射索引。nB*TreeB*Tree索引也是最常用的索引，它使用一个倒置的有序的树形索引也是最常用的索引，它使用一个倒置的有序的树形结构来加速访问表中行的速度。结构来加速访问表中行的速度。 n位映射索引常被用于那些包含较少唯一值的列。位映射索引总位映射索引常被用于那些包含较少唯一值的列。位映射索引总是对表中的每一行包含一条记录。记录的大小取决于索引列中是对表中的每一行包含一条记录。记录的大小取决于索引列中唯一值的数量，因为位
31、映射索引是由位的长字符串组成，如果唯一值的数量，因为位映射索引是由位的长字符串组成，如果列中的唯一值越多，需要位的长字符串就更长。列中的唯一值越多，需要位的长字符串就更长。 在表在表EMPEMP中存在下面一些数据：中存在下面一些数据：SQL select empno, ename, deptno from emp;SQL select empno, ename, deptno from emp;EMPNO ENAME DEPTNOEMPNO ENAME DEPTNO- - - - -3926 Chen de Jun 2023926 Chen de Jun 2027101 Hao xin 703
32、7101 Hao xin 7033927 Zhan zou 3023927 Zhan zou 3022911 Ge hen Cao 7032911 Ge hen Cao 7031923 Bo Wei 4101923 Bo Wei 4105836 Jiang qin 5035836 Jiang qin 5034926 Luo qun 7004926 Luo qun 7006627 Li tao 8026627 Li tao 8028312 Guo hua 4108312 Guo hua 4109536 Shun Jian 5039536 Shun Jian 5038926 Liu quan 70
33、08926 Liu quan 700n对对ENAMEENAME字段建立字段建立B*TreeB*Tree索引，则其倒置的树型结构如下所示：索引，则其倒置的树型结构如下所示：B*TreeB*Tree索引有以下几个特点：索引有以下几个特点：n它们保持数据有序，使得查询某一范围的记录更容易。它们保持数据有序，使得查询某一范围的记录更容易。n对于用复合列索引，可用前沿列来快速查询，即使那个查询并对于用复合列索引，可用前沿列来快速查询，即使那个查询并未引用索引中所有列。未引用索引中所有列。n它们自动保持平衡，由于所有的叶子结点在同一层，所以检索它们自动保持平衡，由于所有的叶子结点在同一层，所以检索一条记录
34、所需要的时间对索引中的全部数据是一致的。一条记录所需要的时间对索引中的全部数据是一致的。n性能保持相对连续，即使当被索引的表的大小增加时也如此。性能保持相对连续，即使当被索引的表的大小增加时也如此。雇员表雇员表EMPNO EMPNO 是否退休是否退休 状态状态- - - - -3926 Yes C3926 Yes C7101 Yes E7101 Yes E3927 No U3927 No U2911 No O2911 No O1923 Yes E1923 Yes E如果要在是否退休列上建立位映射索引，则其概念视图如下所示：如果要在是否退休列上建立位映射索引，则其概念视图如下所示：EMPNO Y
35、es NoEMPNO Yes No- - -3926 1 03926 1 07101 1 07101 1 03927 0 13927 0 12911 0 12911 0 11923 1 01923 1 0如果在状态列上建立位映射索引，则其概念视图如下所示：如果在状态列上建立位映射索引，则其概念视图如下所示：EMPNO C E U OEMPNO C E U O- - -3926 1 0 0 03926 1 0 0 07101 0 1 0 07101 0 1 0 03927 0 0 1 03927 0 0 1 02911 0 0 0 12911 0 0 0 11923 0 1 0 01923 0
36、1 0 0n从上面看出，如果所在列在只有从上面看出，如果所在列在只有2 2个唯一值个唯一值“Yes/NoYes/No”时，位映时，位映射索引只需要求射索引只需要求2 2位的字符串就能表示，而当其有位的字符串就能表示，而当其有4 4个唯一值时，个唯一值时，就至少需要有就至少需要有4 4位的字符串才能表示。位的字符串才能表示。n所以当列的唯一值很多时，会导致位映射索引很大。所以当列的唯一值很多时，会导致位映射索引很大。n所以对于那些有多个唯一值的列，不适合使用它们。所以对于那些有多个唯一值的列，不适合使用它们。n一个列中唯一值数量越多，位映射索引效率就越低。一个列中唯一值数量越多，位映射索引效率就
37、越低。n另外位映射索引仅在另外位映射索引仅在OracleOracle企业版中可用。企业版中可用。创建索引创建索引使用使用CREATE INDEXCREATE INDEX命令创建索引的语法如下所示：命令创建索引的语法如下所示：CREATE UNIQUE INDEX indx_name CREATE UNIQUE INDEX indx_name ON tablename(colnames) ON tablename(colnames) TABLESPACE tablespacename TABLESPACE tablespacename STORAGE (storage_setting) STOR
38、AGE (storage_setting) NOLOGGING NOSORT|REVERSE NOLOGGING NOSORT|REVERSEnUNIQUEUNIQUE：指定索引的每一条记录是唯一的。：指定索引的每一条记录是唯一的。nTABLESPACETABLESPACE：指定索引的表空间。：指定索引的表空间。nSTORAGESTORAGE：指定存储参数，如果不指定将采用表空间默认的：指定存储参数，如果不指定将采用表空间默认的存储参数。存储参数。nNOLOGGINGNOLOGGING：指定建立索引而不向数据：指定建立索引而不向数据REDOREDO日志文件记录索日志文件记录索引的建立工作。使用
39、此参数可以提高创建速度，缺点是由于引的建立工作。使用此参数可以提高创建速度，缺点是由于没写入日志文件，所以如果通过日志文件恢复数据库以后，没写入日志文件，所以如果通过日志文件恢复数据库以后，将无法恢复此索引，必须重建。将无法恢复此索引，必须重建。nNOSORTNOSORT：指定创建索引时不用对表中索引数据进行排序。如果：指定创建索引时不用对表中索引数据进行排序。如果表中的数据已经被排序，可以使用此选项，如果在建立索引期表中的数据已经被排序，可以使用此选项，如果在建立索引期间，发现表中的数据实际并未排序则将产生异常。间，发现表中的数据实际并未排序则将产生异常。nREVERSEREVERSE：指定
40、建立反向索引。所谓反向索引是在每个索引记录：指定建立反向索引。所谓反向索引是在每个索引记录中反转字节，比如中反转字节，比如chendjchendj的反向索引值是的反向索引值是jdnehcjdnehc。当有大量索。当有大量索引记录以相同的字母开始时，并且反向这些字母将删除那个集引记录以相同的字母开始时，并且反向这些字母将删除那个集合时，可以考虑采用此选项；但如果本身正在查询一个范围内合时，可以考虑采用此选项；但如果本身正在查询一个范围内的数据时，就不适合使用的数据时，就不适合使用REVERSEREVERSE选项。选项。为表为表DEPTDEPT的的DNAMEDNAME字段建立一个索引的例子。字段建
41、立一个索引的例子。CREATE UNIQUE INDEX INDX_DEPT_DNAME CREATE UNIQUE INDEX INDX_DEPT_DNAME ON DEPT (DNAME) ON DEPT (DNAME) TABLESPACE INDX TABLESPACE INDX STORAGE ( INITIAL 10K NEXT 20K MAXEXTENTS UNLIMITED) STORAGE ( INITIAL 10K NEXT 20K MAXEXTENTS UNLIMITED) NOLOGGING REVERSE NOLOGGING REVERSEn建立建立B-B-树索引树索
42、引Create unique index Create unique index 模式模式.索引名称索引名称on (on (模式模式.).)表名称表名称( (列名列名asc|desc ,asc|desc ,列名列名asc|descasc|desc) )Create index booknm_indexCreate index booknm_indexon bookinfo(bookname);on bookinfo(bookname);Create index authorinfo_indexCreate index authorinfo_indexon authorinfo(birthdat
43、e,hometown,briefhistory);on authorinfo(birthdate,hometown,briefhistory);Create index book_press_indexCreate index book_press_indexon bookinfo(pressname asc);on bookinfo(pressname asc);n建立位映射索引建立位映射索引Create bitmap index Create bitmap index 模式模式.索引名称索引名称on (on (模式模式.).)表名称表名称( (列名列名asc|desc ,asc|desc
44、,列名列名asc|descasc|desc) )n设有一个汽车数据表设有一个汽车数据表carinfocarinfo，记录非常多，有，记录非常多，有10001000多万多万条记录，其中包含了大量的低基数列，如条记录，其中包含了大量的低基数列，如color(color(汽车颜色汽车颜色) )、make(make(汽车品牌汽车品牌) )、model(model(汽车型号汽车型号) )和和year(year(出厂日期出厂日期) )等。这些列各自包含的值数目不超过等。这些列各自包含的值数目不超过100100。create bitmap index car_bmp_indexcreate bitmap i
45、ndex car_bmp_indexon carinfo(color,make,model,year)on carinfo(color,make,model,year)n列出当前用户所拥有表中的所有索引的信息的命令：列出当前用户所拥有表中的所有索引的信息的命令：select index_name,index_type,uniquenessselect index_name,index_type,uniquenessfrom user_indexes;from user_indexes;查询某个索引对应的表名、表列、升降序等信息查询某个索引对应的表名、表列、升降序等信息select select
46、 index_name,table_name,column_name,column_position,index_name,table_name,column_name,column_position,descenddescendfrom user_ind_columnsfrom user_ind_columnswhere index_name=where index_name=BOOKNM_INDEXBOOKNM_INDEX; ;修改索引修改索引n在建立索引后可以改变它，但是，在大多数情况下，仅限于改在建立索引后可以改变它，但是，在大多数情况下，仅限于改变其物理特点。不能向索引中增加列。变其
47、物理特点。不能向索引中增加列。OracleOracle允许进行下列变化允许进行下列变化: :n将一个索引移动到另一个表空间将一个索引移动到另一个表空间n使用不同的存储参数重建索引使用不同的存储参数重建索引n对索引更名对索引更名n重新分配不使用的空间或分配一个新的空间重新分配不使用的空间或分配一个新的空间n标志此索引不可被使用标志此索引不可被使用n其实重新将一个索引定位到另一个表空间，实际上需要索引完其实重新将一个索引定位到另一个表空间，实际上需要索引完全重建。只不过，全重建。只不过，OracleOracle使这个过程自动化了。使这个过程自动化了。n开始或终止对一个索引的使用情况进行监控。开始或
48、终止对一个索引的使用情况进行监控。n可以使用可以使用ALTER INDEXALTER INDEX命令来改变索引。命令来改变索引。 ALTER INDEX indx_dept_dname RENAME TO ALTER INDEX indx_dept_dname RENAME TO indx_dept_dname_new;indx_dept_dname_new;n另外使用另外使用REBUILDREBUILD子句可以进行许多物理变化。重建索引时，子句可以进行许多物理变化。重建索引时，OracleOracle通过使用一套新的存储参数从临时本中重建索引。通过使用一套新的存储参数从临时本中重建索引。 通
49、过重建通过重建indx_dept_dnameindx_dept_dname索引来改变其参数：索引来改变其参数：ALTER INDEX indx_dept_dname REBUILDALTER INDEX indx_dept_dname REBUILDTABLESPACE usersTABLESPACE usersNOLOGGINGNOLOGGINGSTORAGE(INITIAL 5K NEXT 5K);STORAGE(INITIAL 5K NEXT 5K);ALTER INDEX BOOK_PRESS_INDEX REBUILDALTER INDEX BOOK_PRESS_INDEX REBU
50、ILDTABLESPACE USERSTABLESPACE USERSONLINEONLINESTORAGE(INITIAL 5K NEXT 5K);STORAGE(INITIAL 5K NEXT 5K);Tablespace usersTablespace users表明在表明在usersusers表空间里重建索引，表空间里重建索引，OnlineOnline指定在重建过程中希望用户访问表。指定在重建过程中希望用户访问表。也可以在也可以在OEMOEM中，通过可视化的中，通过可视化的GUIGUI界面来修改一个索引。其创界面来修改一个索引。其创建的步骤如下所示：建的步骤如下所示：n启动启动OEMO
51、EM并注册到数据库。并注册到数据库。n打开索引文件夹，选取准备更改的索引。打开索引文件夹，选取准备更改的索引。n右击此索引并从弹出菜单选择编辑。将弹出编辑窗口，其界面右击此索引并从弹出菜单选择编辑。将弹出编辑窗口，其界面和新建索引类似。和新建索引类似。n修改相关信息后点修改相关信息后点“确认确认”，OEMOEM将执行索引的修改操作。将执行索引的修改操作。删除索引删除索引n可以使用可以使用DROP INDEXDROP INDEX命令从数据库中删除索引。命令从数据库中删除索引。 DROP INDEX INDX_DEPT_DNAMEDROP INDEX INDX_DEPT_DNAME；DROP IN
52、DEX AUTHORINFO_INDEX;DROP INDEX AUTHORINFO_INDEX;不能够直接删除与主键或唯一键约束相关的索引。必须先删除不能够直接删除与主键或唯一键约束相关的索引。必须先删除相应的主键或唯一键约束。相应的主键或唯一键约束。n在在OEMOEM中，可以通过在索引名字上右击并从弹出菜单中选择中，可以通过在索引名字上右击并从弹出菜单中选择“移移去去”来删除一个索引。来删除一个索引。聚簇聚簇n聚簇是聚簇是(clusters)(clusters)一种可选的数据库对象，它将经常在相一种可选的数据库对象，它将经常在相同数据块中一起使用的表进行物理分组，使经常被同时访同数据块中一
53、起使用的表进行物理分组，使经常被同时访问的表在物理位置上可以存储在一起。问的表在物理位置上可以存储在一起。n聚簇分为表簇聚簇分为表簇(table cluster)(table cluster)和哈希簇和哈希簇(hash cluster)(hash cluster)。n一个表簇由若干个共享着相同数据块的表构成，这些表具一个表簇由若干个共享着相同数据块的表构成，这些表具有一个或多个相同的表列并且常常一起使用。有一个或多个相同的表列并且常常一起使用。n同一个表簇中的各个表中相关的列称为簇键同一个表簇中的各个表中相关的列称为簇键(cluster (cluster key)key)。簇键用一个簇索引。簇
54、键用一个簇索引(cluster index)(cluster index)来进行索引。来进行索引。对于簇中的多个表，簇键值只存储一次。对于簇中的多个表，簇键值只存储一次。创建表簇之前的准备：创建表簇之前的准备：n主要用于执行查询操作，而不是插入或更新操作。主要用于执行查询操作，而不是插入或更新操作。n表经常一起被查询或连接。表经常一起被查询或连接。创建表簇创建表簇CREATE CLUSTER CREATE CLUSTER 模式模式.表簇名称表簇名称( (列名列名1 1 数据类型数据类型,表列表列名名2 2 数据类型数据类型 ) ) 物理属性组句物理属性组句 SIZE SIZE 数目数目 K|M
55、K|MTABLESPACE TABLESPACE 表空间名称表空间名称 存储属性组句存储属性组句;sizesize用于预计簇键和其相关的表列所需要的平均存储空间用于预计簇键和其相关的表列所需要的平均存储空间字节数。字节数。n估计能够装入一个成簇的数据块内的簇键个数。估计能够装入一个成簇的数据块内的簇键个数。n限制了成簇数据块内簇键的个数，最大化优化了簇内键值限制了成簇数据块内簇键的个数，最大化优化了簇内键值的存储效率。的存储效率。n簇和簇索引可以创建在不同的表空间里。簇和簇索引可以创建在不同的表空间里。n在表在表bookinfobookinfo和和authorinfoauthorinfo里都有
56、表列里都有表列authornameauthorname，把这，把这两个表生成簇，两个表生成簇，oracleoracle会把会把authornameauthorname表列放在同一个数表列放在同一个数据块里。建立表簇据块里。建立表簇book_authorbook_author，以，以authornameauthorname为簇键。为簇键。Create cluster book_author(authorname varchar2(20)Create cluster book_author(authorname varchar2(20)pctused 80pctused 80pctfree 5pc
57、tfree 5size 600size 600tablespace userstablespace usersstorage(initial 200kstorage(initial 200knext 300knext 300kminextents 2minextents 2maxextents 20maxextents 20pctincrease 33);pctincrease 33);创建表簇内的表创建表簇内的表(clustered tables)(clustered tables)n创建完表簇后，就可以在表簇内创建表了。创建完表簇后，就可以在表簇内创建表了。Create table boo
58、kinfoCreate table bookinfo(bookno number(38) primary key,(bookno number(38) primary key, bookname varchar2(40) not null, bookname varchar2(40) not null, isbn varchar2(20), isbn varchar2(20), bookkind varchar2(10), bookkind varchar2(10), authorname varchar2(20) not null references authorinfo, authorn
59、ame varchar2(20) not null references authorinfo,) )Cluster book_author(authorname);Cluster book_author(authorname);Create table authorinfoCreate table authorinfo(authorname varchar2(20) primary key,(authorname varchar2(20) primary key, birthdate date, birthdate date, hometown varchar2(40), hometown
60、varchar2(40), briefhistory varchar2(200), briefhistory varchar2(200), contact varchar2(40) contact varchar2(40) )Cluster book_author(authorname);Cluster book_author(authorname);创建簇索引创建簇索引(cluster indexes)(cluster indexes)n在向簇内表插入数据之前，可以为簇建一个索引。在向簇内表插入数据之前，可以为簇建一个索引。Create index Create index 索引名称索引名称
61、on cluster on cluster 表簇名称表簇名称tablespace tablespace 表空间名称表空间名称 存储属性组句存储属性组句;n为表簇为表簇book_authorbook_author创建簇索引创建簇索引book_author_indexbook_author_index。Create index book_author_indexCreate index book_author_indexon cluster book_authoron cluster book_authortablespace userstablespace usersstorage (initi
62、al 50kstorage (initial 50knext 50knext 50kminextents 2minextents 2maxextents 10maxextents 10pctincrease 33)pctincrease 33)pctfree 5;pctfree 5;n列出当前用户所拥有的所有聚簇信息的命令列出当前用户所拥有的所有聚簇信息的命令select cluster_name,tablespace_name,key_size,select cluster_name,tablespace_name,key_size, cluster_type cluster_typeFro
63、m user_clusters;From user_clusters;修改聚簇信息修改聚簇信息可修改已创建的表簇的如下属性：可修改已创建的表簇的如下属性：n物理属性物理属性(PCTFREE(PCTFREE、PCTUSEDPCTUSED、INITRANSINITRANS、MAXTRANSMAXTRANS和存和存储属性储属性) )n存储簇键信息所需的平均空间存储簇键信息所需的平均空间( (即即SIZE)SIZE)n默认的并行度默认的并行度n修改表簇修改表簇book_authorbook_author的的sizesize为为900900Alter cluster book_authorAlter c
64、luster book_authorSize 900;Size 900;Select cluster_name,key_size,cluster_typeSelect cluster_name,key_size,cluster_typeFrom user_clustersFrom user_clustersWhere cluster_name=Where cluster_name=BOOK_AUTHORBOOK_AUTHOR; ;删除聚簇删除聚簇n如果一个簇里的各个表不再需要了，就可以删除这个簇。当删如果一个簇里的各个表不再需要了，就可以删除这个簇。当删掉一个簇时，簇里的表和相应的簇索引同时也
65、被删掉。掉一个簇时，簇里的表和相应的簇索引同时也被删掉。drop cluster stuenroll_cluster;drop cluster stuenroll_cluster;n如果删除的簇中含有一个或多个表，想同时删除。如果删除的簇中含有一个或多个表，想同时删除。drop cluster drop cluster 聚簇名称聚簇名称 including tables;including tables;n如果簇中的一个或多个表包含外键，参照了这个簇以外的表，如果簇中的一个或多个表包含外键，参照了这个簇以外的表，想删除这个簇和内部的表。想删除这个簇和内部的表。drop cluster drop
66、 cluster 聚簇名称聚簇名称 including table cascade constraints;including table cascade constraints;同义词管理同义词管理n同义词是一个对象同义词是一个对象( (表、视图、序列、过程、函数、包、快照或表、视图、序列、过程、函数、包、快照或其它同义词其它同义词) )的一个代替的名字。的一个代替的名字。n同义词能使多个用户使用同一个对象而不用将模式作为前缀在同义词能使多个用户使用同一个对象而不用将模式作为前缀在对象前面。通过使用对象前面。通过使用CREATE SYNONYMCREATE SYNONYM和和DROP SYN
67、ONYMDROP SYNONYM命令来管命令来管理同义词。理同义词。n对另一个数据对象而言同义词是一个别名。对另一个数据对象而言同义词是一个别名。OracleOracle支持两种类支持两种类型的同义词：公共的和专有的。其中公共同义词是针对所有用型的同义词：公共的和专有的。其中公共同义词是针对所有用户的，相对而言专有同义词则只针对对象拥有者或被授予权限户的，相对而言专有同义词则只针对对象拥有者或被授予权限的用户。的用户。n同义词的另一个用途是能使两个应用程序用两个不同名字指向同义词的另一个用途是能使两个应用程序用两个不同名字指向同一个表。同一个表。创建同义词创建同义词n采用命令方式建立同义词采用
68、命令方式建立同义词只要具有只要具有CREATE SYNONYMCREATE SYNONYM权限，就可以建立同义词，如果建立权限，就可以建立同义词，如果建立的是公共同义词，则需要有的是公共同义词，则需要有CREATE PUBLIC SYNONYMCREATE PUBLIC SYNONYM权限。权限。建立了同义词，并不意味着就能访问此同义词代表的对象。必建立了同义词，并不意味着就能访问此同义词代表的对象。必须要由目标对象的所有者授予你相应的访问权限，此同义词才须要由目标对象的所有者授予你相应的访问权限，此同义词才能被正确访问。能被正确访问。n使用命令创建同义词的语法如下：使用命令创建同义词的语法如
69、下：CREATE PUBLIC SYNONYM schema.synonym_name CREATE PUBLIC SYNONYM schema.synonym_name FOR schema. objectdb_link;FOR schema. objectdb_link;n为当前数据库为当前数据库bookcreatorbookcreator的表的表bookinfobookinfo建立公有同义词建立公有同义词bookbook。 create public synonym bookcreate public synonym bookfor bookcreator.bookinfo;for bo
70、okcreator.bookinfo;n查看查看bookbook结构，看看与结构，看看与bookcreator.bookinfobookcreator.bookinfo表是否相同。表是否相同。 describe book;describe book;n创建同义词时，创建同义词时，oracleoracle不会检查其有效性，用户应自己检查。不会检查其有效性，用户应自己检查。n在用户在用户USER1USER1下建立指向下建立指向SCOTTSCOTT用户下用户下EMPEMP表的同义词表的同义词SQL conn user1/user1SQL conn user1/user1已连接。已连接。SQL cre
71、ate synonym employee for scott.emp;SQL create synonym employee for scott.emp;n未授予权限时，测试结果未授予权限时，测试结果SQL select empno, ename from employee;SQL select empno, ename from employee;select empno, ename from employeeselect empno, ename from employeeERROR ERROR 位于第位于第 1 1 行行: :ORA-00942: ORA-00942: 表或视图不存在表
72、或视图不存在n虽然用户虽然用户USER1USER1已经建立了指向已经建立了指向SCOTTSCOTT用户下用户下EMPEMP表的同义词，但表的同义词，但SCOTTSCOTT用户并没有授予用户并没有授予USER1USER1访问表访问表EMPEMP的相关权限，所以在访问的相关权限，所以在访问时出现上述错误。时出现上述错误。授予权限授予权限SQL conn scott/tigerSQL conn scott/tiger已连接。已连接。SQL grant select on emp to user1;SQL grant select on emp to user1;授权成功。授权成功。再次访问同义词再次
73、访问同义词SQL conn user1/user1;SQL conn user1/user1;已连接。已连接。SQL select empno, ename from employee;SQL select empno, ename from employee; EMPNO ENAME EMPNO ENAME- - - 7369 SMITH 7369 SMITH 7499 ALLEN 7499 ALLEN 7521 WARD 7521 WARD 7566 JONES 7566 JONES通过通过OEMOEM建立同义词的步骤如下所示：建立同义词的步骤如下所示：n启动启动OEMOEM并注册到数据库
74、并注册到数据库n右击同义词文件夹从弹出菜单中选择创建右击同义词文件夹从弹出菜单中选择创建n用建立同义词所需要的信息填充用建立同义词所需要的信息填充“一般信息一般信息”选项卡选项卡n单击单击“创建创建”，OEMOEM将执行创建同义词的操作。将执行创建同义词的操作。 删除同义词删除同义词DROP PUBLIC SYNONYM synonym_name;DROP PUBLIC SYNONYM synonym_name;n如果删除公共同义词一定要包括如果删除公共同义词一定要包括PUBLICPUBLIC关键字在内。关键字在内。drop public synonym book;drop public sy
75、nonym book;n如果要通过如果要通过OEMOEM企业管理器来删除同义词，只需要选中需要删除企业管理器来删除同义词，只需要选中需要删除的同义词，右击它，从弹出的菜单中选择的同义词，右击它，从弹出的菜单中选择“移去移去”就能完成删除就能完成删除操作。操作。n可以通过数据字典中的可以通过数据字典中的DBA_SYNONYMSDBA_SYNONYMS、ALL_SYNONYMSALL_SYNONYMS和和USER_SYNONYMSUSER_SYNONYMS等视图查询有关同义词信息。等视图查询有关同义词信息。select * from all_synonymsselect * from all_sy
76、nonymswhere synonym_name=where synonym_name=BOOKBOOK; ;小结小结n本章主要介绍了视图、序列、索引和同义词的管理。其中视图本章主要介绍了视图、序列、索引和同义词的管理。其中视图通过隐藏基本表的复杂性来有助于快速访问数据。作为一种安通过隐藏基本表的复杂性来有助于快速访问数据。作为一种安全措施，视图也可以帮助用户分离数据。视图以一条全措施，视图也可以帮助用户分离数据。视图以一条SELECTSELECT语语句存储在数据字典中，它们并不存储数据。句存储在数据字典中，它们并不存储数据。n序列可以用来为主键生成唯一值，也可以用于审计跟踪。序列可以用来为主键生成唯一值，也可以用于审计跟踪。nOracleOracle支持两种索引，支持两种索引，B*TreeB*Tree索引和位映射索引。在数据库中索引和位映射索引。在数据库中通过索引的使用可以提高数据访问的效率。通过索引的使用可以提高数据访问的效率。n同义词可以使开发者以短名称来引用表，从而提高开发效率，同义词可以使开发者以短名称来引用表，从而提高开发效率，也可以简化应用程序从一种模式到另一模式的迁移。也可以简化应用程序从一种模式到另一模式的迁移。
