sql materialized view что это

CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL)

В этой статье приведены сведения об инструкции CREATE MATERIALIZED VIEW AS SELECT T-SQL в Azure Synapse Analytics, используемой при разработке решений. Здесь также приведены примеры кодов.

Материализованное представление хранит данные, возвращенные запросом определения представления, и автоматически обновляется после изменений данных в базовых таблицах. Это повышает производительность сложных запросов (обычно запросы с объединениями и агрегатами), а также упрощает обслуживание. Благодаря возможности автоматического сопоставления плана выполнения материализованное представление не нужно указывать в запросе, чтобы оптимизатор учитывал его при подстановке. Эта возможность позволяет специалистам по обработке данных реализовать материализованные представления в виде механизма повышения времени отклика запроса без необходимости изменять запросы.

Синтаксические обозначения в Transact-SQL

Синтаксис

Этот синтаксис не поддерживается бессерверным пулом SQL в Azure Synapse Analytics.

Аргументы

schema_name
Имя схемы, которой принадлежит представление.

materialized_view_name
Имя представления. Имена представлений должны соответствовать требованиям, предъявляемым к идентификаторам. Указывать имя владельца представления не обязательно.

distribution option
Поддерживаются только распределения HASH и ROUND_ROBIN.

select_statement
Список SELECT в определении материализованного представления должен соответствовать как минимум одному из следующих двух условий:

В списке SELECT определения материализованного представления должны использоваться агрегатные функции. Поддерживаемые агрегаты: MAX, MIN, AVG, COUNT, COUNT_BIG, SUM, VAR, STDEV.

Если в списке SELECT определения материализованного представления используются агрегаты MIN или MAX, применяются следующие требования:

Необходимо использовать предложение FOR_APPEND. Пример:

Если в связанных базовых таблицах используется инструкция UPDATE или DELETE, материализованное представление отключается. Это ограничение не относится к инструкциям INSERT. Чтобы повторно включить материализованное представление, выполните инструкцию ALTER MATERIALIZED VIEW с параметром REBUILD.

Remarks

Материализованное представление в хранилище данных Azure похоже на индексированное представление SQL Server.Оно имеет практически те же ограничения, что и индексированное представление (подробности см. в статье Создание индексированных представлений) за исключением того, что материализованное представление поддерживает агрегатные функции.

Несмотря на то, что CREATE MATERIALIZED VIEW не поддерживает COUNT, DISTINCT, COUNT (выражение DISTINCT) и COUNT_BIG (выражение DISTINCT), запросы SELECT с этими функциями могут по-прежнему пользоваться преимуществами материализованных представлений для повышения производительности, так как оптимизатор Synapse SQL может автоматически перезаписывать эти агрегаты в пользовательском запросе для сопоставления с существующими материализованными представлениями. Дополнительные сведения см. в разделе с примером этой статьи.

APPROX_COUNT_DISTINCT не поддерживается в CREATE MATERIALIZED VIEW AS SELECT.

Материализованное представление поддерживает только кластеризованный индекс columnstore.

Материализованное представление не может ссылаться на другие представления.

Материализованные представления невозможно создать для таблицы с динамическим маскированием данных (DDM), даже если столбец DDM не является частью материализованного представления. Если столбец таблицы является частью активного или отключенного материализованного представления, DDM невозможно добавить в этот столбец.

Невозможно также создать материализованное представление для таблицы с включенной безопасностью на уровне строк.

Материализованные представления можно создавать в секционированных таблицах. Операции SPLIT и MERGE для секций поддерживаются для базовых таблиц материализованных представлений. Переключение (SWITCH) секций не поддерживается.

Таблицы, связанные с материализованными представлениями, не поддерживают инструкцию ALTER TABLE SWITCH. Перед использованием инструкции ALTER TABLE SWITCH отключите или удалите все материализованные представления. Ниже приведены сценарии, в которых для создания материализованного представления в него нужно добавить новые столбцы.

