|
Данные, удаленные по
сети, равно как и данные в очень больших
таблицах нередко оказываются неудобными
для приложения: или к ним долго
обращаться, или доступ к ним ненадежен,
или и то и другое вместе. Одно из
решений Oracle, позволяющее отчасти
сгладить такое неудобство – выводимые
таблицы с хранимым результатом (materialized
views). Они позволяют построить хранимую
локально выжимку исходных данных, и уже
эффективность обращения к этой выжимке
не отличается от обычных таблиц. В
данной статье рассматриваются механизмы
построения materialized views в
распределенных БД и в БД для складов
данных. Во второй части статьи будут
рассматриваться различные механизмы
обновления хранимого результата с целью
его синхронизации с обновлениями
исходных данных.
Разновидности выводимых
таблиц (“виртуальных”, “вторичных”;
“представлений”) в Oracle:
- именованные выводимые таблицы –
views: для моделирования данных и
регулирования доступа к данным
-
| именованные выводимые
таблицы с хранимым
результатом –
materialized views: для
повышения эффективности
доступа к данным или для
программирования доступа |
- неименованные выводимые таблицы
без хранения результата – inline
views: для формулирования запросов к
данным
В отличие от большинства
других видов объектов, materialized
views (за исключением одной их
разновидности) не являются функционально
самостоятельным видом объектов и чаще
всего их функциональность может
моделироваться вручную с помощью
аппарата триггеров и системного пакета
DBMS_JOB. В этом случае их использование
просто повышает уровень абстракции при
программировании БД в Oracle.
Materialized views, так
же как и обычные именованные выводимые
таблицы, являются с точки зрения
словаря-справочника Oracle хранимыми
объектами и создаются, изменяются и
удаляются SQL-командами CREATE, ALTER и
DROP, например:
CREATE
MATERIALIZED VIEW имя [ENABLE
QUERY REWRITE] AS SELECT
...
- Если в предложении выше
присутствует фраза ENABLE QUERY
REWRITE, это выводимая хранимая
таблица для возможности
перенаправления к ней запроса,
предъявленного к базовым.
- Иначе, если в предложении SELECT
присутствует обращение к удаленной
таблице (в другой БД), это выводимая
хранимая таблица для локализации
удаленных данных.
- Если в предложении CREATE
MATERIALIZED VIEW нет ни того, ни
другого, это обычная выводимая
таблица с хранимым результатом,
создаваемая для технических
ухищрений программирования работы с
данными в Oracle.
Кроме этого materialized
views могут характеризоваться другими
важными для этих объектов признаками:
- Наличием в своем определении
обобщения, например агрегатного
выражения с GROUP BY.
- Наличием в своем определении
операции соединения над базовыми
таблицами.
В целом materialized
views характеризуются следующими
группами свойств:
- Описание ожидаемого результата,
задаваемое предложением SELECT
- Схема обновления результата
- Схема внутренней организации
результата
- Свойства хранения и доступа
Все свойства этих групп
формулируются собственными
синтаксическими конструкциями в
предложениях CREATE/ALTER MATERIALIZED
VIEW
Сведения об имеющихся
выводимых таблицах с хранимым
результатом и их свойства хранятся в
системных USER/ALL/DBA_-таблицах с
подстрокой MVIEW в имени, например
USER_MVIEWS
USER_MVIEW_LOGS
USER_MVIEW_AGGREGATES
USER_MVIEW_DETAIL_RELATIONS
USER_MVIEW_JOINS
USER_MVIEW_KEYS
Часть свойств
materialized views в этих таблицах
унаследована от выводимых таблиц
(обновляемость), часть от хранимых
таблиц (внутренняя организация,
организация хранения, а часть свойств
является собственными (схемы обновления
хранимого результата).
В то же время при работе
с materialized views в схеме
автоматически создаются специальные
служебные объекты (таблицы, индексы).
Сведения о них доступны из “обычных”
справочных таблиц, в первую очередь из
USER_OBJECTS.
В примерах далее будет
использована стандартная схема SCOTT.
Для дальнейшей работы пользователю SCOTT
нужно дать от имени SYS привилегию
создавать materialized view:
GRANT CREATE
SNAPSHOT TO scott;
Ниже приводятся примеры
построения materialized views нескольких
важных категорий.
Эта разновидность
materialized views в ранних версиях
Oracle существовала под названием
snapshots. В ряде случаев Oracle
продолжает поддерживать старое название
snapshot на равных правах с более
поздним materialized view.
Возможны два варианта
использования materialized views для
тиражирования данных: одностороннее
тиражирование (хранимый результат
доступен для выборки и закрыт для
изменений приложением) и двустороннее
тиражирование (хранимый результат может
изменяться приложением). Для простоты
здесь будет рассматриваться первый
вариант, одностороннего тиражирования.
Для иллюстрации
использования materialized view для
тиражирования данных необходимо
перевести БД на глобальную систему имен
и создать связь с удаленной БД.
Назначим для БД REM_BASE
домен CLASS. Пусть логическое имя
соединения с этой БД – REMOTE_DB.
(1) Проставим в INIT.ORA
DB_DOMAIN="class" и перезапустим СУБД
для этой базы по этому файлу параметров
(2) Выдадим от имени SYS
в REM_BASE:
ALTER DATABASE RENAME
GLOBAL_NAME TO rem_base.class;
(3) Выдадим от имени
SCOTT в локальной БД:
CREATE DATABASE LINK
rem_base.class
CONNECT TO scott IDENTIFIED BY tiger
USING 'remote_db';
Убедиться, что созданная
связь работает, можно выдав:
SELECT * FROM
emp@rem_base.class;
Выдадим от имени SCOTT:
CREATE
MATERIALIZED VIEW loc_emp AS
SELECT * FROM emp@rem_base.class;
Появившиеся в результате
новые объекты схемы SCOTT можно
посмотреть так:
SELECT object_name,
object_type FROM user_objects;
Просмотр “локальных”
данных об “удаленных” сотрудниках:
SELECT * FROM loc_emp;
Эта разновидность
materialized view может создаваться
только на основе таблицы, находящейся в
той же схеме. Кроме этого, для ее
создания нужно иметь особую привилегию
QUERY REWRITE.
Выдадим от имени SYS:
GRANT QUERY REWRITE TO
scott;
Выдадим в SQL*Plus от
имени SCOTT:
CREATE
MATERIALIZED VIEW dept_salaries
ENABLE QUERY REWRITE
AS
SELECT dname, COUNT(emp.deptno)
emp_count, SUM(sal) tot_sal
FROM emp, dept
WHERE emp.deptno (+) = dept.deptno
GROUP BY dname;
(В следующем примере и в
двух далее одинаковым фоном выделены
одинаковые участки кода).
Выводимая таблица
DEPT_SALARIES показывает список отделов,
число работающих в них и фонд зарплаты.
Число отделов:
SELECT COUNT(*)
FROM dept_salaries;
Проанализируем таблицы
(желательно) и сравним планы:
ANALYZE TABLE emp
COMPUTE STATISTICS;
ANALYZE TABLE dept COMPUTE STATISTICS;
ANALYZE TABLE dept_salaries COMPUTE
STATISTICS;
SET AUTOTRACE TRACEONLY
EXPLAIN
SELECT dname,
COUNT(emp.deptno) emp_count,
SUM(sal) tot_sal
FROM emp, dept
WHERE emp.deptno (+) =
dept.deptno
GROUP BY dname; |
ALTER SESSION SET
QUERY_REWRITE_ENABLED=TRUE;
SELECT dname,
COUNT(emp.deptno) emp_count,
SUM(sal) tot_sal
FROM emp, dept
WHERE emp.deptno (+) =
dept.deptno
GROUP BY dname; |
Два последних оператора
SELECT идентичны.
Пример показывает, что
мы можем продолжать работать с исходными
таблицами независимо от того, построена
выводимая таблица DEPT_SALARIES, или
нет. СУБД сама определила, что таковая
имеется, и переадресовала запрос к ней.
Сама таблица DEPT_SALARIES не несет в
себе новых данных и ее наличие, подобно
наличию индекса, позволяет в некоторых
случаях сократить время доступа к
исходной информации.
Следующие примеры
свидетельствуют, что для подобной
автоматической переадресации к данным в
приложении не обязательно повторять в
точности формулировку имеющейся
выводимой таблицы. Достаточно, чтобы в
выводимой таблице с хранимым результатом
хватало данных для ответа:
SELECT dname,
COUNT(emp.deptno) emp_count
FROM emp, dept
WHERE emp.deptno (+) =
dept.deptno
GROUP BY dname; |
SELECT dname,
COUNT(emp.deptno) emp_count, SUM(sal)
tot_sal
FROM emp, dept
WHERE emp.deptno (+) = dept.deptno
AND dept.deptno <> 10
GROUP BY dname;
Выводимые таблицы с
хранимым результатом могут
использоваться не только в
распределенной среде или для повышения
скорости доступа к большим таблицам,
например:
CREATE MATERIALIZED VIEW
jobsal AS
SELECT job, SUM(sal) FROM emp GROUP BY
job;
Мотивом для такого
создания могут служить попытки найти в
Oracle технические решения для
конкретных манипуляций с данными в БД.
|
