暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

Oracle 从视图中创建

askTom 2018-02-16
529

问题描述

使用视图的MV创建强制使用最小1个表,即使该表是虚拟的。
直接在view上创建MV失败,并ORA-12015 ORA错误。此外,如果我使用对偶而不是虚拟表,它会给出不同的ORA错误ORA-30354。下面请找到使用的代码。

---Major tables:
create table DEPT2(dept_id number(10) Primary key, Dname varchar2(20));

create table emp2(empid number(20) primary key, ename varchar2(20), sal number(10,2), dept_id number(10) references dept2(dept_id));

---Data inserted.

insert into dept2 values(10,'IT');
insert into dept2 values(20,'HR');
insert into dept2 values(30,'MAT');

insert into emp2 values(1,'PK',20000,10);
insert into emp2 values(2,'ANISH',30000,20);
insert into emp2 values(3,'RAJ',20000,20);
insert into emp2 values(4,'TOM',40000,30);

Commit;

----------Log tables creation

CREATE MATERIALIZED VIEW LOG on DEPT2
WITH ROWID, Primary Key, SEQUENCE
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG on EMP2
WITH ROWID, Primary Key, SEQUENCE
INCLUDING NEW VALUES;

-------Create view
create or replace view V_DEPT2_EMP2
as
select a.rowid erowid,b.rowid drowid ,b.dept_id did,b.dname, a.ename,a.sal, a.dept_id edeptid
from emp2 a, dept2 b
where a.dept_id=b.DEPT_id;

-----Check the o/p of the view

select * from V_DEPT2_EMP2;

Scripts for scenarios:

1.       Failed scenario : MV with View

     CREATE MATERIALIZED VIEW MV_EMP2_DEPT2 
     BUILD DEFERRED
     REFRESH FAST ON DEMAND
     WITH PRIMARY KEY
     ENABLE QUERY REWRITE
     As
     select * from V_DEPT2_EMP2;

   ORA-12015: cannot create a fast refresh materialized view from a complex query


2.       Failed scenario: MV with DUAL

     CREATE MATERIALIZED VIEW MV_EMP2_DEPT2 
     BUILD DEFERRED
     REFRESH FAST ON DEMAND
     WITH PRIMARY KEY
     ENABLE QUERY REWRITE
     As
     select  A.*, D.rowid drowid 
     from V_DEPT2_EMP2 A, Dual D

     ORA-30354: Query rewrite not allowed on SYS relations

3.       Success scenario : MV with view and dummy table

     Create table ABC(id number(1) primary key );

     Insert into ABC values(1);

     Commit;

     CREATE MATERIALIZED VIEW LOG on ABC
     WITH ROWID, Primary Key, SEQUENCE
     INCLUDING NEW VALUES;

     CREATE MATERIALIZED VIEW MV_EMP2_DEPT2 
     BUILD DEFERRED
     REFRESH FAST ON DEMAND
     WITH PRIMARY KEY
     ENABLE QUERY REWRITE
     As
     select  A.*, D.rowid ABCrowid 
     from V_DEPT2_EMP2 A, ABC D;
     
     
     execute dbms_MView.refresh('MV_EMP2_DEPT2 ','C',atomic_refresh=>FALSE)

     select * from MV_EMP2_DEPT2;
     
     erowid   drowid    did dname ename  sal edeptid empid abcrowid
   AABGN0AAAAAAGcMAAA AABGNyAAAAAAF5cAAA 10 IT PK  20000 10  1 AABGOHAAAAAAGiEAAA
   AABGN0AAAAAAGcMAAB AABGNyAAAAAAF5cAAB 20 HR ANISH 30000 20  2 AABGOHAAAAAAGiEAAA
   AABGN0AAAAAAGcMAAC AABGNyAAAAAAF5cAAB 20 HR RAJ  20000 20  3 AABGOHAAAAAAGiEAAA
   AABGN0AAAAAAGcMAAD AABGNyAAAAAAF5cAAC 30 MAT TOM  40000 30  4 AABGOHAAAAAAGiEAAA




4.       Success scenario : MV with directly using the tables in the view.

     Drop MATERIALIZED VIEW MV_EMP2_DEPT2;

     CREATE MATERIALIZED VIEW MV_EMP2_DEPT2 
     BUILD DEFERRED
     REFRESH FAST ON DEMAND
     WITH PRIMARY KEY
     ENABLE QUERY REWRITE
     As
     select a.rowid erowid,b.rowid drowid ,b.dname, a.*
     from emp2 a, dept2 b
     where a.dept_id=b.DEPT_id;
     
     execute dbms_MView.refresh('MV_EMP2_DEPT2 ','C',atomic_refresh=>FALSE);
     
     select * from MV_EMP2_DEPT2;
erowid    drowid     dname empid ename sal  dept_id
AABGN0AAAAAAGcMAAA AABGNyAAAAAAF5cAAA IT 1  PK  20000 10
AABGN0AAAAAAGcMAAB AABGNyAAAAAAF5cAAB HR 2  ANISH 30000 20
AABGN0AAAAAAGcMAAC AABGNyAAAAAAF5cAAB HR 3  RAJ  20000 20
AABGN0AAAAAAGcMAAD AABGNyAAAAAAF5cAAC MAT 4  TOM  40000 30
复制



结论问题:
请让我们知道为什么语句1和语句2失败,尤其是1 as 1和3将具有逻辑上相同的输出。


专家解答

谢谢你的耐心。

我与在mviews上工作的开发人员进行了核对。场景1是一个错误-应该允许快速刷新,因为允许视图解析。

我会记录一只虫子。解决方法是直接使用视图的文本,就像你已经发现的那样。
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论