Частичный индекс в «Оракле»
В «Оракле», в отличие от «Постгреса», частичных индексов в чистом виде нет, но есть возможность их сэмулировать — достаточно построить функциональный индекс и возвращать 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-й тут сможет оптимизацию сделать):
Несмотря на то, что в выборке по индексу значений с is_send_paper других значений, кроме единицы нет, «Оракл» всё равно дофильтровывает по этому значению.
Тут есть мнение, что это как раз таки и есть оптимизация: оракл может решить сначала выбрать блоки данных по предикатам доступа, а потом уже отфильтровать их пропусками через фильтр.
http://stackoverflow.com/questions/1464469/access-and-filter-predicates-in-oracle-execution-plan
Комментарий для Горбунов Олег:
Ну это же странно — ему всё равно идти по всему индексу, так зачем что-то ещё фильтровать? Толку ноль. Если это оптимизация, она не сработала в этом месте.
Комментарий для grammar:
Спасибо, поправил в заметке.
Комментарий для Евгения Степанищева:
Эм, я как понимаю, фильтры могут применяться и к поиску по индексу тоже, они не обязательно предполагают, что это фильтр по вытянутым данным, т. е. это могут быть просто 2 прохода по индексам.
Комментарий для Горбунов Олег:
А какой тут смысл два раза идти по индексу, тем более, что в первый раз условие is_send_paper уже было использовано?
Комментарий для Евгения Степанищева:
CREATE INDEX resolution_notice_d_o_r ON resolution_notice(
CASE WHEN is_send_paper = 1 THEN org_id END
TRUNC(rdate)
)
После END запятую забыли.
Комментарий для Кирилл:
Спасибо! После редактирования пропала, сейчас поправлю.
Комментарий для Евгения Степанищева:
«возвращать NULL на значениях, которые нам в индексе не нужны» нужно для всех колонок.
Если хоть в одной колонке есть non-null значение, то запись в индекс попадает.
В конкретном случае, это нужно было указывать
CASE WHEN is_send_paper = 1 THEN org_id END,
CASE WHEN is_send_paper = 1 THEN TRUNC(rdate) END
Запросто может оказаться ошибкой отображения плана. Как-никак, а план выполнения это лишь фикция.
Вот если ты perf’ом или ещё как-то доказал, что при выполнении запроса реально происходит фильтрация, тогда другое дело.
Комментарий для Vladimir Sitnikov:
А можно ссылку на какой-то источник?
Комментарий для Vladimir Sitnikov:
Спасибо, нашёл: http://my-oracle.it-blogs.com.ua/post-486.aspx
Буду знать.