Индексы
📖 Описание
Индекс (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.