《Oracle数据库管理、开发与实践 教学课件 ppt 作者 杨永健 刘尚毅 第9章 视图、索引、序列、同义词》由会员分享，可在线阅读，更多相关《Oracle数据库管理、开发与实践 教学课件 ppt 作者 杨永健 刘尚毅 第9章 视图、索引、序列、同义词（28页珍藏版）》请在金锄头文库上搜索。
1、,本章要求：,第9章 视图、索引、序列、同义词,掌握视图对象的创建和管理 掌握索引对象的创建和维护 掌握序列对象的创建和管理 了解同义词对象,主要内容,1.视图对象 2.索引 3.序列 4.同义词 5.综合实例创建多表连接视图,第9章 视图、索引、序列、同义词,9.1 视图对象,9.1.1 创建视图 9.1.2 管理视图,创建视图是使用CREATE VIEW语句完成的。为了在当前用户模式中创建视图，要求数据库用户必须具有CREATE VIEW系统权限；如果要在其它用户模式中创建视图，则用户必须具有CREATE ANY VIEW系统权限，创建视图最基本的语法如下： create or repla
2、ce view alias,alias) as with check option constraint constraint_name with read only 参数说明如下： alias：用于指定视图列的别名， subquery：用于指定视图对应的子查询语句 with check option：该子句用于指定在视图上定义的CHECK约束； with read only：该子句用于定义只读视图。 在创建视图时，如果不提供视图列别名，Oracle会自动使用子查询的列名或列别名；如果视图子查询包含函数或表达式，则必须定义列别名。下面通过若干示例说明建立和使用视图的方法。 1简单视图 简单视图
3、是指基于单个表建立的，不包含任何函数、表达式和分组数据的视图，下面来看一个例子。,9.1.1 创建视图,【例9-1】 在SCOTT模式下，创建一个查询部门编号为20的视图，代码及运行结果如下。 SQL connect scott/1qaz2wsx 已连接。 SQL create or replace view emp_view as 2 select empno,ename,job,deptno 3 from emp 4 where deptno = 20; 视图已创建。 上述语句建立一个视图EMP_SALES_VIEW。因为建立视图时没有提供列别名，所以视图的列名分别为EMPNO、ENAME
4、、JOB和DEPTNO，用户可以通过SELECT语句像查询普通的数据表一样查询视图的信息，来看下面的例子。,在创建视图后，用户还可以对视图进行管理，主要包括：查看视图的定义信息、修改视图定义、重新编译视图和删除视图。 （1）查看视图定义 前面介绍过，数据库并不存储视图中数值，而是存储视图的定义信息。用户可以通过查询数据字典视图USER_VIEWS，已获得视图的定义信息。 【例9-8】 使用desc命令查看USER_VIEWS数据字典的结构，代码如下。 本例运行结果如图9-4所示。 图9-4 USER_VIEWS数据字典的结构 在USER_VIEWS视图中，TEXT列存储了用户视图的定义信息，即
5、构成视图的SELECT语句，来看下面的例子。,9.1.2 管理视图,9.2 索引,9.2.1 索引概述 9.2.2 创建索引 9.2.3 修改索引 9.2.4 删除索引 9.2.5 显示索引信息,9.2.1 索引概述,如果一个数据表中存有海量的数据记录，当对表执行指定条件的查询时。常规的查询方法会将所有的记录都读取出来，然后再把读取的每一条记录与查询条件进行比对，最后返回满足条件的记录。这样进行操作的时间开销和I/O开销都十分巨大的。对于这种情况，就可以考虑通过建立索引来减小系统开销。 如果要在表中查询指定的记录，在没有索引的情况下，必须遍历整个表，而有了索引之后，只需要在索引中找到符合查询条
6、件的索引字段值，就可以通过保存在索引中的ROWID快速找到表中对应的记录。举个例子来说，如果将表看作一个本书，则索引的作用则类似于书中的目录。在没有目录的情况下，要在书中查找指定的内容必须阅读全书，而有了目录之后，只需要通过目录就可以快速找到包含所需内容的页码（相当于ROWID）。 Oracle系统对索引与表的管理有很多相同的地方，不仅需要在数据字典中保存索引的定义，还需要在表空间中为它分配实际的存储空间。创建索引时，Oracle会自动在用户的默认表空间或指定的表空间中创建一个索引段，为索引数据提供空间。 说明：将索引和对应的表分别放在不同硬盘的不同的表空间中能够提高查询的速度，因为Oracl
7、e能够并行读取不同硬盘的数据，这样的查可以避免产生I/O冲突。 用户可以在Oracle中创建多种类型的索引，以适应各种表的特点。按照索引数据的存储方式可以将索引分为B树索引、位图索引、反向键索引和基于函数的索引；按照索引列的唯一性又可以分为唯一索引和非唯一索引；按照索引列的个数又可以分为单列索引和复合索引。,建立和规划索引时，必须选择合适的表和列，如果选择的表和列不合适，不仅无法提高查询速度，反而会极大地降低DML操作的速度，所以建立索引必须要注意以下几点： 索引应该建立在WHERE子句频繁引用表列上，如果在大表上频繁使用某列或某几个列作为条件执行索引操作，并且检索行数低于总行数15%，那么应
8、该考虑在这些列上建立索引。 如果经常需要基于某列或某几个列执行排序操作，那么在这些列上建立索引可以加快数据排序速度。 限制表的索引个数。索引主要用于加快查询速度，但会降低DML操作的速度。索引越多，DML操作速度越慢，尤其会极大地影响INSERT和DELETE操作的速度。因此，规划索引时，必须仔细权衡查询和DML的需求。 指定索引块空间的使用参数。基于表建立索引时，Oracle会将相应表列数据添加到索引块。为索引块添加数据时，Oracle会按照PCTFREE参数在索引块上预留部分空间，该预留空间时为将来的INSERT操作准备的。如果将来在表上执行大量INSERT操作，那么应该在建立索引时设置较
9、大的PCTFREE。 将表和索引部署到相同的表空间，可以简化表空间的管理；将表和索引部署到不同的表空间，可以提高访问性能。 当在大表上建立索引时，使用NOLOGGING选项可以最小化重做记录。使用NOLOGGING选项可以节省重做日志空间、降低索引建立时间、提高索引并行建立的性能。 不要在小表上建立索引。 为了提高多表连接的性能，应该在连接列上建立索引。,在创建索引时，Oracle首先对将要建立索引的字段进行排序，然后将排序后的字段值和对应记录的ROWID存储在索引段中。建立索引可以使用CREATE INDEX语句，通常由表的所有者来建立索引。如果要以其它用户身份建立索引，则要求用户必须具有C
10、REATE ANY INDEX系统权限或者相应表的INDEX对象权限。具体语法格式如下： create index 索引名称 on 表名（列名） 1建立B树索引 B树索引是Oracle数据库最常用的索引类型（也是默认的），它是以B树结构组织并存放索引数据的。默认情况下，B树索引中的数据是以升序方式排列的。如果表包含的数据非常多，并且经常在WHERE子句中引用某列或某几个列，则应该基于该列或这几个列建立B树索引。B树索引由根块、分支块和叶块组成，其中主要数据都集中在叶子节点，如图9-6所示。 图9-6 B树索引的逻辑结构图,9.2.2 创建索引,根块：索引顶级块，它包含指向下一级节点的信息。 分
11、支块：它包含指向下一级节点（分支块或叶块）的信息。 叶块：通常也称叶子，它包含索引入口数据，索引入口包含索引列的值和记录行对应的物理地址ROWID。 在B树索引中无论用户要搜索哪个分支的叶块，都可以保证所经过的索引层次是相同的。Oracle采用这种方式的索引，可以确保无论索引条目位于何处，都只需要花费相同的I/O即可获取它，这就是为什么被称为B树索引（B是英文BALANCED的缩写）。 例如，使用这个B树索引搜索编号为“A004”的节点时，首先要访问根节点，从根节点中可以发现，下一步应该搜索左边的分支，由于值A004小于值B001，因此不需第二次读取数据，而直接读取左边的分支节点。从左边的分支
12、节点可以判断出，要搜索的索引条目位于右侧的第一个叶子节点中。在那里可以很快找到要查询的索引条目，并根据索引条目中的ROWID进而找到所有要查询的记录。 如果在WHERE子句中要经常应用某列或某几列，应该基于这些列建立B树索引。下面来看一个例子。,9.2.3 修改索引,修改索引是通常使用ALTER INDEX语句来完成的。一般情况下，修改索引是由索引的所有者完成的，如果要以其它用户身份修改索引，则要求该用户必须具有ALTER ANY INDEX系统权限或在相应表上的INDEX对象权限。 1合并索引和重建索引 为表建立索引后，随着对表不断进行更新、插入和删除操作，索引中会产生越来越多的存储碎片，这
13、对索引的工作效率会产生负面影响。这是可以采取两种方式来清除碎片重建索引或合并索引。合并索引只是将B树中叶子节点的存储碎片合并在一起，并不会改变索引的物理组织结构。下面来看一个例子。 【例9-19】 在SCOTT模式下，对索引emp_deptno_index执行合并操作，代码及运行结果如下。 SQL alter index emp_deptno_index 2 coalesce deallocate unused; 索引已更改。 图9-11显示了对索引执行合并操作后的效果。假设在执行该操作之前，B树索引的前两个叶块分别有70%和30%的空闲空间。合并索引后，可以将它们的数据合并到一个索引叶子块中
14、。 图9-11 对B树索引进行合并操作,【例9-20】 在SCOTT模式下，对索引emp_deptno_index执行进行重建，代码及运行结果如下。 SQL alter index emp_deptno_index rebuild; 索引已更改。 在使用ALTER INDEXREBUILD语句重建索引时，还可以在其中使用REVERSE子句将一个反向键索引更改为普通索引，反之可以将一个普通的B树索引转换为反向键索引。另外，也可以使用TABLESPACE子句指定重建索引的存放位置，如下面的例子。 【例9-21】 在SCOTT模式下，对索引emp_deptno_index执行进行重建，并重新指定该索
15、引对象的表空间，代码及运行结果如下。 SQL alter index emp_deptno_index rebuild 2 tablespace example; 索引已更改。,9.2.4 删除索引,删除索引是使用DROP INDEX语句完成的。一般情况下，删除索引是由索引所有者完成的，如果以其它身份删除索引，则要求该用户必须具有DROP ANY INDEX系统权限或在相应表上的INDEX对象权限。通常在如下情况下需要删除某个索引。 如果移动了表中的数据，导致索引中包含过多的存储碎片，此时需要删除并重建索引。 通过一段时间的监视，发现很少有查询会使用到该索引。 该索引不再需要时应该删除该索引，
16、以释放其所占用的空间。 索引被删除后，它所占用的所有盘区都将返回给包含它的表空间，并可以被表空间中的其它对象使用。索引的删除方式与索引创建采用的方式有关，如果使用CREATE INDEX语句显式地创建该索引，则可以用DROP INDEX语句删除该索引，下面来看一个例子。 【例9-22】 在SCOTT模式下，删除函数索引emp_job_fun，代码及运行结果如下。 SQL drop index emp_job_fun; 索引已删除。 如果索引是定义约束时由Oracle系统自动建立的，则必须禁用或删除该约束本身。另外，在删除一个表时，Oracle也会删除所有与该表有关的索引。 关于索引最后需要注意一点，虽然一个表可以拥有任意数目的索引，但是表中的索引数据越多，维护索引所需要的开销也就越大。每当向表中插入，删除和更新一条记录时，Oracle都必须对该表的所有索引进行更新。因此，用户还需要在表的查询速度和更新速度之间找到一个合适的平衡点。也就是说，应该根据表的实际情况限制在表中创建
