当我在Oracle 10g上运行以下代码时：
drop materialized view test4; drop materialized view test3; drop table test2; drop table test1; create table test1 ( x1 varchar2(1000),constraint test1_pk primary key (x1) ); create materialized view log on test1 with sequence; create table test2 ( x2 varchar2(1000),constraint test2_pk primary key (x2) ); create materialized view log on test2 with sequence; create materialized view test3 refresh complete on demand as ( select x1 from test1 union all select null from dual where 0 = 1 ); alter table test3 add constraint test3_pk primary key (x1); create materialized view log on test3 with sequence; create materialized view test4 refresh fast on commit as ( select t1.rowid as rid1,t2.rowid as rid2,t1.x1 u1,t2.x2 from test3 t1,test2 t2 where t1.x1 = t2.x2 );
尝试创建物化视图test4时收到此错误：
sql Error: ORA-12053: this is not a valid nested materialized view 12053. 00000 - "this is not a valid nested materialized view" *Cause: The list of objects in the FROM clause of the definition of this materialized view had some dependencies upon each other. *Action: Refer to the documentation to see which types of nesting are valid.
我不明白“FROM子句”中的任何对象是否相互依赖.
我该如何让这个工作？目前我唯一可以想到的办法就是用普通表替换test3,并手动删除和刷新数据.这种方法有效,但似乎有点黑客.
或者(也许最好)我只想看一个可以有两个表的示例,并将它们连接到一个物化视图中,其中一个基表是批量更新的(并且不需要在物化视图中反映出来) ),但其他更新应反映在物化视图中(即,它是一种“半”快速刷新提交,一半完全刷新需求).我尝试使用刷新力,但是当使用EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW()时,我没有发现提交时可以刷新刷新的迹象.我也想这样做与工会alls以及.
解决方法
您可以使test4实现视图刷新速度如下：
sql> create table test1 2 ( x1 varchar2(1000) 3,constraint test1_pk primary key (x1) 4 ) 5 / Table created. sql> create materialized view log on test1 with rowid 2 / Materialized view log created. sql> create table test2 2 ( x2 varchar2(1000) 3,constraint test2_pk primary key (x2) 4 ) 5 / Table created. sql> create materialized view log on test2 with rowid 2 / Materialized view log created. sql> create materialized view test4 2 refresh fast on commit 3 as 4 select t1.rowid as rid1 5,t2.rowid as rid2 6,t1.x1 u1 7,t2.x2 8 from test1 t1 9,test2 t2 10 where t1.x1 = t2.x2 11 / Materialized view created. sql> insert into test1 values ('hello') 2 / 1 row created. sql> insert into test2 values ('hello') 2 / 1 row created. sql> commit 2 / Commit complete. sql> select * from test4 2 / RID1 RID2 ------------------ ------------------ U1 --------------------------------------------- X2 --------------------------------------------- AAATU5AAEAAAssfAAA AAATU8AAEAAAssvAAA hello hello 1 row selected.
你的情况不起作用,因为一个嵌套的MV工作,一个基础的MV不能是一个基本的MV.这首先听起来很奇怪,但是你需要一个像你这样做的技巧,使它成为可能.此外,要使连接MV工作,基础表的物化视图日志需要使用ROWID创建.
你可能想看看我写的关于快速刷新的物化视图错误的一系列博文.他们描述几乎所有的限制：
Basic MV’s
Join MV’s
Aggregate MV’s
Union all MV’s
Nested MV’s
MV_CAPABILITIES_TABLE
Summary
问候,
抢.
补充：29-09-2011
这里是一个使用嵌套MV的示例,它使用了union2上的所有技巧：
sql> create table test1 2 ( x1 varchar2(1000) 3,constraint test2_pk primary key (x2) 4 ) 5 / Table created. sql> create materialized view log on test2 with rowid 2 / Materialized view log created. sql> create materialized view test2_mv 2 refresh fast on commit 3 as 4 select rowid rid 5,x2 6,'A' umarker 7 from test2 8 union all 9 select rowid 10,x2 11,'B' 12 from test2 13 where 1=0 14 / Materialized view created. sql> alter table test2_mv add constraint test2_mv_pk primary key(x2) 2 / Table altered. sql> create materialized view log on test2_mv with rowid 2 / Materialized view log created. sql> create materialized view test3 2 refresh fast on commit 3 as 4 select rowid rid 5,x1 6,'A' umarker 7 from test1 8 union all 9 select rowid 10,x1 11,'B' 12 from test1 13 where 0 = 1 14 / Materialized view created. sql> alter table test3 add constraint test3_pk primary key (x1) 2 / Table altered. sql> create materialized view log on test3 with rowid 2 / Materialized view log created. sql> create materialized view test4 2 refresh fast on commit 3 as 4 select t1.rowid as rid1 5,t2.x2 8 from test3 t1 9,test2_mv t2 10 where t1.x1 = t2.x2 11 / Materialized view created. sql> insert into test1 values ('hello') 2 / 1 row created. sql> insert into test2 values ('hello') 2 / 1 row created. sql> commit 2 / Commit complete. sql> select * from test4 2 / RID1 RID2 ------------------ ------------------ U1 --------------------------------------------------- X2 --------------------------------------------------- AAATXbAAEAAAstdAAA AAATXXAAEAAAstNAAA hello hello 1 row selected.
希望这可以帮助！
