Oracle Date索引速度慢.没有它，查询速度要快300倍

Question

我有一个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:[e-mail]: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秒内运行。我这样做是基于另一篇文章，以避免使用日期索引。不过，这似乎有违直觉——指数让事情变得如此缓慢。

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

那么为什么索引会使查询速度变慢这么多呢?我能对索引做些什么——不要认为使用"+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

查询一个月，抑制目的地编号索引——完整扫描:

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

Answer 1

似乎有悖直觉——指数使事情变得如此缓慢。

只有当你不理解索引的工作原理时才会违反直觉。

索引适合检索单个行。它们不适合检索大量记录。你没有费心提供任何指标，但似乎你的查询是触摸大量的行。在这种情况下，全表扫描或其他基于set=的操作将更有效。

---

调优日期范围查询很棘手，因为数据库很难知道有多少记录位于两个边界之间，无论我们的统计数据是多么最新。(更棘手的是，当日期界限可以改变时——一天与一个月或一年是不同的。)因此，我们经常需要利用我们对数据的了解来帮助优化器。

我不认为使用"+0"是最好的解决方案…

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

然而，有更现代的解决方案。未记录的基数提示为1:

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

…应该足以阻止优化器使用索引——前提是您已经为查询中的所有表收集了准确的统计信息。

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

 select /*+ full(big_table) */ 

无论如何，你不能对索引做什么来改变数据库的工作方式。您可以通过分区使事情更快，但我猜如果您的组织已经购买了分区选项，您已经在使用它了。

Answer 2

以下是使用索引降低查询速度的原因:

1. 满表会更快。如果需要检索很大一部分行，就会发生这种情况。具体的数字取决于各种因素，但是作为经验法则，在通常情况下，如果检索超过10-20%的行，使用索引会变慢。

2. 使用另一个索引会更好，因为在第一阶段之后剩下的行更少。在表上使用某个索引通常意味着不能使用其他索引。

现在是优化器的工作来决定哪个变体是最好的。要执行此任务，他必须猜测(除其他事项外)在应用某些过滤子句后还剩下多少行。这个估计是基于表的统计数据，通常是相当不错的。它甚至考虑到倾斜的数据，但如果你的统计数据已经过时，或者你有一个相当不寻常的数据分布，它可能会失效。例如，如果在示例中插入二月的数据之前计算了统计数据，那么优化器可能会错误地得出结论，认为在应用日期范围筛选器之后只剩下几行(如果有的话)。

在多个列上使用组合索引也可能是一个选项，具体取决于您的数据。

关于"倾斜数据问题"的另一个注意事项:在某些情况下，如果在列A上有一个索引，优化器会检测到列A中的倾斜数据，但如果在列A和列B上只有一个组合索引，则不会，因为组合可能会使分布更均匀。这是A、B上的索引不会使A上的索引冗余的少数情况之一。

apc的回答展示了如何使用提示来指导优化器在正确的方向上，如果它仍然产生错误的计划，即使有正确的统计。