Oracle Partition - List partitioning (finite domain)

About

A list partitioning defined a fix set of partition key

Articles Related

Example

For example, a column holding names of U.S. states contains a finite and small number of values.

This type of column calls for list partitioning, in which the partitions hold discrete values instead of ranges.

Syntax

Partitioning by 'CT', 'CA' and otherwise default

create table sales (
	product_id	number,
	trans_amt	number,
	sales_dt	date,
	state_code	varchar2(2)
)
partition by list (state_code)
(
partition ct    values ('CT'),
partition ca    values ('CA'),
partition def   values (default)
);

Management

Oracle Partition - Alter Table

Add Partition

With a default subpartition

ALTER TABLE myTableName ADD PARTITION myPartitionName VALUES ('myValue');

Add Partition Add Sub-partition

ALTER TABLE myTableName
   ADD PARTITION myPartitionName VALUES ('myValue') 
   (SUBPARTITION mySubPartitionName VALUES (myValueList));

Modify Partition Add SubPartition

List Subpartition

ALTER TABLE cust 
   MODIFY PARTITION P_List
      ADD SUBPARTITION P_List_Sub2  VALUES ('MySubValues');

Add default Subpartition

ALTER TABLE myTable
   MODIFY PARTITION myPartition
      ADD SUBPARTITION VALUES (DEFAULT);