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视图名来查看错误 对单表的视图操作 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‘,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; 更新视图 SQL> update pen_view set o_status=‘d‘where o_status=‘p‘; 提示with check option违反where子句 创建只读视图 查看视图 为视图插入记录 创建带有错误的视图 因为不存在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自动创建的表中。 删除物化视图 如果对此有兴趣,请扫下面二维码免费获取更多详情 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |