Complex Partitioning in 8i

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

 CREATE TABLE POS_DAY_RNG_HSH   PCTFREE 10   PCTUSED 89   PARALLEL (DEGREE 10)   NOLOGGING   PARTITION BY RANGE (period_id)   SUBPARTITION BY HASH(product_id)   SUBPARTITION TEMPLATE     (        SUBPARTITION sp001,        SUBPARTITION sp002,        SUBPARTITION sp003,        SUBPARTITION sp004     )     (        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_HSH_PK   ON POS_DAY_RNG_HSH (PERIOD_ID, LOCATION_ID, PRODUCT_ID)   PCTFREE 1   PARALLEL (DEGREE 10)   NOLOGGING   LOCAL; CREATE BITMAP INDEX POS_DAY_RNG_HSH_B1   ON POS_DAY_RNG_HSH (PERIOD_ID)   PCTFREE 1   PARALLEL (DEGREE 10)   NOLOGGING   LOCAL; CREATE BITMAP INDEX POS_DAY_RNG_HSH_B2   ON POS_DAY_RNG_HSH (LOCATION_ID)   PCTFREE 1   PARALLEL (DEGREE 10)   NOLOGGING   LOCAL; CREATE BITMAP INDEX POS_DAY_RNG_HSH_B3   ON POS_DAY_RNG_HSH (PRODUCT_ID)   PCTFREE 1   PARALLEL (DEGREE 10)   NOLOGGING   LOCAL; 

Oracle states that composite range-hash partitioning provides the improved manageability of range partitioning and the data placement, striping, and parallelism advantages of hash partitioning.

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_HSH      P001_SP001      TABLE SUBPARTITION   65,536 POS_DAY_RNG_HSH      P001_SP002      TABLE SUBPARTITION   65,536 POS_DAY_RNG_HSH      P001_SP003      TABLE SUBPARTITION   65,536 POS_DAY_RNG_HSH      P001_SP004      TABLE SUBPARTITION   65,536 POS_DAY_RNG_HSH      P002_SP001      TABLE SUBPARTITION   65,536 POS_DAY_RNG_HSH      P002_SP002      TABLE SUBPARTITION   65,536 POS_DAY_RNG_HSH      P002_SP003      TABLE SUBPARTITION   65,536 POS_DAY_RNG_HSH      P002_SP004      TABLE SUBPARTITION   65,536 POS_DAY_RNG_HSH      P003_SP001      TABLE SUBPARTITION   65,536 POS_DAY_RNG_HSH      P003_SP002      TABLE SUBPARTITION   65,536 POS_DAY_RNG_HSH      P003_SP003      TABLE SUBPARTITION   65,536 POS_DAY_RNG_HSH      P003_SP004      TABLE SUBPARTITION   65,536 POS_DAY_RNG_HSH      P004_SP001      TABLE SUBPARTITION   65,536 POS_DAY_RNG_HSH      P004_SP002      TABLE SUBPARTITION   65,536 POS_DAY_RNG_HSH      P004_SP003      TABLE SUBPARTITION   65,536 POS_DAY_RNG_HSH      P004_SP004      TABLE SUBPARTITION   65,536 POS_DAY_RNG_HSH_B1   P001_SP001      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B1   P001_SP002      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B1   P001_SP003      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B1   P001_SP004      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B1   P002_SP001      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B1   P002_SP002      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B1   P002_SP003      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B1   P002_SP004      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B1   P003_SP001      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B1   P003_SP002      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B1   P003_SP003      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B1   P003_SP004      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B1   P004_SP001      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B1   P004_SP002      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B1   P004_SP003      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B1   P004_SP004      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B2   P001_SP001      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B2   P001_SP002      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B2   P001_SP003      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B2   P001_SP004      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B2   P002_SP001      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B2   P002_SP002      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B2   P002_SP003      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B2   P002_SP004      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B2   P003_SP001      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B2   P003_SP002      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B2   P003_SP003      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B2   P003_SP004      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B2   P004_SP001      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B2   P004_SP002      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B2   P004_SP003      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B2   P004_SP004      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B3   P001_SP001      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B3   P001_SP002      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B3   P001_SP003      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B3   P001_SP004      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B3   P002_SP001      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B3   P002_SP002      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B3   P002_SP003      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B3   P002_SP004      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B3   P003_SP001      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B3   P003_SP002      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B3   P003_SP003      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B3   P003_SP004      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B3   P004_SP001      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B3   P004_SP002      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B3   P004_SP003      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_B3   P004_SP004      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_PK   P001_SP001      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_PK   P001_SP002      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_PK   P001_SP003      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_PK   P001_SP004      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_PK   P002_SP001      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_PK   P002_SP002      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_PK   P002_SP003      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_PK   P002_SP004      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_PK   P003_SP001      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_PK   P003_SP002      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_PK   P003_SP003      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_PK   P003_SP004      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_PK   P004_SP001      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_PK   P004_SP002      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_PK   P004_SP003      INDEX SUBPARTITION   65,536 POS_DAY_RNG_HSH_PK   P004_SP004      INDEX SUBPARTITION   65,536 80 rows selected. 

Finally, some people try to implement complex partitioning under Oracle 8i utilizing multi-column range partitioning. But this really is nothing more than an overcomplicated, manual workaround to approximate complex partitioning. Yes, it's quite doable. But, you as the DBA must decide if it's really worthwhile.