Невозможно установить атрибут обновления ON COMMIT при создании материализованного представления, содержащего частичный первичный ключ в Oracle

Мне нужно извлечь уникальные значения столбца, который является частью первичного ключа, из таблицы в материализованное представление. Я могу создать материализованное представление, если использую «обновление завершено», но безуспешно пытаюсь использовать «быстрое обновление при фиксации». Может ли кто-нибудь указать, пропустил ли я что-нибудь или Oracle не поддерживает такие действия.

Пример вывода приведен ниже. Спасибо.

SQL> create table TEST( col1 number, col2 number, col3 varchar(32), CONSTRAINT test_pk Primary Key (col1, col2));

Table created.

SQL> create materialized view test_mv build immediate refresh fast on commit as select distinct col2 from test;
create materialized view test_mv build immediate refresh fast on commit as select distinct col2 from test
                                                                                                     *
ERROR at line 1:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view


SQL> create materialized view test_mv build immediate refresh complete as select distinct col2 from test;

Materialized view created.

SQL> drop materialized view test_mv;

Materialized view dropped.

SQL> create materialized view log on test;

Materialized view log created.

SQL> create materialized view test_mv build immediate refresh fast on commit as select distinct col2 from test;
create materialized view test_mv build immediate refresh fast on commit as select distinct col2 from test
                                                                                                     *
ERROR at line 1:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

oracle primary-key materialized-views
person Jason    schedule 23.10.2015    source источник

Ответы (2)


arrow_upward
3
arrow_downward

Основная проблема вашего взгляда - это предложение DISTINCT. При фиксации быстрое обновление очень чувствительно к базовому запросу. Существует множество правил, которые необходимо выполнить, чтобы материализованное представление поддерживало быстрое обновление. DISTINCT предотвращает это.

Вы можете проверить возможности материализованного представления, используя DBMS_MVIEW.EXPLAIN_MVIEW процедуру:

DECLARE
    result SYS.EXPLAINMVARRAYTYPE := SYS.EXPLAINMVARRAYTYPE();
BEGIN
    DBMS_MVIEW.EXPLAIN_MVIEW('TEST_MV', result);

    FOR i IN result.FIRST..result.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(result(i).CAPABILITY_NAME || ': ' || CASE WHEN result(i).POSSIBLE = 'T' THEN 'Yes' ELSE 'No' || CASE WHEN result(i).RELATED_TEXT IS NOT NULL THEN ' because of ' || result(i).RELATED_TEXT END || '; ' || result(i).MSGTXT END);
    END LOOP;
END;

Дополнительную информацию можно найти в документации http://docs.oracle.com/cd/B28359_01/server.111/b28313/basicmv.htm#i1007007.

person Husqvik    schedule 23.10.2015
comment
Вы правы, что этому мешает четкость. Но, как ни странно, вы можете создать его с GROUP BY, и Oracle сможет использовать его для DISTINCT запросов. Подробности смотрите в моем ответе. - person Jon Heller; 24.10.2015

arrow_upward
1
arrow_downward

Представления с быстрым обновлением разборчивы. Это решение требует материализованного представления журнала с определенными свойствами и материализованного представления с несколькими дополнительными функциями и другим синтаксисом.

DISTINCT не поддерживается. Но есть агрегированные материализованные представления, которые поддерживают GROUP BY. Если это материализованное представление создано с помощью ENABLE QUERY REWRITE, Oracle может использовать его в DISTINCT запросе. Также есть дополнительный COUNT(*), потому что «COUNT (*) должен присутствовать всегда, чтобы гарантировать все типы быстрого обновления».

Создайте таблицу, материализованное представление журнала и материализованное представление.

create table test(col1 number, col2 number, col3 varchar(32)
  ,constraint test_pk primary key (col1, col2));
create materialized view log on test with rowid (col2) including new values;
create materialized view test_mv
  build immediate
  refresh fast on commit
  enable query rewrite as
  select col2, count(*) total from test group by col2;

Запросы могут использовать материализованное представление.

Эти планы объяснения показывают, что материализованное представление работает как для GROUP BY, так и для DISTINCT запроса.

explain plan for select col2 from test group by col2;
select * from table(dbms_xplan.display);

explain plan for select distinct col2 from test;
select * from table(dbms_xplan.display);


Plan hash value: 1627509066

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| TEST_MV |     1 |    13 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
person Jon Heller    schedule 24.10.2015
comment
Поскольку я использую Oracle SE, у меня нет поддержки перезаписи запросов, но я все еще могу создать материализованное представление без предложения включения перезаписи запроса. Недостатком является то, что это приведет к полному доступу к таблице при выполнении двух запросов в вашем ответе. Во всяком случае, я не беспокоюсь об этом прямо сейчас и считаю, что, когда я перейду на Oracle EE, проблема исчезнет. Что меня сейчас смущает, так это то, что после обновления col2 в тесте базовой таблицы mv не обновляется / обновляется. Mv будет обновляться после вставки новых записей в базовую таблицу. Есть ли еще что-то, что я упустил / понял? - person Jason; 29.10.2015
comment
Я кое-что упустил - агрегированные материализованные представления должны включать COUNT(*). Модифицированный пример работает лучше. - person Jon Heller; 29.10.2015
comment
работать как шарм! mv успешно обновлен. Большой! - person Jason; 30.10.2015