Oracle物化视图错误：包含代码

Question

当我在 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()时，我没有发现提交时快速刷新可用的证据。我也想用union all来做到这一点。

Answer 1

您可以像这样快速刷新 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。乍一看这听起来很奇怪，但您需要像使用 test3 那样的技巧才能使其工作。此外，要使连接 MV 正常工作，需要使用 ROWID 创建基础表的物化视图日志。

您可能想查看我撰写的有关快速可刷新物化视图错误的一系列博客文章。它们描述了几乎所有限制：

基本 MV
加入 MV
聚合 MV
合并所有 MV
嵌套 MV
MV_CAPABILITIES_TABLE
摘要

问候，
抢。

---

Added: 29-09-2011

下面是一个在 test2 上使用 union all 技巧的嵌套 MV 的示例：

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 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.rowid as rid2
  6         , t1.x1 u1
  7         , 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.

希望这会有所帮助！

Answer 2

引用自 Oracle

使用多层物化视图的限制

既掌握物化视图又基于物化视图
物化视图必须：

• 成为主键物化视图
• 驻留在兼容级别为 9.0.1 或更高版本的数据库中

注意：COMPATIBLE 初始化参数控制数据库的
兼容性级别。

不过，我会为你尝试一个解决方案。我会回来的。

更新：抱歉，我没有成功。你的限制太多了:)

Answer 3

根据 Oracle 文档，您可能不走运：

http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/basicmv.htm#i1006734

您可以在物化视图上创建嵌套物化视图，但所有父级和基级
物化视图必须包含联接或聚合。如果物化视图的定义查询不包含联接或聚合，则不能嵌套。定义该物化视图的所有基础对象（物化视图或表）都必须具有物化视图日志。所有底层对象都被视为表。此外，您可以使用物化视图的所有现有选项。