Oracle Date索引很慢。没有它，查询速度要快300倍 [英] Oracle Date index is slow. Query is 300 times faster without it
问题描述
我有一个Oracle查询，运行时间为10分钟或更长时间：
I had an Oracle query as below that took 10 minutes or longer to run:
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:1301@company.com:5060;user=phone',1,8) || '%'
group by
r.range_text
order by
r.range_text
如果我将查询的日期部分更改为：
If I changed the date part of the query to:
(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秒内运行。我基于另一篇文章做了这个，以避免使用日期索引。虽然看起来很直观 - 索引使事情变得如此之慢。
It runs in 2 seconds. I did this based on another post to avoid using the date index. Seems counter intuitive though--the index slowing things down so much.
完成解释计划，新查询和更新查询之间似乎相同。唯一的区别是MERGE JOIN操作在旧查询中是16,269字节，在新查询中是1,218字节。实际上，旧查询中的基数也更高。而且我实际上没有在解释计划中看到旧的或新查询的INDEX操作，只是针对destinationnumber字段上的索引。
Ran the explain plan and it seems identical between the new and updated query. Only difference is the the MERGE JOIN operation is 16,269 bytes in the old query and 1,218 bytes in the new query. Actually cardinality is higher in the old query as well. And I actually don't see an "INDEX" operation on the old or new query in the explain plan, just for the index on the destinationnumber field.
那么为什么是索引如此放慢了查询速度？我可以对索引做些什么 - 不要认为使用+0是未来的最佳解决方案...
So why is the index slowing down the query so much? What can I do to the index--don't think using the "+0" is the best solution going forward...
查询两天的数据，禁止使用destinationnumber索引：
Querying for two days of data, suppressing use of destinationnumber index:
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天：
Querying for 2 days using destinationnumber index:
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
查询一个月，抑制destinationnumber索引 - 完全扫描：
Querying for one month, suppressing destinationnumber index--full scan:
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
推荐答案
似乎反直觉 - 索引减慢了很多东西。
Seems counter intuitive though--the index slowing things down so much.
只有在你不理解时才会有直觉索引如何工作。
Counter-intuitive only if you don't understand how indexes work.
索引适用于检索单个行。它们不适合检索大量记录。您没有费心提供任何指标，但您的查询似乎可能触及大量行。在这种情况下，全表扫描或其他基于set =的操作将更有效。
Indexes are good for retrieving individual rows. They are not suited to retrieving large numbers of records. You haven't bothered to provide any metrics but it seems likely your query is touching a large number of rows. In which case a full table scan or other set=based operation will be more much efficient.
调整日期范围查询是棘手，因为无论我们的统计数据是多么最新，数据库都很难知道两个边界之间有多少记录。 （当日期界限可能变化时调整甚至更棘手 - 一天与一个月或一年不同。）因此，我们经常需要通过使用我们对数据的了解来帮助优化器。
Tuning date range queries is tricky, because it's very hard for the database to know how many records lie between the two bounds, no matter how up-to-date our statistics are. (Even more tricky to tune when the date bounds can vary - one day is a different matter from one month or one year.) So often we need to help the optimizer by using our knowledge of our data.
不要认为使用+0是未来的最佳解决方案......
don't think using the "+0" is the best solution going forward...
为什么不呢？人们一直在使用该技术来避免在几十年内在特定查询中使用索引。
Why not? People have been using that technique to avoid using an index in a specific query for literally decades.
然而，还有更多现代化的解决方案。未记录的基数提示是一个：
However, there are more modern solutions. The undocumented cardinality hint is one:
select /*+ cardinality(big_table,10000) */
...应足以阻止优化器使用索引 - 只要您为 all收集了准确的统计信息查询中的表。
... should be enough to dissuade the optimizer from using an index - provided you have accurate statistics gathered for all the tables in the query.
或者你可以强制优化器用...进行全表扫描。
Alternatively you can force the optimizer to do a full table scan with ...
select /*+ full(big_table) */
无论如何，你什么都没有做索引来改变数据库的工作方式。你可以通过分区来加快速度，但我想如果你的组织已经购买了分区选项您已经在使用它了。
Anyway, there's nothing you can do to the index to change the way databases work. You could make things faster with partitioning, but I would guess if your organisation had bought the Partitioning option you'd be using it already.
这篇关于Oracle Date索引很慢。没有它，查询速度要快300倍的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
