Complex Partitioning in 9i

With Oracle 9i, there are now two ways to implement complex partitioning: with composite range-hash partitioning (exactly the same as shown in the previous section) and composite range-list partitioning (as shown below). Note that we had to add the TIME?ZONE column (denoted in bold) to the primary key to sub-partition by it. Oracle requires the sub-partition criteria to be part of the primary key or unique index for the table.

CREATE TABLE POS_DAY_RNG_LST
  PCTFREE 10
  PCTUSED 89
  PARALLEL (DEGREE 10)
  NOLOGGING
  PARTITION BY RANGE (period_id)
  SUBPARTITION BY LIST(time_zone)
  SUBPARTITION TEMPLATE
    (
      SUBPARTITION east     VALUES ('EST'),
      SUBPARTITION central  VALUES ('CST'),
      SUBPARTITION mountain VALUES ('MST'),
      SUBPARTITION west     VALUES ('PST')
    )
    (
       PARTITION p001 VALUES LESS THAN (1073),
       PARTITION p002 VALUES LESS THAN (1081),
       PARTITION p003 VALUES LESS THAN (1089),
       PARTITION p004 VALUES LESS THAN (1097),
       PARTITION p005 VALUES LESS THAN (1105),
       PARTITION p006 VALUES LESS THAN (1113),
       PARTITION p007 VALUES LESS THAN (1121),
       PARTITION p008 VALUES LESS THAN (1129),
       PARTITION p009 VALUES LESS THAN (1137),
       ...
    )
AS
 SELECT /*+ parallel(pos_day) full(pos_day) */ *
 FROM pos_day;

CREATE UNIQUE INDEX POS_DAY_RNG_LST_PK
  ON POS_DAY_RNG_LST (PERIOD_ID, LOCATION_ID,
                      PRODUCT_ID, TIME_ZONE)
  PCTFREE 1
  PARALLEL (DEGREE 10)
  NOLOGGING
  LOCAL;

CREATE BITMAP INDEX POS_DAY_RNG_LST_B1
  ON POS_DAY_RNG_LST (PERIOD_ID)
  PCTFREE 1
  PARALLEL (DEGREE 10)
  NOLOGGING
  LOCAL;

CREATE BITMAP INDEX POS_DAY_RNG_LST_B2
  ON POS_DAY_RNG_LST (LOCATION_ID)
  PCTFREE 1
  PARALLEL (DEGREE 10)
  NOLOGGING
  LOCAL;

CREATE BITMAP INDEX POS_DAY_RNG_LST_B3
  ON POS_DAY_RNG_LST (PRODUCT_ID)
  PCTFREE 1
  PARALLEL (DEGREE 10)
  NOLOGGING
  LOCAL;

Oracle states that composite range-list partitioning provides the manageability of range partitioning and the explicit control of list partitioning for sub-partitions.

Note that the space requirements for this partitioning method are slightly (or much, depending on your viewpoint) more complicated. Each partition and index partition create one segment per sub-partition. Let's assume we created just four partitions, p001 through p004; we'd thus create a grand total of 80 segments (listed below). Thus, complex partitioning via sub-partitions requires the DBA to carefully plan initial and next extent sizes because there are so many segments.

SEGMENT_NAME         PARTITION_NAME  SEGMENT_TYPE          BYTES
-------------------- --------------- ------------------ --------
POS_DAY_RNG_LST      P001_CENTRAL    TABLE SUBPARTITION   65,536
POS_DAY_RNG_LST      P001_EAST       TABLE SUBPARTITION   65,536
POS_DAY_RNG_LST      P001_MOUNTAIN   TABLE SUBPARTITION   65,536
POS_DAY_RNG_LST      P001_WEST       TABLE SUBPARTITION   65,536
POS_DAY_RNG_LST      P002_CENTRAL    TABLE SUBPARTITION   65,536
POS_DAY_RNG_LST      P002_EAST       TABLE SUBPARTITION   65,536
POS_DAY_RNG_LST      P002_MOUNTAIN   TABLE SUBPARTITION   65,536
POS_DAY_RNG_LST      P002_WEST       TABLE SUBPARTITION   65,536
POS_DAY_RNG_LST      P003_CENTRAL    TABLE SUBPARTITION   65,536
POS_DAY_RNG_LST      P003_EAST       TABLE SUBPARTITION   65,536
POS_DAY_RNG_LST      P003_MOUNTAIN   TABLE SUBPARTITION   65,536
POS_DAY_RNG_LST      P003_WEST       TABLE SUBPARTITION   65,536
POS_DAY_RNG_LST      P004_CENTRAL    TABLE SUBPARTITION   65,536
POS_DAY_RNG_LST      P004_EAST       TABLE SUBPARTITION   65,536
POS_DAY_RNG_LST      P004_MOUNTAIN   TABLE SUBPARTITION   65,536
POS_DAY_RNG_LST      P004_WEST       TABLE SUBPARTITION   65,536
POS_DAY_RNG_LST_B1   P001_CENTRAL    INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B1   P001_EAST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B1   P001_MOUNTAIN   INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B1   P001_WEST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B1   P002_CENTRAL    INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B1   P002_EAST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B1   P002_MOUNTAIN   INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B1   P002_WEST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B1   P003_CENTRAL    INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B1   P003_EAST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B1   P003_MOUNTAIN   INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B1   P003_WEST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B1   P004_CENTRAL    INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B1   P004_EAST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B1   P004_MOUNTAIN   INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B1   P004_WEST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B2   P001_CENTRAL    INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B2   P001_EAST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B2   P001_MOUNTAIN   INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B2   P001_WEST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B2   P002_CENTRAL    INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B2   P002_EAST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B2   P002_MOUNTAIN   INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B2   P002_WEST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B2   P003_CENTRAL    INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B2   P003_EAST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B2   P003_MOUNTAIN   INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B2   P003_WEST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B2   P004_CENTRAL    INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B2   P004_EAST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B2   P004_MOUNTAIN   INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B2   P004_WEST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B3   P001_CENTRAL    INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B3   P001_EAST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B3   P001_MOUNTAIN   INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B3   P001_WEST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B3   P002_CENTRAL    INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B3   P002_EAST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B3   P002_MOUNTAIN   INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B3   P002_WEST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B3   P003_CENTRAL    INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B3   P003_EAST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B3   P003_MOUNTAIN   INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B3   P003_WEST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B3   P004_CENTRAL    INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B3   P004_EAST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B3   P004_MOUNTAIN   INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_B3   P004_WEST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_PK   P001_CENTRAL    INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_PK   P001_EAST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_PK   P001_MOUNTAIN   INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_PK   P001_WEST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_PK   P002_CENTRAL    INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_PK   P002_EAST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_PK   P002_MOUNTAIN   INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_PK   P002_WEST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_PK   P003_CENTRAL    INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_PK   P003_EAST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_PK   P003_MOUNTAIN   INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_PK   P003_WEST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_PK   P004_CENTRAL    INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_PK   P004_EAST       INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_PK   P004_MOUNTAIN   INDEX SUBPARTITION   65,536
POS_DAY_RNG_LST_PK   P004_WEST       INDEX SUBPARTITION   65,536

80 rows selected.