List Partitioning

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.

Search Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to The scripts, tips and articles appearing on cannot be reproduced elsewhere without the prior permission from the webmaster.