Эта статья была опубликована на сайте www.optim.ru
Журнал Технология клиент/сервер №3, 2000

Indexed View (индексированные представления)

Одно из интереснейших нововведений MS SQL Server 2000 – Indexed View. Помните недавний спор Microsoft и Oracle? Потрясающие результаты Oracle, послужившие причиной предложения миллионного пари Ларри Элисона были основаны на материализованных view. Ларри Элисон заявил, что на одном из стандартных тестов Microsoft SQL Server будет работать не менее, чем в 100 раз медленнее. Подробнее об условиях пари можно прочитать в 1 номере нашего журнала за 1999 год, или на www.optim.ru/KS/KS199/larry1.html. Изначально смысл теста TPC-D заключался в измерении производительности сервера на ресурсоемких операциях типа агрегирования данных в больших таблицах. Применение материализованных view привело к тому, что результаты таких запросов стали постоянно храниться в БД, а оптимизатор Oracle позволил неявно использовать эти результаты. Тогда Microsoft заявила, что тест является нечестным, и даже смогла продемонстрировать сходные по времени результаты с помощью не самого SQL Server 7.0, а входящего в его поставку SQL Server OLAP Services. Теперь же, видимо, Microsoft решила ответить той же крапленой картой, реализовав такую же функциональность под другим названием. Правда, мы вряд ли услышим предложение пари от Билла Гейтса. Учитывая предстоящий выход Windows 2000 Data Center, нам предстоит веселое время сравнений, тестирований и прочего. Можно надеяться, что Oracle опять укажет Microsoft на недочеты этой версии SQL Server, и Microsoft снова прислушается к этим справедливым замечаниям. Правда, непонятно, зачем Oracle заниматься решением проблем Microsoft.

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

Indexed view улучшают производительность следующих типов запросов:

• Соединяющих (joins) и/или агрегирующих большое количество строк.

• Часто выполняемые операции соединения и агрегирования. Например, в OLTP-базе, хранящей складскую информацию, множество запросов будет соединять таблицы Parts, PartSupplier и Suppliers. Каждый из запросов может обрабатывать всего несколько строк, но суммарные затраты на обработку сотен и тысяч мелких запросов будут весьма существенны. Поскольку эти связи вряд ли будут часто обновляться, общая производительность системы может улучшиться после создания indexed view, хранящего объединенные результаты. Правда, такое положение вещей свидетельствует о неидеальной работе оптимизатора запросов. Например, для Oracle соединение большого количества таблиц практически не сказывается на скорости выполнения самого запроса. У SQL Server порог чувствительности к соединению большого количества таблиц был поднят еще в 7 версии, и для большинства реальных запросов значения не имеет, но, всё же, он ниже, чем у Oracle8i.

• Системы поддержки решений. Для аналитических систем характерно хранение обобщенных и агрегированных, нечасто обновляемых данных. Хотя до выхода SQL Server 2000 Microsoft и заявлял, что отсутствие в SQL Server поддержки OLAP не является серьезной проблемой, в жизни встретить аналитическую систему, не интегрированную с OLTP-системой, трудно. Копирование данных, необходимое при старом подходе Microsoft, очевидно, не лучшее решение проблемы.

Indexed view обычно не увеличивают производительности:

• OLTP-систем.

• Часто обновляемых БД.

• Запросов, не содержащих агрегирования или соединений.

• Расширяющих соединений (expanding joins), то есть view, чьи результаты шире, чем исходные данные базовых таблиц.

• Запросов, агрегирующих данных с большой кардинальностью ключа. Под кардинальностью ключа в виду имеется ключ, содержащий множество различных значений. Уникальный ключ имеет наивысшую кардинальность, поскольку все ключи имеют разные значения. Indexed view повышают производительность, уменьшая число строк, к которым должен обращаться запрос. Если результат запроса ко view имеет столько же строк, сколько и базовая таблица, выигрыш в производительности будет невелик. Например, в случае запроса к таблице, содержащей 1000 записей:

SELECT PriKey, SUM(SalesCol)
FROM ExampleTable
GROUP BY PriKey

• Если кардинальность ключа таблицы – 100, Indexed view, построенное на результатах этого запроса будет включать только 100 записей. Запросы, использующие это view, в среднем читают в десять раз меньше, чем использующие базовую таблицу в случае отсутствия Indexed view. Если же ключ уникален, его кардинальность равна 1000, и Indexed view будет включать 1000 записей. Запрос не получит выигрыша в производительности от чтения Indexed view вместо базовой таблицы.

