Oracle как перестроить индекс

Обновлено: 03.07.2024

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

Есть только две реальных причины пересоздавать индексы. Одна связана с ответом на вопрос: "Будет ли прирост производительности оправдывать затраты на пересоздание этого индекса?". Другая возникает при ответе на похожий вопрос: "Будет ли преимущество для администратора операвдывать затраты?".

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

Стандартный аргумент в пользу пересоздания индексов основан на том факте, что индексы на основе B-дерева имеют тенденцию к "вырождению" (т.е., становятся менее эффективны с точки зрения используемого пространства) по мере заполнения данными. И этот аргумент верен, но только потому, что стандартная теория предсказывает тенденцию заполнения индексов на основе B-дерева на 75 процентов, а сервер Oracle создает их, по умолчанию, с заполнением блоков на 90 процентов. (Фактически, Oracle не объединяет соседние мало используемые листовые блоки автоматически, поэтому упаковка часто будет на несколько процентов меньше, чем теоретическое значение). Поэтому вопрос пересоздания обычно обычно сводится к следующему неявному вопросу:

Если предполагается, что индекс работает с упаковкой на X процентов, надо ли пересоздавать его с упаковкой на 90 процентов?

Давайте начнем с рассмотрения значимости изменения заполнения с X процентов, прежде чем беспокоиться о значениях магического числа X.

Потенциальные преимущества

Когда вы пересоздаете индекс, физический эффект состоит в уменьшении размера индекса (особенно в уменьшении количества ключевых блоков). Это может привести к трем возможным преимуществам:

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

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

Оценка стоимости оптимизатором

Если вы читали статью "Почему Oracle не использует мой индекс?!", то знакомы с формулой, исходно опубликованной Вольфгангом Брейтлингом (Wolfgang Breitling) на конференции IOUG-2002, которая дает общую оценку стоимости доступа к таблице по индексу как:

Рассмотрим индекс по 10000000 строк, со средним размером записи 40 байтов. Это даст нам около 200 записей в листовом блоке при использовании блоков размером 8 Кбайт. Предположим, что индекс (в соответствии с некоей статистической информацией в представлении index_stats ) работает с 50-процентной эффективностью и, поэтому, в соответствии с типичными предписаниями, является хорошим кандидатом на пересоздание (с принятым по умолчанию заполнением блоков на 90 процентов - но мы будем использовать в нашем примере 100 процентов). Другими словами, сейчас в листовом блоке индекса находится в среднем около 100 записей.

Давайте займемся арифметикой, сначала до пересоздания:

Теперь, после пересоздания (со значением pctfree = 0):

Итак, индекс состоит из трех уровней как до, так и после пересоздания, но количество листовых блоков сократилось после пересоздания со 100000 до 50000. Говорит ли это нам что-нибудь о новой оценке стоимости? На самом деле, нет, хотя стоит учесть небольшую, но важную деталь: количество уровней b-дерева, являющееся одним из компонентов при расчете стоимости, при пересоздании индекса меняется редко. Экспоненциальная зависимость максимального количества строк от количества уровней b-дерева делает это практически неизбежным.

Нам надо учесть не только количество листовых блоков, но и все слагаемое стоимости: избирательность * количество_листовых_блоков . Насколько изменилась эта величина? Ну, это зависит от того, сколько строк соответствует каждому значению индекса. Давайте проверим пару значений, одно для "высокоточного" индекса (пять строк на значение), а другое - для индекса "качеством" пониже (50 строк на значение).

Из-за округления (я уверен, что при вычислении по формуле в этот момент сервер Oracle выполняет округление), это слагаемое оценки стоимости не меняется.

Снова это слагаемое формулы не изменилось. Фактически, оно и не изменится, пока каждому значению ключа не будет соответствовать 100 строк таблицы - тогда предполагаемая стоимость в результате пересоздания индекса с упаковкой на 100 процентов вместо 50 уменьшится.

Итак, бывают случаи, когда пересоздание индекса изменит мнение оптимизатора о его полезности, но в вашей системе эти случаи могут быть весьма нечастыми.

Типичные запросы

Итак, что, если оптимизатор не осознает, что ваш индекс стал лучше; может, лучше начнут работать запросы конечных пользователей, которые уже используют этот индекс? Как обычно, важно знать особенности данных и приложения. Когда вы начинаете задумываться о пересоздании индекса "поскольку, судя по index_stats , он заполнен на 50 процентов", прежде всего, подумайте о том (например), что если эти 50 процентов означают, что половина индекса почти заполнена, а другая половина - почти пуста, то одно магическое число для всего индекса может и не давать никакой полезной информации. (Если точно ничего не известно, можно попытаться выполнить весьма ресурсоемкий treedump , чтобы уточнить детали).

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

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

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

