Using List Partitioning List partitioning gives you explicit control of how each value in the
partitioning column maps to a partition by specifying discrete values from the partitioning column.
Range partitioning is usually not suitable for discrete values that do not have a natural and
consecutive range of values, such as state codes. Hash partitioning is not suitable for assigning
discrete values to a particular partition because, by its nature, a hash partition may map several
related discrete values into different partitions.
To use list partitioning, you must specify the following three criteria:
Partitioning method (list)
Partitioning column
Partition names, with each partition associated with a discrete list of literal values that
place it in the partition
NOTE
As of Oracle 10g, list partitioning can be used for tables with LOB
columns.
In the following example, you will use list partitioning to record sales information for the data
warehouse into three partitions based on sales region: the Midwest, the western seaboard, and the
rest of the country. Here is the create table command:
create table oe.sales_by_region_by_day
(state_cd char(2),
sales_dt date,
sales_amt number(16,2))
partition by list (state_cd)
(partition midwest values ('WI','IL','IA','IN','MN')
tablespace prd01,
partition westcoast values ('CA','OR','WA')
tablespace prd02,
partition other_states values (default)
tablespace prd03);
Sales information for Wisconsin, Illinois, and the other Midwestern states will be stored in
the midwest partition; California, Oregon, and Washington state will end up in the westcoast
partition.
Pages:
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840