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.