《Oracle数据库应用》课件10.ppt

1、第第1010章章 其他方案对象其他方案对象视图视图1索引索引2序列序列3同义词同义词410.1 10.1 视图视图n 视图是一个基于基表和视图是一个基于基表和(或或)其他视图的逻辑表其他视图的逻辑表，本身不存放数据，其数据来源于对应的基表，本身不存放数据，其数据来源于对应的基表n 提供了从某个角度观察数据的窗口提供了从某个角度观察数据的窗口n 视图的作用具体表现为视图的作用具体表现为l限制用户对数据的访问限制用户对数据的访问l使复杂查询对报表等编程人员变得容易使用使复杂查询对报表等编程人员变得容易使用l为应用提供数据独立性为应用提供数据独立性l为相同的数据提供不同的视角为相同的数据提供不同的视

2、角n 创建视图时需注意以下几点：创建视图时需注意以下几点：l创建视图的子查询不能使用序列，不可选取创建视图的子查询不能使用序列，不可选取currvalcurrval、nextvalnextval伪列伪列l只有加别名才能使用只有加别名才能使用rowidrowid、rownumrownum、levellevel等伪列等伪列l创建视图的子查询一般不应包含创建视图的子查询一般不应包含ORDER BYORDER BY子句子句l最好不要在视图上再建视图最好不要在视图上再建视图l用用DESCRIBEDESCRIBE命令查看视图的结构命令查看视图的结构n 1.1.创建与删除视图创建与删除视图l创建创建/重建视

3、图语法：重建视图语法：CREATE OR REPLACE VIEW view CREATE OR REPLACE VIEW view ;l删除视图语法删除视图语法DROP VIEW schema.view CASCADE CONSTRAINTS;DROP VIEW schema.view CASCADE CONSTRAINTS;n 2.2.对视图执行对视图执行DMLDML操作的限制操作的限制l可对简单视图执行可对简单视图执行DMLDML操作操作l子查询包含如下内容，则不能通过它删除基表数据子查询包含如下内容，则不能通过它删除基表数据分组函数分组函数GROUP BYGROUP BY子句子句DIS

4、TINCTDISTINCT关键字关键字 ROWNUMROWNUM伪列伪列l子查询包含如下内容，则不能通过它修改基表数据子查询包含如下内容，则不能通过它修改基表数据分组函数分组函数GROUP BYGROUP BY子句子句DISTINCTDISTINCT关键字关键字ROWNUMROWNUM伪列伪列计算列计算列l子查询包含如下内容，则不能通过它增加基表数据子查询包含如下内容，则不能通过它增加基表数据分组函数分组函数GROUP BYGROUP BY子句子句DISTINCTDISTINCT关键字关键字 ROWNUMROWNUM伪列伪列计算列计算列基表中的基表中的NOT NULLNOT NULL列未全被包

5、含在子查询中列未全被包含在子查询中例题：例题：P161-162P161-16210.2 10.2 索引索引n 索引索引l与表和簇相关，用于缩短查询数据的时间与表和簇相关，用于缩短查询数据的时间l索引中存储了记录的关键字和索引中存储了记录的关键字和ROWIDROWIDl考虑按索引的逻辑设计、物理实现分类建立索引考虑按索引的逻辑设计、物理实现分类建立索引索引的逻辑分类从应用角度划分，比如：唯一索引的逻辑分类从应用角度划分，比如：唯一索引的物理分类从其物理存储划分，比如：分区索引的物理分类从其物理存储划分，比如：分区l索引在逻辑上和物理上都与相关表的数据无关索引在逻辑上和物理上都与相关表的数据无关l

6、索引需要单独的存储空间。否则使用表所在表空间索引需要单独的存储空间。否则使用表所在表空间l创建创建/删除一个索引不会影响基于数据表的数据库应删除一个索引不会影响基于数据表的数据库应用或其他索引用或其他索引l创建索引后一般会增加数据更新操作的执行时间创建索引后一般会增加数据更新操作的执行时间n OracleOracle数据库的索引模式数据库的索引模式lB-B-树索引树索引lB-B-树簇索引树簇索引l散列簇索引散列簇索引l全局和本地索引全局和本地索引l反序索引反序索引l位图索引位图索引l基于函数的索引基于函数的索引l域索引域索引B-B-树索引树索引(B-tree index)B-tree inde

7、x)又称平衡树索引，是现代关系又称平衡树索引，是现代关系型数据库中最为普遍采用的索引型数据库中最为普遍采用的索引n 管理索引的准则管理索引的准则l在表中插入数据后创建索引在表中插入数据后创建索引l索引正确的表和列索引正确的表和列l为性能而安排索引列为性能而安排索引列l限制每个表的索引的数量限制每个表的索引的数量l删除不再需要的索引删除不再需要的索引l指定索引数据块空间使用参数指定索引数据块空间使用参数l估计索引大小并设置存储参数估计索引大小并设置存储参数l为每个索引指定表空间为每个索引指定表空间l考虑创建并行索引考虑创建并行索引l考虑创建具有考虑创建具有NOLOGGINGNOLOGGING的索

