Oracle 11g R2 视图

视图是一个虚表,不占用物理空间,因为视图本身的定义语句存储在数据字典里。视图中的数据是从一个或多个实际的表中获得。

物化视图:也成实体化视图,含有实际数据,占用存储空间,在数据仓库中经常应用物化视图

创建视图的语法

CREATE [OR REPLACE] [FORCE |NO FORCE] VIEWview_name [(alias [,alias]..)] as select_statement [WITH CHECK_OPTION[CONSTRAINT constraint]] [WITH READ ONLY];

在语法中

OR REPLACE:如果视图已经存在,此选项将重新创建该视图。

FORC:如果使用此关键字,则无论基表是否存在,都将创建视图

NO FORCE:这是默认值,如果使用此关键字,则仅当基表存在时才创建视图

VIEW_NAME:要创建的视图名

ALIAS:指定由视图的查询所选择的的表达式或列的别名。别名的数目必须与视图所选择的的表达式的数据相匹配。

 

select_statement:SELECT 语句

WITH CHECK_OPTION:此选项指定只能插入或更新视图可以访问的行,constraint标识CHECK OPTION约束指定的名称

WITH READ ONLY:此选项保证不能再视图上执行任何修改操作。

创建带有错误的视图

如果在CREATE VIEW语法中使用FORCE选项,即使存在以下情况,也会创建视图

视图定义的查询引用了一个不存在的表

视图定义的查询引用了现有表中无效的列。

视图的所有者没有所需的权限。

在这些情况下,oracle仅检查CREATE VIEW语句中语法错误,如果语法正确,将会创建视图,并将视图的定义存储在数据字典中,但是该视图却不能使用。这种视图被认为是带有错误创建的。可以用SHOW ERRORS VIEW视图名来查看错误

 对单表的视图操作

SQL> create table order_master (ordernonumber(5) CONSTRAINT p_ord PRIMARY KEY,

 2  odate DATE,vencode number(5),

 3  o_status char(1));

插入数据

SQL> insert into order_master values (1,to_date('2010-01-01','yyyy-mm-dd'),1,'a');

SQL> insert into order_master values(2,to_date('2011-01-01','yyyy-mm-dd'),2,'p');

创建订单状态为"p"的视图,提示没有创建视图的权限

授予SCOTT用户创建视图的权限

创建视图

SQL> create view pen_view as select *from order_master where o_status = 'p';

查询视图


通过视图修改数据,将状态为“p”的订单修改为“d”

SQL> update pen_view SET o_status='d'where o_status='p';

如果修改成功,在查询视图将查询不出任何记录,因为修改了创建视图是作为条件的列


为了避免修改视图后查询不到记录的现象,使用with check option语句创建检查约束以防止上述情况的发生,同时可以使用CONSTRAINT指定约束名称

SQL> create or replace view pen_view asselect * from order_master where o_status='p'

 2  with check option constraintpenv;

为order_master插入记录

SQL> insert into order_master values(3,to_date('2011-01-01','yyyy-mm-dd'),3,'p');

更新视图

SQL> update pen_view set o_status='d'where o_status='p';

提示with check option违反where子句

创建只读视图

SQL> create or replace view pen_view asselect * from order_master with read only;

查看视图

为视图插入记录


创建带有错误的视图

因为不存在venmast表

创建表venmast

SQL> create table venmast (id int);

手动编译刚才创建的错误视图


查看视图

创建带ORDER BY子句的视图

SQL> create or replace view pen_view asselect * from order_master order by orderno;

复杂视图

DML语句是指用于修改数据的INSERT,DELETE,UPDATE语句。因为视图是一个虚表,所以这些语句也可以与视图一同使用。一般情况下不通过视图修改数据,而是直接修改基本表,因为这样调理更清晰。在视图上使用DML语句有如下限制:(相对于表)

DML语句只能修改视图中的一个基表

如果对记录的修改违反了基表的约束条件,则将无法更新视图

如果创建的视图包含连接运算符,DISTINCT运算符,集合运算符,聚合函数和group BY子句,则将无法更新视图。

如果创建的视图包含伪列或表达式,则将无法更新视图。

简单视图基于单个基表,不包括函数和分组函数,那么可以在此视图中进行INSERT,UPDATE,DELETE操作。这些操作实际上是在基表中插入、更新和删除行。