Преимущества для буферизации

Понятно, что при наличии хорошо упакованного индекса, он, вероятно, будет меньше "загрязнять" буферный кеш - в индексе меньше блоков, так что весь индекс может поместиться в буферном кеше, а поскольку меньше блоков будут "выбивать" блоки с другими данными из буферного кеша, общий объем ввода-вывода в системе может уменьшиться. С другой стороны, хорошо упакованный индекс может вызвать лишние конфликты при вставках, изменениях и удалениях. Оценить подобные ситуации сложнее, но пересоздание индекса (секции), который предполагается использовать только для чтения, вероятно, имеет смысл.

Учтите, однако, три следующих соображения. Если этот индекс действительно популярен, то принцип LRU и счетчик количества обращений могут сохранять его в буферном кеше постоянно в любом случае, так что, хотя он и занимает "лишнее пространство" в буферном кеше, к дополнительному вводу-выводу это может и не приводить. Во-вторых, для многих запросов основная составляющая стоимсоти выполнения запроса - это стоимость чтения блоков таблицы, которые редко посещаются повторно и вызывают намного более агрессивный сброс буферов на диск, так что беспокойство об индексах может отвлекать вас от более насущной проблемы. Наконец, возможно, что, слишком часто пересоздавая индексы, вы усложняете проблему, а не решаете ее - можете выполнить следующий (сравнительно долго работающий; он может выполняться несколько минут) тест на системе с размером блока 8 Кбайтов, в табличном пространстве без использования ASSM , если вы работаете в Oracle 9:

Проверьте количество листовых блоков в индексе после пересоздания, а затем повторите эксперимент без пересоздания. Различие результатов может вас удивить. (В моем случае, после "повышающего производительность" пересоздания листовых блоков было 2227, но если не пересоздавать, то в индексе оказалось всего 1778 блоков).

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

Напоминание

Я также игнорировал возможность пересоздания индексов (или, по крайней мере, секций индексов) со 100-процентным заполнением блоков ( pctfree = 0) непосредственно перед переводом табличного пространства в режим только чтения. Даже в этом случае усилия могут оказаться неоправданными, если при пересоздании возникают проблемы (помните, что в ходе оперативного пересоздания индексов возникала достаточно серьезная ошибка, которая может быть и в последних версиях сервера Oracle).

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

Вывод

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

Будут ли общие затраты на пересоздание индекса оправданы полученными преимуществами для системы?

Ответ на этот вопрос, зачастую, - громкое НЕТ. Фактически, иногда общее влияние пересоздания активного индекса будет губительным для системы. Однако, все еще есть много заблуждений относительно индексов, приводящих к тому, что АБД тратят ценное время и усилия на ненужные пересоздания индексов.

НО, если есть регулярный период, когда система не используется, есть простое пакетное задание, которое работает в этот период времени, не мешая работе других пакетных заданий, и задание это всегда срабатывает успешно - можете свободно пересоздавать все "безопасные" индексы, которые хотите; часто так можно добиться небольшого повышения производительности, а если оно вам ничего не стоит, вполне можно на это пойти.

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

Синтаксис CREATE INDEX

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

Уникальный индекс, UNIQUE INDEX

Индекс может быть уникальным unique index, что не позволяет иметь в таблице дублированных записей с одинаковыми значениями индексируемых полей.

ПРИМЕЧАНИЕ: при создании уникального индекса транзакция будет отклонена, если уже имеются идентичные значения в записях таблицы по индексируемым полям. Для уникального индекса таблицы с несколькими полями комбинация значений должна быть единственной, но каждое из значений поля может и не быть уникальным.

Отличие PRIMARY KEY и UNIQUE INDEX

Ограничения "primary key" и unique index обеспечивают уникальность значений полей таблицы, в которой они определены. По умолчанию primary key создает кластерный индекс на столбце, а "unique index" - некластерный. Другим отличием является то, что "primary key" не может иметь нулевых записей, т.е. поле NOT NULL, в то время как "unique index" допускает только одну нулевую запись (NULL). Таблица может иметь только один первичный ключ, но несколько "unique index".

Таким образом, можно считать, что "primary key" это приблизительно unique index + NOT NULL .