После создания материализованные представления отображаются в SQL Server Management Studio в папке представлений экземпляра Azure Synapse Analytics.

Пользователи могут определить место, используемое материализованным представлением, с помощью инструкций SP_SPACEUSED и DBCC PDW_SHOWSPACEUSED.

Материализованное представление можно удалить с помощью инструкции DROP VIEW. Отключить или перестроить материализованное представление можно с помощью инструкции ALTER MATERIALIZED VIEW.

Материализованное представление — это автоматический механизм оптимизации запросов. Пользователям не требуется запрашивать материализованное представление напрямую. При отправке пользовательского запроса подсистема проверяет разрешения пользователя на объекты запроса и завершает запрос без выполнения, если у пользователя нет доступа к таблицам или обычным представлениям в запросе. Если разрешение пользователя подтверждено, оптимизатор автоматически использует для выполнения запроса соответствующее материализованное представление в целях повышения производительности. Пользователи получают одинаковые данные независимо от того, обрабатываются ли запросы путем обращения к базовым таблицам или материализованному представлению.

План EXPLAIN и графический предполагаемый план выполнения в SQL Server Management Studio предоставляют сведения о том, учитывает ли оптимизатор запросов материализованное представление во время выполнения запроса. и графический предполагаемый план выполнения в SQL Server Management Studio предоставляют сведения о том, учитывает ли оптимизатор запросов материализованное представление во время выполнения запроса.

владельца;

Разрешения

Для создания материализованного представления пользователю, кроме соблюдения требований к владению объектами, нужны следующие разрешения.

Пример

A. В этом примере показано, как оптимизатор Synapse SQL автоматически использует материализованные представления при выполнении запроса для лучшей производительности, даже если в запросе используются функции, которые не поддерживаются в CREATE MATERIALIZED VIEW, такие как COUNT(выражение DISTINCT). Запрос, который раньше выполнялся несколько секунд, теперь выполняется за долю секунды без каких-либо изменений в пользовательском запросе.

Б. В этом примере пользователь User2 создает материализованное представление в таблицах, принадлежащих пользователю User1. Материализованное представление принадлежит пользователю User1.

Источник

материализованные представления;

Материализованные представления предоставляют статистический запрос к исходной таблице. Материализованные представления всегда возвращают актуальный результат запроса статистической обработки (всегда в актуальном состоянии). Запрос материализованных представлений является более производительным, чем выполнение статистической обработки непосредственно над исходной таблицей, которая выполняется для каждого запроса.

Используйте следующие команды для управления материализованными представлениями:

Зачем использовать материализованные представления?

Благодаря инвестиционным ресурсам (хранилищу данных, фоновым циклам ЦП) для материализованных представлений часто используемых агрегатов вы получаете следующие преимущества:

Улучшение производительности: Запросы к материализованным представлениям обычно выполняются лучше, чем запросы к исходной таблице для тех же агрегатных функций.

Актуальность: Запрос материализованных представлений всегда возвращает самые последние результаты независимо от времени последнего материализации. Запрос объединяет материализованный элемент представления с записями в исходной таблице, которые еще не были материализованными ( delta часть), всегда предоставляя наиболее актуальные результаты.

Снижение стоимости.запросы материализованных представлений потребляют меньше ресурсов из кластера, чем выполнение статистической обработки по исходной таблице. Политика хранения исходной таблицы может быть снижена, если требуется только статистическая обработка. Эта установка сокращает затраты на оперативный кэш для исходной таблицы.

Варианты использования материализованных представлений

Ниже приведены распространенные сценарии, которые можно решить с помощью материализованных представлений.

Примеры всех вариантов использования см. в разделе Материализованный Просмотр команды CREATE.

Принцип работы материализованных представлений

Материализованный вид состоит из двух компонентов:

