Выводимые таблицы с хранимым результатом. Часть 2

Часть 1

Содержание

• Синхронизация с изменениями в исходных данных
  • Явное обновление полученных данных
  • Неявное обновление данных
  • Журналы базовых таблиц
  • Задание схемы обновления
    • Указание объема вычислений при обновлении
    • Указание времени обновления
• Прочие потребительские свойства
  • Создание с отложенным построением результата
  • Создание на основе имеющихся данных
  • Источники данных

Синхронизация с изменениями в исходных данных

Синхронизация образованных при создании materialized view данных с изменениями в базовых таблицах требуется почти всегда. Принципы синхронизации общие для всех категорий materialized view. Синхронизация может осуществляться явно, либо выполняться автоматически.

Схема обновления хранимого результата характеризуется двумя свойствами:

• Режим обновления. Указывает, будет ли обновление осуществляться по фиксации транзакции (ON COMMIT) или с помощью API (ON DEMAND), процедурам из состава системных пакетов Oracle, вызываемым явно или неявно (автоматически).
• Метод обновления. Два основных метода - полное перевычисление результата (COMPLETE) и экономное (FAST), достигаемое путем внесения в результат только изменений, вызванных изменениям в базовых таблицах.

Оба свойства могут указываются во фразе REFRESH предложения CREATE/ALTER MATERIALIZED VIEW:

CREATE MATERIALIZED VIEW имя [REFRESH ...];

При указании режима ON DEMAND дополнительно можно задать желаемое время внесения обновлений. Вот возможные сочетания задаваемых свойств схемы обновления:

Обновления всех видов можно на время запретить, переведя materialized view в специальное состояние командой

ALTER MATERIALIZED VIEW имя NEVER REFRESH;

Явное обновление полученных данных

Явное обновление результатов materialized view осуществляется через API, обращением к процедуре REFRESH из состава системного пакета DBMS_MVIEW (старое название - DBMS_SNAPSHOT):

EXECUTE DBMS_MVIEW.REFRESH('jobsal')

Первый параметр этой процедуры, LIST, может содержать имя выводимой таблицы или их список через запятую. Помимо него у процедуры есть несколько необязательных параметров. Среди них параметр METHOD используется для указания одного из возможных для данной таблицы метода обновления. Пример явного обновления с полным перевычислением результата:

EXECUTE DBMS_MVIEW.REFRESH(LIST => 'jobsal', METHOD => 'C')

Другие значения параметра METHOD: A (Always, то же, что и ”C”, COMPLETE), ”F” (FAST, быстрое обновление, путем внесения изменений), ”?” (форсированное обновление).

Явное обновление применимо к materialized view с любыми установленными режимом и методом обновления.

Неявное обновление данных

Происходит в режиме ON COMMIT или ON DEMAND. Режим ON DEMAND (явно можно не указывать) позволяет организовать автоматический пересчет результата по графику.

Метод для этих режимов обновления можно указывать любой: и FAST, и COMPLETE.

Если пересчет ведется методом FAST, то для таблицы нужно создать журналы всех ее базовых таблиц (materialized view logs). Это будут вспомогательные таблицы, накапливающие сведения об изменениях, совершаемых в базовых. Они и позволят внести необходимые поправки в данные materialized view. После выполнения процедуры обновления журналы автоматически чистятся.

Для пересчета результата методом COMPLETE журнал не нужен.

Журналы базовых таблиц

Пример создания журнала для исходной (базовой) таблицы:

CREATE MATERIALIZED VIEW LOG ON emp;

После этой команды в схеме появится служебная таблица для журнализации изменений в EMP и служебный триггер для актуализации таких изменений. (В последних версиях Oracle этот триггер сделан внутренним и в таблице USER_TRIGGERS не виден).

Объем данных, попадаемых в журнал, можно регулировать фразой WITH предложения CREATE MATERIALIZED VIEW LOG, вставляемой после фразы ON:

CREATE MATERIALIZED VIEW LOG ON имя WITH ...

Вот возможные указания для обычных таблиц:

• PRIMARY KEY: можно не указывать, так как в последних версиях первичный ключ заносится в журнальную строку автоматически.
• ROWID: при внесении изменений в базовую таблицу в журнальной будет отмечаться ее физический адрес.
• (список_столбцов): при внесении изменений в базовую таблицу в журнальную будут заноситься значения полей.
• SEQUENCE: при добавлении в журнальную таблицу новой строки она будет специально нумероваться
• INCLUDING NEW VALUES: в журнал будут помещаться не только старые, но и новые значения. По умолчанию используется EXCLUDING NEW VALUES.

(Для более экзотических объектных таблиц можно еще указывать WITH OBJECT ID).

Примеры:

DROP MATERIALIZED VIEW LOG ON emp;

CREATE MATERIALIZED VIEW LOG ON emp WITH ROWID;

DROP MATERIALIZED VIEW LOG ON emp;

CREATE MATERIALIZED VIEW LOG ON emp
WITH ROWID, SEQUENCE, (sal,comm);

DROP MATERIALIZED VIEW LOG ON emp;

