Hash partitioning is designed to achieve a good spread of data across many different devices (disks) or just to segregate data out into more manageable chunks.
The hash key chosen for a table should be a column or set of columns that are unique or at least have as many distinct values as possible to provide for a good spread of the rows across partitions.
If you choose a column that has only four values, and you use two partitions, then all the rows could quite easily end up hashing to the same partition, obviating the goal of partitioning in the first place!
We will create a hash table with two partitions in this case. We will use a column named HASH_KEY_COLUMN as our partition key.
Oracle will take the value in this column and determine the partition this row will be stored in by hashing that value:
$ sqlplus eoda/foo@PDB1
SQL> CREATE TABLE hash_example( hash_key_column date,data varchar2(20))PARTITION BY HASH (hash_key_column)( partition part_1 tablespace p1,partition part_2 tablespace p2);Table created.
Figure 13-2 shows that Oracle will inspect the value in the HASH_KEY_COLUMN, hash it, and determine which of the two partitions a given row will appear in.
Figure 13-2. Hash partition insert example
As noted earlier, hash partitioning gives you no control over which partition a row ends up in. Oracle applies the hash function, and the outcome of that hash determines where the row goes. If you want a specific row to go into partition PART_1 for whatever reason, you should not—in fact, you cannot—use hash partitioning.
The row will go into whatever partition the hash function says to put it in. If you change the number of hash partitions, the data will be redistributed over all of the partitions (adding or removing a partition to a hash partitioned table will cause all of the data to be rewritten, as every row may now belong in a different partition).
Hash partitioning is most useful when you have a large table, such as the one shown in the “Reduced Administrative Burden” section, and you would like to divide and conquer it. Rather than manage one large table, you would like to have 8 or 16 smaller tables to manage.
Hash partitioning is also useful to increase availability to some degree, as demonstrated in the “Increased Availability” section; the temporary loss of a single hash partition permits access to all of the remaining partitions. Some users may be affected, but there is a good chance that many will not be.
Additionally, the unit of recovery is much smaller now. You do not have a single large table to restore and recover; you have a fraction of that table to recover. Lastly, hash partitioning is useful in high update contention environments, as mentioned in the “Reduced Contention in an OLTP System” section. Instead of having a single hot segment, we can hash partition a segment into 16 pieces, each of which is now receiving modifications.