Oracle Partition - List partitioning (finite domain)

Card Puncher Data Processing

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





Discover More
Card Puncher Data Processing
Oracle Partition - Hash partitioning

Sometimes it may not be possible to define the ranges of—or a set of known values for—each partition. To create the CUST example table with four hash partitions: A typical example is a table,...
Card Puncher Data Processing
Oracle Partition - Partitioning Scheme

Oracle Database provides many types of partitioning options, including: Range: , , ,
Oracle Subpartition By List
Oracle Partition - Range/List (Partition by Range, Sub-partition by List)

Range/List is a range composite scheme Partition by Range, Sub-partition by List For the range syntax, see range composite syntax. The subpartition list syntax is: subpartition_by_list...
Oracle Split Table Partition Syntax
Oracle Partition - Split

How to split actual: range partition range interval partition list partition in two. where: AT Clause: The AT clause applies only to range partitions. VALUES Clause: The VALUES clause...



Share this page:
Follow us:
Task Runner