Simple Partitioning in 9i


With Oracle 9i, there are two ways to implement simple partitioning: with range partitioning (exactly the same as shown in the prior section) or with list partitioning (as shown below):

 CREATE TABLE POS_DAY_LST   PCTFREE 10   PCTUSED 89   PARALLEL (DEGREE 10)   NOLOGGING   PARTITION BY LIST (period_id)     (        PARTITION p001 VALUES         (1065,1066,1067,1068,1069,1070,1071,1072),        PARTITION p002 VALUES         (1073,1074,1075,1076,1077,1078,1079,1080),        PARTITION p003 VALUES         (1081,1082,1083,1084,1085,1086,1087,1088),        PARTITION p004 VALUES         (1089,1090,1091,1092,1093,1094,1095,1096),        PARTITION p005 VALUES         (1097,1098,1099,1100,1101,1102,1103,1104),        PARTITION p006 VALUES         (1105,1106,1107,1108,1109,1110,1111,1112),        PARTITION p007 VALUES         (1113,1114,1115,1116,1117,1118,1119,1120),        PARTITION p008 VALUES         (1121,1122,1123,1124,1125,1126,1127,1128),        PARTITION p009 VALUES         (1129,1130,1131,1132,1133,1134,1135,1136),        ...     ) AS  SELECT /*+ parallel(pos_day) full(pos_day) */ *  FROM pos_day; CREATE UNIQUE INDEX POS_DAY_LST_PK   ON POS_DAY_LST (PERIOD_ID, LOCATION_ID, PRODUCT_ID)   PCTFREE 1   PARALLEL (DEGREE 10)   NOLOGGING   LOCAL; CREATE BITMAP INDEX POS_DAY_LST_B1   ON POS_DAY_LST (PERIOD_ID)   PCTFREE 1   PARALLEL (DEGREE 10)   NOLOGGING   LOCAL; CREATE BITMAP INDEX POS_DAY_LST_B2   ON POS_DAY_LST (LOCATION_ID)   PCTFREE 1   PARALLEL (DEGREE 10)   NOLOGGING   LOCAL; CREATE BITMAP INDEX POS_DAY_LST_B3   ON POS_DAY_LST (PRODUCT_ID)   PCTFREE 1   PARALLEL (DEGREE 10)   NOLOGGING   LOCAL; 

Oracle defines list partitioning as a method that enables you to explicitly control how rows map to partitions. You do this by specifying a list of discrete values for the partitioning key in the description of each partition. The advantage of list partitioning is that you can group and organize unordered and unrelated sets of data in a natural way (see the Oracle 9i Concepts manual).

Note that the space requirements for this partitioning method are also very straightforward and simple. Again, each partition and index partition create 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. 


Oracle DBA Guide to Data Warehousing and Star Schemas
Oracle DBA Guide to Data Warehousing and Star Schemas
ISBN: 0130325848
EAN: 2147483647
Year: 2003
Pages: 79
Authors: Bert Scalzo

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net