oracle 全局分区索引是否比非分区索引更好（更快）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1358490/
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
Is a globally partitioned index better (faster) than a non-partitioned index?
提问by Kevin Babcock
I'm interested to find out if there is a performance benefit to partitioning a numeric column that is often the target of a query. Currently I have a materialized view that contains ~50 million records. When using a regular b-tree index and searching by this numeric column I get a cost of 7 and query results in about 0.8 seconds (with non-primed cache). After adding a global hash partition (with 64 partitions) for that column I get a cost of 6 and query results in about 0.2 seconds (again with non-primed cache).
我很想知道对通常作为查询目标的数字列进行分区是否有性能优势。目前我有一个包含约 5000 万条记录的物化视图。当使用常规 b 树索引并按此数字列搜索时，我得到的成本为 7，查询结果大约需要 0.8 秒（使用非启动缓存）。在为该列添加全局散列分区（具有 64 个分区）后，我得到 6 的成本，查询结果在大约 0.2 秒内（再次使用非启动缓存）。
My first reaction is that the partitioned index has improved the performance of my query. However, I realize that this may just be a coincidence and could be totally dependent on the values being searched on, or others I'm not aware of. So my question is: is there a performance benefit to adding a global hash partition to a numeric column on a large table or is the cost of determining which index partitions to scan out-weighed by the cost of just doing a full range scan on a non-indexed partition?
我的第一反应是分区索引提高了我的查询性能。但是，我意识到这可能只是巧合，可能完全取决于正在搜索的值或我不知道的其他值。所以我的问题是：将全局散列分区添加到大表上的数字列是否有性能优势，或者确定要扫描哪些索引分区的成本是否超过了仅对数据进行全范围扫描的成本非索引分区？
I'm sure this, like many Oracle questions, can be answered with an "it depends." :) I'm interested in learning what factors I should consider to determine the benefits of each approach.
我敢肯定，这与许多 Oracle 问题一样，可以用“视情况而定”来回答。:) 我有兴趣了解我应该考虑哪些因素来确定每种方法的好处。
Thanks!
谢谢！
采纳答案by Svetlozar Angelov
I'm pretty sure you have found this reference in your research - Partitioned Tables and Indexes. However I give a link to it if somebody is interested, this is a very good material about partitioning.
我很确定你在你的研究中找到了这个参考 - Partitioned Tables and Indexes。但是，如果有人感兴趣，我会提供一个链接，这是关于分区的非常好的材料。
Straight to the point - Partitioned index just decomposes the index into pieces (16 in your situation) and spread the data depending on their hashed partitioning key. When you want to use it, Oracle "calculates" the hash of the key and determine in which section to continue with searching.
直截了当 - 分区索引只是将索引分解为多个部分（在您的情况下为 16 个）并根据散列分区键分布数据。当您想使用它时，Oracle 会“计算”键的散列值并确定在哪个部分继续搜索。
Knowing how index searching works, on really huge data I think it is better to choose the partitioned index in order to decrease the index tree you traverse (regular index). It really depends on the data, which is in the table (how regular index tree is composed) and is hashing and direct jump to lower node faster than regular tree traverse from the start node.
知道索引搜索是如何工作的，对于非常大的数据，我认为最好选择分区索引以减少您遍历的索引树（常规索引）。它实际上取决于表中的数据（常规索引树是如何组成的），并且比从起始节点遍历常规树更快地进行散列和直接跳转到较低节点。
Finally, you must be more confident with the test results. If one technique gives better results on your exact data than some other don't worry to implement it.
最后，您必须对测试结果更有信心。如果一种技术在您的确切数据上提供了比其他技术更好的结果，请不要担心实施它。
