Oracle Partition - Range/List (Partition by Range, Sub-partition by List)

About

Range/List is a range composite scheme

Partition by Range,
Sub-partition by List

Articles Related

Syntax

For the range syntax, see range composite syntax.

The subpartition list syntax is: subpartition_by_list

Example

Basis

CREATE TABLE T_CNT_AGG_KPI_WEEK_TEST
  (
    WEEK_CODE         VARCHAR2(7 CHAR),
    CATEGORIE_CODE    VARCHAR2(7 CHAR)
  )
  PARTITION BY RANGE (WEEK_CODE) SUBPARTITION BY LIST (CATEGORIE_CODE)
  (
    PARTITION P_WEEK1 VALUES LESS THAN ('2013.27')
      (
      SUBPARTITION P_WEEK1_KPI VALUES ('CAT1'),
      SUBPARTITION P_WEEK2_KPI VALUES ('CAT2')
      ),
    PARTITION P_WEEK2 VALUES LESS THAN ('2013.28'),
    PARTITION P_WEEK3 VALUES LESS THAN ('2013.29'),
    PARTITION P_MAX VALUES less than (maxvalue)
   );

The values of the predicate must be between parentheses

Subpartition template

PARTITION BY RANGE ("D_TIME") 
  SUBPARTITION BY LIST ("D_COUNTRY") 
  SUBPARTITION TEMPLATE ( 
    SUBPARTITION "FR" VALUES ( 33 ), 
    SUBPARTITION "ZZ" VALUES ( DEFAULT ) ) 
 (PARTITION p0  VALUES LESS THAN (1000) , 
 PARTITION p1  VALUES LESS THAN (2000) 
 PARTITION p2  VALUES LESS THAN (MAXVALUE)