The secret of Oracle hash partitioning

Transferred from: http://www.hellodb.net/2009/12/hash_partition.html

In the interview often ask a question, please list the hash in the database internal application, the principle of hash is simple, but it can be said in the database is everywhere. Where hash partition is a simple application of hash in the database, although it does not have a range partition so commonly used, but we do the database level split, in fact, is the use of the principle of hash partition, It is simple to use the hash function to operate on a key and distribute it to different hosts.

We encountered a problem in the design, when the number of partitions need to change, based on the principle of hash, the data may be moved from one partition to another partition, because a key in 4 partitions, may be distributed in Partition 3, and in 8 partitions, may be distributed in Partition 5. This will require the full redistribution of data whenever the number of partitions changes, at a high cost.

So how does Oracle do it? The first thing to be sure is that Oracle's hash partition does not need to be re-distributed when the partition is increased. I was told that Oracle's hash function compares cattle, which allows the hash function to keep the original data in the old partition when the number of partitions increases, and the new data can be distributed in the new partition. Oracle's functions are nothing more than Get_hash_value or Ora_hash (10g), which is impossible from the principle of hashing.

We have a common sense of hash partition, that is, the number of partition is the best 2 of the time, that is, 2,4,8,16 ..., otherwise the partition will appear non-zonal equalization phenomenon, according to the principle of the hash, whether it is a few partitions, can be fully balanced, Why not balanced, in fact, the answer has come out, Oracle in order to be able to increase the partition, reserved for you a few invisible partitions.

Let's say we have 6 partitions, a total of 8,000 data, the distribution of data such as:

Hash partition can not directly increase the partition, but split the current partition, when the need to increase to 8 partitions, is actually partition 3 and partition 4 respectively split to produce a new partition 7 and partition 8, such as:

How Oracle can increase the number of partitions, the data of other partitions is not affected, it is very simple, Oracle in the hash operation, reserved partitions, such as 6 partitions, in fact, with 8 partition of the hash to operate, but the missing partition data merge to other partitions, In this way, there is a case of unbalanced data. The Oracle formula is this, with a minimum of 2 n squares equal to or greater than the current number of partitions, such as 6 partitions to make 8 hash buckets. Let's consider the case of 2,4,8,16 (2 N-square), for example, to add 4 partitions to 5 partitions because they are already 2 N, so the data is evenly distributed, and Oracle uses 4 hash buckets. The new partition 5 actually generated after partition 1 split, because there are 5 partitions, so the use of 8 hash buckets. At this point, Oracle's hash function is compared to cattle, it can guarantee 2,4,8,16 partition, the same key value is distributed in the same partition or the corresponding partition can be merged, look at the following sql:

Select Ora_hash (' Hellodba ', 1) +1 par2,ora_hash (' Hellodba ', 3) +1 par4,ora_hash (' Hellodba ', 7) +1 Par8,ora_hash (' Hellodba ', +1 par16 from dual;

      PAR2       PAR4       PAR8      PAR16----------------------------------------         2          4          4         12

The above SQL we see the number of partitions in 2,4,8,16, HELLODBA this key falls in the 2,4,4,12 partition, although it falls on different partitions, but partition 4 and partition 12 is the corresponding can be merged, so that the data is not required to move. A summary is that hash bucket is always 2 of the N-square, if the number of partitions is insufficient, it will merge data, resulting in uneven situation, so that when the partition is increased, only the corresponding partition data to do split. Similarly, reducing partitions is not a simple drop, but merging partitions.

Back to our project, in order to solve this problem, we have adopted a more simple processing scheme, directly done 1024 partitions, we have 8 physical databases, each database has 128 tables, and later when the split, just move the tables, and modify the corresponding relationship in the application can be. In fact, the idea of merging and splitting Oracle is the same.

This question was actually told in Daniel Lewis's practical Oracle8i, and I did not think it over carefully, and now I think it is clear. Some things, understand that it is very simple, I hope to help everyone.

The secret of Oracle hash partitioning