使优化器使用索引的所有列 [英] make the optimizer use all columns of an index

问题描述

我们有一些存储时间数据的表，这些数据具有包含3列的自然主键.示例:当天的最高温度.这是组合主键索引(按此顺序):

we have a few tables storing temporal data that have natural a primary key consisting of 3 columns. Example: maximum temperature for this day. This is the Composite Primary key index (in this order):

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 am，新计算的值以kill_at时间戳存储为'31 .12. 2999'.

Simplified logic: When we make a forecast at 10:00am, then the last entry found for this id/day combination has his create_at changed to 9:59am and the newly calculated value is stored with a kill_at timestamp of '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/天的组合，有200多个条目具有不同的kill_at值.

There are plenty of timeseries that we do not forecast. That means we get one valued when it's measured and it never changes. But there are some timeseries that we forecast 200-300 times. So for one id/day combination there are 200+ entries with different values for kill_at.

我们目前只有主键(id，day，kill_at)作为此表上唯一(唯一)的索引.但是，当我使用查询2(确切的ID和日期范围)进行查询时，优化器决定只使用索引的第一列.

We currently only have the primary key (id, day, kill_at) as the only (unique) index on this table. But when I query with query 2 (exact id and day range), then the optimizer decides to only use the first column of the index.

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列的方法，但是我找不到关于它的提示.有一个吗?

This really hurts us for those timeseries that have been updates 200+ times. Now I was looking for a way to force the optimizer to use all 3 columns of our index, but I can't find a hint for that. Is there one?

或者关于如何加快查询速度还有其他建议吗?我们尝试减少高峰时间.平均持续时间受到的关注较少.

Or are there any other suggestions on how to speed up my query? We try to reduce the peak durations. The average Durations are of lesser concern.

让我感到困惑的是: 上面的执行计划是我在dba_hist_sql_plan中看到的.这是此语句的唯一执行计划.但是，当我让客户显示说明计划时，search_columns有时为1或3.但是，当我们的应用程序运行此Statement时，它永远不会为3.

what confuses me: The above execution plan is what I see in dba_hist_sql_plan. It is the only execution plan for this statement. But when I let my client show the explain plan, then it is sometimes a 1 or a 3 for search_columns. But it never is 3 for when our application runs this Statement.

推荐答案

我们实际上找到了此问题的原因.我们正在使用JPA/JDBC，而JDBC日期类型没有正确建模.尽管oracle日期类型具有第二精度，但有人(我现在讨厌他)在我们类型为java.sql.Timestamp的实体中设置了"day"属性(尽管只有一天没有时间). 这样做的结果是，Oracle需要先将表中的每个条目强制转换(使用一个函数)以使其成为时间戳记，然后才能与Timestamp查询参数进行比较.这样，索引将无法正确使用.

we actually found the cause of this problem. We're using JPA/JDBC and the JDBC date types weren't modeled correctly. While the oracle date type is with second precision, somebody (I now hate him) made the "day" attribute in our entity of type java.sql.Timestamp (although it is only day without time). The effect is that Oracle will need to cast (use a function on) each entry in the table to make it a Timestamp before it can compare with the Timestamp query parameter. That way the index cannot be used properly.

这篇关于使优化器使用索引的所有列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！