首页 文章

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

提问于
浏览 次
13

当我在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 并手动删除和刷新数据 . 这种方法有效，但看起来有点像黑客 .

或者（也许最好）我"half" fast refresh on commit ，有一半 complete refresh on demand ） . 我尝试使用 refresh force ，但在使用 EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW() 时，我发现在提交可用时没有fash刷新的证据 . 我也想用 union all 来做这件事 .

sql database oracle materialized-views

3 回答

  • 2

    您可以像这样快速刷新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创建基础表的实体化视图日志 .

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

    Basic MV's
    Join MV's
    Aggregate MV's
    Union all MV's
    Nested MV's
    MV_CAPABILITIES_TABLE
    Summary

    问候，
    抢 .

    已添加：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.

    希望这可以帮助！

    回复于
  • 0

    引自Oracle

    使用多层实体化视图的限制基于实体化视图的主物化视图和物化视图必须：是主键物化视图驻留在9.0.1或更高兼容级别的数据库中注意：COMPATIBLE初始化参数控制数据库的兼容级别 .

    但是，我会为你尝试一个解决方案 . 我会回来的 .

    Update ：对不起，我没有成功 . 你有太多的限制:)

    回复于
  • 3

    根据Oracle文档，您可能会运气不好：

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

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

    回复于

相关问题