Logo Море(!) аналитической информации!
IT-консалтинг Software Engineering Программирование СУБД Безопасность Internet Сети Операционные системы Hardware
Обучение от Mail.Ru Group.
Онлайн-университет
для программистов с
гарантией трудоустройства.
Набор открыт!

Выводимые таблицы с хранимым результатом: часть 1

В.В. Пржиялковский , опубликовано в Мастерской Oracle, Interface

Общие положения

Разновидности выводимых таблиц ("виртуальных", "вторичных"; "представлений") в 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 технические решения для конкретных манипуляций с данными в БД.

  • Новости мира IT:

    Архив новостей

    Последние комментарии:

    Вышло обновление Firefox 57.0.1 (1)
    Среда 06.12, 09:14

    IT-консалтинг Software Engineering Программирование СУБД Безопасность Internet Сети Операционные системы Hardware

    Информация для рекламодателей PR-акции, размещение рекламы — adv@citforum.ru,
    тел. +7 985 1945361
    Пресс-релизы — pr@citforum.ru
    Обратная связь
    Информация для авторов
    Rambler's Top100 TopList liveinternet.ru: показано число просмотров за 24 часа, посетителей за 24 часа и за сегодня This Web server launched on February 24, 1997
    Copyright © 1997-2000 CIT, © 2001-2015 CIT Forum
    Внимание! Любой из материалов, опубликованных на этом сервере, не может быть воспроизведен в какой бы то ни было форме и какими бы то ни было средствами без письменного разрешения владельцев авторских прав. Подробнее...