Удаление DROP INDEX

Удаление индекса не воздействует на содержание полей. Синтаксис оператора удаления индекса drop index :

ALTER INDEX

В разных СУБД имеются существенные различия по использованию оператора alter index. Так например MySQL не поддерживает данный оператор, в Interbase можно использовать данный оператор для отключения и повторного включения индекса, в результате чего будет выполнена переиндексация данных.

В СУБД PostgresSQL индекс можно переименовать с использованием оператора alter index. Синтаксис переменования индекса :

ALTER INDEX в Oracle

Платформа Oracle также поддерживает инструкцию alter index. Данный оператор используется для изменения или перестройки существующего индекса без его удаления и повторного создания.

Синтаксис оператора для переименования индекса в Oracle имеет следующий вид :

Для переиндексации данных необходимо использовать следующий синтаксис оператора alter index :

COALESCE

При использовании coalesce таблица не блокируется и переиндексация выполняется online. При этом индекс размещается в пределах существующей индексной структуры - соединяет блоки листа в пределах имеющихся ветвей дерева. Индексные листовые блоки быстро освобождаются для использования и не требуется много дискового пространства.

Однако coalesce генерирует много записей в журналах повторного выполнения (redo). При этом данный операнд может вызвать ошибку ORA-01555 ( coalesce определяет "работу" Oracle с листовыми блоками, определенных количеством малых транзакций. А много малых транзакций, выполненных одной сессией, могут вызвать у другой сессии, выполняющей продолжительную транзакцию, эту ошибку). Кроме этого coalesce не опускает HWM индекс, т.е. место на диске не освобождает и не может переместить индекс в другое табличное пространство.

REBUILD

Использование rebuild позволяет быстро перемещать индекс в другое табличное пространство. Кроме этого "rebuild" создает новое дерево и уменьшает его высоту при необходимости. А также дает возможность быстро изменять storage и tablespace параметры, без необходимости удалять индекс. Может быть использован для уменьшения расходования ресурсов - передвигается отметка HWM.

Однако rebuild связан с более высокими издержками - требуется больше дискового пространства, чтобы разместить старый и новый индекс в соответствующем табличном пространстве. Кроме этого rebuild может вызвать ошибку ORA-01410: Invalid ROWID.

Rebuild "offline" может использовать существующий индекс для создания новой версии индекса, но блокирует таблицу во время выполнения.

Rebuild "online" не блокирует таблицу во время непосредственной перестройки индекса, и индекс доступен практически все время при перестроении, кроме времени переключения. Однако при этом блокируется таблица в начале и в конце перестроения. При этом старый индекс не используется для перестроения индекса, но с ним работают пользователи. Все изменения тем временем вносятся в журнальную таблицу, затем уже будут перенесены в новый индекс. Может потребоваться большая сортировка.

Таким образом, оператор coalesce особенно эффективен, когда процент проблематичного пространства к общему индексному пространству невелик (20% листовых блоков) и фрагментирован индекс несущественно. rebuild особенно эффективен, когда процент проблематичного пространства к общему индексному пространству велик и средняя степень фрагментации в пределах индексного блока листа сравнительно высокая.

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

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

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

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

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

Уникальные и неуникальные индексы. Уникальные индексы основаны на уникальном столбце – обычно вроде номера карточки социального страхования сотрудника. Хотя уникальные индексы можно создавать явно, Oracle не рекомендует это делать. Вместо этого следует использовать уникальные ограничения. Когда накладывается ограничение уникальности на столбец таблицы, Oracle автоматически создает уникальные индексы по этим столбцам.
Первичные и вторичные индексы. Первичные индексы – это уникальные индексы в таблице, которые всегда должны иметь какое-то значение и не могут быть равны null. Вторичные индексы – это прочие индексы таблицы, которые могут и не быть уникальными.
Составные индексы – индексы, содержащие два или более столбца из одной и той же таблицы. Они также известны как сцепленные индексы (concatenated index). Составные индексы особенно полезны для обеспечения уникальности сочетания столбцов таблицы в тех случаях, когда нет уникального столбца, однозначно идентифицирующего строку.

Руководство по созданию индексов

Хотя хорошо известно, что индексы повышают производительность базы данных, следует знать, как их заставить работать должным образом. Добавление ненужных или неподходящих индексов к таблице может даже привести к снижению производительности. Ниже предоставлены некоторые рекомендации по созданию эффективных индексов в базе данных Oracle.

