Триггеры INSTEAD OF позволяют обойти некоторые ограничения, накладываемые на обновляемые представления.

Чтобы обновлять распределенные секционированные представления и те таблицы, на основании которых эти представления построены, следует соблюдать определенные требования. В первой статье данной серии ("Распределенные секционированные представления", в первом номере журнала за 2001 год) все эти требования были перечислены. Если все же возникла необходимость создать секционированные представления, которые им не удовлетворяют (к примеру, если столбец, по которому производится секционирование, не входит в состав первичного ключа исходной таблицы или если представление включает не все столбцы базовой таблицы), то для внесения изменений в представление можно применять триггеры INSTEAD OF. Однако планы исполнения запросов, которые формирует оптимизатор для представлений с триггерами INSTEAD OF, могут оказаться менее эффективными, чем планы, формируемые для обновляемых представлений. Это обусловлено тем, что ряд новых методик оптимизации зависит от правил, которые лежат в основе условий обновления представлений.

Создание триггеров

Пусть решено не включать столбец customerid в состав первичного ключа таблицы, лежащей в основе представления Orders, вместо этого используется первичный ключ меньшего размера, состоящий только из столбца orderid. В листинге 1 приведен код, который позволяет удалить и создать заново первичные ключи всех секций таблиц, составляющих распределенное представление Orders. Для повышения производительности обработки запросов при поиске по столбцу customerid, вы, возможно, захотите задействовать индекс по этому столбцу, как показано в листинге 1. Попробуйте теперь произвести вставку, как показано в листинге 2 и получите следующее сообщение об ошибке:


Server: Msg 4436, Level 16, State 13, Line 1
UNION ALL view `Orders` is not updatable because a partitioning
column was not found.

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

Чтобы разрешить обновлять информацию в представлении Orders из всех узлов сети, необходимо создать на всех узлах триггеры INSTEAD OF для представления Orders. В листинге 3 показан триггер INSTEAD OF INSERT, который должен срабатывать каждый раз, когда требуется вставить строку в представление.

Триггер анализирует виртуальную таблицу вставляемых строк, которая содержит всю информацию, подлежащую вводу в представление. Сначала триггер INSTEAD OF INSERT определяет, все ли новые строки удовлетворяют ограничениям, наложенным на столбец customerid во всех секциях таблицы. Если находятся строки, которые выходят за рамки введенных ограничений, триггер выполняет откат операции вставки. Затем триггер фильтрует информацию из таблицы вводимых данных и производит три операции вставки фрагментов данных в соответствующие секции представления. Фильтры триггера используют точно такие же критерии проверки ограничений, как и секции представления, куда будет вставляться информация. Перед началом ввода триггер проверяет, существуют ли строки, которые удовлетворяют условиям. В нашем примере такую проверку мы выполняем локально, чтобы избежать формирования ненужных удаленных запросов. Обратите внимание на то, что все модификации возникают в распределенной транзакции. Помимо этого заметьте, что при обращении к локальной секции представления триггер использует только имя соответствующей таблицы, в то время как при обращении к удаленным секциям он применяет полные названия таблиц.

В листинге 4 приведен триггер INSTEAD OF DELETE. Поскольку при удалении не происходит добавления каких-либо данных, удаляющему триггеру не требуется производить проверку выполнения в удаляемых строках ограничений, наложенных на столбец customerid в таблицах, на основании которых построено представление. Триггер INSTEAD OF DELETE включает три предложения удаления данных, по одному для каждой секции распределенного представления. Каждое предложение удаления выполняет соединение одной из таблиц секций с виртуальной таблицей удаляемых строк. Это гарантирует, что из таблицы, лежащей в основе представления, будут удалены только те строки, которые показаны в представлении. Чтобы облегчить чтение кода, мы добавили в каждое предложение удаления фильтр (который соответствует ограничению для таблицы секции), но он является избыточным и необязательным.

Триггер INSTEAD OF UPDATE представляет собой комбинацию триггера INSTEAD OF INSERT и триггера INSTEAD OF DELETE. Как показано в листинге 5, триггер INSTEAD OF UPDATE сначала удаляет старые строки, соединяя каждую секцию с таблицей удаляемых строк, точно так же, как это делал триггер INSTEAD OF DELETE для удаления строк из представления. После этого триггер INSTEAD OF UPDATE вставляет новые версии строк точно таким же образом, как этот делает триггер INSTEAD OF INSERT при добавлении строк в представление.

Однако триггеру INSTEAD OF UPDATE присуще одно ограничение, о котором читателям следует помнить. Никакие предложения модификации данных в представлении Orders не могут быть выполнены, если существуют ограничения внешнего ключа для каких-либо таблиц, которые ссылаются на любой столбец таблицы, лежащей в основе представления. Отказ произвести модификацию данных вызывает не само существование ограничения внешнего ключа, а попытка удалить строки, на которые имеются какие-либо ссылки в другой таблице. Это немедленно приведет к нарушению ограничения внешнего ключа и такое обновление не будет выполнено.

