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. |