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.