Индекс имеет смысл, если нужно обеспечить доступ одновременно не более чем к 4-5% данных таблицы. Альтернативной использования индекса для доступа к данным строки является полное последовательное чтение таблицы от начала до конца, что называется полным сканированием таблицы. Полное сканирование таблицы больше подходит для запросов, которые требуют извлечения большего процента данных таблицы. Помните, что применение индексов для извлечения строк требует двух операций чтения: индекса и затем таблицы.
Избегайте создания индексов для сравнительно небольших таблиц. Для таких таблиц больше подходит полное сканирование. В случае маленьких таблиц нет необходимости в хранении данных и таблиц, и индексов.
Создавайте первичные ключи для всех таблиц. При назначении столбца в качестве первичного колюча Oracle автоматически создаст индекс по этому столбцу.
Индексируйте столбцы, участвующие в многотабличных операциях соединения.
Индексируйте столбцы, которые часто используются в конструкциях WHERE.
Индексируйте столбцы, участвующие в операциях ORDER BY и GROUP BY или других операциях, таких как UNION и DISTINCT, включающих сортировку. Поскольку индексы уже отсортированы, объем работы по выполнению необходимой сортировки данных для упомянутых операций будет существенно сокращен.
Столбцы, стоящие из длинно-символьных строк, обычно плохие кандидаты на индексацию.
Столбцы, которые часто обновляются, в идеале не должны быть индексированы из-за связанных с этим накладных расходов.
Индексируйте таблицы в которых мало строк имеют одинаковые значения.
Сохраняйте количество индексов небольшим.
Составные индексы могут понадобиться там, где одностолбцовые значения сами по себе не уникальны. В составных индексах первым столбцом ключа должен быть столбец в котором количество строк с одинаковым значением минимально.

Всегда помните золотое правило индексации таблиц: индекс таблицы должен быть основан на типах запросов, которые будут выполняться над столбцами этой таблицы. На таблице можно создавать более одного индекса: например, можно создать индекс на столбце X, или столбце Y, или обоих сразу, а также один составной индекс на обоих столбцах. Принимая правильное решение относительно того, какие индексы следует создавать, подумайте о наиболее часто используемых типах запросов данных таблицы.

Схемы индексации Oracle

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

(B*tree)

В реализации индексов на основе B-деревьев используется концепция сбалансированного (на что указывает буква ‘B’ (balanced)) дерева поиска в качестве основы структуры индекса. В Oracle имеется собственный вариант B-дерева. Это обычные индексы, создаваемые по умолчанию, когда вы применяете оператора CREATE INDEX.

Индексы на основе B-деревьев структурированы в форме обратного дерева, где блоки верхнего уровня называются блоками ветвей (branch blocks), а блоки нижнего уровня – листовыми блоками (leaf blocks). В иерархии узлов все узлы кроме вершины, или корневого узла, имеют родительский узел и могут иметь ноль или более дочерних узлов. Если глубина древовидной структуры , т.е. количество уровней, одинакова от каждого листового блока до корневого узла, то такое дерево называется сбалансированным, или B-деревом.

B-деревья автоматически поддерживают необходимый уровень индекса по размеру таблицы. B-деревья также гарантируют, что индексные блоки всегда будут заполнены не меньше, чем наполовину, и менее, чем на 100%. B-деревья допускают операции выборки, вставки и удаления с очень небольшим количеством операций ввода-вывода на один оператор. Большинство B-деревьев имеет всего три и менее уровней. При использовании B-дерева нужно читать только блоки B-дерева, так что количество операций ввода-вывода будет ограничено числом уровней B-дерева (скажем, тремя) плюс две операции ввода-вывода на выполнение обновления или удаления (одна для чтения и одна для записи). Для выполнения поиска по B-дереву понадоисят всего три или менее обращений к диску.

Реализация B-дерева от Oracle – всегда сохраняет дерево сбалансированным. Листовые блоки содержат по два элемента: индексированные значения столбца и соответствующий идентификатор ROWID для строки, которая содержит это значение столбца. ROWID – уникальный указатель Oracle, идентифицирующий физическое местоположение строки и обеспечивающий самый быстрый способ доступа к строке в базе данных Oracle. Сканирование индекса быстро дает ROWID строки, и отсюда можно быстро получить к ней доступ непосредственно. Если запрос нуждается лишь в значении индексированного столбца, то конечно, последний шаг исключается, поскольку извлекать дополнительные данные, кроме прочитанных из индекса, не потребуется.

Оценка размера индекса

