15. Индексы (INDEX)

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

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

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

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

Предупреждение

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

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

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

Индекс может быть упорядочен по возрастанию значений столбцов, входящих в его состав (ASCENDING — значение по умолчанию) или по убыванию этих значений (DESCENDING). В любой момент времени работы с базой данных индекс может быть сделан активным (ACTIVE), то есть все изменения столбцов таблицы, входящих в состав этого индекса, тут же отражаются в самом индексе, или неактивным (INACTIVE), когда никакие изменения в строках соответствующей таблицы базы данных не затрагивают содержание индекса.

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

Для создания индекса для существующей таблицы базы данных используется оператор CREATE INDEX. Его синтаксис представлен в листинге .

Листинг 15.1 Синтаксис оператора создания индекса CREATE INDEX

CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]]
INDEX <имя индекса> ON <таблица>
{(<столбец> [, <столбец> ...]) | COMPUTED BY (<выражение>)}
[WHERE <условие>]
[[IN] TABLESPACE {<имя табличного пространства> | PRIMARY}];

Примечание

Создать индекс может только владелец таблицы, для которой создан индекс, администратор и пользователь с привилегией ALTER ANY TABLE.

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

Имя индекса должно быть уникальным среди имен всех индексов базы данных, а также среди имен ограничений на уровне столбцов таблицы и ограничений на уровне таблиц. Когда при задании ограничений первичного, уникального или внешнего ключа (см. главу 10) вы указываете и имя ограничения в предложении CONSTRAINT, система строит индекс с тем же самым именем. Если же при описании этих ключей задается и предложение USING, то автоматически создаваемый индекс получает имя, указанное в предложении USING.

Ключевое слово UNIQUE задает создание уникального индекса, оно указывает, что в индексе не может быть двух строк с одинаковыми значениями всех столбцов индекса. Но уникальные индексы могут содержать дубликаты значения NULL в соответствии со стандартом SQL-99 (в том числе и в многосегментном индексе).

Ключевое слово ASCENDING (сокращенный вариант ASC) означает, что записи индекса упорядочиваются по возрастанию значений столбцов, входящих в состав индекса. Этот вариант принимается по умолчанию.

Ключевое слово DESCENDING (сокращение DESC) указывает, что записи индекса упорядочиваются по уменьшению значений столбцов индекса.

Пример.

Если для таблицы PEOPLE требуются и возрастающий и убывающий индексы по столбцу, хранящему фамилии людей LAST_NAME, то нужно создать два индекса, выполнив операторы:

CREATE ASCENDING INDEX ASC_PEOPLE ON PEOPLE (LAST_NAME);
CREATE DESCENDING INDEX DESC_PEOPLE ON PEOPLE (LAST_NAME);

При создании индекса вместо одного или нескольких столбцов также можно указать одно выражение, используя предложение COMPUTED BY. Такой индекс называется вычисляемым или индексом по выражению. Вычисляемые индексы используются в запросах, в которых условие в предложениях WHERE, ORDER BY или GROUP BY в точности совпадает с выражением в определении индекса. Выражение в вычисляемом индексе может использовать несколько столбцов таблиц.

CREATE INDEX IDX_NAME_UPPER ON PERSONS
COMPUTED BY (UPPER (NAME));

Предложение WHERE позволяет создать индекс, распространяющийся только на записи таблицы, удовлетворяющие указанному условию. Частичный индекс может быть создан как уникальный (ключевое слово UNIQUE). В этом случае, каждый ключ в индексе должен быть уникальным. Это позволяет обеспечить уникальность для некоторого подмножества строк таблицы. Частичный индекс будет использован только в следующих случаях:

• Если условие WHERE содержит точно такое же выражение, как и то, которое определено для индекса;

• Если условие, определенное для индекса, содержит несколько выражений, объединенных оператором OR, и одно из них явно включено в условие WHERE;

• Если условие, определенное для индекса, содержит IS NOT NULL, а условие WHERE включает выражение для того же поля, про которое известно, что оно игнорирует NULL.

Пример.

CREATE INDEX IT1_COL ON T1 (COL) WHERE COL < 100;

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

CREATE INDEX idx_name ON PERSONS (NAME) TABLESPACE tablespace_name;

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

Предупреждение

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

Ограничения на индексы

Максимальная длина ключа индекса составляет $1/4$ размера страницы. Максимальная длина индексируемой строки на 9 байтов меньше, чем максимальная длина ключа. Максимальная длина индексируемой строки зависит от размера страницы и набора символов:

Размер страницы	Максимальная длина индексируемой строки для набора символов, байт/символ
	1	2	3	4	6
4096	1015	507	338	253	169
8192	2039	1019	679	509	339
16384	4087	2043	1362	1021	682
32768	9183	4087	2721	2039	1356

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

Размер страницы	Число индексов в зависимости от количества столбцов в индексе
	1	2	3
4096	203	145	113
8192	408	291	227
16384	818	584	454

15.2. Изменение индекса

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

В некоторых случаях бывает полезным на некоторое время «отключить» индекс, сделать его неактивным. Это может сэкономить время при выполнении так называемых пакетных операций с таблицей, когда в таблицу, для которой создан индекс, из какого-либо файла записывается достаточно большое количество строк или в таблице изменяется или из таблицы удаляется большое количество строк. Перед началом такой операции индекс переводится в неактивное (INACTIVE) состояние, а после завершения операции — снова в активное (ACTIVE). При этом при активизации индекса осуществляется полное пересоздание индекса. Все вновь введенные, измененные или удаленные строки будут учтены в новом состоянии индекса.

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

