什么类型的索引最适合 Oracle 上的 DATE 类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/979945/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What type of index in best for DATE type on Oracle?
提问by stic
Basing on your experience with Oracle, what will be the best type and settings for index that you would set on a column of DATE type?
根据您使用 Oracle 的经验，您将在 DATE 类型的列上设置的最佳索引类型和设置是什么？
- I don't necessarily need to go for partitioned index.
- It is a logging kind of table.
- You don't really care about unique id as a primary key (in fact date is close enough to be uniques most of the time, but due to nature of it, never will be).
- 我不一定需要去分区索引。
- 它是一种日志记录表。
- 您并不真正关心作为主键的唯一 ID（实际上日期在大多数情况下都足够接近唯一，但由于它的性质，永远不会如此）。
Will it be fair to create a cluster index?
创建集群索引是否公平？
What I'm interested in is to optimize execution of queries like SELECT * FROM Log WHERE [Date] > '20-06-2009' ORDER BY [Date] DESC, not slowing down inserts massively. (btw. in real world I would use the correct TO_DATE syntax to avoid truncation and missing the index)
我感兴趣的是优化诸如 SELECT * FROM Log WHERE [Date] > '20-06-2009' ORDER BY [Date] DESC 之类的查询的执行，而不是大量减慢插入速度。（顺便说一句。在现实世界中，我会使用正确的 TO_DATE 语法来避免截断和丢失索引）
Cheers,
干杯，
回答by Khb
A regular index should do just fine. Since it is a log the new entries should always have an increasing date value, never dates in the past, which makes for easy index appending. Not a big slowdown for the inserts.
常规索引应该可以正常工作。由于它是日志，因此新条目应始终具有递增的日期值，而不是过去的日期，这使得索引附加变得容易。插入的速度没有大的放缓。
Only consider more complex indices if you experience problems with the above.
如果您遇到上述问题，请仅考虑更复杂的索引。
Regards K
问候 K
回答by David Aldridge
A regular b-tree index would be appropriate, but if this is a log table with increasing values on the date then look out for index block contention. If you have a lot of sessions inserting new values into the index and those values belong in the same block then you could hit a performance problem. One mitigation for this is a reverse key index, but that makes queries of the type you give more expensive because reverse key indexes cannot support range scans. You would get a full index scan or a fast full index scan instead.
常规的 b 树索引是合适的，但如果这是一个在日期上具有递增值的日志表，那么请注意索引块争用。如果您有很多会话将新值插入索引，并且这些值属于同一块，那么您可能会遇到性能问题。对此的一种缓解措施是反向键索引，但这会使您提供的类型的查询更加昂贵，因为反向键索引不能支持范围扫描。您将获得完整索引扫描或快速完整索引扫描。
It would also make the index larger because the index block splits would be 50/50, instead of the 90/10 that Oracle uses when it detects a rightward growth pattern in the indexed values.
它还会使索引变大，因为索引块拆分为 50/50，而不是 Oracle 在检测到索引值中的向右增长模式时使用的 90/10。
回答by Jeffrey Kemp
I would reconsider partitioning, depending on the volume of data - Oracle can use partition pruning when running the query - which carries the benefit of also being able to easily archive old log data later on.
我会重新考虑分区，这取决于数据量——Oracle 可以在运行查询时使用分区修剪——这带来了以后还可以轻松归档旧日志数据的好处。