Для оценки размера нового индекса можно использовать пакет DBMS_SPACE. Процедуре CREATE_INDEX_COST этого пакета потребуется передать оператор DDL, создающий индекс, в качестве атрибута.

Обратите внимание на отличие между атрибутами, касающимися размера, в процедуре CREATE_INDEX_COST:

Used_bytes показывает количество байт, которыми представлены данные индекса;
Alloc_bytes показывает количество байт, которое займет индекс в табличном пространстве после его создания.

Создание индекса

Индекс создается с помощью оператора CREATE INDEX

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

Для создания уникального индекса служит оператор CREATE UNIQUE INDEX.

Специальные типы индексов

Нормальный или типовой индекс, который создается в базе данных, называется индексом кучи (heap index), или неупорядоченным индексом. Oracle также предоставляет несколько специальных типов индексов для специфических нужд.

Битовые индексы (bitmap indexes)

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

Битовые индексы состоят из битового потока (единиц и нулей) для каждого столбца индекса. Битовые индексы очень компактны по сравнению с нормальными индексами на основе B-деревьев.

Индексы B-деревьев	Битовые индексы
Хороши для данных с высокой кардинальностью	Хороши для данных с низкой кардинальностью
Хороши для баз данных OLTP	Хороши для приложений хранилищ данных OLAP
Занимают много места	Используют, относительно мало места
Легко обновляются	Трудно обновляются

Для создания битового индекса используется оператор

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

Индексы с реверсированным ключом

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

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

Индексы со сжатым ключом

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

Приведенный выше оператор сжимает все дублированные вхождения индексированного ключа в листовом блоке индекса (на уровне 1).

Индексы на основе функций

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

Ниже показано, как создать индекс на основе функции LOWER

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

Секционированные индексы

Секционированные индексы используются для индексации секционированных таблиц. Oracle предлагает два типа индексов для таких таблиц: локальные и глобальные.

Существенное различие между ними заключается в том, что локальные индексы основаны на разделах таблицы, по которой они созданы. Если таблица секционирована на 12 разделов по диапазонам дат, то индексы также будут распределены по тем же 12 разделам. Другими словами, между разделами индексов и разделами таблиц существует соответствие «один к одному». Такого соответствия нет между глобальными индексами и разделами таблицы, потому что глобальные индексы секционируются независимо от базовых таблиц.

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

Глобальные индексы

Глобальные индексы на секционированных таблицах могут быть как секционированными, так и несекционированными. Глобальные несекционированные индексы подобны обычным индексам Oracle для несекционированных таблиц. Для создания таких индексов применяется обычный синтаксис CREATE INDEX.

Ниже приведен пример глобального индекса на таблице ticket_sales:

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

Давайте в качестве примера воспользуемся таблицей ticket_sales, чтобы разобраться, почему это так. Предположим, что вы ежеквартально уничтожаете самый старый раздел, чтобы освободить место для нового раздела, в который поступят данные за новый квартал. Когда уничтожается раздел, относящийся к таблице ticket_sales, глобальные индексы могут стать недействительными, потому что часть данных, на которые они указывают, перестают существовать. Чтобы предотвратить такое объявление недействительным индекса из-за уничтожения раздела, необходимо использовать опцию UPDATE GLOBAL INDEXES вместе с оператором DROP PARTITION:

Если не включить оператор UPDATE GLOBAL INDEXES, то все глобальные индексы станут недействительными. Опцию UPDATE GLOBAL INDEXES можно также использовать при добавлении, объединении, обмене, слиянии, перемещении, разделении или усечении секционированных таблиц. Разумеется, с помощью ALTER INDEX..REBUILD можно перестраивать любой индекс, который становится недействительным, но эта опция также требует дополнительных затрат времени и обслуживания.

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

Локальные индексы

Локально секционированные индексы, в отличие от глобально секционированных индексов, имею отношение «один к одному» с разделами таблицы. Локально секционированные индексы можно создавать в соответствии с разделами и даже подразделами. База данных конструирует индекс таким образом, чтобы он был секционирован так же, как и его таблица. При каждой модификации раздела таблицы база автоматически сопровождает это соответствующей модификацией раздела индекса. Это, наверное, самое большое преимущество использования локально секционированных индексов – Oracle автоматически перестраивает их всегда, когда уничтожается раздел или над ним выполняется какая-то другая операция DDL.

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

Невидимые индексы

