Секционировать таблицу Oracle одновременно по полям с датой и годом
Имеется матвью (умышленно денормализованное для аналитики) с большим количеством строк (оказанные услуги). У каждой строки есть поле service_date (дата услуги) и вычисляемое от неё поле service_year =extract(year from service_date). Множество пользовательских запросов используют именно года и для выборки и для группировок, поэтому оно сохранено и индексировано для повышения производительности. Но за 10+ лет таблица выросла кратно и ощущаются заметные тормоза даже с индексами.
Логичный шаг - пора секционировать её. Для начала всего на две части - "горячую" скажем 5 последних лет и "историческую", к которой обращаются реже. Но если делать только по дате, то запросы по году смотрят во все партиции. Почитал что можно и по нескольким полям секционировать, сделал так:
PARTITION BY RANGE (SERVICE_DATE,SERVICE_YEAR)
( PARTITION HIST VALUES LESS THAN (TO_DATE('01.01.2020','DD.MM.YYYY'),2020),
PARTITION LAST VALUES LESS THAN (MAXVALUE,MAXVALUE)
)
В результате если условие по дате - все работает как ожидалось, PARTITION RANGE SINGLE в плане запроса. А вот по году - не работает, PARTITION RANGE ALL
В какую сторону копать, чтобы запрос смотрел в нужную партицию при условиях как по дате так и по годам? Есть мысль сделать subpartition по годам, но есть большие сомнения что это правильный путь. А если бы три поля было?
P.S. Решение: сделал глобальный индекс по году, секционированный аналогично (до 2020 и после). Результат вроде как раз то что нужно.
select * from service where y >= 2023
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4003577 | 1361216180 | 139905 | 00:00:06 |
| 1 | PARTITION RANGE SINGLE | | 4003577 | 1361216180 | 139905 | 00:00:06 |
| 2 | MAT_VIEW ACCESS BY GLOBAL INDEX ROWID BATCHED | SERVICE | 4003577 | 1361216180 | 139905 | 00:00:06 |
| * 3 | INDEX RANGE SCAN | SERVICE_Y_IDX | 4003577 | | 9554 | 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------