Частичный индекс в «Оракле»

В «Оракле», в отличие от «Постгреса», частичных индексов в чистом виде нет, но есть возможность их сэмулировать — достаточно построить функциональный индекс и возвращать NULL на значениях, которые нам в индексе не нужны. Например:

CREATE INDEX resolution_notice_d_o_r ON resolution_notice(
    CASE WHEN is_send_paper = 1 THEN org_id END,
    TRUNC(rdate)
)

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

Чтобы «Оракл» узнал искомое в индексе, сравнение в запросе должно выглядеть так же. В общем, всё как в случае любого функционального индекса (лишнее я убрал):

SELECT …
     FROM …
INNER JOIN resolution_notice rn ON …
    WHERE dn.n = 0
      AND CASE WHEN is_send_paper = 1 THEN dn.org_id END = :org_id
     AND TRUNC(rn.rdate) BETWEEN :start_date AND :end_date
GROUP BY …
ORDER BY rdate DESC, …

Мне стало интересно — если попробовать сунуть условие индекса в сам индекс, догадается ли «Оракл» дальше не дофильтровывать по нему? Ну, то есть, сделать что-то такое:

CREATE INDEX resolution_notice_d_o_r ON resolution_notice(
     org_id,
     TRUNC(rdate),
     CASE WHEN is_send_paper = 1 THEN 1 END
)

А в запрос добавить:

… CASE WHEN is_send_paper = 1 THEN 1 END = 1…

Оказалось, «Оракл» не догадывается (у нас он 11-й, может 12-й тут сможет оптимизацию сделать):

Условие (34.98КиБ)

Несмотря на то, что в выборке по индексу значений с is_send_paper других значений, кроме единицы нет, «Оракл» всё равно дофильтровывает по этому значению.

программированиеoracle

Комментарии 11

Горбунов Олег № 1

Тут есть мнение, что это как раз таки и есть оптимизация: оракл может решить сначала выбрать блоки данных по предикатам доступа, а потом уже отфильтровать их пропусками через фильтр.
http://stackoverflow.com/questions/1464469 … ution-plan

I wouldn't worry about the filter predicate that appears to be redundant — it seems to be a quirk of explain plan, probably something to do with the fact that it has to do a sort of skip-scan on the index
Евгений Степанищев (bolknote.ru) автор № 3
Ну это же странно — ему всё равно идти по всему индексу, так зачем что-то ещё фильтровать? Толку ноль. Если это оптимизация, она не сработала в этом месте.
Горбунов Олег № 5
Комментарий для Евгения Степанищева:

Эм, я как понимаю, фильтры могут применяться и к поиску по индексу тоже, они не обязательно предполагают, что это фильтр по вытянутым данным, т.е. это могут быть просто 2 прохода по индексам.
Евгений Степанищев (bolknote.ru) автор № 6
Комментарий для Горбунов Олег:

А какой тут смысл два раза идти по индексу, тем более, что в первый раз условие is_send_paper уже было использовано?
Кирилл № 7
Комментарий для Евгения Степанищева:

CREATE INDEX resolution_notice_d_o_r ON resolution_notice(
CASE WHEN is_send_paper = 1 THEN org_id END
TRUNC(rdate)
)

После END запятую забыли.
Vladimir Sitnikov № 9

Комментарий для Евгения Степанищева:

CASE WHEN is_send_paper = 1 THEN org_id END,
TRUNC(rdate)

«возвращать NULL на значениях, которые нам в индексе не нужны» нужно для всех колонок.
Если хоть в одной колонке есть non-null значение, то запись в индекс попадает.

В конкретном случае, это нужно было указывать
CASE WHEN is_send_paper = 1 THEN org_id END,
CASE WHEN is_send_paper = 1 THEN TRUNC(rdate) END

ему всё равно идти по всему индексу, так зачем что-то ещё фильтровать?

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

Евгений Степанищев (bolknote.ru) автор № 10
«возвращать NULL на значениях, которые нам в индексе не нужны» нужно для всех колонок.
Если хоть в одной колонке есть non-null значение, то запись в индекс попадает.

А можно ссылку на какой-то источник?