Использование Indexed View в запросах

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

• Представьте часто выполняемый запрос, агрегирующий данные в одной БД, агрегирующий данные в другой БД и объединяющий результаты. Поскольку Indexed view не может ссылаться на таблицы нескольких БД, создать единое view для такого процесса нельзя. Но можно создать Indexed view, выполняющее агрегирование в каждой БД. Если оптимизатор сможет использовать indexed view в существующих запросах, будет ускорено хотя бы агрегирование – без переписывания существующих запросов. Объединение результатов не ускоряется, но в целом запрос будет выполняться быстрее за счет использования агрегированных данных, хранящихся в indexed view.

• Представьте, что часто выполняемый запрос соединяет данные из нескольких таблиц, и затем использует UNION для объединения результатов. UNION неприменим в indexed view. Вы опять же можете разработать indexed view для каждой из индивидуальных операций агрегирования. Оптимизатор может затем выбирать indexed view, чтобы ускорить запросы без потребности в переписывании запросов. Процессы агрегирования ускоряются, хотя сама обработка UNION - нет.

Лучше всего создавать indexed view, удовлетворяющие нескольким операциям. Оптимизатор может использовать indexed view, даже если оно не указано в операторе FROM, и хорошо продуманное indexed view может ускорить обработку многих запросов. Например, рассмотрим создание индекса для следующего view:

CREATE VIEW ExampleView (PriKey, SumColx, CountColx, SquareColx)
AS
SELECT PriKey, SUM(Colx), COUNT_BIG(Colx)
FROM MyTable
GROUP BY PriKey

Это view может не только удовлетворять запросам, которые непосредственно ссылаются на его столбцы, но и использоваться для запросов, которые обращаются к базовой таблице и содержат выражения типа SUM(Colx), COUNT_BIG(Colx), COUNT(Colx) и AVG(Colx). Все такие запросы станут быстрее, потому что они должны всего лишь получить небольшое число записей из view вместо чтения всех записей базовых таблиц.

Создание Indexed View

View называют также виртуальными таблицами, потому что набор результатов, возвращаемый view, в общем имеет ту же самую форму, что и таблица со столбцами и строками. На view можно ссылаться из SQL-запроса так же, как и на таблицу. Набор результатов обычного view не сохраняется в базе данных. Каждый раз, когда запрос ссылается на view, Microsoft SQL Server 2000 динамически объединяет логику, необходимую для формирования набора результатов view c логикой, необходимой для формирования результатов всего запроса по данным основных таблиц. Процесс формирования результатов view Microsoft называет материализацией view (что-то знакомое...).

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

Еще одно преимущество создания индекса для view, это то, что оптимизатор начинает использовать этот индекс даже в тех запросах, которые не ссылаются на view напрямую. Уже существующие запросы могут выиграть от увеличения эффективности поиска данных в индексированных view без переписывания.

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

Для создания кластерного индекса view должно соответствовать следующим требованиям:

• При создании View Опции ANSI_NULLS и QUOTED_IDENTIFIER должны быть установлены в ON.

• Опция ANSI_NULLS должна иметь значение ON при создании всех таблиц, вызываемых view.

• View не должно ссылаться на другие view, только на базовые таблицы.

• Все таблицы, вызываемые view, должны находиться в той же самой базе данных, что и view, и иметь того же самого владельца.

• View следует создавать с опцией SCHEMABINDING. SCHEMABINDING связывает view со схемой базовых таблиц, на которых основывается view.

• Пользовательские функции, используемые в Indexed view, должны быть созданы с опцией SCHEMABINDING.

• Таблицы и пользовательские функции должны вызываться по названиям, состоящим из 2-х частей. Названия из 1-, 3- или 4-х частей недопустимы.

• Все функции, используемые выражениями view, должны быть детерминированы. IsDeterministic, свойство функции OBJECTPROPERTY, сообщает, детерминирована ли определяемая пользователем функция.

• Выражение SELECT во view не может содержать следующие элементы синтаксиса Transact-SQL:

• В списке колонок SQL-запроса нельзя использовать синтаксис * или table_name. * для определения колонок. Названия колонок должны быть заданы явно.

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

