29 подписчиков

Что такое функциональный индекс и как он ускоряет запросы с функциями

СегодняСегодня

2 мин

Коллеги, всем привет! С вами Кирилл Михалько, надеюсь я вас не сильно утомляю своими портянками 😊 Сегодня поговорим о функциональных индексах. Это мощный инструмент, о котором порой даже не догадываются. Если ваши запросы используют функции в условиях WHERE или JOIN, обычные индексы не помогут - СУБД всё равно выполнит полное сканирование таблицы (есть нюансы). Функциональный индекс решает эту проблему, индексируя не сами данные, а результат вычисления функции. Как это работает Обычный индекс хранит значения столбца в отсортированном виде. Функциональный индекс идёт дальше - он сохраняет результаты применения функции к данным. Когда запрос использует ту же функцию в условии, оптимизатор может использовать этот индекс вместо пересчёта функции для каждой строки. Классический пример - поиск по email без учёта регистра: -- Без функционального индекса - полное сканирование SELECT * FROM users WHERE LOWER(email) = 'user@example.com'; -- Создаём функциональный индекс CREATE INDEX idx_use

Коллеги, всем привет!

С вами Кирилл Михалько, надеюсь я вас не сильно утомляю своими портянками 😊

Сегодня поговорим о функциональных индексах. Это мощный инструмент, о котором порой даже не догадываются.

Если ваши запросы используют функции в условиях WHERE или JOIN, обычные индексы не помогут - СУБД всё равно выполнит полное сканирование таблицы (есть нюансы). Функциональный индекс решает эту проблему, индексируя не сами данные, а результат вычисления функции.

Как это работает

Обычный индекс хранит значения столбца в отсортированном виде. Функциональный индекс идёт дальше - он сохраняет результаты применения функции к данным. Когда запрос использует ту же функцию в условии, оптимизатор может использовать этот индекс вместо пересчёта функции для каждой строки.

Классический пример - поиск по email без учёта регистра:

-- Без функционального индекса - полное сканирование

SELECT * FROM users

WHERE LOWER(email) = 'user@example.com';

-- Создаём функциональный индекс

CREATE INDEX idx_users_email_lower

ON users (LOWER(email));

-- Теперь запрос использует индекс

SELECT * FROM users

WHERE LOWER(email) = 'user@example.com';

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

Практические сценарии применения

🔹 Поиск по датам: индексирование TRUNC(date_column) для запросов, игнорирующих время

🔹 Нормализация значений: NVL(status, 'N') или COALESCE(field, 0) прямо в индексе, чтобы не городить сложные условия в запросах

🔹 Текстовый поиск: индексирование UPPER(name) или конкатенации полей

Пример с датами:

-- Индекс для запросов по дням без учёта времени

CREATE INDEX idx_orders_date_only

ON orders (TRUNC(order_date));

-- Быстрый поиск всех заказов за конкретный день

SELECT * FROM orders

WHERE TRUNC(order_date) = DATE '2025-01-15';

Подводные камни

⚠️ Точное совпадение функции: индекс работает только если функция в запросе идентична функции в определении индекса: TRUNC(dt) и TRUNC(dt, 'HH') - это уже разные вещи.

⚠️ Стоимость обновлений: при каждом INSERT/UPDATE СУБД пересчитывает функцию. Сложные вычисления могут замедлить операции записи.

⚠️ Размер индекса: функциональный индекс может занимать больше места, чем обычный, особенно если функция возвращает длинные строки.

Важный нюанс для Oracle: функция должна быть детерминированной (всегда возвращать одинаковый результат для одинаковых входных данных). Недетерминированные функции нельзя индексировать.

🎁 Бонус

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

-- Пример создания функции

CREATE OR REPLACE FUNCTION my_func (a IN NUMBER, b IN NUMBER)

RETURN NUMBER DETERMINISTIC

BEGIN

RETURN a + b;

END;

У Дениса даже есть видос на эту тему.

Подведем итог

🔸Индексируйте результат функции, а не исходные данные

🔸Убедитесь, что функция в запросе точно совпадает с функцией в индексе