Table of Contents

About

A list partitioning defined a fix set of partition key

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);