《Oracle数据库实用教程（第二版）》课件第06章 索引与视图

索引与表和簇相关，用于缩短查询数据的时间索引中存储了记录的关键字和ROWID考虑按索引的逻辑设计、物理实现分类建立索引索引的逻辑分类从应用的角度划分索引的物理分类从其物理存储划分索引在逻辑上和物理上都与相关表的数据无关索引需要单独的存储空间。否则使用表所在表空间创建或删除一个索引不会影响基于数据表的数据库应用或其他索引创建索引后一般会增加数据更新操作的执行时间6.1管理索引

Oracle数据库的索引模式1.索引概述(1)B-树索引(2)B-树簇索引(3)散列簇索引(4)全局和本地索引(5)反序索引(6)位图索引(7)基于函数的索引(8)域索引B-树索引(B-treeindex)又称平衡树索引，是现代关系型数据库中最为普遍采用的索引管理索引的准则(1)在表中插入数据后创建索引(2)索引正确的表和列(3)为性能而安排索引列(4)限制每个表的索引的数量(5)删除不再需要的索引(6)指定索引数据块空间使用参数(7)估计索引大小并设置存储参数(8)为每个索引指定表空间(9)考虑创建并行索引(10)考虑创建具有NOLOGGING的索引(11)考虑合并或重建索引时的损益(12)在停用或删除索引之前考虑开销1.索引概述如果满足如下条件则需要创建索引(1)列有较大的取值范围(2)列有很多空值，但经常查询所有具有值的行(3)一列或多列被经常一起用于WHERE条件或连接条件(4)表很大，且大部分查询只返回不到2%～4%的数据行1.索引概述具有如下一个或多个特征的列适合作索引列(1)列中的值相对比较唯一(2)取值范围大(适合建常规索引)(3)取值范围小(适合建位图索引)(4)列中有许多空值，但经常查询所有具有值的行

