|
Навигация
|
Главная » Sql Выводимые таблицы с хранимым результатом. Часть 1Владимир Пржиялковский Содержание
Общие положения Разновидности выводимых таблиц ("виртуальных", "вторичных"; "представлений") в Oracle:
Некоторые основные свойства Materialized views, так же как и обычные именованные выводимые таблицы, являются с точки зрения словаря-справочника Oracle хранимыми объектами и создаются, изменяются и удаляются SQL-командами CREATE, ALTER и DROP, например: CREATE MATERIALIZED VIEW имя [ENABLE QUERY REWRITE] AS SELECT ...
Справочная информацияСведения об имеющихся выводимых таблицах с хранимым результатом и их свойства хранятся в системных 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
Пример показывает, что мы можем продолжать работать с исходными таблицами независимо от того, построена выводимая таблица DEPT_SALARIES, или нет. СУБД сама определила, что таковая имеется, и переадресовала запрос к ней. Сама таблица DEPT_SALARIES не несет в себе новых данных и ее наличие, подобно наличию индекса, позволяет в некоторых случаях сократить время доступа к исходной информации. Следующие примеры свидетельствуют, что для подобной автоматической переадресации к данным в приложении не обязательно повторять в точности формулировку имеющейся выводимой таблицы. Достаточно, чтобы в выводимой таблице с хранимым результатом хватало данных для ответа:
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 технические решения для конкретных манипуляций с данными в БД. Часть 2 Создаем пользователя с аутнетификацией на уровне ОС и парольной аутентификацией. Один из методов работы с конфигурационными файлами в С++ (Qt). Приемы взаимодействия Visual Basic и SQL. SQL Server: Раскрытие тайны задержек SQL Server. Использование хэш-ключей в базах данных. Главная » Sql |
© 2024 Team.Furia.Ru.
Частичное копирование материалов разрешено. |