Заставить оптимизатор использовать все столбцы индекса

Question

У нас есть несколько таблиц, хранящих временные данные, которые имеют естественный первичный ключ, состоящий из 3 столбцов. Пример: максимальная температура за этот день. Это индекс составного первичного ключа (в следующем порядке):

id number(10): the id of the timeserie.
day date: the day for which this data was reported
kill_at timestamp: the last timestamp before this data was deleted or updated.

Упрощенная логика: когда мы делаем прогноз в 10:00 утра, то для последней записи, найденной для этой комбинации id/ день, его create_at изменяется на 9:59, а вновь вычисленное значение сохраняется с отметкой времени kill_at '31.12.2999'.

Типичные запросы в этой таблице:

1) where id=? and day=? and kill_at=?
2) where id=? and day between (? and ?) and kill_at=?
3) where id=? and day between (? and ?)
4) where id=?

Есть множество временных рядов, которые мы не прогнозируем. Это означает, что мы получаем одно значение, когда оно измеряется, и оно никогда не меняется. Но есть несколько временных рядов, которые мы прогнозируем 200-300 раз. Таким образом, для одной комбинации идентификатор / день существует более 200 записей с разными значениями для kill_at.

В настоящее время у нас есть только первичный ключ (id, day, kill_at) в качестве единственного (уникального) индекса этой таблицы. Но когда я запрашиваю запрос 2 (точный идентификатор и диапазон дней), оптимизатор решает использовать только первый столбец индекса.

ID  OPERATION         OPTIONS          OBJECT_NAME  OPTIMIZER  SEARCH_COLUMNS
 0  SELECT STATEMENT                                ALL_ROWS   0
 1  FILTER                                                     0
 2  TABLE ACCESS      BY INDEX ROWID   DPD                     0
 3  INDEX             RANGE SCAN       DPD_PK                  1

Это очень больно для тех временных рядов, которые обновлялись более 200 раз. Теперь я искал способ заставить оптимизатор использовать все 3 столбца нашего индекса, но я не могу найти подсказки для этого. Есть один?

Или есть еще какие-нибудь предложения о том, как ускорить мой запрос? Мы стараемся сократить продолжительность пиков. Средняя продолжительность менее важна.

что меня смущает: приведенный выше план выполнения - это то, что я вижу в dba_hist_sql_plan. Это единственный план выполнения этого заявления. Но когда я позволяю своему клиенту показывать план объяснения, тогда иногда для столбца search_columns указывается 1 или 3. Но это никогда не 3, когда наше приложение выполняет это заявление.

Answer 1

Мы на самом деле нашли причину этой проблемы. Мы используем JPA/JDBC, и типы дат JDBC не были смоделированы правильно. В то время как тип даты оракула имеет вторую точность, кто-то (я теперь его ненавижу) сделал атрибут "день" в нашей сущности типа java.sql.Timestamp (хотя это только день без времени). В результате Oracle необходимо будет привести (использовать функцию) каждую запись в таблице, чтобы сделать ее меткой времени, прежде чем ее можно будет сравнивать с параметром запроса метки времени. Таким образом, индекс не может быть использован должным образом.