Oracle 视图索引

大家好，又见面了，我是你们的朋友全栈君。

第五章 视图索引的操作

5.1 视图的功能

一个视图实际上就是封装了一条复杂的查询语句

注：为了在当前用户模式中创建视图，要求数据库用户必须有create any view（创建任何视图）的权限。

5.2 创建视图的语法

create [or replace] view 视图名称 as 查询语句

例：建立一个视图，包含全部部门编号为20的部门的雇员信息（雇员编号，姓名，工作，部门编号）

create view emp_view as select empno,ename,job,deptno

from emp where deptno=20;

试图创建完成之后，就可以像查找表那样直接对视图进行查询的操作

5.3 查询视图

select * from emp_view;

此时，是通过试图找到的20部门的所有数据，也就是发现，可以使用视图包装需要的查询语句。

例：向emp_view视图中插入一条数据

5.4 更新视图信息

例：修改视图中的9527的部门编号

update emp_view set ename=’西方’ where emono=9527;

5.5 删除视图中的信息

delete from 视图名字 [where 条件]

例：删除9527的信息   delete fromemp_view where empno=9527;

5.6 建立只读视图

with read only:创建的视图只读

定义只读视图后，数据库用户只能在该视图上执行select语句。

例：创建只能读的视图  

create or replace view VIEW_EMP_READONLY

as select * from dept where deptno!=88 with read only;

例：更新视图中所有部门位置为‘长春’的记录（选择非只读视图）

update VIEW_EMP_READONLY set loc=’长春’;

5.7 复杂视图

复杂视图是指包含函数，表达式，分组数据的视图，主要目的是为了简化操作，需要注意的是，当视图的查询包含函数或者表达式的时候，必须定义别名。

例：创建一个视图，要求查询每个部门的工资情况(最高，最低，平均)。

create or replace view emp_view_complex

as 

select deptno 部门编号,max(sal) 最高工资,min(sal) 最低工资,avg(sal) 平均工资

from emp

group by deptno;

select * from emp_view_complex group by 部门编号;

5.8 连接视图

连接视图是基于多个表所建立的视图，使用连接视图的主要目的是为了简化连接插叙。

注：建立连接视图时，必须使用where子句中指定有效的连接条件。

例：创建一个dept和emp表相互关联的视图，并要求该视图只能查询部门编号为20的记录信息。

create or replace view emp_view_union

as

select dept.deptno,dname,emp.ename,sal

from dept,emp

where dept.deptno=emp.deptno

and dept.deptno=20;

5.9 删除视图

格式：drop view 视图名称

例：删除视图emp_view_union   drop view emp_view_union;

5.10 索引概述

若一个表存在海量的数据记录，当对表执行指定条件查询的时候，常规的查询方法是将所有记录全部读取出来，然后把读取的每条记录与查询条件进行对比，最后返回满足条件的结果。这样进行的操作时间的开销和I/O开销很大，此时，可以考虑通过建立索引来减小系统开销。

若要在表中查询指定的记录，在没有索引的情况下，必须遍历整个表，而有了索引之后，只需要在索引中查询条件的索引字段值，就可以通过保存在索引中的rowid（系统为每个记录分配的物理地址）快速找到表中对应的记录。

若将表看成一本书，索引的作用就是目录。在没有目录的情况下，要找到指定的内容就必须阅读全书，有了目录，就能通过目录快速找到知识点。

5.11 创建索引

建立索引的语法：create index语句

注：在创建索引时，要求用户具有create any index系统权限，若无此权限先授权。

Grant create any index to scott;

例：在scott模式下，为emp表的deptno列创建索引。

create index emp_deptno_index on emp(deptno);

复合索引：基于两个列或多个列的索引在同一张表上可以有多个索引，但是要求列的组合必须不同。如：

create index emp_idx1 on emp(ename,job);

create index emp_idx2 on emp(job,ename);

5.12 删除索引

格式：drop index 索引名称

注：需要有drop any index权限

例：删除索引emp_deptno_index    drop index emp_deptno_index;

5.13 索引有一些先天不足

1.系统需占用大约为表1.2倍的硬盘和内存空间来保存索引。（占用内存空间）

2.更新数据时，系统需有额外的时间来同时对索引进行更新，维持数据和索引的一致性。（除了查询操作，其他都慢）

因此，不恰当的索引不但于事无补，反而会降低系统性能。因为大量的索引在进行插入，修改和删除操作时比没有索引花费更多的系统时间。

修改用户的密码：alter user 用户名 identified by 密码;

例：将scott的用户密码修改为hello    alter user scott identified by hello;