全局分区索引是否比非分区索引更好（更快）？ [英] Is a globally partitioned index better (faster) than a non-partitioned index?

问题描述

我很想知道分区通常是查询目标的数字列是否有性能优势。目前我有一个包含约5000万条记录的物化视图。当使用常规b树索引并按此数字列进行搜索时，我得到的成本为7，查询结果为0.8秒（使用非初始化缓存）。为该列添加全局散列分区（具有64个分区）后，我得到的成本为6，查询结果大约为0.2秒（同样使用非初始化缓存）。

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).

我的第一反应是分区索引提高了查询的性能。但是，我意识到这可能只是巧合，可能完全依赖于搜索的值，或者其他我不知道的值。所以我的问题是：将一个全局散列分区添加到大型表上的数字列是否有性能优势，或者是确定要扫描出哪些索引分区的成本 - 只需对一个全范围扫描的成本进行加权非索引分区？

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?

我确信这与许多Oracle问题一样，可以用取决于来回答。 :)我有兴趣了解我应该考虑哪些因素来确定每种方法的好处。

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.

谢谢！

推荐答案

我很确定你在研究中找到了这个参考 - 分区表和索引。但是如果有人感兴趣的话，我给它一个链接，这是一个关于分区的非常好的材料。

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.

直截了当 - 分区索引只是将索引分解成碎片（在你的情况下16）并根据他们的散列分区键传播数据。当您想要使用它时，Oracle会计算密钥的哈希并确定在哪个部分继续搜索。

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.

了解索引搜索的工作方式，真的很大数据我认为最好选择分区索引以减少遍历的索引树（常规索引）。它实际上取决于表中的数据（如何组成常规索引树），并且比起始节点的常规树遍历更快地进行散列和直接跳转到较低节点。

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.

这篇关于全局分区索引是否比非分区索引更好（更快）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！