Ошибка материализованной версии Oracle: включен код

Когда я запускаю следующий код на 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 на обычную таблицу и вручную удалить и обновить данные. Этот подход работает, но кажется немного взломанным.

В качестве альтернативы (и, возможно, предпочтительно) я просто хотел бы увидеть пример, где могут быть две таблицы, и объединить их в материализованное представление, где одна из базовых таблиц многократно обновляется (и не нуждается в отражении в материализованное представление), но другие обновления должны отражаться в материализованном виде (т.е. это "половина" fast refresh on commit и половина complete refresh on demand). Я попытался использовать refresh force, но при использовании EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW() я не обнаружил доказательств обновления fash при наличии коммита. Я также хотел бы сделать это с помощью union all.

Ответы

Ответ 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 для соединения необходимо создать материализованные журналы просмотра базовой таблицы WITH ROWID.

Возможно, вам захочется взглянуть на серию сообщений в блогах, которые я написал о быстро обновляемых материализованных ошибках. Они описывают почти все ограничения:

Основные MV
Присоединиться к MV
Агрегат MV
Объединить все MV
Вложенные MV
MV_CAPABILITIES_TABLE
Резюме

С уважением,
Роб.

Добавлено: 29-09-2011

Вот пример с вложенным MV с помощью объединения всех трюков на test2:

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.

Надеюсь, это поможет!

Ответ 2

Цитата из Oracle

Ограничения для использования многоуровневых материализованных представлений

Оба материализованных представления и материализованные взгляды на основе материализованные представления должны:

  • Первичные ключевые материализованные представления
  • Оставайтесь в базе данных с уровнем совместимости 9.0.1 или выше

Примечание. Параметр инициализации COMPATIBLE управляет базой данных уровень совместимости.

Однако я попробую решение для вас. Я вернусь.

Обновление: Извините, я не добился успеха. У вас слишком много ограничений:)

Ответ 3

Возможно, вам не повезло в документации Oracle:

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

Вы можете создать вложенное материализованное представление для материализованных представлений, но все родительские и базовые материализованные представления должны содержать объединения или агрегаты. Если определяющие запросы для материализованного представления не содержат объединений или агрегатов, он не может быть вложен. Все базовые объекты (материализованные представления или таблицы), на которых определено материализованное представление, должны иметь материализованный журнал представлений. Все базовые объекты обрабатываются так, как если бы они были таблицами. Кроме того, вы можете использовать все существующие варианты материализованных представлений.