Блог

у нас есть несколько таблиц, хранящих временные данные, которые имеют естественный первичный ключ, состоящий из 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 / day, изменяет его 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 раз. Таким образом, для одной комбинации id / day существует более 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. Это единственный план выполнения для этого оператора. Но когда я разрешаю своему клиенту показывать план объяснения, то иногда это 1 или 3 для search_columns. Но оно никогда не равно 3, когда наше приложение запускает эту инструкцию.

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