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

Синтаксис CREATE INDEX

CREATE [UNIQUE] INDEX <index name> ON [schema_name.]table_name
  (<column name> [,<column name>]...);

Добавление индекса снижает производительность запросов, связанных с добавлением, изменением или удалением данных, поскольку каждый раз при выполнении транзакции данные индекса также обновляются, что требует выполнения от сервера дополнительной работы. Однако выполение запросов 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 :

DROP INDEX <index name>;

ALTER INDEX

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

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

-- переименование индекса в СУБД PostgresSQL
ALTER INDEX [IF EXISTS] index_name
  RENAME TO index_name_new;

ALTER INDEX в Oracle

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

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

-- переименование индекса в СУБД Oracle
ALTER INDEX index_name RENAME TO index_name_new;

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

ALTER INDEX index_name [ coalesce | [ rebuild | rebuild online ] ];

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 особенно эффективен, когда процент проблематичного пространства к общему индексному пространству велик и средняя степень фрагментации в пределах индексного блока листа сравнительно высокая.