Database Reference
In-Depth Information
If we continue this experiment up to 16 partitions, we would see the same effects for the ninth through the
fifteenth partitions—a skewing of the data to the interior partitions, away from the edges—and then upon hitting the
sixteenth partition, you would see a flattening-out again. The same would be true again up to 32 partitions, and then 64,
and so on. This example just points out the importance of using a power of two as the number of hash partitions.
List Partitioning
List partitioning was a new feature of Oracle9
i
Release 1. It provides the ability to specify in which partition a row
will reside, based on discrete lists of values. It is often useful to be able to partition by some code, such as a state or
region code. For example, we might want to pull together in a single partition all records for people in the states of
Maine (ME), New Hampshire (NH), Vermont (VT), and Massachusetts (MA), since those states are located next to or
near each other and our application queries data by geographic region. Similarly, we might want to group together
Connecticut (CT), Rhode Island (RI), and New York (NY).
We can't use a range partition, since the range for the first partition would be ME through VT, and the second
range would be CT through RI. Those ranges overlap. We can't use hash partitioning since we can't control which
partition any given row goes into; the built-in hash function provided by Oracle does that.
With list partitioning, we can accomplish this custom partitioning scheme easily:
EODA@ORA12CR1> create table list_example
2 ( state_cd varchar2(2),
3 data varchar2(20)
4 )
5 partition by list(state_cd)
6 ( partition part_1 values ( 'ME', 'NH', 'VT', 'MA' ),
7 partition part_2 values ( 'CT', 'RI', 'NY' )
8 )
9 /
Table created.
Figure
13-3
shows that Oracle will inspect the
STATE_CD
column and, based on its value, place the row into the
correct partition.
