Материализованное представление
Материализованное представление — структурированная совокупность записей, содержащих результаты запроса к данным одной или нескольких логических таблиц. Материализованное представление позволяет предварительно вычислить результат запроса и сохранить его для будущего использования.
Материализованное представление строится на основе данных одной СУБД хранилища (далее — СУБД-источник), а его данные размещаются в другой СУБД. Это позволяет создавать инсталляции, где одна СУБД служит полноценным хранилищем исходных данных, а остальные СУБД отвечают за быструю выдачу данных по запросам чтения. В текущей версии системы доступно создание материализованных представлений в ADG на основе данных из ADB.
Материализованное представление помогает ускорить запросы к данным в следующих случаях:
- если представление содержит результаты сложного запроса, который на исходных данных выполняется дольше;
- если запросы к представлению возвращают значительно меньше данных, чем запросы к исходным данным;
- если запросы относятся к категории, которую СУБД хранилища, где размещены данные представления, выполняет более эффективно, чем СУБД-источник (например, ADG быстрее всех из поддерживаемых СУБД обрабатывает чтение по ключу).
Материализованное представление дает доступ к актуальным и архивным состояниях объектов. Чтение горячих данных из представления недоступно: это позволяет избежать чтения изменений, загруженных из СУБД-источника только частично. Данные материализованного представления хранятся аналогично данным логических таблиц — в физических таблицах хранилища, которые автоматически создаются при создании представления.
Связи материализованного представления с физическими таблицами
Система поддерживает целостность данных материализованных представлений, периодически синхронизируя их с данными СУБД-источника (см. ниже).
Синхронизация материализованного представления
Материализованные представления можно создавать и удалять. Из материализованного представления можно запрашивать и выгружать данные — так же, как из логических таблиц и логических представлений. Загрузка и обновление данных недоступны для материализованных представлений.
При запросе или выгрузке данных из материализованного представления можно указать момент времени, по состоянию на который запрашиваются данные. Если момент времени не указан, система возвращает (выгружает) данные, актуальные на момент последней синхронизации представления, иначе — данные, актуальные на запрашиваемый момент времени.
При запросе или выгрузке данных на указанный момент времени может оказаться, что материализованное представление отстало от СУБД-источника и не содержит запрошенные данные. В этом случае система перенаправляет запрос к исходным таблицам СУБД-источника (см. раздел Маршрутизация запросов к данным материализованных представлений). Перенаправленный запрос может выполняться дольше, однако это позволяет получить данные, полностью актуальные на указанный момент времени.
Синхронизация материализованных представлений
Система периодически проверяет, нужно ли синхронизировать материализованные представления окружения с СУБД-источником. Периодичность проверки настраивается в конфигурации системы с помощью параметра MATERIALIZED_VIEWS_SYNC_PERIOD_MS ; по умолчанию проверка запускается раз в 5 секунд.
При необходимости синхронизацию материализованных представлений можно отключить, установив значение параметра MATERIALIZED_VIEWS_SYNC_PERIOD_MS равным 0.
Проверка материализованных представлений запускается только по таймеру и не запускается по другим событиям, таким как создание материализованного представления или загрузка данных в СУБД-источник. При срабатывании таймера система проверяет, появились ли в СУБД-источнике дельты, закрытые после последней синхронизации и, если такие дельты появились, система синхронизирует материализованные представления с СУБД-источником.
Количество одновременно синхронизируемых представлений задается в конфигурации системы с помощью параметра MATERIALIZED_VIEWS_CONCURRENT . По умолчанию одновременно синхронизируется максимум два представления, а остальные, если они есть, ожидают следующего цикла проверки.
Данные представления синхронизируются отдельно по каждой закрытой дельте — с полным сохранением изменений, выполненных в этих дельтах. В каждой дельте для материализованного представления рассчитывается и сохраняется результат запроса, указанного при создании этого представления. Таким образом, материализованное представление имеет такой же уровень историчности данных, как и исходные логические таблицы, на которых построено представление.
Если системе не удалось синхронизировать материализованное представление, она делает несколько повторных попыток. Максимальное количество попыток синхронизации представления задается в конфигурации системы (см. параметр MATERIALIZED_VIEWS_RETRY_COUNT ), по умолчанию система делает до 10 попыток. Если количество попыток исчерпано, но материализованное представление так и не удалось синхронизировать, система прекращает попытки синхронизировать это представление (до перезапуска). После перезапуска системы счетчики попыток по всем представлениям обнуляются, и, если какие-либо представления остались несинхронизированными, система возобновляет попытки их синхронизировать.
Пример синхронизации материализованного представления
Рассмотрим пример со следующими условиями:
- логическая БД sales содержит логическую таблицу sales и материализованное представление sales_by_stores ;
- логическая БД содержит две дельты:
- дельта 0: в таблицу sales загружено две записи (с идентификаторами 100 и 101);
- дельта 1: в таблицу sales загружено еще две записи (с идентификаторами 102 и 103);
На рисунках ниже показан порядок синхронизации материализованного представления sales_by_stores . В каждой дельте рассчитывается и сохраняется сумма по столбцу product_units таблицы sales с группировкой по столбцам store_id и product_code . При этом неважно, когда было создано материализованное представление: до дельты 0, после дельты 1 или в какой-то момент между этими дельтами.
Что такое материализованное представление?
Программирование и разработка
В мире вычислений база данных — это организованный набор данных, которые обычно хранятся и доступны в электронном виде из компьютерной системы.
Система управления базой данных, также известная как СУБД, представляет собой часть программного обеспечения, которое позволяет конечным пользователям (наряду с различными приложениями) взаимодействовать с самой базой данных для сбора и анализа содержащейся внутри информации.
Всё это важно понимать в контексте материализованных представлений, которые играют важную роль в том, как пользователи взаимодействуют с базами данных и извлекают из них ценность в различных ситуациях. Сегодня мы подробно обсудим материализованные представления с примерами.
Что такое материализованное представление?
Материализованное представление — это особый тип объекта базы данных, который содержит любые результаты, полученные в результате запроса. Думайте об этом как о реплике целевого мастера из определённого момента времени. Материализованные представления предварительно вычисляются. Они будут периодически кэшировать результаты запросов для повышения производительности базы данных.
В зависимости от ситуации это может быть локальная копия данных, которая хранится где-то удалённо, или это может быть продукт результата соединения, или это может быть даже сводка указанных данных, созданная с помощью агрегатной функции.
Материализованные представления были впервые реализованы в Oracle Database и были доступны во всех версиях, начиная с 8i. Дополнительные среды, поддерживающие материализованные представления, включают PostgreSQL, SQL Server, Sybase SQL Anywhere, BigQuery и другие.
Примеры материализованных представлений
Например, предположим, что у вас есть база данных с двумя таблицами: одна содержит количество сотрудников в вашем бизнесе, а другая — количество отделов в вашем бизнесе.
Используя материализованное представление, вы можете запросить базу данных, чтобы получить всех сотрудников, связанных с определённым отделом.
Или, скажем, у вас есть база данных с двумя таблицами: одна для общего количества произведённых вами продаж, а другая для общей суммы дохода, который вы генерируете. Вы можете использовать материализованное представление, чтобы в режиме реального времени видеть, какой доход приносит каждая продажа.
Зачем использовать материализованные представления
Каждый раз, когда вы запрашиваете базу данных, вы всегда накапливаете какие-то затраты. Даже такая, казалось бы, простая задача, как запрос, по-прежнему означает, что вы анализируете, проверяете, планируете, оптимизируете и выполняете этот запрос, что соответствует времени ЦП, использованию памяти, альтернативным издержкам и многому другому.
По мере того, как ваше приложение продолжает расти и становиться всё более требовательным, естественно, вы ищете возможности максимально снизить эти затраты.
Здесь в игру вступают материализованные представления.
Поскольку результаты материализованного представления хранятся в памяти, они обновляются только тогда, когда это явно необходимо. Благодаря этому они могут значительно снизить ваши общие затраты по сравнению с запросами к таблицам или использованием логических представлений.
Плюсы материализованных представлений
- Повышение производительности за счёт предварительных вычислений дорогостоящих операций.
- Увеличьте скорость запросов в очень больших базах данных.
- Эффективно выполняйте дорогостоящие запросы или дорогостоящие части ваших запросов.
Минусы материализованных представлений
- Не каждый тип базы данных поддерживает материализованные представления.
- Материализованные представления доступны только для чтения.
- Вы не можете создавать ключи, ограничения, триггеры или статьи.
Как создать материализованное представление
Чтобы создать материализованное представление в инструменте, с которым вы работаете, вы можете использовать оператор DML, чтобы создать базовую таблицу, загрузить в неё данные и в результате создать материализованное представление.
Пример команды SQL, которую можно использовать для создания материализованного представления, выглядит следующим образом:
Пример команды в Oracle, которую можно использовать для создания материализованного представления, выглядит следующим образом:
А также пример команды для PostgreSQL, которую можно использовать для создания материализованного представления, выглядит следующим образом:
Версия 9.3 и новее изначально поддерживают материализованные представления.
Материализованное представление против представления
Итак, каковы основные различия между представлением и материализованным представлением, и почему вы должны использовать одно вместо другого?
Представление — это особый тип виртуальной таблицы, которая создаётся с помощью команды «Создать представление». Он содержит все данные, полученные из соответствующего выражения запроса.
Если вы работаете с одной или даже несколькими базовыми таблицами или представлениями, вы можете использовать это для создания представления. Вы также можете запросить своё представление таким же образом, как вы использовали бы одну из этих базовых таблиц.
Ключевой момент, который нужно понять, — это то, что View всегда вычисляется каждый раз, когда к нему обращаются каким-либо образом. Из-за этого вы всегда получаете самые свежие данные в любом представлении, которое вы запрашиваете.
Если вы сделаете какое-либо обновление содержимого в представлении, оно всегда будет «отодвинуто назад» и обновлено в исходной таблице. Точно так же верно и обратное: любые изменения, внесённые в исходную базовую таблицу, мгновенно отражаются в представлении.
Однако это означает, что производительность представления всегда будет ниже, чем у материализованного представления. Основным преимуществом является то, что View на самом деле не требует места для хранения. Вы также можете полностью контролировать, какие пользователи могут или не могут просматривать конфиденциальную информацию в самой базе данных.
С другой стороны, материализованное представление — это физическая копия исходных базовых таблиц. Думайте об этом больше как о фотографии оригинального базового стола. Ключевое отличие состоит в том, что материализованное представление не будет обновляться каждый раз при взаимодействии с ним.
Если у вас нет проблем с обновлением его вручную, вы можете сделать это с помощью определённых предопределённых триггеров.
В результате материализованное представление всегда будет отвечать на запрос быстрее, чем традиционное представление, но вы также можете получать устаревшие данные, если не будете осторожны.
Материализованное представление против таблицы
Ответ на этот вопрос в контексте таких сред, как Oracle или MySQL, в конце концов сводится к концепции, называемой «динамическое переписывание запросов». Ключ к пониманию того, что материализованное представление не может правильно определять отношения между данными и исходной базовой таблицей. Это также позволит пользователям предварительно вычислять дорогостоящие объединения и агрегации.
Точно так же материализованное представление также будет иметь определённые параметры, которые можно использовать для автоматического обновления запрашиваемых данных. Это не только в итоге упростит обслуживание, но и значительно повысит эффективность, поскольку эти изменения можно отслеживать по строкам.
Другими словами, материализованное представление всегда будет синхронизировано с «реальными данными» в таблице. На самом деле вам не нужно изо всех сил делать что-либо, потому что в этот момент база данных сделает это за вас.
Заключение
Материализованные представления — отличный способ снизить затраты во время разработки вашего приложения, особенно когда оно продолжает расти. Вы не только сэкономите время настенных часов, но и повысите эффективность, а также сэкономите такие вещи, как время процессора и использование памяти, что приведёт к реальной экономии денежных средств, прежде чем вы об этом узнаете.
Но есть ещё чему поучиться. Чтобы продолжить обучение, вам следует изучить следующие концепции:
Представления в Oracle
Представление (view), или виртуальная таблица в базе данных Oracle Database — это специфический образ таблицы или набора таблиц, определенный оператором SELECT. Представление не существует физически как обычная таблица, являющаяся частью табличного пространства.Фактически представление создает виртуальную таблицу или подтаблицу только с теми строками и/или столбцами, которые нужно показать пользователю.
Представление Oracle — результат хранимого запроса, поэтому в словаре данных сохраняется только определение представления. При экспорте базы данных Oracle можно видеть предложение “exporting views” (“экспорт представлений”), но под этим имеется в виду только определения представлений, а не физические объекты.
При условии, что пользователь имеет соответствующие права доступа к лежащим в основе представления таблицам, можно запрашивать представления или даже модифицировать, удалять либо добавлять данные с использованием операторов UPDATE, DELETE и INSERT. Например, если выдать только привилегию INSERT для базовой таблицы, на которой определено представление, то этот пользователь сможет только вставлять строки в таблицу, но не сможет ни выбирать, ни вставлять, ни удалять строки.
Кончено же у читателя моего блога встает самый главный и насущный вопрос. Зачем все же нужны представления в базе данных Oracle? Представления Oracle используются в приложениях по нескольким причинам, включая перечисленные ниже:
- уменьшение сложности;
- повышение безопасности;
- повышение удобства;
- переименование столбцов таблицы;
- настройка данных для пользователей;
- защита целостности данных.
Представления создаются с помощью оператора SQL, описывающего композицию представления. При вызове представления выполняется запрос, по которому оно определено, и затем возвращается результат. Запрос, адресованный к представлению,выглядит в точности как обычный запрос, но база данных преобразует его в идентичный запрос к лежащим в его основе таблицам. Чтобы создать представление в своей схеме, необходимо иметь системную привилегию CREATE VIEW, а чтобы создать представление в любой схеме, а не только в собственной, понадобится системная привилегия CREATE ANY VIEW. Вдобавок нужно либо владеть лежащими в основе таблицами,либо иметь права на операции SELECT, INSERT, UPDATE и DELETE со всеми таблицами,на которых определено представление. Представление можно использовать для добавления к таблице мер безопасности уровня столбца или уровня значения. Безопасность уровня столбца обеспечивается созданием представлений, которые дают доступ лишь к избранным столбцам таблицы. Безопасность уровня значений включает применение конструкции WHERE в определении представления, которое отображает лишь избранные строки базовых таблиц. Чтобы использовать представление, пользователю нужны привилегии для доступа к нему, а не к базовым таблицам, на которых оно определено.
Следующий оператор создает представление по имени MY_EMPLOYEES, которое выдает информацию только о сотрудниках, подчиненных конкретному менеджеру:
Совет. Добавление к оператору CREATE VIEW конструкции WITH READ ONLY гарантирует, что пользователи смогут только осуществлять выборку данных из представления. Это означает, что пользователи не смогут модифицировать представление и тем самым неявно обновлять, вставлять или удалять строки базовых таблиц. В противном случае по умолчанию Oracle позволяет обновлять представление.
Теперь менеджер с идентификатором 122 сможет опрашивать представление my_employees, как если бы это была обычная таблица, но содержащая лишь сотрудников, подчиненных этому менеджеру. В листинге 7.16 показан вывод, полученный в результате запроса к представлению.
При создании представления в конструкции FROM можно специфицировать несколько базовых таблиц или даже других представлений. Созданные подобным образом представления называются соединенными (joined views), и следующий пример демонстрирует создание такого представления:
Хотя представления используются в основном для запросов, при некоторых обстоятельствах их можно также применять в командах INSERT, DELETE и UPDATE. Например,допускается выполнять операции DML над представлениями, которые не имеют в своем определении конструкций GROUP BY, START WITH или CONNECT BY, либо каких-то под-запросов в своей конструкции SELECT. Однако поскольку представление в действительности не существует как отдельная физическая сущность, на самом деле происходит модификация данных лежащих в его основе таблиц, и само представление будет, таким образом, субъектом тех же ограничений целостности, что и таблицы, на которых оно основано. В следующем примере показано, как вставлять строки в представление по имени sales_view, которое зависит от таблицы employees.
Приведенный оператор вставляет новую строку в базовую таблицу по имени employees. Обновления, удаления и вставки в представления подчиняются нескольким ограничениям. Например, при использовании ограничения CHECK при создании представления не получится вставить строку или обновить базовую таблицу этой строкой,если данное представление не может извлечь упомянутую строку из базовой таблицы.
Уничтожается представление с помощью команды DROP VIEW, как показано ниже:
Вместо уничтожения и пересоздания представления можно воспользоваться конструкцией OR REPLACE для переопределения представления, например:
Если в базе данных есть другие представления, которые зависят от заменяемого,они станут недействительными. Недействительные представления следует перекомпилировать с помощью оператора ALTER VIEW. Если программная единица PL/SQL, такая как процедура или функция, зависит от представления, то база данных может сделать ее недействительной, если изменения в новом представлении касаются количества столбцов или их имен либо же их типов данных.
Использование материализованных представлений
Всякий раз, когда нужен доступ к представлению, Oracle должен выполнить запрос,по которому определено представление, и вернуть результат. Этот процесс наполнения представления называется разрешением представления (view resolution) и он повторяется при каждом обращении пользователя к представлению. Если вы имеете дело с представлениями с множеством конструкций JOIN и GROUP BY, то этот процесс разрешения представления может потребовать очень длительного времени. Если нужно часто обращаться к представлению, будет весьма неэффективно каждый раз повторять разрешение представления.
Материализованные представления Oracle предлагают выход из этого затруднения.Упомянутые представления можно воспринимать как специализированные представления, в отличие от обычных представлений, имеющие физическое воплощение. Они занимают место и требуют хранения подобно обычным таблицам. Материализованные представления можно даже секционировать и при необходимости создавать на них индексы.
На заметку! Представление всегда вычисляется на лету, и его данные не хранятся отдельно от таблиц, на которых оно определено. Таким образом, запросы, использующие представления,по определению гарантированно вернут самые свежие данные. Материализованные представления в базе данных Oracle Database, с другой стороны, являются статическими объектами, которые наследуют свои данные от лежащих в их основе базовых таблиц. Если вы будете обновлять свои материализованные представления нечасто, то данные в них могут устареть по отношению к данным таблиц, на которых они основаны.
Традиционно хранилища данных и прочие крупные базы данных для выполнения своей работы всегда нуждались в итоговых или агрегатных таблицах. Определение таких итоговых таблиц и постоянное поддержание их в актуальном состоянии — непростая задача. При каждом добавлении данных к таблице деталей необходимо вручную обновлять итоговые таблицы и их индексы. Материализованные представления Oracle предлагают способ упрощения управления итоговой информацией в крупных базах данных. Материализованные представления в таких средах называются также итогами (summaries), поскольку хранят итоговые данные.
В качестве источника для материализованного представления могут служить таблицы, представления, а также другие материализованные представления. Исходные таблицы называются главными таблицами (master tables), а в средах хранилищ данных их часто также называют таблицами деталей. При создании материализованного представления Oracle автоматически создает внутреннюю таблицу для хранения данных этого материализованного представления. Таким образом, материализованное представление будет занимать физическое место в базе данных, в то время как обычное представление — нет, поскольку последнее является всего лишь выводом запроса SQL.
Над материализованными представлениями можно делать следующие действия:
- создавать индексы на материализованном представлении;
- создавать материализованное представление на секционированной таблице;
- секционировать само материализованное представление.
Совет. Индекс для доступа к материализованному представлению можно использовать непосредственно, как это делается в отношении таблицы. Аналогично, можно также обращаться к материализованному представлению непосредственно в операторе INSERT, UPDATE или DELETE.Однако в Oracle не рекомендуют поступать подобным образом; напротив, следует позволить стоимостному оптимизатору Oracle (Cost Based Optimizer — CBO) принять решения относительно необходимости переписать обычные запросы, что обеспечит возможность воспользоваться преимуществами материализованного представления. Если план выполнения, применяющий материализованное представление, имеет меньшую стоимость доступа по сравнению с прямым обращением к таблицам, то Oracle автоматически использует его.
В материализованном представлении допустимы различные типы агрегации, вроде SUM, COUNT(*), AVG, MIN и MAX. В определении материализованного представления так-же можно использовать соединения множества таблиц.
Создание материализованного представления очень просто, но его оптимизация может оказаться довольно сложной. Оптимизация материализованного представления включает как проверку того, переписывает ли стоимостной оптимизатор Oracle запросы пользователя для использования ранее созданного материализованного представления, так и поддержку данных материализованного представления в актуальном состоянии. Давайте кратко рассмотрим эти два аспекта оптимизации материализованных представлений.
Переписывание запросов
В крупных базах данных Oracle с интенсивными действиями, затратными по времени и вычислительной мощности процессоров, такими как соединение таблиц и использование агрегатных функций вроде SUM, материализованные представления ускоряют запросы.Материализованные представления обеспечивают более быстрое выполнение запросов за счет перерасчета и хранения результатов дорогостоящих соединений и агрегатных операций. Прелесть материализованных представлений Oracle заключается в том, что при их создании можно указать, что база данных должна автоматически обновлять материализованные представления, когда происходят изменения в положенных в их основу таблицах. Материализованные представления полностью прозрачны для пользователей. Если пользователи пишут запросы с обращением к лежащим в основе таблицам, то Oracle автоматически переписывает их для использования материализованных представлений, и такая техника оптимизации запросов называется переписыванием запроса (query rewrite). Стоимостной оптимизатор Oracle автоматически распознает необходимость в переписывании запроса для использования материализованного представления вместо исходных таблиц, если оценочная стоимость такого запроса оказывается ниже. Под стоимостью запроса здесь подразумевается объем ввода-вывода, а также затраты времени процессора и памяти, связанные с обработкой SQL-запроса. Сложные соединения таблиц обходятся в этом смысле дорого, а применение материализованных представлений позволяет использовать уже сохраненную информацию в предварительно вычисленном виде, и запросы требуют гораздо меньше ресурсов и потому выполняются намного быстрее.
Прием автоматической оптимизации с переписыванием запроса лежит в основе применения материализованных представлений. Параметр инициализации QUERY_REWRITE_ENABLED позволяет включать и отключать это средство на глобальном уровне.
Этот параметр может принимать следующие значения.
- FALSE. База данных не переписывает никакие запросы.
- TRUE. База данных сравнивает стоимость запроса с переписыванием и без, и выбирает наиболее дешевый метод.
- FORCE. База данных всегда переписывает запрос, не оценивая стоимости.Используйте вариант FORCE, если уверены, что это даст выигрыш во времени получения ответа.
Значением по умолчанию для этого параметра является TRUE, как в ситуации, если установить QUERY_REWRITE_ENABLED в 10.0.0 и выше (значение равно FALSE, если установить QUERY_REWRITE_ENABLED в 9.2.0 и ниже); это означает, что Oracle автоматически использует средство переписывания запроса. Когда упомянутый параметр установлен в TRUE, Oracle оценит стоимость запроса в исходном виде и в переписанном,и выберет вариант с минимальной стоимостью. Включение переписывания запросов действует на уровне системы, т.е. для всей базы данных.
Значение FORCE для параметра QUERY_REWRITE_ENABLED должно специфицироваться, только если есть абсолютная уверенность, что это принесет выгоду. Чтобы разрешить переписывание запроса для определенного материализованного представления,необходимо явно указать конструкцию ENABLE QUERY REWRITE при создании материализованного представления.
Подсказка Rewrite_or_Error
Предположим, что после создания нового материализованного представления обнаружено, что нужные запросы не переписываются, и преимущества нового материализованного представления не задействуются. Если выполнение запроса без материализованного представления требует слишком много времени, можно заставить Oracle прекратить выполнение запроса без материализованного представления. Чтобы заставить Oracle генерировать ошибку вместо выполнения непереписанного запроса, используется подсказка (создаваемая пользователем директива, которая служит указанием для стоимостного оптимизатора; это средство детально рассматривается в главе 19).Подсказка называется REWRITE_ON_ERROR и применяется так:
Если запрос не переписывается, вы увидите следующую ошибку:
После получения такой ошибки с помощью процедуры DBMS_MVIEW.EXPLAIN_REWRITE можно узнать, почему запрос не переписывается, и решить проблему так, чтобы он был переписан и, таким образом, преимущества материализованного представления были задействованы.
Целостность при переписывании
После настройки переписывания запроса Oracle по умолчанию использует только свежие данные из материализованных представлений. Затем он использует только ограничения первичного, уникального или внешнего ключа типа ENABLED VALIDATED.Параметр инициализации QUERY_REWRITE_INTEGRITY задает поведение оптимизатора в этом отношении. Поведение по умолчанию известно как режим ENFORCED. Кроме этого режима параметр QUERY_REWRITE_INTEGRITY может принимать еще два значения.
TRUSTED. В этом режиме оптимизатор принимает во внимание несколько отношений помимо тех, что приняты в режиме ENFORCED. Так, например, оптимизатор принимает наряду с декларируемыми и принудительные отношения, но не ограничения первичного или уникального ключа ENABLED VALIDATED. Поскольку вы позволяете оптимизатору принимать отношения на веру (не принудительно), то большинство запросов могут быть подвергнуты переписыванию.
STALE_TOLERATED. Оптимизатор будет принимать свежие и старые данные до тех пор, пока они действительны. Конечно, в этом режиме перепишется больше запросов, но вы также рискуете получить некорректные результаты, если старые данные неточно представляют истинную природу текущей таблицы.
Обновление данных материализованного представления
Поскольку материализованное представление определяется на основе главных таблиц, при изменении данных этих таблиц материализованное представление устаревает.Чтобы справиться с этой проблемой, материализованные представления обновляются,синхронизируя их с содержимым главных таблиц. В следующих разделах описаны опции обновления материализованных представлений.
Режим обновления
При обновлении можно выбирать между режимами ON COMMIT и ON DEMAND.
- ON COMMIT. В этом режиме при всякой фиксации изменений данных в главных таблицах материализованное представление обновляется автоматически, отражая эти изменения.
- ON DEMAND. В этом режиме для обновления материализованного представления потребуется выполнить процедуру типа DBMS_MVIEW.REFRESH.
По умолчанию принимается режим ON DEMAND.
Тип обновления
Доступен выбор одного из следующих четырех типов обновлений.
- COMPLETE. Эта опция обновления полностью заново вычисляет запрос, лежащий в основе материализованного представления. Таким образом, материализованное представление, на создание которого ушло 12 часов, потребует почти такого же времени для перестройки. Очевидно, что не особенно эффективно использовать эту опцию при каждом обновлении, удалении или вставке данных в таблицы.
- FAST REFRESH. Для реализации механизма быстрого обновления Oracle использует журнал материализованного представления для регистрации всех изменений в главных таблицах. Затем журнал материализованного представления применяется для обновления этого материализованного представления. Упомянутый журнал представляет собой таблицу, основанную на ассоциированном материализованном представлении. Каждая из таких таблиц, соединенная с материализованным представлением, нуждается в собственном журнале материализованного представления, чтобы фиксировать изменения в таблицах. Для быстрого обновления материализованного представления Oracle также может использовать данные из операций обслуживания разделов или загрузки данных, выполненной с применением метода загрузки в прямом режиме SQL*Loader.
- FORCE. В случае выбора этой опции Oracle попытается применить механизм быстрого обновления (fast refresh). Если по некоторым причинам он не может быть использован, применяется метод полного обновления.
- NEVER. Эта опция никогда не обновляет материализованное представление.Очевидно, что это неподходящий вариант для материализованных представлений, чьи главные таблицы со временем подвергаются серьезным изменениям.
Типом обновления по умолчанию является FORCE.
Использование пакета DBMS_MVIEW
Даже после того, как вы специфицируете механизм обновления запроса, стоимостной оптимизатор Oracle не всегда сможет автоматически переписать запрос, и обратится к главным таблицам вместо материализованного представления. Таким образом,даже несмотря на наличие материализованного представления, оптимизатор игнорирует его, сводя на нет смысл создания и обслуживания материализованного представления. Оптимизатор Oracle поступает так потому, что некоторые условия для переписи запросов могут быть не выполнены. Для диагностики этой и прочих проблем материализованного представления служит поставляемый Oracle пакет DBMS_MVIEW.
Процедуры пакета DBMS_MVIEW используются следующим образом.
- Процедура EXPLAIN_MVIEW применяется для того, чтобы увидеть, какие типы переписывания запросов возможны.
- Процедура EXPLAIN_REWRITE служит для определения, почему определенный запрос не переписан с использованием материализованного представления.
- Процедура TUNE_MVIEW используется для включения переписывания запроса. Эта процедура подскажет, как следует изменить материализованное представление,чтобы сделать его доступным для переписывания запросов. Процедура TUNE_MVIEW также сообщит, каким образом удовлетворить требования быстро обновляемого материализованного представления. Процедура примет ввод и построит сценарий создания материализованного представления (вместе со всеми необходимыми журналами материализованного представления), готового к реализации.
Создание материализованных представлений
В этом разделе будет показано, как создать базовое материализованное представление с использованием некоторых опций, описанных в предыдущих разделах. Если вы не уверены в том, какие материализованные представления нужно создавать, можете воспользоваться инструментом Oracle SQL Access Advisor, который предоставит ценные рекомендации относительно индексов и материализованных представлений. SQL Access Advisor спроектирует материализованное представление и сообщит о его готовности для участия в переписанных запросах. В разделе “Использование SQL Access Advisor” далее в главе мы детально опишем этот инструмент.
Для ввода в действие материализованного представления необходимо выполнить следующие три шага, хотя само его создание достаточно просто.
- Выдать необходимые привилегии.
- Создать журнал материализованного представления (предполагая использование опции обновления FAST).
- Создать материализованное представление.
Выдача необходимых привилегий
Первым делом потребуется выдать необходимые привилегии пользователю, создающему материализованные представления. Главные привилегии — это те, что позволяют создавать материализованное представление. Вдобавок необходимо выдать пользователю привилегию QUERY REWRITE, используя для этого либо привилегию GLOBAL QUERY REWRITE, либо специфические привилегии QUERY REWRITE для каждого объекта, не являющегося частью пользовательской схемы. Ниже приведены операторы GRANT, которые позволяют пользователю создавать материализованное представление в его схеме:
В дополнение, если пользователь еще не имеет их, потребуется выдать права на создание таблиц с помощью следующего оператора GRANT:
Если пользователь не владеет никакими главными таблицами, являющимися частью определения материализованного представления, необходимо выдать ему привилегию на SELECT в отношении этих индивидуальных таблиц или же сделать так:
Создание журнала материализованного представления
Давайте включим механизм быстрого обновления для материализованного представления. В большинстве случаев для этого необходимо создать журнал материализованного представления. Разумеется, это потребует создания двух журналов материализованного представления, фиксирующих изменения в двух главных таблицах, которые станут основой нашего материализованного представления.
Для использования механизма быстрого обновления материализованного представления сначала нужно создать журналы материализованного представления для каждой из таблиц — частей этого материализованного представления. В нашем случае это таблицы products и sales. В дополнение необходимо специфицировать конструкцию ROWID в операторе CREATE MATERIALIZED VIEW LOG. Также следует перечислить все столбцы, упоминаемые в материализованном представлении, и предусмотреть конструкции SEQUENCE и INCLUDING NEW VALUES, например:
Этот пример демонстрирует создание двух журналов материализованного представления для фиксации изменений в главных таблицах products и sales. В следующем разделе будет показано, как создать само материализованное представление.
Создание материализованного представления
Теперь все готово для создания материализованного представления. В примере, показанном в листинге 7.17, с помощью конструкции FAST REFRESH специфицируется механизм обновления материализованного представления.
Совет. Если в базе данных уже есть таблицы, содержащие некоторого рода агрегаты или итоговые результаты, можно воспользоваться оператором CREATE MATERIALIZED VIEW с конструкцией ON PREBUILT TABLE для регистрации имеющейся итоговой таблицы в качестве материализованного представления.
Рассмотрим некоторые важные конструкции оператора CREATE MATERIALIZED VIEW.
- BUILD IMMEDIATE немедленно наполняет материализованное представление; эта опция принята по умолчанию. Альтернатива заключается в использовании опции BUILD DEFERRED, которая в действительности загружает материализованное представление данными позднее, в указанное время.
- REFRESG FAST специфицирует, что материализованное представление должно использовать метод обновления FAST, что для фиксации всех изменений главных таблиц требует наличия двух журналов материализованных представлений, которые были созданы на предыдущем шаге. Часть COMMIT конструкции REFRESH указывает на то, что все зафиксированные изменения главных таблиц распространялись на материализованное представление немедленно после фиксации этих изменений.
- ENABLE QUERY REWRITE означает, что оптимизатор Oracle прозрачно перепишет все запросы для использования материализованных представлений вместо лежащих в основе главных таблиц.
- Подзапрос AS определяет материализованное представление. Oracle сохранит вывод этого подзапроса в материализованном представлении, которое вы создаете.Здесь допустим любой подзапрос SQL.
- Последние четыре строки кода содержат действительный запрос, определяющий материализованное представление; они извлекают вывод из главных таблиц и делают его частью материализованного представления.
На заметку! Из-за ограниченности объема книги здесь был представлен только простейший пример создания материализованного представления и его журналов. В действительности, чтобы иметь возможность создавать такие объекты, может понадобиться удовлетворить дополнительным требованиям. Например, чтобы иметь возможность создания быстро обновляемых материализованных представлений с журналами, вы должны удовлетворять специальным требованиям. Полный список этих требований можно найти в руководствах Oracle (в частности, в Data Warehousing Guide).
Обратите внимание на две возможности включения переписывания запросов: указание конструкции ENABLE QUERY REWRITE при создании материализованного представления (см. листинг 7.16) или применение оператора ALTER MATERIALIZED VIEW с этой конструкцией после того, как материализованное представление уже существует.
Для просмотра предложенного плана выполнения запроса вместо процедуры EXPLAIN_REWRITE из пакета DBMS_MVIEW можно воспользоваться инструментом EXPLAIN PLAN. При этом EXPLAIN PLAN не должен отображать никаких ссылок на лежащие в основе базовые таблицы. Если запрос был действительно переписан с использованием нового материализованного представления, там должно присутствовать обращение к этому представлению.
Совет. Соберите статистику оптимизатора (см. главу 19) для материализованного представления сразу после его создания. Это поможет Oracle оптимизировать процесс переписывания запросов.
Если вы считаете, что материализованное представление не нужно, можете уничтожить его с помощью оператора DROP MATERIALIZED VIEW:
PostgreSQL | Что такое Представление?
Изучая СУБД PostgreSQL можно часто слышать слово «Представление» ( View ) рядом со словом «Таблица» ( Table ). Что это такое простыми словами?
Представление — это способ задать ИМЯ для запроса SELECT на выборку данных и по этому ИМЕНИ быстро получать нужную «собранную» таблицу. То есть можно создать сложный запрос в базу данных и дать ему имя, чтобы каждый раз не расписывать полный SELECT .
Представление — это как-бы обёртка над запросом выборки данных через SELECT .
Представление — это «вычисленный» результат в виде таблицы.
Имейте ввиду, что в PostgreSQL существует 2 вида Представлений:
- Обычное Представление ( VIEW )
- Материализованное Представление ( MATERIALIZED VIEW )
В чём разница между Обычным и Материализованным Представлениями?
Ключевая разница в СКОРОСТИ ОТВЕТА.
Обычное Представление не хранит данные, а только выполняет описанный в себе запрос. Это значит, что тратится некоторое время на получения ответа от самого Представления и его Запроса, так как данных в готовом виде нет.
Материализованное Представление — это готовая таблица с данными, которые были сформированы в момент создания самого Материализованного Представления. Когда выполнился запрос на выборку данных, то на это был сформирован и запущен SELECT. Когда данные пришли в виде готовой таблицы, то эта таблица сохранилась в самой базе данных. В таком виде нам не нужно тратить время на «собирание» таблицы т. к. она уже собрана. Ответ будет мгновенным .
НО. Из-за скорости приходится жертвовать дисковым пространством, потому что по факту данные клонируются в новую таблицу. Данные(строки) в Материализованном Представлении нельзя изменить или удалить. Можно удалить только всё Материализованное Представление, при этом данные в оригинальной таблице/таблицах никуда не денутся.
Материализованное Представление — это способ дублирования данных и защита оригинальных данных.
Так как Материализованное Представление формируется в момент своего создания, то его периодически нужно обновлять. Если оригинальная таблица изменяется, то данные в Материализованном Представлении будут устаревать. Как часто обновлять? Нет ответа. Это зависит от многих факторов.
Какой командой создаётся «Представление» в PostgreSQL?
Команда CREATE VIEW создаёт «Представление» в СУБД PostgreSQL.
Синтаксис «Представления» в PostgreSQL
имя — это имя создаваемого представления (возможно, дополненное схемой).
имя_столбца — это необязательный список имён, назначаемых столбцам представления. Если отсутствует, имена столбцов формируются из результатов запроса.
запрос — это команда SELECT или VALUES, которая выдаёт столбцы и строки представления.
Квадратными скобками указаны опциональные параметры, которых может не быть в SQL-запросе.
Пример использования обычного Представления для ограничения выборки данных
Например. У вас есть одна огромная таблица, в которой есть 8 столбцов. Пусть это будут автомобили.
12 автомобилей в оригинальной таблице в 8 столбцов в PostgreSQL
Вы можете создать другую таблицу на её основе, в которой будут видны только данные из 3 столбцов. Пусть это будут: Брэнд авто, Модель авто и Пробег авто.
При этом в новой возвращённой таблице не будет никаких данных. Новая таблица будет ссылаться на данные своего родителя. Пусть Схема в БД будет назваться test1.
Мы называем наше Представление — cbmm . В результате вы получаете возможность ограничить вывод информации при отборе и ещё можете это как-то обозвать для удобства обращения.
Вот это и называется «Представление»
Оно всегда выполняется на выборку данных. Как правило через оператор SELECT.
В момент запроса к Представлению осуществляется реальный Запрос в базу данных. Выборка данных также будет занимать некоторое время как в случае с обычной таблицей.
Получили таблицу из Представления в 3 столбцах в PostgreSQL
В ответ мы получаем таблицу с ограниченным набором данных, относительно оригинальной таблицы из которой мы сделали это Представление.
Но отдавать такую таблицу на Клиент лучше не стоит т. к. заголовки столбцов берутся напрямую из базы данных. Так раскрывается внутреннее устройство БД. Этого лучше не делать.
Пример № 2
У вас есть две таблицы с данными.
Таблицы никак не связаны друг с другом. Нет связи «один ко многим», «один к одному».
Вы можете создать такой шаблон склеивания данных из двух таблиц, при котором получится третья таблица.
При этом в новой таблице не будет никаких данных. Новая таблица будет ссылаться на данные из других таблиц.
Альтернатива Представлениям. Что можно использовать для получения сборных таблиц?
У представлений есть один жирный минус. Представлениями нельзя создавать динамические запросы. В представления нельзя взять и подставить нужные значения, для получения индивидуального табличного результата.
Также представления жёстко связываются с объектами СУБД, которые описаны в ЗАПРОСЕ самого представления. Использовать их на этапе разработки не имеет смысла.
Если по какой-то причине, нам нужно будет поменять структуру таблиц, которые есть в ЗАПРОСЕ представления, то может произойти каскадное удаление самого представления. В результате мы можем потерять саму логику работы ЗАПРОСА нашего представления.
Чтобы не потерять нужные элементы работы базы данных, нужно создавать ТАБЛИЧНЫЕ ФУНКЦИИ в PostgreSQL. С ними гораздо удобнее работать, потому что они не удалятся самостоятельно. Если структура поменяется, то функция просто выполнится с ошибкой. И такую ошибку можно будет отследить.