FirebirdSQL logo
 TABLEVIEW 

Индекс — это объект базы данных, используемый для более быстрого извлечения данных из таблицы или для ускорения сортировки в запросе.Кроме того, индексы используются для обеспечения ограничений целостности — PRIMARY KEY, FOREIGN KEY, UNIQUE.

В данном разделе описываются вопросы создания индексов, перевода их в активное/неактивное состояние, удаление индексов и сбор статистики (пересчёт селективности) для индексов.

CREATE INDEX

Назначение

Создание индекса для таблицы.

Доступно в

DSQL, ESQL.

CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]]
INDEX indexname ON tablename
{(<column_list>) | COMPUTED [BY] (<value_expression>)}
[WHERE <search_condition>]

<column_list> ::= col [, col ...]
Table 1. Параметры оператора CREATE INDEX
Параметр Описание

indexname

Имя индекса.Может содержать до 63 символов.

tablename

Имя таблицы, для которой строится индекс.

col

Столбец таблицы.В качестве столбцов не могут быть использованы поля типа BLOB, ARRAY и вычисляемые поля.

value_expression

Выражение, содержащее столбцы таблицы. Значение этого выражения будут ключами индекса.

search_condition

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

Оператор CREATE INDEX создаёт индекс для таблицы, который может быть использован для ускорения поиска, сортировки и/или группирования.Кроме того, индекс может быть использован при определении ограничений, таких как первичный ключ, внешний ключ или ограничениях уникальности.Индекс может быть построен на столбцах любого типа кроме BLOB и массивов.Имя индекса должно быть уникальным среди всех имён индексов.

Note
Индексы в ключах

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

ALTER TABLE COUNTRY
ADD CONSTRAINT PK_COUNTRY PRIMARY KEY (ID);

docnext count = 20

Уникальные индексы

Если при создании индекса указано ключевое слово UNIQUE, то индекс гарантирует уникальность значений ключей.Такой индекс называется уникальным.Уникальный индекс не является ограничением уникальности.

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

Направление индекса

Все индексы в Firebird являются однонаправленными.Индекс может быть построен в восходящем и нисходящем порядке.Ключевые слова ASC[ENDING] (сокращённо ASC) и DESC[ENDING] используются для указания направленности индекса.По умолчанию создаётся восходящий ASC[ENDING] индекс.Допускается одновременное определение восходящего и нисходящего индекса на одном и том же столбце или наборе ключей.

Tip

Убывающий (DESC[ENDING]) индекс может быть полезен при поиске наивысших значений (максимум, последнее и т.д.)

Вычисляемые индексы или индексы по выражению

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

Частичные индексы

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

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

Частичный индекс можно использовать только в следующих случаях:

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

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

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

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

Максимальная длина ключа индекса ограничена 1/4 размера страницы.

Ограничения на длину индексируемой строки

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

Table 1. Длина индексируемой строки и набор символов

Размер страницы

Максимальная длина индексируемой строки для набора символов, байт/символ

1

2

3

4

6

4096

1015

507

338

253

169

8192

2039

1019

679

509

339

16384

4087

2043

1362

1021

681

32768

8183

4091

2727

2045

1363

Максимальное количество индексов на таблицу

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

Table 1. Число индексов и количество столбцов

Размер страницы

Число индексов в зависимости от количества столбцов в индексе

1

2

3

4096

203

145

113

8192

408

291

227

16384

818

584

454

32768

1637

1169

909

Кто может создать индекс?

Выполнить оператор CREATE INDEX могут:

  • Администраторы

  • Владелец таблицы, для которой создаётся индекс;

  • Пользователи с привилегией ALTER ANY TABLE.

Примеры

Example 1. Создание индекса
CREATE INDEX IDX_UPDATER ON SALARY_HISTORY (UPDATER_ID);
Example 2. Создание индекса с сортировкой ключей по убыванию
CREATE DESCENDING INDEX IDX_CHANGE
ON SALARY_HISTORY (CHANGE_DATE);
Example 3. Создание многосегментного индекса
CREATE INDEX IDX_SALESTAT ON SALES (ORDER_STATUS, PAID);
Example 4. Создание индекса, не допускающего дубликаты значений
CREATE UNIQUE INDEX UNQ_COUNTRY_NAME ON COUNTRY (NAME);
Example 5. Создание вычисляемого индекса
CREATE INDEX IDX_NAME_UPPER ON PERSONS
COMPUTED BY (UPPER (NAME));

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