Листинг 15.2 Синтаксис оператора изменения индекса ALTER INDEX

ALTER INDEX <имя индекса>
{ { ACTIVE | INACTIVE }
| SET TABLESPACE [TO] {<имя табличного пространства> | PRIMARY}
}

Примечание

Состояние индекса может изменять только владелец таблицы, для которой создан индекс, администратор и любой пользователь с привилегией ALTER ANY TABLE.

Ключевое слово INACTIVE указывает, что индекс переводится в неактивное состояние. Перевод индекса в неактивное состояние по своему действию похоже на команду DROP INDEX за исключением того, что определение индекса сохраняется в базе данных. Невозможно перевести в неактивное состояние индекс участвующий в ограничении.

Активный индекс может быть отключен, только если отсутствуют запросы использующие этот индекс, иначе будет возвращена ошибка «object in use».

Ключевое слово ACTIVE задает перевод неактивного индекса в активное состояние. После перевода индекса из неактивного в активное состояние система заново полностью создает весь индекс. Активация неактивного индекса безопасна. Тем не менее, если есть активные транзакции, модифицирующие таблицу, то транзакция, содержащая оператор ALTER INDEX потерпит неудачу, если она имеет атрибут NO WAIT. Если транзакция находится в режиме WAIT, то она будет ждать завершения параллельных транзакций.

С другой стороны, если оператор ALTER INDEX начинает перестраивать индекс на COMMIT, то другие транзакции, изменяющие эту таблицу, потерпят неудачу или будут ожидать в соответствии с их WAIT/NO WAIT атрибутами. Та же самая ситуация будет и при выполнении CREATE INDEX.

Примечание

Даже если индекс находится в активном состоянии оператор ALTER INDEX ... ACTIVE всё равно перестраивает индекс. Таким образом, эту команду можно использовать как часть обслуживания БД, для индексов большой таблицы в которую происходят частые вставки, обновления и удаления. Для перестройки индексов, автоматически созданных для ограничений PRIMARY KEY, FOREIGN KEY, UNIQUE, для которых выполнение оператора ALTER INDEX ... INACTIVE невозможно.

Принудительный перевод индексов, созданных для ограничений PRIMARY KEY, FOREIGN KEY и UNIQUE не допускается. Тем не менее, выполнение оператора ALTER INDEX ... INACTIVE работает так же хорошо для индексов ограничений как и другие инструменты для других индексов.

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

ALTER INDEX idx_name SET TABLESPACE TO tablespace_name;

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

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

ALTER INDEX idx_name SET TABLESPACE TO PRIMARY;

Примечание

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

Предупреждение

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

Пример.

Чтобы перевести индекс DESC_PEOPLE перед выполнением какой-либо пакетной операции в неактивное состояние, нужно выполнить следующий оператор:

ALTER INDEX DESC_PEOPLE INACTIVE;

После завершения соответствующих действий нужно перевести его в активное состояние, выполнив:

ALTER INDEX DESC_PEOPLE ACTIVE;

15.3. Удаление индекса

Для удаления индекса, созданного пользователем, используется оператор DROP INDEX. Его синтаксис представлен в листинге .

Листинг 15.3 Синтаксис оператора удаления индекса DROP INDEX

DROP INDEX <имя индекса>;

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

При наличии зависимостей для существующего индекса (если он используется в ограничении) удаление не будет выполнено.

Примечание

Удалить индекс может только владелец таблицы, для которой создан индекс, администратор и пользователь с привилегией ALTER ANY TABLE.

Пример.

Чтобы удалить индекс DESC_PEOPLE, нужно выполнить следующий оператор:

DROP INDEX DESC_PEOPLE;

15.4. Селективность индекса

Селективность (избирательность) индекса — это некоторое состояние, задаваемое числовым значением, которое определяет эффективность использования данного индекса при выборке данных. Селективность определяется числом от нуля до единицы. Чем меньше это число, тем выше селективность (полезность) индекса, тем выше эффективность использования индекса для поиска записей.

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

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

Листинг 15.4 Синтаксис оператора изменения селективности индекса SET STATISTICS

SET STATISTICS INDEX <имя индекса>;

Оператор улучшает, оптимизирует селективность указанного индекса.

Примечание

Только владелец таблицы, для которой был создан индекс, администратор и пользователь с ролью ALTER ANY TABLE имеют привилегии на использование SET STATISTICS INDEX.

15.5. Примечание индекса

Для индекса можно создать примечание, используя следующий вариант оператора COMMENT (листинг ).

Листинг 15.5 Синтаксис оператора создания примечания индекса COMMENT ON INDEX

COMMENT ON
   INDEX <имя индекса> IS {'<текст>' | NULL};

Если в качестве текста примечания задать NULL, то будет удалено существующее примечание индекса.

Пример.

Чтобы добавить примечание к индексу ASC_PEOPLE, нужно выполнить оператор:

COMMENT ON
    INDEX ASC_PEOPLE IS 'Индекс, упорядоченный по возрастанию фамилий людей';