Oracle 数据库管理与应用：第9章 视图与索引

1、第9章 索引与视图的创建 本章要点索引的定义与存储 1索引的创建与应用 2视图的概念与优点 33创建与管理视图 44利用视图更新数据源表 359.1.1 索引的概念 索引是建立在数据表之上的数据库模式对象，其作用就像图书目录一样，可以帮助用户快速查找需要的数据，提高SQL查询语句的速度。 索引是一个单独的物理存储结构,可以有自己的存储空间，不必与相关联的表处在同一个表空间中。索引是由表中一列或多列值的集合和这些值所在行的ROWID组成。 在Oracle系统中，对索引的应用和维护是自动完成的。当用户执行了INSERT、UPDATE、DELETE操作后，系统自动更新索引列表。当用户执行SELECT

2、、UPDATE、DELETE操作时，系统自动选择合适的索引来优化操作。 适合创建索引的字段应具备以下特征：取值范围较大的字段。null值比较多的字段。经常作为查询或连接条件的字段。经常需要排序的字段。不适合建立索引的表或字段具备的特征：较小的表。经常更新的表。不常作为查询条件或连接条件的字段。 9.1.2 索引的类型 常见的索引类型包括B树索引位图索引反向键索引基于函数的索引全局索引和局部索引等。 1B树索引 该索引是最常见的索引结构，也是Oracle采用的默认索引类型。B树索引的组织结构类似于一个树，其中的主要数据都集中在叶子结点上。各叶子结点中包括索引列的值和数据表中对应行的ROWID。

3、2位图索引该索引也采用B树索引，只是索引值全部集中在叶子节点。位图中的每个位对应一个ROWID。如位值为1，则意味着对应的ROWID的行包含该索引键值。所以，位图的映射功能是将数据位的位置转化为实际的ROWID。 3反向键索引 反向键索引是一种特殊类型的B树索引，特别适合基于有序数列建立的索引。在存储结构方面，与常规的B树索引相同。但如果用户使用序列编号在表中输入新记录，则反向键索引首先反向每个列键值的字节，然后在反向后的新数据上进行索引。 4基于函数的索引基于函数的索引也只是普通的B树索引，但它是基于表中某些字段的函数建立的，而不是直接建立在某些字段上的。函数索引的两个主要作用：只对限定的行

4、创建索引，节约空间，提高检索速度。优化WHERE子句中使用了函数的sql语句。 5全局索引和局部索引 局部分区索引全局分区索引 全局非分区索引 9.2 创建索引 创建索引的语法如下：CREATE UNIQUE | BITMAP INDEX schema.ON schema.( | ASC| DESC,| ASC| DESC,)TABLESPACE STORAGE()LOGGING | NOLOGGINGNOSORT | REVERSEPARTITION | GLOBAL PARTITION 注意，如果一个列已经包含了索引那么无法在该列上再创建索引。9.2.1 创建B树索引 B树索引是创建索引时

5、的默认类型。当用户为表创建主键约束时，系统将自动为该列创建一个B树索引。也可以使用CREATE INDEX命令创建B树索引。 例9.1 在scott.emp表的sal字段上创建一个名为index_sal的B树索引，按字段值的降序排列。CREATE INDEX index_sal ON scott.emp(sal desc) TABLESPACE users;例9.2 在scott.dept表的dname字段上创建一个具有唯一性的B树索引，索引值按字母序排序。CREATE UNIQUE INDEX index_dname ON scott.dept(dname) 例9.3 在scott.emp表

6、的ename和deptno字段上创建一个复合索引。CREATE INDEX index1 ON scott.emp(ename,deptno);CREATE INDEX index2 ON scott.emp(deptno,ename);9.2.2 创建位图索引 表中某一个字段的唯一值的个数比较少（基数小）时，在该字段上建立位图索引比较合适。 创建位图索引时，必须指定BITMAP关键字。例9.4 在scott.emp表中的job字段上创建位图索引bit_index。CREATE BITMAP INDEX bit_index ON scott.emp(job) TABLESPACE users;

7、注意：位图索引的字段重复值较多不能是唯 一索引 9.2.3 创建反向键索引 反向键索引本质也是一个B树索引，为了避免“歪树”的产生，反向键索引对键值进行反向存储。创建反向键索引时必须指定关键字REVERSE。 例9.5 在scott.emp表中的empno字段上创建反向索引re_index。CREATE INDEX re_index ON scott.emp(empno) REVERSE;9.2.4 创建基于函数的索引 DML操作中若经常使用某个表达式当作条件，那么可以建立基于该表达式的索引（或称基于函数的索引）。基于函数的索引可以是普通的B树索引，也可以是位图索引，这与函数中字段的取值特点有