8、引的索引l考虑合并或重建索引时的损益考虑合并或重建索引时的损益l在停用或删除索引之前考虑开销在停用或删除索引之前考虑开销l若满足如下条件则需要创建索引：若满足如下条件则需要创建索引：列有较大的取值范围列有较大的取值范围列有很多空值，但经常查询所有具有值的行列有很多空值，但经常查询所有具有值的行一列或多列被经常一起用于一列或多列被经常一起用于WHEREWHERE条件或连接条件条件或连接条件表很大，且大部分查询只返回不到表很大，且大部分查询只返回不到2%2%4%4%的数据行的数据行l具有如下一个或多个特征的列适合作索引列：具有如下一个或多个特征的列适合作索引列：列中的值相对比较唯一列中的值相对比较

9、唯一取值范围大取值范围大(适合建常规索引适合建常规索引)取值范围小取值范围小(适合建位图索引适合建位图索引)列中有许多空值，但经常查询所有具有值的行列中有许多空值，但经常查询所有具有值的行lLONGLONG和和LONG RAWLONG RAW列不能被索引列不能被索引n 1.1.创建索引创建索引l语法图语法图l创建索引的语法创建索引的语法CREATE UNIQUE|BITMAP INDEX schema.indexCREATE UNIQUE|BITMAP INDEX schema.index ON schema.table(column ASC|DESC,ON schema.table(colu

10、mn ASC|DESC,)INITRANS n INITRANS n MAXTRANS n MAXTRANS n PCTFREE nPCTFREE n LOGGING|NOLOGGING LOGGING|NOLOGGING NOSORT NOSORT STORAGE storage STORAGE storage TABLESPACE tablespace;TABLESPACE tablespace;l索引中数据默认按升序索引中数据默认按升序(ASC)ASC)排列排列l系统数据字典系统数据字典USER_INDEXESUSER_INDEXESUSER_IND_COLUMNSUSER_IND_CO

11、LUMNSn 2.2.创建唯一索引创建唯一索引l唯一索引可保证在索引列上不会有两行相同的值唯一索引可保证在索引列上不会有两行相同的值l使用使用CREATE UNIQUE INDEXCREATE UNIQUE INDEX语句语句l当表定义中启用了唯一性约束或主键约束时，系统会当表定义中启用了唯一性约束或主键约束时，系统会自动为这些列创建唯一性索引自动为这些列创建唯一性索引n 3.3.创建位图索引创建位图索引l适用于仅有几个固定值的列适用于仅有几个固定值的列l将整个位图索引段装入内存，提高查找效率将整个位图索引段装入内存，提高查找效率初始化参数初始化参数CREATE_BITMAP_AREA_SIZ

12、ECREATE_BITMAP_AREA_SIZE定义内存大小定义内存大小默认值为默认值为8MB8MBl创建位图索引的限制创建位图索引的限制不能在全局分区索引上创建位图索引不能在全局分区索引上创建位图索引不能同时指定不能同时指定UNIQUEUNIQUE和位图索引和位图索引不能在本地索引中使用位图索引不能在本地索引中使用位图索引不能用位图索引来生成具有唯一性的索引和反序索引不能用位图索引来生成具有唯一性的索引和反序索引l使用使用CREATE BITMAP INDEXCREATE BITMAP INDEX语句语句n 4.4.创建基于函数的索引创建基于函数的索引l适用于经常执行以某种函数形式为条件查询

13、数据的应适用于经常执行以某种函数形式为条件查询数据的应用用l函数值已计算，因此改善了查询的执行性能函数值已计算，因此改善了查询的执行性能l基于函数的表达式索引列被称为基于函数的表达式索引列被称为虚列虚列例题：例题：P165P165n 5.5.索引的删除与重建索引的删除与重建l删除使用删除使用CREATE INDEXCREATE INDEX语句创建的索引语句创建的索引语法：语法：DROP INDEX index_name;DROP INDEX index_name;l删除通过定义主键或唯一约束创建的索引删除通过定义主键或唯一约束创建的索引停用或删除该约束停用或删除该约束l重建索引语法重建索引语法

14、ALTER INDEX schema.index REBUILD ONLINE;ALTER INDEX schema.index REBUILD ONLINE;n 6.6.索引的维护索引的维护l消除索引块中那些数据记录已经删除的无用索引项消除索引块中那些数据记录已经删除的无用索引项l重新平衡索引树，提高检索效率重新平衡索引树，提高检索效率l紧凑索引紧凑索引语法：语法：DROP INDEX index_name COALESCE;DROP INDEX index_name COALESCE;l分配索引空间分配索引空间语法：语法：ALTER INDEX index_name allocate ex

