Материализованное представление

Использование материализованных представлений (materialized view)в Oracle10g позволяет одновременно управлять сводной информацией в хранилище данных и ускорять выполнение запросов.
Создадим materialized view по шагам:
1) Предоставим полномочия

-- создание MV
grant create materialized view to ALEX;
-- оптимизация запросов с учетом MV
grant query rewrite to ALEX;

2) Создадим тестовые таблицы с данными - магазины и продажи в них. Материализованное представление будет выдавать сумму продаж по каждому из магазинов

create table SHOPS(
  id_shop number(5) PRIMARY KEY,
  name_shop varchar2(45) not null
  );
create table SALES(
  id_shop number(5),
  id_good number(5),
  sales_good decimal(8,2)
  )
alter table SALES
add constraint PK_SALES primary key(id_shop, id_good)
add constraint FK_SHOPS_SALES foreign key (id_shop)
references SHOPS(ID_SHOP)

3) Создаем материализованное представление

CREATE MATERIALIZED VIEW MV_SALES
BUILD IMMEDIATE
REFRESH
COMPLETE
ON COMMIT
AS
select sh.id_shop, sum(sa.sales_good) 
       from SHOPS sh,
            SALES sa
       where sh.id_shop = sa.id_shop
       group by sh.id_shop;

BUILD IMMEDIATE заполняет материализованное представление во время его создания (значение по умолчанию). Альтернативное предложение BUILD DEFERRED создает только структуру; заполнить материализованное представление можно позже, используя пакет DBMS_MVIEW.

REFRESH указывает, как Oracle обновляет данные материализованного представления. В приведенном примере полные (COMPLETE) обновления являются единственным вариантом. Возможны также быстрые (FAST) (инкрементные) обновления, однако для них существует ряд ограничений и предостережений.

ON DEMAND – обновление данных только при явном обновлении с использованием пакетf DBMS_MVIEW. ON COMMIT – каждый раз когда выполняется фиксация транзакции для таблиц представления.
4) Обновляем статистику оптимизатора и данные мат.представления (соотв.)

dbms_stats.gather_table_stats(USER, 'MV_SALES') ;
dbms_mview.refresh('MV_SALES', 'A');

Проверка:

SQL> select * from Alex.Mv_Sales
  2  /
 
ID_SHOP SUM(SA.SALES_GOOD)
------- ------------------
      1              41,15
      2              41,57
      3              66,51

Изменим данные в таблицах и повторим запрос:

SQL> update SALES
  2  set sales_good = sales_good + 100
  3  where id_shop = 1 and id_good = 1
  4  /
 
1 row updated
 
SQL> select * from Alex.Mv_Sales
  2  /
 
ID_SHOP SUM(SA.SALES_GOOD)
------- ------------------
      1             141,15
      2              41,57
      3              66,51