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