Oracle 日期索引很慢。没有它查询速度快 300 倍

我有一个 Oracle 查询，如下所示，运行时间为 10 分钟或更长时间：

  select
      r.range_text as duration_range,
      nvl(count(c.call_duration),0) as calls,
      nvl(SUM(call_duration),0) as total_duration
      from
      call_duration_ranges r
      left join
      big_table c
      on c.call_duration BETWEEN r.range_lbound AND r.range_ubound
 and c.aaep_src = 'MAIN_SOURCE'
 and c.calltimestamp_local  >= to_date('01-02-2014 00:00:00' ,'dd-MM-yyyy HH24:mi:ss')
 AND c.calltimestamp_local <=  to_date('28-02-2014 23:59:59','dd-MM-yyyy HH24:mi:ss')
 and          c.destinationnumber LIKE substr( 'abc:[email protected] /cdn-cgi/l/email-protection:5060;user=phone',1,8) || '%'    
 group by
      r.range_text
 order by
      r.range_text

如果我将查询的日期部分更改为：

 (c.calltimestamp_local+0)  >= to_date('01-02-2014 00:00:00' ,'dd-MM-yyyy HH24:mi:ss')
 (AND c.calltimestamp_local+0) <=  to_date('28-02-2014 23:59:59','dd-MM-yyyy HH24:mi:ss')

它在 2 秒内运行。我根据另一篇文章这样做是为了避免使用日期索引。不过，这似乎违反直觉——索引使速度减慢了很多。

运行解释计划，新查询和更新查询之间似乎相同。唯一的区别是旧查询中的 MERGE JOIN 操作为 16,269 字节，新查询中为 1,218 字节。实际上旧查询中的基数也更高。实际上，我在解释计划中没有看到对旧查询或新查询的“INDEX”操作，只是针对目的号码字段上的索引。

那么为什么索引会大大减慢查询速度呢？我能对索引做什么——不认为使用“+0”是未来最好的解决方案......

查询两天的数据，禁止使用destinationnumber索引：

0   SELECT STATEMENT            ALL_ROWS    329382  1218    14
1   SORT    GROUP BY            329382  1218    14
2   MERGE JOIN  OUTER           329381  1218    14
3   SORT    JOIN            4   308 14
4   TABLE ACCESS    FULL    CALL_DURATION_RANGES    ANALYZED    3   308 14
5   FILTER                      
6   SORT    JOIN            329377  65  1
7   TABLE ACCESS    BY GLOBAL INDEX ROWID   BIG_TABLE   ANALYZED    329376  65  1
8   INDEX   RANGE SCAN  IDX_CDR_CALLTIMESTAMP_LOCAL ANALYZED    1104        342104

使用destinationnumber索引查询2天：

0   SELECT STATEMENT            ALL_ROWS    11  1218    14
1   SORT    GROUP BY            11  1218    14
2   MERGE JOIN  OUTER           10  1218    14
3   SORT    JOIN            4   308 14
4   TABLE ACCESS    FULL    CALL_DURATION_RANGES    ANALYZED    3   308 14
5   FILTER                      
6   SORT    JOIN            6   65  1
7   TABLE ACCESS    BY GLOBAL INDEX ROWID   BIG_TABLE   ANALYZED    5   65  1
8   INDEX   RANGE SCAN  IDX_DESTINATIONNUMBER_PART  ANALYZED    4       4

查询1个月，抑制destinationnumber索引--全扫描：

0   SELECT STATEMENT            ALL_ROWS    824174  1218    14
1   SORT    GROUP BY            824174  1218    14
2   MERGE JOIN  OUTER           824173  1218    14
3   SORT    JOIN            4   308 14
4   TABLE ACCESS    FULL    CALL_DURATION_RANGES    ANALYZED    3   308 14
5   FILTER                      
6   SORT    JOIN            824169  65  1
7   PARTITION RANGE ALL         824168  65  1
8   TABLE ACCESS    FULL    BIG_TABLE   ANALYZED    824168  65  1

不过，这似乎违反直觉——索引使速度减慢了很多。

仅当您不了解索引如何工作时才违反直觉。

索引非常适合检索单独的行。它们不适合检索大量记录。您没有费心提供任何指标，但您的查询可能涉及大量行。在这种情况下，全表扫描或其他基于 set=的操作将更加有效。

调整日期范围查询很棘手，因为无论我们的统计数据多么最新，数据库都很难知道有多少记录位于两个界限之间。 （当日期范围可能变化时，调整就更加棘手 - 一天与一个月或一年是不同的事情。）因此，我们经常需要利用我们的数据知识来帮助优化器。

不要认为使用“+0”是未来最好的解决方案......

为什么不？几十年来，人们一直在使用这种技术来避免在特定查询中使用索引。

然而，还有更现代的解决方案。未记录的基数提示是：

 select /*+ cardinality(big_table,10000) */ 

...应该足以阻止优化器使用索引 - 前提是您收集了准确的统计信息all查询中的表。

或者，您可以强制优化器执行全表扫描...

 select /*+ full(big_table) */ 

无论如何，您无法对索引进行任何操作来改变数据库的工作方式。您可以通过分区使事情变得更快，但我猜您的组织是否购买了分区选项 http://docs.oracle.com/cd/E11882_01/license.112/e47877/options.htm#DBLIC152你已经在使用它了。