Запрос материализованных представлений объединяет материализованные части с разностной частью, предоставляя актуальный результат запроса статистической обработки. Процесс автономной материализации принимает новые записи из разностной таблицы в материализованный и заменяет существующие записи. Замена выполняется путем перестроения экстентов, содержащих записи для замены. Если записи в Дельта постоянно пересекаются со всеми сегментами данных в материализованных компонентах, каждый цикл материализации потребует перестроения всей материализованных частей и может не справиться с частотой приема. В этом случае представление станет неработоспособным, а Дельта будет постоянно расти. В разделе наблюдение объясняется, как устранять такие ситуации.

Запросы материализованных представлений

Основным способом запроса материализованных представлений является его имя, например запрос ссылки на таблицу. При запросе материализованных представлений он объединяет материализованный элемент представления с записями в исходной таблице, которые еще не были материализованными ( delta ). Запрос материализованных представлений всегда будет возвращать самые последние результаты на основе всех записей, полученных в исходной таблице. Дополнительные сведения о материализованных и нематериализованных частях в материализованных представлениях см. в статье Работа с материализованными представлениями.

Другим способом запроса представления является использование функции. Этот параметр поддерживает запросы только об материализованных частях представления, при этом указывается максимальная задержка, с которой пользователь готов допускать. Этот параметр не гарантирует возвратить самые актуальные записи, но всегда должен быть более производительным, чем запрос всего представления. Эта функция полезна для сценариев, в которых вы хотите пожертвовать производительностью, например для панелей мониторинга телеметрии.

Материализованные представления могут участвовать в запросах между кластерами или между базами данных, но не включены в объединения с подстановочными знаками или поиск.

Примеры

Запрос ко всему представлению. В исходную таблицу включены самые последние записи:

Выполните запрос ко всему представлению и предоставьте «указание» для использования shuffle стратегии. В исходную таблицу включены самые последние записи:

Запрашивать материализованные части представления, независимо от времени его последнего материализации.

Вопросы производительности

Основные участники, которые могут повлиять на материализованный работоспособность представления:

Ресурсы кластера: Как и любой другой процесс, выполняемый в кластере, материализованные представления потребляют ресурсы (ЦП, память) от кластера. Если кластер перегружен, добавление материализованных представлений может привести к снижению производительности кластера. Отслеживайте работоспособность кластера с помощью метрик работоспособности кластера. Оптимизированное автомасштабирование в настоящее время не учитывается работоспособность материализованных представлений в рамках правил автомасштабирования.

Скорость приема: Жестко запрограммированные ограничения на объем данных или скорость приема в исходной таблице материализованных представлений отсутствуют. Однако Рекомендуемая скорость приема для материализованных представлений не превышает 1 ГБ/с. Более высокие показатели приема могут работать нормально. Производительность зависит от размера кластера, доступных ресурсов и объема пересечений с существующими данными.

Число материализованных представлений в кластере: Приведенные выше рекомендации относятся к каждому отдельному материализованныму представлению, определенному в кластере. Каждое представление использует свои собственные ресурсы, и многие представления будут конкурировать друг с другом на доступных ресурсах. Хотя количество материализованных представлений в кластере не ограничивается жестко заданными ограничениями, кластер может не суметь обрабатывать все материализованные представления, если определено множество. Политика емкости может быть скорректирована, если в кластере больше одного материализованных представлений. Увеличьте значение ClusterMinimumConcurrentOperations в политике, чтобы параллельно выполнялись более материализованные представления.

Определение материализованных представлений. Определение материализованных представлений должно быть определено в соответствии с рекомендациями по запросам для оптимальной производительности запросов. Дополнительные сведения см. в разделе создание советов по повышению производительности команд.

Мониторинг материализованных представлений

Отслеживайте работоспособность материализованных представлений следующими способами.

Материализация никогда не пропускает данные даже в случае постоянных сбоев. Представление всегда гарантированно возвращает самый актуальный моментальный снимок запроса на основе всех записей в исходной таблице. Постоянные сбои значительно снижают производительность запросов, но не будут приводить к неверным результатам в запросах на просмотр.

Устранение неполадок неработоспособных материализованных представлений