LONG和LONGRAW列不能被索引创建索引的语法图1.索引概述创建索引的简要语法CREATE[UNIQUE|BITMAP]INDEX[schema.]indexON[schema.]table(column[ASC|DESC][,…])[INITRANSn] [MAXTRANSn] [PCTFREEn][LOGGING|NOLOGGING[NOSORT][STORAGEstorage][TABLESPACEtablespace];默认情况下，索引中数据按升序(ASC)排列1.索引概述数据字典USER_INDEXESUSER_IND_COLUMNS例6.1为雇员表emp建立以ename为索引列的索引，索引名为emp_ename_index，索引存放到表空间my_index。EXA_06_01.SQL唯一索引可以保证在索引列上不会有两行相同的值

CREATEUNIQUEINDEX语句2.创建唯一索引例6.2为部门表dept的dname列创建唯一索引。EXA_06_02.SQL例6.3唯一索引测试。EXA_06_03.SQL位图索引适用于仅有几个固定值的列3.创建位图索引例6.4给student表的性别列创建位图索引。EXA_06_04.SQL将整个位图索引段装入内存，地提高了查找效率初始化参数CREATE_BITMAP_AREA_SIZE默认值为8MB。创建位图索引的限制(1)不能在全局分区索引上创建位图索引(2)不能同时指定UNIQUE和位图索引(3)不能在本地索引中使用位图索引(4)不能用位图索引来生成具有唯一性的索引和反序索引

CREATEBITMAPINDEX语句适用于经常执行以某种函数形式为条件查询数据的应用因函数值已计算，极大地改善了查询的执行性能基于函数的表达式索引列被称为虚列4.创建基于函数的索引例6.5设student表的学生编号sno列的编码规则为：4位年+2位系别+2位班级+2位顺序号。为学号列按年级查询学生的应用创建一索引。EXA_06_05.SQL在较早版本中，用户需具有如下之一系统特权QUERYREWRITECREATEANYINDEX索引默认保存到与表相同的表空间在约束定义子句中，使用USINGINDEX子句为约束指定存储空间5.设置与约束相关的索引例6.6为student表name列的唯一约束指定索引表空间EXA_06_05.SQL

USINGINDEX子句语法USINGINDEX[[schema.]index|(create_index_statement)|{LOCAL|global_partitioned_index}|{PCTFREEinteger|INITRANSinteger|MAXTRANSinteger|TABLESPACEtablespace|storage_clause|{SORT|NOSORT}|logging_clause}]必须为簇创建索引才能向簇表插入数据创建簇索引不需要指定索引列6.创建簇索引创建簇索引语法CREATEINDEXindex_nameONCLUSTER[schema.]cluster_nameindex_attributes;CREATEINDEXemp_dept_indexONCLUSTERemp_deptSTORAGE(INITIAL50KNEXT50KMINEXTENTS2MAXEXTENTS10PCTINCREASE0)TABLESPACEusersINITRANS2MAXTRANS5PCTFREE5;例:当所有索引分区与表分区对应时，称为本地分区索引全局分区索引的分区列与表分区无关7.对索引创建分区例6.7建立本地索引，分区设置与创建表时一致EXA_06_07.SQL用LOCAL指明建立本地分区索引用GLOBAL指明建立全局分区索引通常全局分区索引比本地分区索引需要更多的维护开销例6.8建立本地分区索引，省略分区设置

EXA_06_08.SQL例6.9分区名称与表分区名不一致，但分区数量一样

EXA_06_09.SQL例6.10建立本地分区索引，索引分区数量与表分区数量不一致

EXA_06_10.SQL例6.11建立全局分区索引

EXA_06_11.SQL导致删除索引的主要原因(1)不再需要该索引(2)索引没有提供所期望的性能改善(3)应用程序没有用该索引来查询数据(4)该索引已经变成无效(5)该索引已经变得太碎8.索引的删除与重建如何删除索引，取决于索引的创建方式使用CREATEINDEX语句定义主键或唯一约束删除索引语法DROPINDEXindex_name;重建索引语法ALTERINDEX[schema.]indexREBUILDONLINE;消除索引块中那些数据记录已经删除的无用索引项重新平衡索引树9.索引的维护例6.12紧凑并重建tudent表sno列基于函数的索引EXA_06_12.SQL例6.13重建PK_EMP索引并指定新的数据块空间使用参数EXA_06_13.SQL例6.14手工分配与回收索引空间。EXA_06_14.SQL10.索引使用的标识例6.15查看scott用户的PK_EMP索引的使用情况。EXA_06_15.SQL索引使用的标识开启对索引使用的监视ALTERINDEXindexMONITORINGUSAGE;查看动态性能监视视图v$object_usage对索引使用情况的监视会降低数据库系统的性能关闭对索引的监视ALTERINDEX…NOMONITORINGUSAGE;视图是一个基于基表和(或)其他视图的逻辑表视图提供了从某个角度观察数据的窗口6.2视图的创建与使用视图的作用具体表现为(1)限制用户对数据的访问(2)使复杂查询对报表等编程人员变得容易使用(3)为应用提供数据独立性(4)为相同的数据提供不同的视角创建视图的子查询不能使用序列，不可选取currval、nextval伪列只有加别名才能使用rowid、rownum、level等伪列创建视图的子查询一般不应包含ORDERBY子句最好不要在视图上再建视图用DESCRIBE命令查看视图的结构1.创建与删除视图创建视图语法CREATE[ORREPLACE][FORCE|NOFORCE]VIEWview[(alias,alias…)]ASsubquery[WITHCHECKOPTION[CONSTRAINTconstraint]][WITHREADONLY[CONSTRAINTconstraint]];(1)子查询中的表达式必须有别名(2)FORCE：强制创建该视图(3)WITHCHECKOPTION：DML语句操作的数据应满足视图定义时的条件(4)WITHREADONLY：视图只读(5)CREATE子句指定的列名优先于在subquery中指定的列名1.创建与删除视图例6.16基于dept表创建一只读视图。EXA_06_16.SQL数据字典USER_VIEWS删除视图语法DROPVIEW[schema.]view[CASCADECONSTRAINTS];重建视图带上ORREPLACE选项，语法如下：CREATEORREPLACEVIEWview……;例6.17为表emp建立视图v_emp_d20，只显示20号部门的雇员和他们的年收入。EXA_06_17.SQL2.对视图执行DML操作的限制可以对简单视图(行列子集视图)执行DML操作

子查询包含如下内容，则不能通过它删除基表数据(1)分组函数(2)GROUPBY子句(3)DISTINCT关键字(4)ROWNUM伪列子查询包含如下内容，则不能通过它修改基表数据(1)分组函数(2)GROUPBY子句(3)DISTINCT关键字(4)ROWNUM伪列(5)计算列子查询包含如下内容，则不能通过它增加基表数据(1)