Запутанный набор взаимоотношений вызывает это ограничение. В нашем примере мы расщепили операцию обновления на две отдельные операции: удаления и вставки. При этом сначала выполняется удаление, в результате чего в таблице, которая осуществляет ссылку на модифицируемую таблицу, некоторые строки могут остаться без «родителей». Однако выполнить вставку перед удалением строк невозможно по другой причине: это приведет к нарушению уникальности первичного ключа в секционированной таблице. Можно было бы написать триггер INSTEAD OF UPDATE, который позволял бы задействовать внешние ключи из секционированной таблицы. Однако такой триггер был бы сложным, особенно если бы он позволял вносить изменения в первичный ключ. Кроме того, такой триггер не способствовал бы сохранению хороших показателей производительности. Так что, в конце концов, все равно пользователи предпочли бы или просто удалить внешний ключ, или отключить его.

Для тех, кто не связан ограничениями внешнего ключа, наш пример триггера INSTEAD OF UPDATE позволит производить модификации даже столбца секционирования. К примеру, для строк, которые изменили значения столбца customerid, операции удаления и вставки просто будут адресованы различным секциям. Так что в результате изменяемая строка как будто переместится из одной секции в другую.

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

Для построения набора триггеров на всех узлах сети можно воспользоваться кодом, приведенным в листинге 6. А теперь попробуйте ввести изменения, предусмотренные сценарием, показанным влистинге 7. В соответствии с этим сценарием сначала очищаются все строки в представлении Orders, а затем представление наполняется сведениями о заказах из базы данных Northwind. Для проведения этих модификаций применяются триггеры INSTEAD OF DELETE и INSTEAD OF INSERT. После этого при помощи триггера INSTEAD OF UPDATE старые идентификаторы заказов меняются на новые, которые вычисляются как разность между числом 21325 и старым значением идентификатора заказа (21325-orderid). После этого по сценарию производится выборка всех строк из представления Orders. Записи появляются в таблице заказов (orders) базы данных Northwind, но в обратном порядке (то есть первый идентификатор заказа в Northwind будет последним идентификатором в представлении Orders).

Почти все методики, рассмотренные в данной статье, применимы не только к распределенным секционированным представлениям, но и к локальным секционированным представлениям. Отличия локальных секционированных представлений следующие:

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

Лучшие примеры из практики

Примеры, которые рассматривались в статье "Запросы к распределенным секционированным представлениям", опубликованной во втором номере нашего журнала за 2001 год, наглядно показывают, какие типы систем больше всего выигрывают от применения распределенных секционированных представлений. К таким системам относятся, прежде всего, системы обработки транзакций в реальном масштабе времени (OLTP), а также автоматизированные системы, обслуживающие множество индивидуальных запросов по Internet, каждый из которых ищет относительно небольшой объем данных. Наиболее эффективный способ обработки таких запросов заключается в том, чтобы направлять каждый запрос на тот сервер, где находится основная часть данных, удовлетворяющих условиям запроса.

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

Однако распределенные секционированные представления обычно не вполне подходят для приложений, работающих с хранилищами данных. Это обусловлено тем, что в таких системах, как правило, обрабатываются значительные объемы информации, распределенные по многим узлам сети, так что для обработки одного запроса пришлось бы обращаться за данными к нескольким узлам. Плата за использование распределенных запросов, адресованных множеству различных узлов сети, в этом случае оказывается слишком высокой, чтобы оправдать применение распределенных секционированных представлений. В противоположность этому в приложениях для хранилищ данных вполне оправданным становится применение локальных секционированных представлений. SQL Server 2000 предоставляет разнообразные способы применения локальных секционированных представлений в приложениях для хранилищ данных.

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

Чтобы обеспечить высокую надежность функционирования секционированной системы, можно воспользоваться возможностями кластеризации Microsoft Cluster Server (MSCS). Каждый кластер состоит из нескольких (от двух до четырех) серверов, которые представляются пользователям и приложениям как один виртуальный сервер. Один узел назначается первичным узлом по обслуживанию запросов пользователей.

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

В прикладных системах с многослойной архитектурой, таких как системы, построенные с использованием Microsoft Windows Distributed interNet Applications (DNA), следует реализовывать правила маршрутизации данных в слое, выполняющем обслуживание бизнес-процессов. При выполнении тестовых запросов наиболее эффективная обработка запросов наблюдалась тогда, когда запрос адресовался тому серверу, где находилась большая часть запрашиваемых данных (а еще лучше, если все нужные данные размещались на этом сервере). Достичь такой эффективной маршрутизации можно при помощи простого приема: поместите ключи каждого сервера в таблицу маршрутизации, к которой бизнес-компонент СОМ+ обращается для того, чтобы определить, на какой сервер направить запрос. Приложения смогут вызывать компонент маршрутизации всякий раз, когда им понадобится отослать сформированный запрос.

Список пожеланий

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

Кэлен Дилани (kalen_delaney@compuserve.com, www.InsideSQLServer.com) обладает сертификатами MCT и MCSE, работает независимым консультантом и преподавателем на северо-западе тихоокеанского побережья США. Начала работать с SQL Server еще в 1987 году. Кэлен написала книгу "Inside SQL Server 7.0", выпущенную издательством Microsoft Press; она также является соавтором книг "SQL Server 6.5 Unleashed" и "Teach yourself SQL Server in 21 days", изданных в Sams Publishing. Ицик Бен-Ган itzikb@hi-tech.co.il имеет сертификаты MCDBA, MCSE+I, MCSD, MCT и SQL Server MVP. Работает старшим преподавателем на курсах по SQL Server в колледже Hi-Tech в Израиле. Является председателем израильской группы пользователей SQL Server.