Индексы

📖 Описание

Индекс (INDEX) — объект базы данных, который может ускорять поиск строк таблицы. Он строится по одному или нескольким столбцам либо выражениям. По умолчанию Oracle создаёт B-tree индекс.

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

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

🧩 Синтаксис

CREATE [UNIQUE] INDEX index_name
  ON table_name (column1, column2, ... column_n);
  • UNIQUE проверяет уникальность индексируемого ключа. Это свойство индекса, а декларативное правило данных задаётся ограничением UNIQUE или PRIMARY KEY.
  • index_name — имя нового индекса; table_name — индексируемая таблица.
  • column1, column2, ... — столбцы в порядке индексного ключа.

💻 Учебные данные и один столбец

CREATE TABLE demo_index_studios (
  studio_id NUMBER(6) PRIMARY KEY,
  studio_name VARCHAR2(60 CHAR),
  city VARCHAR2(40 CHAR) NOT NULL,
  opened_on DATE NOT NULL
);
INSERT ALL
  INTO demo_index_studios VALUES (41, 'River Clay', 'Tashkent', DATE '2026-02-07')
  INTO demo_index_studios VALUES (42, 'river clay', 'Samarkand', DATE '2026-04-11')
  INTO demo_index_studios VALUES (43, NULL, 'Khiva', DATE '2026-06-13')
  INTO demo_index_studios VALUES (44, 'Garden Paper', 'Bukhara', DATE '2026-08-15')
SELECT 1 FROM dual;
COMMIT;
CREATE INDEX demo_studios_name_ix ON demo_index_studios (studio_name);

Индекс построен по имени студии. Обычный восходящий B-tree не содержит строк, у которых все части индексного ключа NULL.

💻 Несколько столбцов

CREATE INDEX demo_studios_name_city_ix
  ON demo_index_studios (studio_name, city);

Порядок ключей — сначала studio_name, затем city. Он важен для возможных способов доступа, но сам индекс не задаёт порядок результата SELECT: для этого нужен ORDER BY.

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

CREATE INDEX demo_studios_city_day_ix
  ON demo_index_studios (city, opened_on);
BEGIN
  DBMS_STATS.GATHER_INDEX_STATS(
    ownname => USER,
    indname => 'DEMO_STUDIOS_CITY_DAY_IX'
  );
END;
/

Старые примеры могли содержать COMPUTE STATISTICS в CREATE INDEX. Этот необязательный исторический синтаксис устарел; для Oracle 19c в примере выше используется обычное создание и явный вызов DBMS_STATS. Статистика помогает оптимизатору оценить стоимость, а не принуждает его выбрать индекс.

⚙️ Индекс на основе функции

В индексном ключе можно использовать выражение, например UPPER имени. Собственная функция для такого индекса должна удовлетворять требованиям Oracle, в том числе DETERMINISTIC; нельзя просто объявить так функцию с меняющимся результатом.

🧩 Синтаксис

CREATE [UNIQUE] INDEX index_name
  ON table_name (expression1, expression2, ...);

expression — выражение над столбцами таблицы и допустимыми функциями; остальные параметры имеют тот же смысл, что и у обычного индекса.

💻 Пример

CREATE INDEX demo_studios_upper_ix ON demo_index_studios (UPPER(studio_name));
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => USER,
    tabname => 'DEMO_INDEX_STUDIOS',
    cascade => TRUE
  );
END;
/
SELECT studio_id, studio_name, UPPER(studio_name) AS normalized_name
FROM demo_index_studios
WHERE UPPER(studio_name) IS NOT NULL
ORDER BY UPPER(studio_name), studio_id;
STUDIO_ID STUDIO_NAME NORMALIZED_NAME
44 Garden Paper GARDEN PAPER
41 River Clay RIVER CLAY
42 river clay RIVER CLAY

Итоговый порядок показан для обычного бинарного сравнения латиницы. Студия 43 исключена условием. План выполнения этим примером не фиксируется. Поиск по конкретному нормализованному имени:

SELECT studio_id, studio_name FROM demo_index_studios
WHERE UPPER(studio_name) = 'RIVER CLAY'
ORDER BY studio_id;

Возвращаются 41 и 42.

⚙️ Переименование индекса

🧩 Синтаксис

ALTER INDEX index_name RENAME TO new_index_name;

index_name — существующее имя; new_index_name — новое свободное имя в схеме.

💻 Пример

ALTER INDEX demo_studios_name_ix RENAME TO demo_studios_label_ix;
SELECT index_name, status FROM user_indexes
WHERE index_name = 'DEMO_STUDIOS_LABEL_IX';

Индекс теперь называется DEMO_STUDIOS_LABEL_IX и имеет статус VALID. В следующих командах используется уже новое имя.

⚙️ Перестроение и сбор статистики

REBUILD заново строит индекс и требует ресурсов. Это отдельная операция, которую не нужно выполнять только ради обновления статистики.

🧩 Синтаксис и пример

ALTER INDEX index_name REBUILD;
-- Конкретный пример для созданного выше учебного индекса:
ALTER INDEX demo_studios_label_ix REBUILD;

Первая строка — схема команды, в учебном сценарии выполняется только вторая. Исторический вариант REBUILD COMPUTE STATISTICS совмещал описание перестроения и сбора статистики; для нового кода показаны отдельные понятные операции. Для статистики существующего индекса без перестроения:

BEGIN
  DBMS_STATS.GATHER_INDEX_STATS(
    ownname => USER,
    indname => 'DEMO_STUDIOS_LABEL_IX'
  );
END;
/

⚙️ Удаление индекса

🧩 Синтаксис

DROP INDEX index_name;

index_name — имя удаляемого индекса. Для учебного индекса, не обеспечивающего ограничение ключа:

DROP INDEX demo_studios_label_ix;
SELECT COUNT(*) AS index_count FROM user_indexes
WHERE index_name = 'DEMO_STUDIOS_LABEL_IX';
SELECT COUNT(*) AS studio_count FROM demo_index_studios;

index_count=0, studio_count=4: строки таблицы не удалены. Остальные индексы учебной таблицы сохраняются. Индекс, используемый активным PRIMARY KEY или UNIQUE, нельзя просто удалить независимо от ограничения.

📝 Примечание

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

См. первичные ключи и UNIQUE.

См. документацию Oracle: CREATE-INDEX.

См. документацию Oracle: ALTER-INDEX.

Дополнительно: DBMS_STATS; история COMPUTE STATISTICS в CREATE INDEX.