复杂视图从多个提取数据,包括函数和分组函数,复杂视图不一定能进行DML操作。

删除视图可以使用

物化视图

物化视图是和普通视图相对应的,在oracle使用普通视图时,它会重复执行创建视图的所有sql语句,如果这样的SQL语句含有多张表的连接或者ORDER BY子句,而且表的数据量很大,则会非常耗时,效率非常低下。为了解决这个问题,oracle提出了物化视图的概念

物化视图就是具有物理存储的特殊视图,占用物理空间,就象表一样,物化视图是基于表,物化视图等创建的。它需要和源表进行同步,不断的刷新物化视图中的数据。物化视图有两个重要概念:查询重写和物化视图的同步

查询重写:

对SQL语句进行重写。当用户使用SQL语句对基表进行查询时,如果已经建立了基于这些基表的物化视图,oracle将自动计算和使用物化视图来完成查询,在某些情况下可以节约查询时间,减少系统I/O。这种查询优化技术成为查询重写。参数QUERY_REWRITE_ENABLED决定是否使用重写查询。在创建物化视图时需要使用ENABLE QUERY REWRITE来启动查询重写功能

可通过SHOW命令查看该参数的值

物化视图的同步:

物化视图是基于表创建的,所以当基表发生变化时,需要同步数据以更新物化视图中的数据,这样保持无话视图中的数据和基表的数据的一致性。oracle提供了两种物化视图刷新方式

ON COMMIT:指物化视图在对基表的DML操作事物提交的通行进行刷新

ON DEMAND:指物化视图在用户需要的时候进行刷新,可以手工通过DBMS_IVIEW.refresh等方法来进行刷新,也可以通过JOB定时刷新

选择刷新方式后,还需要选择一种刷新类型,刷新类型值刷新时基表与物化视图如何实现数据同步,oracle提供了一下4种刷新类型:

COMPLETE:对整个物化视图进行完全刷新。

FAST:采用增量刷新,只刷新自上次刷新以后进行的修改

FORCE:oracle在刷新会判断是否可以进行快速刷新,如果可以则采用FAST刷新方式,否则使用COMPLETE方式。

NEVER:物化视图不进行任何刷新

创建物化视图

创建物化视图的前提条件:

具备创建物化视图的权限,QUERY REWRITE的权限,以及对创建物化视图所涉及的表的访问权限和创建表的权限

使用SCOTT用户来举例说明

1.授予相应的权限

SQL> show user;

USER is "SYS"

SQL> grant create materialized view toscott;

SQL> grant query rewrite to scott;

SQL> grant create any table to scott;

SQL> grant select any table to scott;

2.创建物化视图日志

物化视图日志是用户选择了FAST刷新类型时需要使用的,以增量同步基表的变化。

对SCOTT用户的EMP表和DEPT表创建物化视图,所以对这两个基表创建物化视图日志

SQL> create materialized view log ondept with rowid;

SQL> create materialized view log on empwith rowid;

创建物化视图

通过CREATE MATERIALIEZED VIEW语句来创建物化视图,

SQL> create materialized viewmtrlview_test

 2  build immediate

 3  refresh fast

 4  on commit

 5  enable query rewrite as

 6  selectd.dname,d.loc,e.ename,e.job,e.mgr,e.hiredate,e.sal,d.rowid d_rowid,e.rowide_rowid

 7  from dept d,emp e whered.deptno=e.deptno;

其中:

BUILD IMMEDIATE:该参数的意思是立即创建物化视图;也可以选择BUILD DEFFERED,该参数说明在物化视图定义以后不会立即执行,而是延迟执行,在使用该视图在创建。

REFRESH FAST:刷新数据的类型选择FAST类型

ON COMMIT:在基表有更新时提交后立即更新物化视图

ENABLE QUERY REWRITE :启动查询重写功能,在创建物化视图是明确说明启用查询重写功能。

AS:定义后面的查询语句

查询体:物化视图的查询内容。该SQL语句的查询结果集输出到物化视图中,保存在由oracle自动创建的表中。

删除物化视图

如果对此有兴趣,请扫下面二维码免费获取更多详情



展开阅读全文

没有更多推荐了,返回首页