Simple Partitioning in 8i

With Oracle 8i, there is only one way to implement simple partitioning: with range partitioning (as shown below):

CREATE TABLE POS_DAY_RNG
  PCTFREE 10
  PCTUSED 89
  PARALLEL (DEGREE 10)
  NOLOGGING
  PARTITION BY RANGE (period_id)
    (
       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_PK
  ON POS_DAY_RNG (PERIOD_ID, LOCATION_ID, PRODUCT_ID)
  PCTFREE 1
  PARALLEL (DEGREE 10)
  NOLOGGING
  LOCAL;

CREATE BITMAP INDEX POS_DAY_RNG_B1
  ON POS_DAY_RNG (PERIOD_ID)
  PCTFREE 1
  PARALLEL (DEGREE 10)
  NOLOGGING
  LOCAL;

CREATE BITMAP INDEX POS_DAY_RNG_B2
  ON POS_DAY_RNG (LOCATION_ID)
  PCTFREE 1
  PARALLEL (DEGREE 10)
  NOLOGGING
  LOCAL;

CREATE BITMAP INDEX POS_DAY_RNG_B3
  ON POS_DAY_RNG (PRODUCT_ID)
  PCTFREE 1
  PARALLEL (DEGREE 10)
  NOLOGGING
  LOCAL;

Oracle defines range partitioning as a method that maps data to partitions based on ranges of partition key values that you establish for each partition. It is the most common type of partitioning and is often used with dates (see the Oracle 9i Concepts manual).

Note that the space requirements for this partitioning method are very straightforward and simple. Each partition and index partition creates one segment. Let's assume we created just four partitions, p001 through p004; we'd thus create a grand total of 20 segments (shown below):

SEGMENT_NAME         PARTITION_NAME  SEGMENT_TYPE          BYTES
-------------------- --------------- ------------------ --------
POS_DAY_LST          P001            TABLE PARTITION      65,536
POS_DAY_LST          P002            TABLE PARTITION      65,536
POS_DAY_LST          P003            TABLE PARTITION      65,536
POS_DAY_LST          P004            TABLE PARTITION      65,536
POS_DAY_LST_B1       P001            INDEX PARTITION      65,536
POS_DAY_LST_B1       P002            INDEX PARTITION      65,536
POS_DAY_LST_B1       P003            INDEX PARTITION      65,536
POS_DAY_LST_B1       P004            INDEX PARTITION      65,536
POS_DAY_LST_B2       P001            INDEX PARTITION      65,536
POS_DAY_LST_B2       P002            INDEX PARTITION      65,536
POS_DAY_LST_B2       P003            INDEX PARTITION      65,536
POS_DAY_LST_B2       P004            INDEX PARTITION      65,536
POS_DAY_LST_B3       P001            INDEX PARTITION      65,536
POS_DAY_LST_B3       P002            INDEX PARTITION      65,536
POS_DAY_LST_B3       P003            INDEX PARTITION      65,536
POS_DAY_LST_B3       P004            INDEX PARTITION      65,536
POS_DAY_LST_PK       P001            INDEX PARTITION      65,536
POS_DAY_LST_PK       P002            INDEX PARTITION      65,536
POS_DAY_LST_PK       P003            INDEX PARTITION      65,536
POS_DAY_LST_PK       P004            INDEX PARTITION      65,536

20 rows selected.