SELECT *
FROM PERSONS
WHERE UPPER(NAME) STARTING WITH UPPER('Iv');
Example 6. Создание частичного индекса
CREATE INDEX IT1_COL ON T1 (COL) WHERE COL < 100;

Если при выполнении выборки в условии WHERE будет точно такое же выражение, которое было задано в индексе, индекс будет использован, в противном случае нет.

SELECT * FROM T1 WHERE COL < 100;

-- PLAN (T1 INDEX (IT1_COL))

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

CREATE INDEX IT1_COL2 ON T1 (COL) WHERE COL IS NOT NULL;

Этот индекс может использоваться почти любыми предикатами поиска за исключением IS NULL и IS NOT DISTINCT FROM, поскольку другие выражение игнорируют NULL.

SELECT * FROM T1 WHERE COL > 100;

-- PLAN (T1 INDEX IT1_COL2)

Частичный индекс можно создать по нескольким значениям столбца, для этого их надо перечислить в IN или объединить несколько выражений оператором OR.

CREATE INDEX IT1_COL3 ON T1 (COL) WHERE COL = 1 OR COL = 2;
SELECT * FROM T1 WHERE COL = 2;

-- PLAN (T1 INDEX IT1_COL3)
См. также:

ALTER INDEX, DROP INDEX.

ALTER INDEX

Назначение

Перевод индекса в активное/неактивное состояние, перестройка индекса.

Доступно в

DSQL, ESQL.

Синтаксис
ALTER INDEX indexname {ACTIVE | INACTIVE};
Table 1. Параметры оператора ALTER INDEX
Параметр Описание

indexname

Имя индекса.

Оператор ALTER INDEX переводит индекс в активное/неактивное состояние.Возможность изменения структуры и порядка сортировки ключей этот оператор не предусматривает.

INACTIVE

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

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

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

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

Tip

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

При выборе альтернативы ACTIVE индекс переводится из неактивного состояния в активное. При переводе индекса из неактивного состояния в активное — индекс перестраивается.

Tip

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

Использование ALTER INDEX для индексов ограничений

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

Кто может выполнить ALTER INDEX?

Выполнить оператор ALTER INDEX могут:

  • Администраторы

  • Владелец таблицы, для которой построен индекс;

  • Пользователи с привилегией ALTER ANY TABLE.

Примеры

Example 1. Перевод индекса в неактивное состояние
ALTER INDEX IDX_UPDATER INACTIVE;
Example 2. Возврат индекса в активное состояние
ALTER INDEX IDX_UPDATER ACTIVE;
См. также:

CREATE INDEX, DROP INDEX.

DROP INDEX

Назначение

Удаление индекса из базы данных.

Доступно в

DSQL, ESQL.

Синтаксис
DROP INDEX indexname
Table 1. Параметры оператора DROP INDEX
Параметр Описание

indexname

Имя индекса.

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

Кто может удалить индекс?

Выполнить оператор DROP INDEX могут:

  • Администраторы

  • Владелец таблицы, для которой построен индекс;

  • Пользователи с привилегией ALTER ANY TABLE.

Примеры

Example 1. Удаление индекса
DROP INDEX IDX_UPDATER;
См. также:

CREATE INDEX, ALTER INDEX.

SET STATISTICS

Назначение

Пересчёт селективности индекса.

Доступно в

DSQL, ESQL.

Синтаксис
SET STATISTICS INDEX indexname
Table 1. Параметры оператора SET STATISTICS
Параметр Описание

indexname

Имя индекса.

Оператор SET STATISTICS пересчитывает значение селективности для указанного индекса.

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

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

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

Note

Отметим, что в Firebird статистика индексов автоматически не пересчитывается ни после массовых изменений данных, ни при каких либо других условиях.При создании (CREATE) или его активации (ALTER INDEX ACTIVE) статистика индекса полностью соответствует его содержимому.

Пересчёт селективности индекса может быть выполнен под высоко параллельной нагрузкой без риска его повреждения.Тем не менее следует помнить, что при высоком параллелизме рассчитанная статистика может устареть, как только закончится выполнение оператора SET STATISTICS.

Кто может обновить статистику?

Выполнить оператор SET STATISTICS могут:

  • Администраторы

  • Владелец таблицы, для которой построен индекс;

  • Пользователи с привилегией ALTER ANY TABLE.

Примеры

Example 1. Пересчёт селективности индекса IDX_UPDATER
SET STATISTICS INDEX IDX_UPDATER;
См. также:

CREATE INDEX, ALTER INDEX.