15、tent;ALTER INDEX index_name allocate extent;l回收空闲的索引空间回收空闲的索引空间语法：语法：ALTER INDEX index_name deallocate unused;ALTER INDEX index_name deallocate unused;n 序列序列SequenceSequencel用于产生自增不重复整数，一般称为序列生成器用于产生自增不重复整数，一般称为序列生成器n 1.1.创建创建/删除序列语法删除序列语法l创建序列语法：创建序列语法：CREATE SEQUENCE schema.sequenceCREATE SEQUENCE

16、 schema.sequence INCREMENT BY n INCREMENT BY n START WITH n START WITH n MAXVALUE n|NOMAXVALUE MAXVALUE n|NOMAXVALUE MINVALUE n|NOMINVALUE MINVALUE n|NOMINVALUE CYCLE|NOCYCLE CYCLE|NOCYCLE CACHE n|NOCACHE CACHE n|NOCACHE ORDER|NOORDER;ORDER|NOORDER;10.3 10.3 序列序列CACHECACHE：预分配一组序列号放到内存，默认缓存预分配一组序列号放

17、到内存，默认缓存2020个序号值个序号值CYCLECYCLE：声明当序列值到达声明当序列值到达MAXVALUEMAXVALUE时，是否从最小值时，是否从最小值(MINVALUE)MINVALUE)开始重新启用那些被使用过的序列号开始重新启用那些被使用过的序列号l数据字典数据字典USER_SEQUENCESUSER_SEQUENCESl删除序列语法：删除序列语法：DROP SEQUENCE sequence;DROP SEQUENCE sequence;n 2.2.序列的使用序列的使用l用于访问序列值的用于访问序列值的两个伪列两个伪列NEXTVALNEXTVAL：序列的下一个新的序号值：序列的下

18、一个新的序号值CURRVALCURRVAL：序列的当前序号值序列的当前序号值l引用方法引用方法在在SELECTSELECT语句中借助语句中借助伪表伪表dualdual进行查询进行查询在在NEXTVALNEXTVAL和和CURRVALCURRVAL之前冠以序列的名字，用之前冠以序列的名字，用“.”连接连接SELECT SELECT 序列序列.nextval FROM dual;nextval FROM dual;SELECT SELECT 序列序列.currval FROM dual;currval FROM dual;lNEXTVAL/CURRVALNEXTVAL/CURRVAL可用于以下场合

19、：可用于以下场合：非子查询的非子查询的SELECTSELECT语句的列名表语句的列名表INSERTINSERT语句的语句的SELECTSELECT子查询的列名表子查询的列名表INSERTINSERT语句的语句的VALUESVALUES子句子句UPDATEUPDATE语句的语句的SETSET子句子句lNEXTVAL/CURRVALNEXTVAL/CURRVAL不可用于以下场合：不可用于以下场合：视图定义视图定义带带DISTINCTDISTINCT关键字的关键字的SELECTSELECT语句语句带带GROUP BYGROUP BY、HAVINGHAVING或或ORDER BYORDER BY子句的

20、子句的SELECTSELECT语句语句SELECTSELECT、DELETEDELETE、UPDATEUPDATE语句的子查询语句的子查询CREATE TABLECREATE TABLE和和ALTER TABLEALTER TABLE语句的语句的DEFAULTDEFAULT子句子句例题：例题：P168-169P168-169n 3.3.间隙间隙gapgapl序列可被多个用户共享，其值可能被用于多个表。由序列可被多个用户共享，其值可能被用于多个表。由于使用于使用CACHECACHE选项可能导致部分序列号被丢弃，使得不选项可能导致部分序列号被丢弃，使得不是每个序列号值都会被用上或被用到同一个表中，

21、从是每个序列号值都会被用上或被用到同一个表中，从而产生而产生“间隙间隙”。l间隙即是在一个表中看到的序列号值不连续、不完整间隙即是在一个表中看到的序列号值不连续、不完整。间隙可能正常，也可能不是，需分析作出判断。间隙可能正常，也可能不是，需分析作出判断。10.4 10.4 同义词同义词n 同义词同义词SYNONYMSYNONYMl是对表是对表、视图、序列、存储过程与函数、包、实体、视图、序列、存储过程与函数、包、实体化视图或其它同义词化视图或其它同义词建立的一个别名建立的一个别名。n 同义词的作用同义词的作用l隐藏真实对象名，提高对象访问的安全性隐藏真实对象名，提高对象访问的安全性l简化对象引

22、用表示，如对一个远程对象简化对象引用表示，如对一个远程对象n 创建同义词创建同义词 CREATE PUBLIC SYNONYM CREATE PUBLIC SYNONYM synonym_namesynonym_name FOR FOR schema.objectschema.objectdblinkdblink;n 删除同义词删除同义词 DROP SYNONYM DROP SYNONYM synonym_namesynonym_name;n 了解视图和索引的应用原则了解视图和索引的应用原则n 掌握创建视图的方法掌握创建视图的方法n 掌握创建各类索引的方法掌握创建各类索引的方法n 通过系统数据字典查看视图和索引通过系统数据字典查看视图和索引n 掌握序列的用法掌握序列的用法n 掌握同义词的用法掌握同义词的用法本章小结本章小结