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.