List Partitioning

List partitioning organizes rows in the various assigned tablespaces based on a list of literals. Listing 15.5 illustrates how list partitioning might be implemented across four partitions named East, South, West, and North.

Listing 15.5 illustrates the CREATE TABLE syntax that creates the ST_SALES table based on a list of characters, in no particular order, and so on. If a value is submitted that does not meet the criteria of any of the partitions, an error will be returned to the user or the application. There is no concept of MAXVALUE in list partitioning.

Listing 15.5. Sales Tracking ST_SALES List Partition Table Example

CREATE TABLE st_sales (sales_customer_id NUMBER(6), sales_sale_amt NUMBER(9,2), sales_state VARCHAR(2)) PARTITION BY LIST(sales_state) (PARTITION st_east VALUES ('ME','VT','NH','MA','NY','PA') TABLESPACE ts_st_east, PARTITION st_south VALUES ('FL','TX','SC','NC','TN','KY') TABLESPACE ts_st_south, PARTITION st_west VALUES ('CA','UT','MT','OR','WA','AK') TABLESPACE ts_st_west, PARTITION st_north VALUES ('IA','MN','IL','MO','NE') TABLESPACE ts_st_north ); ok pm

Oracle9i does not support list partitioning for more than one column. The listed values must be unique, NULL can be specified, each partition must have at least one literal, and local/global indexes are supported (index partitioning is covered next).