Question: How do I use the list
partition feature of Oracle partitioning? Are there
examples of using a list partition, and when should I use
Answer: List Partitioning
is used to list together unrelated data into partitions. In
plain English, list partitioning is a technique where you
specify a list of discrete values for the partitioning key
in the description for each partition.
Possible list partitions include a
number of states (in a list) that are partitioned into a
List partitioning was added as a partitioning method in
Oracle 9i, Release 1. List partitioning allows for
partitions to reflect real-world groupings (e.g.. business
units and territory regions). List partitioning differs from
range partition in that the groupings in list partitioning
are not side-by-side or in a logical range. List
partitioning gives the DBA the ability to group together
seemingly unrelated data into a specific partition.
PARTITION BY LIST
( PARTITION region_south VALUES ('FL,
PARTITION region_north VALUES ('NY', MA,
The PARTITION BY LIST line is where the partition key is
identified. In this example, the partition key is STATE_CODE.
Each partition is explicitly named, contains a specific
grouping of VALUES and is contained in its own tablespace.
Note that the last partition with the DEFAULT clause is the "catch all"
partition. This catch all partition should be queried
periodically to make sure that proper data is being entered.
You can easily modify a list partition by altering the