По умолчанию оптимизатор «видит» все индексы. Тем не менее, можно создать невидимый индекс, который оптимизатор не обнаруживает и не принимает во внимание при создании плана выполнения оператора. Невидимый индекс можно применять в качестве временного индекса для определенных операций или его тестирования перед тем, как сделать его «официальным». Вдобавок, иногда объявления индекса невидимым можно использовать в качестве альтернативы уничтожению индекса или объявлению его недоступным. Сделать индекс невидимым можно временно, чтобы протестировать эффект от его уничтожения.

База данных поддерживает невидимый индекс точно так же, как и нормальный (видимый) индекс. После объявления индекса невидимым, его и все прочие невидимые индексы можно сделать вновь видимым для оптимизатора, установив значение параметра optimizer_use_invisible_index равным TRUE на уровне сеанса или всей системы. Значением этого параметра по умолчанию является FALSE, а это означает, что оптимизатор по умолчанию не может использовать невидимые индексы.

Создание невидимого индекса.

Чтобы сделать индекс невидимым, к оператору CRETE INDEX нужно добавить конструкцию INVISIBLE.

С помощью команды ALTER INDEX можно превратить существующий индекс в невидимый.

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

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

Среди подобных задач можно выделить следующие:

Рассмотрим по порядку автоматизацию каждой из этих задач.

UPDATE 2019-06-03:
Но сперва хочется чуток порекламировать опенсорс программу, которую я сделал спустя 5 лет после написания этого поста. Так уж исторически сложилось, что долгое время участвовал в разработке системных тулов для обслуживания SQL Server. За это время накопилось много идей и на определенном этапе захотелось сделать что-то свое.

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

Ключевые особенности SQL Index Manager:

Оптимизированный алгоритм получения фрагментированных индексов
Возможность обслуживания нескольких баз данных за раз
Автоматический выбор действия для индексов исходя из выбранных настроек
Поддержка глобального поиска и сложной фильтрации для более удобной аналитики
Большое число настроек и полезной информации об индексах
Автоматическая генерация скриптов по обслуживанию индексов
Поддержка обслуживания кучи и колумнсторов
Поддержка командной строки
Возможность включать сжатие индексов и обновление статистики вместо ребилда
Возможность экспорта результатов
Кастомизация интерфейса
Поддержка всех редакций SQL Server 2008+ и Azure SQL Database

Но вернемся теперь к изначальному посту. Итак, пункт первый…

Помимо фрагментации файловой системы и лог-файла, ощутимое влияние на производительность базы данных оказывает фрагментация внутри файлов данных:

1. Фрагментация внутри отдельных страниц индекса

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

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

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

2. Фрагментация внутри структур индекса

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

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

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

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

Для борьбы с фрагментацией индексов в арсенале SQL Server предусмотрены команды: ALTER INDEX REBUILD / REORGANIZE.

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

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

Степень фрагментации того или иного индекса можно узнать из динамического системного представления sys.dm_db_index_physical_stats:

В данном запросе, последний параметр задает режим, от значения которого возможно быстрое, но не совсем точное определения уровня фрагментации индекса (режимы LIMITED/NULL). Поэтому рекомендуется задавать режимы SAMPLED/DETAILED.

Мы знаем откуда получить список фрагментированных индексов. Теперь необходимо для каждого из них сгенерировать соответствующую ALTER INDEX команду. Традиционно для этого используют курсор:

Чтобы ускорить процесс пересоздания индекса рекомендуется дополнительно указывать опцию SORT_IN_TEMPDB. Еще нужно отдельно упомянуть про опцию ONLINE — она замедляет пересоздание индекса. Но иногда бывает полезной. Например, чтение из кластерного индекса очень дорогое. Мы создали покрывающий индекс и решили проблему с производительностью. Далее мы делаем REBUILD некластерного индекса. В этот момент нам придется снова обращаться к кластерному индексу — что снижает перфоманс.

SORT_IN_TEMPDB позволяет перестраивать индексы в базе tempdb, что бывает особенно полезно для больших индексов в случае нехватки памяти и ином случае — опция игнорируется. Кроме того, если база tempdb расположена на другом диске — это существенно сократит время создания индекса. ONLINE позволяет пересоздать индекс не блокируя при этом запросы к объекту для которого этот индекс создается.

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

В дополнении, приведенный выше запрос можно переписать без применения курсора:

В результате оба запроса при выполнении будут генерировать запросы по дефрагментации проблемных индексов:

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

UPDATE 2016-04-22: добавил возможность дефрагментации отдельных секций и исправил некоторые баги

Читайте также: