Создание и обновление материализованных представлений

Создание и обновление материализованных представлений (materialized views) зависят от системы управления базами данных (СУБД), которую вы используете. Вот как это делается в наиболее распространенных СУБД:

PostgreSQL

Создание материализованного представления

В PostgreSQL создание материализованного представления выполняется с помощью команды CREATE MATERIALIZED VIEW.

CREATE MATERIALIZED VIEW EmployeeSummary AS
SELECT Department, COUNT(*) AS EmployeeCount, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department;

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

Чтобы обновить материализованное представление и синхронизировать его с базовыми таблицами, используйте команду REFRESH MATERIALIZED VIEW.

REFRESH MATERIALIZED VIEW EmployeeSummary;

По умолчанию обновление происходит в режиме CONCURRENTLY, если используется индекс на представлении и таблица не блокируется, что позволяет обновлять представление без блокировки.

Oracle

Создание материализованного представления

В Oracle создание материализованного представления также выполняется с помощью команды CREATE MATERIALIZED VIEW.

CREATE MATERIALIZED VIEW EmployeeSummary
AS
SELECT Department, COUNT(*) AS EmployeeCount, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department;

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

Для обновления материализованного представления в Oracle можно использовать команду DBMS_MVIEW.REFRESH.

BEGIN
   DBMS_MVIEW.REFRESH('EmployeeSummary');
END;

Также можно настроить автоматическое обновление (например, при изменении данных) или периодическое обновление (по расписанию) с использованием параметров REFRESH в определении представления.

MySQL

В MySQL нет встроенной поддержки материализованных представлений до версии 8.0.23, где появилась поддержка “перманентных таблиц” (аналогичных материализованным представлениям). Для создания аналогов можно использовать обычные таблицы и обновлять их вручную.

Создание "материализованного" представления

Можно создать таблицу и заполнять её результатами запроса:

CREATE TABLE EmployeeSummary AS
SELECT Department, COUNT(*) AS EmployeeCount, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department;

Обновление "материализованного" представления

Обновление выполняется путем перезаписи таблицы:

TRUNCATE TABLE EmployeeSummary;

INSERT INTO EmployeeSummary
SELECT Department, COUNT(*) AS EmployeeCount, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department;

SQL Server

В SQL Server также нет прямой поддержки материализованных представлений, но аналогичное поведение можно получить с помощью индексированных представлений (indexed views).

Создание индексированного представления

CREATE VIEW EmployeeSummary
WITH SCHEMABINDING AS
SELECT Department, COUNT(*) AS EmployeeCount, AVG(Salary) AS AvgSalary
FROM dbo.Employees
GROUP BY Department;

-- Создание уникального кластерного индекса на представление для его материализации
CREATE UNIQUE CLUSTERED INDEX IX_EmployeeSummary
ON EmployeeSummary (Department);

Обновление индексированного представления

Индексированные представления автоматически обновляются при изменении данных в базовых таблицах, если они соответствуют требованиям для автоматического обновления (например, не включают функции или соединения).

Итог

• PostgreSQL и Oracle: Поддерживают создание и обновление материализованных представлений, с возможностью настройки обновления по расписанию или вручную.

• MySQL: Для создания аналогов материализованных представлений требуется использование обычных таблиц и ручное обновление.

• SQL Server: Поддерживает индексированные представления, которые действуют как материализованные представления, автоматически обновляемые при изменении данных.

Выбор подходящего метода зависит от используемой СУБД и требований к обновлению и производительности данных.