Особенности обновления MATERIALIZED VIEW

Рассматривая вопрос обновления материальных представлений обнаружил некоторые особенности. Первое это то, что запрос который мы передаем на создание матвью может быть изменен системой. В частности оператор ORDER BY для главного селекта пропадает, а для вложенных запросов нет. Приведу пример, чтобы было лучше понятно.

Создаем достаточно большую таблицу и создаем матвью.

drop table test_table purge;
CREATE TABLE TEST_TABLE
  (
    id NUMBER(10),
    X  VARCHAR2(255),
    Y  VARCHAR2(255)
  );

BEGIN
 FOR I IN 1 .. 1
 LOOP
 INSERT INTO TEST_TABLE SELECT rownum, a.object_name, a.status FROM all_objects a, holidays h;
 END LOOP;
 COMMIT;
 END;

create MATERIALIZED view MV2_test_table
 as
 select *
 from test_table
 order by 1 desc

Матвью создалась, но если посмотреть DDL запрос создания этой матвью, то мы увидим там следующее:

CREATE MATERIALIZED VIEW "DKSD"."MV2_TEST_TABLE" ("ID", "X", "Y")
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
  AS
select *
from test_table

Order by исчез. Соответственно, после обновления матвью у нас данные будут в другом порядке. Слева столбец до обновления, справа — после.

Но если создать матвью со следующим запросом, то тогда ORDER BY не пропадает.

create MATERIALIZED view MV2_test_table
as
select * from (
select *
from test_table
order by 1 desc
)

Здесь необходимо сказать, что применять оператор ORDER BY для создания представления не очень хорошая идея. Лучше полагаться на индексы и грамотно построенные запросы, чем на то как данные физически лежат.

Второе, хотел бы обратить внимание на параметр ATOMIC_REFRESH, который содержит функция обновления материализованных представлений DBMS_MVIEW.REFRESH. Дело в том, что скорость обновления таблицы сильно зависит от того стоит этот параметр в значении TRUE или FALSE. По-умолчанию в последних версиях Oracle эта функция выполняется с параметром TRUE и это, как правило, занимает больше времени.

Объяснение такому поведению нашел на сайте http://kubilaykara.blogspot.ru/2010/04/materialized-view-refresh-and.html Оказывается, параметр TRUE вызывает оператор DELETE для удаления данных старой матвью, в то время как FALSE вызывает более экономный для процессора оператор TRUNCATE.

Я повторил эксперимент, который описан по ссылке выше, и вот что у меня получилось. Итак, в созданной таблице test_table более миллиона строк. Создаем новое представление.

CREATE materialized VIEW mv_TEST_TABLE
AS
SELECT * FROM TEST_TABLE;

Теперь произведем некоторые изменения с основной таблицей.

UPDATE TEST_TABLE SET Y='1';
COMMIT;

Запускаем обновление

BEGIN
  DBMS_MVIEW.REFRESH('mv_TEST_TABLE','C',ATOMIC_REFRESH => TRUE);
END;

Запрос выполнен за 54,270 сек.

Снова обновляем данные и запускаем обновление с параметром ATOMIC_REFRESH => FALSE

UPDATE TEST_TABLE SET Y=2';
COMMIT; 

BEGIN
 DBMS_MVIEW.REFRESH('mv_TEST_TABLE','C',ATOMIC_REFRESH => FALSE);
END;

Обновление заняло 2,782 сек.

Конечно, прирост производительности в 20 раз это особенности моего тестового стенда. На реальных серверах и очень больших матвью вряд ли это возможно, но следует держать в уме возможность повышения скорости обновления установлением параметра ATOMIC_REFRESH в значение FALSE.