• SELECT ColumnA, ColumnB, ColumnA

• А эти - нет:

• SELECT ColumnA, AVG(ColumnA), ColumnA + Column B AS AddColAColB
• SELECT SUM(ColumnA), ColumnA % ColumnB AS ModuloColAColB

• Rowset -функций

• Выражений UNION

• Подзапросов

• Outer или self joins

• TOP

• ORDER BY

• DISTINCT

• COUNT(*) (вместо него можно использовать COUNT_BIG(*))

• Агрегатных функций AVG, MAX, MIN, STDEV, STDEVP, VAR или VARP. Если в запросах, использующих indexed view, используются AVG, MAX, MIN, STDEV, STDEVP, VAR или VARP, оптимизатор зачастую может вычислить необходимый результат, если список выбора view содержит следующие заменяющие их функции:

• Например, Indexed view не может иметь колонок, содержащих выражение AVG(SomeColumn), но если view содержит колнку с выражениями SUM(SomeColumn) и COUNT_BIG(SomeColumn), SQL Server может вычислить среднее для запроса, который ссылается на view и использует AVG(SomeColumn).

  • Функции SUM, ссылающейся на колонки, допускающие NULL-значение.

  • Текстовые предикаты CONTAINS или FREETEXT

  • COMPUTE, COMPUTE BY

• Если GROUP BY не применяется, колонки Indexed view не могут содержать агрегатных выражений.

• Если применяется GROUP BY, колонки Indexed view должны содержать выражение COUNT_BIG(*), а view не может определять HAVING, CUBE, or ROLLUP.

• Индексируемая колонка Indexed view не может иметь тип float.

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

Требования при создании индексов view схожи с перечисленными ранее для самого view, но имеют и некоторые отличия. Так, выражение CREATE INDEX должно соответствовать следующим требованиям в дополнение к обычным для CREATE INDEX:

• Пользователь, выполняющий выражение CREATE INDEX, должен быть владельцем view.

• Следующие опции сессии должны быть включены (ON):

  • ANSI_NULLS

  • ANSI_PADDING

  • ANSI_WARNINGS

  • ARITHABORT

  • CONCAT_NULL_YIELDS_NULL

  • QUOTED_IDENTIFIERS

• Опция сессии NUMERIC_ROUNDABORT должна быть выключена (OFF).

• View не может включать колонок text, ntext или image, даже если они не вкходят в выражение CREATE INDEX.

• Если выражение SELECT в определении view содержит оператор GROUP BY, то ключ уникального кластерного индекса может ссылаться только на колонки, указанные в операторе GROUP BY.

После того, как кластерный индекс создан, любое подключение к серверу, пытающееся изменить данные, на которых основывается view, должно иметь те же установки опций, что и требуемые для создания индекса. SQL Server генерирует ошибку и откатывает любые выражения INSERT, UPDATE, или DELETE, пытающиеся изменить набор результатов view, если подключение, их выполняющее, не имеет соответствующих установок опций.

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

В то время, как в выражении CREATE UNIQUE CLUSTERED INDEX определены только столбцы, которые составляют ключ кластерного индекса, в базе данных сохраняется полный набор результатов. Как и в кластерном индексе базовой таблицы, структура B-дерева кластерного индекса содержит только колонки ключа, а строки данных содержат все столбцы набора результатов view.

Если вы хотите добавить индексы к view в существующей системе, можно сбросить view и создать заново, указав WITH SCHEMABINDING. Другой способ - создать другое view с тем же текстом, что и существующее, но с другим именем. Оптимизатор учтет индексы нового view, даже если оно не указано напрямую в операторе FROM запроса.

Следует убедиться, что новое view соответствует всем требованиям к indexed view. Это может потребовать смены владельца view и всех базовых таблиц, чтобы все они имели одного владельца.

Indexed View для ROLAP-разделов

Если режим хранения раздела - реляционный OLAP (ROLAP), и исходные данные хранятся в Microsoft SQL Server 2000, SQL Server 2000 Analysis Services пытаются создавать indexed view, хранящие агрегаты раздела. Если Analysis Services не могут создать indexed view, вместо них автоматически генерируются и используются агрегированные таблицы. Создание и использование индексированных view для агрегирования требует соблюдения условий, обязательных для indexed view.