MaterializedViewHealth Метрика указывает, является ли материализованный представление работоспособным. До тех пор, пока материализованный представление становится неработоспособным, его возраст, отмеченный MaterializedViewAgeMinutes метрикой, постепенно увеличится.

Материализованные представления могут стать неработоспособными по любой из следующих причин.

Метрика Материализедвиевресулт

MaterializedViewResult Метрика предоставляет сведения о результате цикла материализации и может использоваться для определения проблем в состоянии работоспособности материализованных представлений. Метрика включает и, и, а Database MaterializedViewName также Result измерение.

Result Измерение может иметь одно из следующих значений:

Мониторинг потребления ресурсов

Материализованные представления потребление ресурсов. ресурсы, потребляемые процессом материализации материализованных представлений, могут быть отслеживанием с помощью команды. Отфильтруйте записи для определенного представления, используя следующие команды (Replace DatabaseName и ViewName ):

Источник

Материализованные представления, как средство контроля целостности данных

Контроль целостности данных — одна из важнейших функций СУБД. Чем тщательнее этот контроль организован, тем проще реализовывать прикладную логику, ведь чем больше ограничений контролируется базой данных, тем меньше вариаций «а что, если» следует предусмотреть при реализации логики. В то же самое время контроль целостности оказывается достаточно удобно использовать и для проверки корректности работы прикладного слоя. Что-то вроде юнит-тестов. «Лишняя» проверка, порой может сослужить очень добрую службу.

Традиционный набор ограничений — ограничение первичного, внешнего ключей, уникальности при использовании нормализации позволяет удовлетворить подавляющее большинство случаев потребности контроля. Однако в случае, когда ограничение оказывается зависимым от значений в нескольких таблицах и строках, этих средств оказывается недостаточно. Такие ограничения приходится реализовывать триггерной логикой. И реализация далеко не всегда оказывается проста. Разработчику приходится держать в уме то, что модификация данных может проводиться в конкурентной среде, потому необходимо самостоятельно заботиться о блокировании ресурсов, при этом, еще и пытаясь избегать взаимных блокировок. Реализация ограничения строки может потребовать доступа к другим строкам этой же таблицы, что, в свою очередь является ограничением платформы — Oracle не позволяет обращаться к изменяемому в настоящее время(мутирующему) набору данных.

Но есть и другой путь. В некоторых случаях оказывается возможным использование ограничений, наложенных на материализованные представления, обновляемые по факту фиксации транзакций (fast refresh on commit). Такие ограничения будут работать как отложенные (deferred) и не будут позволять зафиксировать транзакцию, если вдруг целостность данных оказалась нарушенной. В рамках же модифицирующей транзакции ограничения могут нарушаться. С одной стороны это упрощает модификацию данных, с другой, мешает идентифицировать источник ошибки. В этой статье я хотел бы привести пару простых примеров реализации таких ограничений.

Постановка задачи

Реализацию подхода я хотел бы показать на вымышленном упрощенном примере. Оказалось достаточно сложно подобрать такой пример, чтобы он был достаточно прост для восприятия, но при этом, чтобы применение подхода было оправданным, не обессудьте если что вдруг получилось не так.

Пусть мы имеем необходимость учета товара в разрезе зоны размещения. Размещением в данном случае выступает магазин(S) или склад(W).

Зона — территория физическая или логическая каждого конкретного размещения. К примеру — торговый зал, или же даже полки торгового зала, материальная комната, холодильник, зона утерянного товара. Каждое размещение может иметь более одной зоны каждого типа, но одна зона каждого типа обязательно должна быть помечена как основная. Она будет использоваться по умолчанию, если зона операции явно не определена. Основная зона должна быть одна и только одна для каждого типа зоны. Это будет первый вид ограничения, который мы попытаемся реализовать.

Реализация

таблицы

Ограничение количества основных зон