CREATE MATERIALIZED VIEW LOG ON emp
WITH (sal,comm) INCLUDING NEW VALUES;

Разный объем информации, включаемой в журнал, может быть востребован разными схемами обновления. Некоторые схемы обновления могут требовать включения в состав строк журнала определенных полей, а некоторые - нет.

Задание схемы обновления

Указание объема вычислений при обновлении

Синтаксически для указания метода обновления используются следующие ключевые слова во фразе REFRESH:

• REFRESH COMPLETE: указывает СУБД, что при автоматическом обновлении хранимого результата он будет перевычисляться полностью путем повторения оператора SELECT, сформулированного для materialized view. Это гарантированно надежный вариант обновления.
• REFRESH FAST: указывает СУБД, что при неявном обновлении в хранимый результат будут вноситься изменения на основе информации, собранной в журналах базовых таблиц. Это более быстрый вариант.
• REFRESH FORCE: указывает СУБД выбрать режим FAST, если это возможно, иначе - COMPLETE. Это вариант по умолчанию.

Примеры:

DROP MATERIALIZED VIEW LOG ON emp;

CREATE MATERIALIZED VIEW LOG ON emp
WITH (sal,comm), ROWID INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW jobsal REFRESH FAST;

DROP MATERIALIZED VIEW LOG ON emp;

ALTER MATERIALIZED VIEW jobsal REFRESH COMPLETE;

Пример показывает, что экономное обновление агрегирующего хранимого результата возможно только при выполнении некоторых условий на полноту журнальной таблицы.

Указание времени обновления

Синтаксические конструкции фразы REFRESH для указания времени осуществления обновлений:

• ON COMMIT: режим, при котором обновление хранимого результата будет производиться по всякой фиксации транзакции (COMMIT). Время фиксации возрастет.
• ON DEMAND: режим, при котором обновление будет осуществляться процедурами из состава системного пакета DBMS_MVIEW.
  • START WITH первый_раз NEXT потом: обновление будет выполнено единожды первый_раз, после чего автоматически повторяться по формуле, вычисляемой потом. Может быть только уточнением к режиму ON DEMAND.

Автоматическое выполнение обновлений по графику возможно только в случае, если в составе СУБД запущены необязательные фоновые процессы SNPn. Их запуск достигается путем указания параметра СУБД JOB_QUEUE_PROCESSES. До версии 9 умолчанием для него был 0.

Пример:

CREATE MATERIALIZED VIEW LOG ON emp WITH ROWID INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW jobsal
REFRESH START WITH SYSDATE NEXT SYSDATE+1/1440;

COMMIT;

SELECT * FROM jobsal;

Примеры обновлений в режиме ON COMMIT:

DROP MATERIALIZED VIEW LOG ON emp;

CREATE MATERIALIZED VIEW emp2
REFRESH COMPLETE ON COMMIT
AS SELECT * FROM emp;

CREATE MATERIALIZED VIEW jobtotals
REFRESH ON COMMIT
AS
SELECT job, COUNT(*), COUNT(comm), SUM(comm), SUM(sal)
FROM emp GROUP BY job;

Проверка:

UPDATE emp SET sal = 8000 WHERE ename = 'SMITH';

SELECT sal FROM emp2 WHERE ename = 'SMITH';

SELECT * FROM jobtotals;

COMMIT;

SELECT sal FROM emp2 WHERE ename = 'SMITH';

SELECT * FROM jobtotals;

Можно заметить, что последний пример позволяет обойти проблему Mutating Trigger при попытке автоматического обновления хранимых агрегатов (например, сумм) после обновления полей с исходными данными.

Прочие потребительские свойства

Создание с отложенным построением результата

Фраза BUILD IMMEDIATE (умолчательная) в предложении CREATE MATERIALIZED VIEW сообщает, что сам хранимый результат будет вычислен автоматически немедленно после создания materialized view. Фраза BUILD DEFERRED сообщает, что вычисление хранимого результата произойдет позже, при выполнении первого обновления:

CREATE MATERIALIZED VIEW имя BUILD DEFERRED AS SELECT ...

Создание на основе имеющихся данных

Фраза ON PREBUILT TABLE позволяет сформировать хранимый результат без начального вычисления, на основе хранимой таблицы с той же структурой, или незначительно отличающейся. Ниже приводится простой пример первого варианта:

CREATE TABLE e4 AS SELECT * FROM emp WHERE deptno = 20;

CREATE MATERIALIZED VIEW e4 ON PREBUILT TABLE
AS SELECT * FROM emp;

SELECT * FROM e4;

Обратите внимание на то, что при таком построении materialized view сведения о бывшей самостоятельной роли таблицы E4 после создания выводимой E4 не теряются. Они восстановятся после удаления materialized view (что невозможно при обычном создании):

DROP MATERIALIZED VIEW e4;

SELECT * FROM e4;

Источники данных

Подобно обычным именованным выводимым таблицам, materialized views могут базироваться не только на хранимых таблицах, но и, в свою очередь, на выводимых: как views, так и materialized views.