PARTITIONS

Partitioning allows subdividing tables, indexes, and index-organized tables into smaller pieces.

Partitions enable you to manage and access these database objects at a finer level of granularity.

Syntax

partition_block ::= PARTITIONS '[' { list_partition | range_partition }... ']'
list_partition ::= CREATE LIST PARTITION ON table_name '[' column_name ']';
range_partition ::= CREATE RANGE PARTITION ON table_name '[' column_name ']'
                     INTERVALPERIOD '[' period_value ']'
                     [INTERVALVALUE '[' integer ']' ]
                    ;
period_value ::= “YEAR” | “QUARTER” | “MONTH” | “DAY” | “CUSTOM”;

Note:

• You must use INTERVALVALUE only with period_type DAY and CUSTOM. When used with CUSTOM, it signifies an interval in months.
• For range partitioning, column_name must be of datatype DATE or TIMESTAMP.
• A table can have only one partition.

Example

PARTITIONS
[
   // LIST 
   CREATE LIST PARTITION ON PRODUCT_SALES[PROD_CATEGORY];
   
   // RANGE 
   CREATE RANGE PARTITION ON PROMO_SALES[PROMO_DATE] INTERVALPERIOD["DAY"]
   INTERVALVALUE[15]; 
]