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.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.