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.