PostgreSQL: материализованные представления и FDW
Вы наверняка знаете, что в Postgres есть материализованные представления (materialized views) и обертки сторонних данных (foreign data wrappers, FDW). Материализованные представления позволяют материализовывать запросы и обновлять их по требованию. Обертки сторонних данных предоставляют функциональность загрузки данных из внешних источников, таких как, например, NoSQL-хранилища или другие серверы Postgres.
Вероятно, что вариант использования материализованных представлений совместно с обертками сторонних данных вы еще не рассматривали. Материализованные представления ускоряют доступ к данным: результаты запросов сохраняются и отпадает необходимость выполнять их еще раз. Доступ к сторонним данным через FDW может быть довольно медленным, поскольку они находятся в других системах. Объединив эти функции, можно в итоге получить быстрый доступ к сторонним данным.
Давайте подтвердим это практикой! Для начала создадим стороннюю таблицу (foreign table):
заполним ее данными:
и создадим материализованное представление на основе сторонней таблицы:
Теперь мы можем сравнить время выборки из сторонних таблиц и материализованных представлений:
Материализованное представление оказалось намного быстрее, однако не все так радужно, поскольку его обновление (refresh) занимает практически столько же времени, сколько и выборка из сторонней таблицы:
Вышеприведенные команды выполнялись в Postgres 9.6. Однако уже в десятой версии появилось вот такое улучшение:
Выполнение агрегатных функций на серверах FDW, когда это возможно (Jeevan Chalke, Ashutosh Bapat).
В Postgres 10 агрегаты сторонних таблиц выполняются быстрее, чем в 9.6, но все же пока медленнее, чем выборки из материализованных представлений:
Использовать агрегаты в материализованных представлениях совсем не обязательно — можно просто скопировать стороннюю таблицу целиком и обновлять соответствующую вьюшку по необходимости (но логическая репликация в Postgres 10 подходит для этого еще лучше):
Теперь мы можем сравнить скорость выполнения запроса к сторонней таблице и ее локальной копии:
Materialized views что это
Материализованные представления в Postgres Pro основаны на системе правил, как и представления, но их содержимое сохраняется как таблица. Основное отличие между:
состоит в том, что материализованное представление впоследствии нельзя будет изменить непосредственно, а запрос, создающий материализованное представление, сохраняется точно так же, как запрос представления, и получить актуальные данные в материализованном представлении можно так:
Информация о материализованном представлении в системных каталогах Postgres Pro ничем не отличается от информации о таблице или представлении. Поэтому для анализатора запроса материализованное представление является просто отношением, как таблица или представление. Когда запрос обращается к материализованному представлению, данные возвращаются непосредственно из него, как из таблицы; правило применяется, только чтобы его наполнить.
Хотя обращение к данным в материализованном представлении часто выполняется гораздо быстрее, чем обращение к нижележащим таблицам напрямую или через представление, данные в нём не всегда актуальные (но иногда это вполне приемлемо). Рассмотрим таблицу с данными продаж:
Если пользователям нужно быстро обработать исторические данные, возможно их интересуют только общие показатели, а полнота данных на текущий момент не важна:
Это материализованное представление может быть полезно для построения графика в информационной панели менеджеров по продажам. Для ежесуточного обновления статистики можно запланировать задание по расписанию, которое будет выполнять этот оператор:
Теперь давайте проверим написание слова. Сначала непосредственно через обёртку file_fdw :
Если же теперь обратиться к материализованному представлению, запрос выполнится гораздо быстрее:
В любом случае слово записано неправильно, поэтому давайте попробуем найти то, что имелось в виду. Сначала опять через file_fdw :
Затем через материализованное представление:
Если периодическое обновление данных из другого источника в локальной базе данных вас устраивает, этот подход может дать значительный выигрыш в скорости.
База данных Oracle Database для начинающих: основы базы данных
Представление (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 позволяет включать и отключать это средство на глобальном уровне.
Этот параметр может принимать следующие значения.
Значением по умолчанию для этого параметра является 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 DEMAND.
Тип обновления
Доступен выбор одного из следующих четырех типов обновлений.
Типом обновления по умолчанию является FORCE.
Использование пакета DBMS_MVIEW
Даже после того, как вы специфицируете механизм обновления запроса, стоимостной оптимизатор Oracle не всегда сможет автоматически переписать запрос, и обратится к главным таблицам вместо материализованного представления. Таким образом,даже несмотря на наличие материализованного представления, оптимизатор игнорирует его, сводя на нет смысл создания и обслуживания материализованного представления. Оптимизатор Oracle поступает так потому, что некоторые условия для переписи запросов могут быть не выполнены. Для диагностики этой и прочих проблем материализованного представления служит поставляемый Oracle пакет DBMS_MVIEW.
Процедуры пакета DBMS_MVIEW используются следующим образом.
Создание материализованных представлений
В этом разделе будет показано, как создать базовое материализованное представление с использованием некоторых опций, описанных в предыдущих разделах. Если вы не уверены в том, какие материализованные представления нужно создавать, можете воспользоваться инструментом Oracle SQL Access Advisor, который предоставит ценные рекомендации относительно индексов и материализованных представлений. SQL Access Advisor спроектирует материализованное представление и сообщит о его готовности для участия в переписанных запросах. В разделе “Использование SQL Access Advisor” далее в главе мы детально опишем этот инструмент.
Для ввода в действие материализованного представления необходимо выполнить следующие три шага, хотя само его создание достаточно просто.
Выдача необходимых привилегий
Первым делом потребуется выдать необходимые привилегии пользователю, создающему материализованные представления. Главные привилегии — это те, что позволяют создавать материализованное представление. Вдобавок необходимо выдать пользователю привилегию 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.
На заметку! Из-за ограниченности объема книги здесь был представлен только простейший пример создания материализованного представления и его журналов. В действительности, чтобы иметь возможность создавать такие объекты, может понадобиться удовлетворить дополнительным требованиям. Например, чтобы иметь возможность создания быстро обновляемых материализованных представлений с журналами, вы должны удовлетворять специальным требованиям. Полный список этих требований можно найти в руководствах 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:
