Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is a globally partitioned index better (faster) than a non-partitioned index?

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?

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.

Thanks!

like image 969
Kevin Babcock Avatar asked Aug 31 '09 17:08

Kevin Babcock


People also ask

Does partitioning improve performance Oracle?

Partitioning can also improve the performance of multi-table joins by using a technique known as partition-wise joins. Partition-wise joins can be applied when two tables are being joined and both tables are partitioned on the join key, or when a reference partitioned table is joined with its parent table.

What is the difference between global index and local index?

The docs says that a "global index can be partitioned by the range or hash method, and it can be defined on any type of partitioned, or non-partitioned, table". Local Index: A local index is a one-to-one mapping between a index partition and a table partition.

Can global index be partitioned?

A global index can be range or hash partitioned, though it can be defined on any type of partitioned table. A global index is created by specifying the GLOBAL attribute.

What is the difference between partitioning and indexing?

Indexes are used to speed the search of data within tables. Partitions provide segregation of the data at the hdfs level, creating sub-directories for each partition. Partitioning allows the number of files read and amount of data searched in a query to be limited.


1 Answers

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.

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.

like image 74
Svetlozar Angelov Avatar answered Oct 15 '22 07:10

Svetlozar Angelov