Рекомендации по использованию индексов в конкретных приложениях

Для обеспечения быстрого доступа к строкам таблицы СУБД Oracle используются индексы. Индексы обеспечивают быстрый доступ к данным при операциях, когда выбирается относительно небольшое число строк таблицы.

Хотя Oracle позволяет использовать неограниченное число индексов в таблице, индексы приносят пользу только в том случае, когда они применяются для ускорения запросов. В противном случае, они лишь занимают место и уменьшают производительность сервера при обновлении индексированных столбцов. Необходимо использовать возможность EXPLAIN PLAN (план выполнения и статистики), чтобы определить, каким образом индексы используются в ваших запросах. Иногда, если индекс не используется по умолчанию, можно использовать подсказки к запросу по использованию индекса.

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


Создавайте индексы после вставки табличных данных

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


Переключайтесь на временное табличное пространство, чтобы избежать проблем со свободным пространством, при создании индексов

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

1. Создайте новое временное табличное пространство с помощью оператора CREATE TABLESPACE.
2. Укажите это новое временное пространство в параметре TEMPORARY TABLESPACE оператора ALTER USER.
3. Создайте индекс оператором CREATE INDEX.
4. Уничтожьте это табличное пространство командой DROP TABLESPACE. Затем при помощи оператора ALTER USER восстановите исходное табличное пространство в качестве временного.

Правильно выбирайте таблицы и стобцы для индексации

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

1. Создавайте индекс, если вы часто выполняете выборку относительно небольшого (меньше 15%) числа строк большой таблицы. Этот процент сильно зависит от относительной скорости просмотра таблицы и от того, как кластеризованны данные строк в индексном ключе. Чем выше скорость просмотра, тем меньший процент, чем больше кластеризованы данные строки, тем процент больше.
2. Индексируйте столбцы, которые используются в соединениях, для повышения производительности соединения нескольких таблиц.
3. По первичному и уникальному ключу индексы создаются автоматически.
4. Небольшие таблицы индексировать не нужно. Если вы заметили, что время выполнения запросов сильно увеличилось, то, по всей видимости, она стала большой.

Некоторые столбцы являются явными кандидатами на индексирование. Они обладают такими характеристиками:

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

  WHERE COL_X > -9.99 *power(10, 125)

  нежели
  

  WHERE COL_X IS NOT NULL

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

Ограничивайте число индексов для каждой таблицы

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

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


Выбирайте порядок столбцов в составных индексах

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


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

Индексы могут использоваться более эффективно, если в базе данных собирается и поддерживается статистика о таблицах, используемых в запросах. Вы можете собирать статистику во время создания индекса, задав ключевое слово COMPUTE STATISTICS в операторе CREATE INDEX. Поскольку данные постоянно обновляются, а также меняется распределение значений, статистики следует периодически обновлять при помощи процедур DBMS_STATS.GATHER_TABLE_STATISTICS и DBMS_STATS.GATHER_SCHEMA_STATISTICS.


Уничтожайте ненужные индексы

Индекс удаляется в следующих случаях:

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

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