Partitioning - Expert Oracle Database Architecture - page 590

Hash Partitioning
When hash partitioning a table, Oracle will apply a hash function to the partition key to determine in which of the
N partitions the data should be placed. Oracle recommends that N be a number that is a power of 2 (2, 4, 8, 16, and
so on) to achieve the best overall distribution, and we'll see shortly that this is absolutely good advice.
How Hash Partitioning Works
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:
EODA@ORA12CR1> CREATE TABLE hash_example
2 ( hash_key_column date,
3 data varchar2(20)
4 )
5 PARTITION BY HASH (hash_key_column)
6 ( partition part_1 tablespace p1,
7 partition part_2 tablespace p2
8 )
9 /
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.