8、关系。 9.2.4 创建基于函数的索引例9.6 在scott.emp表中的hiredate字段上创建一个基于函数的索引。CREATE INDEX index_hire ON scott.emp(to_char(hiredate, yyyy-mm-dd);如果对scott.emp表执行下面的查询，那么该索引可以提高查询速度。SELECT * FROM scott.emp WHERE to_char(hiredate,yyyy-mm-dd)2002-5-19.3 应用索引 Oracle中索引的应用是由优化器决定的，优化器根据优化的结果自动选择合适的索引使用。 Oracle对查询语句的执行过程包括：

9、解析优化:找到执行用户查询的最佳途径，选择索引数据表的大小 用户获取记录的多少 生成代码执行代码 9.3 应用索引例9.7 设置autotrace参数为跟踪状态后，执行对scott.emp表的查询命令，查看索引应用的跟踪结果。CONNECT system/abcdef;SET AUTOTRACE TRACEONLY;SELECT * FROM scott.emp;9.4 视图的概述 视图是一个虚拟表，其内容由查询定义。 逻辑的组织数据，可以像表一样操作但是并不永久性的存数据视图的形成：9.4.2 视图的优点 1简化数据操作 包含的数据较少 将对多个表的操作简化为对一个视图的操作2增强数据的安全

10、性 可以将安全性控制到任意的数据子集3定制数据 不同的级别的用户可以看到不同用途的数据集 4合并与分割数据 5利用视图修改源表9.5 创建与管理视图 创建视图需要的权限：CREATE VIEW权限 或CREATE ANY VIEW权限 对视图所引用表的查询权限 9.5.1 创建和应用视图 创建视图的语法如下：CREATE OR REPLACE FORCE | NOFORCE VIEW schema.view_name(column ,.n )AS SELECT_statementWITH CHECK OPTION | WITH READ ONLY 修改视图当数据源不存在时强制创建视图为视图中的

11、数据指定新的列标题定义视图的select语句利用视图修改或添加新数据时，检查是否符合视图的查询条件创建只读视图例9.8 利用scott.emp表创建一个一般视图。CREATE OR REPLACE VIEW view1 AS SELECT empno,ename,job,sal FROM scott.emp;使用该视图浏览数据：SELECT * FROM view1;例9.9 利用scott.emp和scott.dept两张表的连接查询创建新视图，并且为视图的字段重新命名。CREATE OR REPLACE VIEW view2(雇员姓名, 所在部门) AS SELECT ename, dna

12、me FROM scott.emp e INNER JOIN scott.dept d ON e.deptno=d.deptno;使用该视图浏览数据：SELECT * FROM view2;例9.10 选择FORCE选项，强制创建视图。CREATE OR REPLACE FORCE VIEW view3 AS SELECT * FROM table1;在这种情况下，由于表table1不存在，所以视图虽然被创建了，但是带有编译错误。 9.5.2 重新编译视图 若修改了视图的数据源表的结构，那么在使用视图前必须对其进行重新编译： ALTER VIEW view_name COMPILE;例9.11

13、 为scott.emp表增加一个新字段后，对例9.8中创建的视图view1重新编译。ALTER TABLE scott.emp ADD(c1 NUMBER);ALTER VIEW view1 COMPILE;9.5.3 删除视图 命令格式如下：DROP VIEW view_name视图被删除后，对视图所引用的数据源表并没有影响。 9.6 创建可更新视图 可更新视图是指用户可以对视图执行INSERT、UPDATE、DELETE操作的视图 。但实际影响的是数据源表可更新视图或视图的可更新列有如下特点： 9.6 创建可更新视图创建可更新视图的注意事项：创建视图时不能选择WITH READ ONLY选

14、项。视图中的非计算列或非聚合运算，即数据源表中的原始字段，才可以被更新。视图的定义中SELECT语句不能包含DISTINCT关键字。视图的定义中SELECT语句不能包含集合操作如，UNION、INTERSECT等。视图的定义中SELECT语句不能包含GROUP BY子句和HAVING子句。用户必须对视图的数据源表具有显示的操作权限。只有在视图中可见的行和列才可能被修改或删除。例9.13 创建视图testview，查询scott.emp表中的姓名、工资和工资的1.2倍。Grant Create Any View to scott;CONNECT scott/tiger; CREATE OR RE

15、PLACE VIEW testviewAS SELECT ename,sal,sal*1.2 as new_sal FROM scott.emp;查询该视图中的数据：SELECT * FROM testview; 利用以上视图将员工SMITH的工资改为1500。UPDATE testview SET sal=1500 WHERE ename=SMITH; -成功利用以上视图将员工SMITH的工作改为SALESMAN。UPDATE testview SET job=SALESMAN WHERE ename=SMITH;-操作失败，因为job列在视图中不可见判断视图中的列是否可以被更新：根据常识，

16、如本例中的sal 和 sal*1.2利用下面的命令判断：-定义各列的显示宽度COLUMN owner format a10 COLUMN table_name format a10COLUMN column_name format a10 -查看视图的可更新列SELECT * FROM USER_UPDATABLE_COLUMNS WHERE table_name=TESTVIEW; 例9.14 创建视图，分别从scott.emp表和scott.dept表中查询empno、ename、sal、dname、loc字段，并利用USER_UPDATABLE_COLUMNS查看视图的列的更新性。 CR

17、EATE OR REPLACE VIEW emp_dept AS SELECT empno,ename,sal,d.deptno,dname,loc FROM scott.emp e,scott.dept d WHERE e.deptno=d.deptno;通过USER_UPDATABLE_COLUMNS查看视图的可更新性COLUMN owner format a10 -定义owner列的显示宽度COLUMN table_name format a10COLUMN column_name format a10注意：如果上例中执行了上面三条命令，本例中可以忽略SELECT * FROM USER_UPDATABLE_COLUMNS WHERE table_name=EMP_DEPT; 分析该实验的结果，总结连接查询中什么样的列可以更新，什么样的列不可以更新？（主表从表）update emp_dept set loc=aaa where empno=7369;ORA-01779: 无法修改与非键值保存表对应的列例9.15 使用WITH CHECK OPTION选项创建视图，用户对视图进行修改和插入操作时，新数据受到WHERE子句的限制。CREA