Oracle9i New Feature Series: List Partitioning

Oracle9i introduces another type of partitioning in addition to the RANGE and HASH partitions of 8i. List partitioning lets you create partitions based on a list of values. For example, if you have a sales table, and you want to partition the table by the region, you might create the table as:

create table list_example (

state char(2),

amount number)

partition by list (state)

(

partition south values ('TX','LA','OK'),

partition  north values ('NY','DE','MA'),

partition  others values (DEFAULT)

)

/

If you omit the partition name, then Oracle assigns partition names of the form SYS_Pn.

The DEFAULT keyword, introduced in Oracle9i release 2, creates a partition into which Oracle will insert any row that does not map to another partition. Therefore, you can specify DEFAULT for only one partition, and you cannot specify any other values for that partition. Further, the default partition must be the last partition you define (similar to the use of MAXVALUE for range partitions).

The index-organized table cannot be list-partitioned.