Для реализации этого ограничения создадим материализованное представление, которое будет производить подсчет основных зон для каждого типа зоны размещения, и сверху на него наложим ограничение, контролирующее строгое равенство единице рассчитанного значения. Для запросов, на основе которых строятся материализованные представления определен целый ряд ограничений, который, к тому еще сильно ужесточается наложением требований к обновлению методом fast. В нашем случае мы имеем аггрегирующее материализованное представление, а потому должны создать materialized view log для таблицы zone, включающий rowid и новые значения, список полей которого должен включить все значения, которые могут повлиять на результат запроса

Так же мы обязаны включить в результат, возвращаемый запросом значение «count(*)»

Здесь следует отметить: для того чтобы оценить, может ли материализованное представление построенное по запросу использовать для обновления метод fast, существует процедура dbms_mivew.explain_mview. Крайне желательно использовать ее для контроля доступен ли для представления метод обновления fast. К примеру, если бы мы забыли указать в запросе count(*), материализованное представление с успехом бы создалось и корректно работало при выполнении операции вставки. Однако при модификации, удалении значение primary_count — не пересчитывалось бы, что нарушило бы логику нашего ограничения. Однако если мы используем explain_mview, оракл услужливо подскажет нам наш просчет.

Итак, материализованное представление создано, осталось только добавить ограничение

Прошу обратить внимание на то, что ограничение создается отложенным(deferred). Дело в том, что в процессе обновления представления ораклом, на каком-то промежуточном этапе, может оказаться что ограничение будет временно нарушено. Чтобы избежать таких ложных срабатываний, лучше устанавливать такие ограничения заведомо отложенными.

Проверим работу этого ограничения

Попытаемся создать зону с типом, не имеющем отметки «основной».

Попытаемся определить две основные зоны для размещения с одинаковым типом.

Ограничение состава зон размещения

Это ограничение от предыдущего отличается тем, что опирается на значения не одной таблицы, а двух. Т.е. одновременно необходимо удовлетворить требования метода обновления fast для представлений с соединениями и аггрегирующих представлений. Но это не возможно. Мы не можем одновременно вывести в результат и rowid присоединенной строки и count(*). По этой причине придется строить каскад материализованных представлений. В одном будет производится соединение наборов данных, в другом — аггрегация.

Для начала необходимо создать materialized veiw log для таблицы размещений. Для таблицы зон будет использован ранее созданный лог.

Следом создаем join mivew. К сожалению ANSI синтаксис здесь оракл не воспринимает, испльзуем old-style join.

Создаем materialized veiw log для join представления

Создаем аггрегирующеее материализованное представление

ну и сами ограничения

Проверим работу ограничений:

Размещение успешно создано в статусе «Черновик». Попробуем активировать его:

Нет. Нельзя активировать размещение, если для него не определены необходимые для его типа зоны

Заключение

Когда это использовать

В первую очередь тогда, когда связь на столько сложна, что высок риск что разработчик приложения сможет не все учесть, а рассогласование критично. Я впервые такой подход использовал, когда проектировал структуру, закольцованную на себя же внешними ключами через семь таблиц. Причем эти таблицы ведутся разными бизнес-подразделениями. Это ограничение стоит и по сей день. И по сей день пользователи находят лазейки, шлют скришноты когда это ограничение срабатывает, а воспроизвести, чтоб закрыть лазейку в прикладном модуле — не удается, требуется стечение обстоятельств от нескольких пользователей.

Очень удобно иметь такие ограничения на этапе тестирования, старта, стабилизации проектов, когда не достает доверия к корректности работы логики, а данные могут модифицироваться и мимо прикладного звена.

Когда это не использовать

Очевидно, что не стоит использовать такой подход в случаях, когда он дает заметную просадку по производительности.

В случае модификации, уточнения схемы данных, содержимое представлений может оказаться не достоверным и потребует полного обновления. Если время полного обновления материализованного представления может поставить под угрозу выполнение регламента технических работ, наверное, тоже не стоит использовать такой подход.

Источник

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 подходит для этого еще лучше):

Теперь мы можем сравнить скорость выполнения запроса к сторонней таблице и ее локальной копии:

Источник