In Oracle9i, Oracle supports multiple type partitions: range partition, hash partition, list partition, and mixed partitions
To create a range partition, the range value for the partition must be specified as part of the CREATE TABLE.
CREATE TABLE EMP
Empno Number (10) Primary Key,
Name varchar2 (40),
Deptno Number (2),
SALARY NUMBER (7, 2),
Birth_date date,
SOC_SEC_NUM VARCHAR2 (9),
State_code char (2),
ConsTRAINT FK_DEPTNO FOREIGN Key (DePTNO)
References de PEPT (DePTNO),
ConsTRAINT FK_STATACODE Foreign Key (State_code)
References State (state_code)
);
- If you want to store a large number of records in the EMP table, you may want to divide the EMP to multiple tables, to divide the records according to the range, you can use
The Partition by Range sentence of the create table, which determines the value stored in each partition
The columns used as partition logic bases are very small. The maximum division basis is an external health in the table.
example:
CREATE TABLE EMP
Empno Number (10) Primary Key,
Name varchar2 (40),
Deptno Number (2),
SALARY NUMBER (7, 2),
Birth_date date,
SOC_SEC_NUM VARCHAR2 (9),
State_code char (2),
ConsTRAINT FK_DEPTNO FOREIGN Key (DePTNO)
References de PEPT (DePTNO),
ConsTRAINT FK_STATACODE Foreign Key (State_code)
References State (state_code)
)
Partition by Range (DePTNO)
(Partition Part1 Values Less Than (11) TableSpace Part1_TS,
Partition Part2 Values Less Than (21) TableSpace Part2_TS,
Partition Part3 Values Less Than (31) TableSpace Part3_TS,
Partition Part4 Values Less Than (MaxValue) TABLESPACE Part 4_TS
);
- Query directly from the partition table
Select * from EMP Partition (Part2)
WHERE DEPTNO BETWEEN 11 and 20;
- Create a list-based compound partition table
Create Table Test
User_id varchar2 (40),
ORG_ID VARCHAR2 (40)
)
Partition By Range (User_ID) - Home Partition
Subpartition by list (org_id) - subband
Subpartition Template - Set Sub Partition Template
(Subpartition org_a Values ('a'),
Subpartition org_b valuees ('b'),
Subpartition org_c value ('c'),
Subpartition org_d value ('d'),
Subpartition org_e value ('e'), Subpartition org_f Values ('f'),
Subpartition org_g values ('g'),
Subpartition org_other value (default)
)
(--- Composite partition
Partition User_y Values LESS THAN ('Y') - Composite Partition Principal Partition Condition Settings
(Subpartition user_y_org_a values ('a') TableSpace Users,
SubphaTition User_Y_ORG_B VALUES ('B') TableSpace Users,
Subpartition user_y_org_c values ('c') TableSpace Uses,
Subpartition user_y_org_d values ('d') TableSpace Users,
Subpartition user_y_org_e value ('e') TableSpace Users,
Subpartition user_y_org_f value ('f') TableSpace Users,
Subpartition user_y_org_g values ('g') TableSpace Users,
Subpartition user_y_org_other values (default) tablespace users,
Partition User_z Values LESS TAN ('Z')
(Subpartition user_z_org_a values ('a') TableSpace Users,
Subpartition user_z_org_b value ('b') TableSpace Uses,
Subpartition user_z_org_c value ('c') TableSpace Users,
Subpartition user_z_org_d values ('d') TableSpace Users,
Subpartition user_z_org_e value ('e') TableSpace Users,
Subpartition user_z_org_f value ('f') tablespace users,
Subpartition user_z_org_g values ('g') TableSpace Users,
Subpartition user_z_org_other values (default) TableSpace users,
Partition User_max Values Less Than (MaxValue)
(Subpartition User_max_org_a value ('a') tablespace users,
Subpartition User_max_org_b valuees ('b') TableSpace Users,
Subpartition User_max_org_c value ('c') TableSpace Uses,
Subpartition user_max_org_d values ('d') TableSpace Users,
Subpartition user_max_org_e value ('e') tablespace users, subsartition user_max_org_f values ('f') tablespace users,
Subpartition user_max_org_g values ('g') TableSpace Users,
Subpartition User_max_org_other value (default) tablespace user
) ----- complement of the composite partition
or
Create Table Test
User_id varchar2 (40),
ORG_ID VARCHAR2 (40)
)
Partition by Range (user_id)
Subpartition by list (ORG_ID)
Subpartition Template
(Subpartition org_a Values ('a'),
Subpartition org_b valuees ('b'),
Subpartition org_c value ('c'),
Subpartition org_d value ('d'),
Subpartition org_e Values ('E'),
Subpartition org_f value ('f'),
Subpartition org_g values ('g'),
Subpartition org_other value (default)
)
(
Partition user_y values less ('y'),
Partition User_z Values Less Than ('Z'),
Partition User_max Values Less Than (MaxValue)
)
- Creating a composite partition based on a renum sub-partition
CREATE TABLE EMP
Empno Number (10) Primary Key,
Name varchar2 (40),
Deptno Number (2),
SALARY NUMBER (7, 2),
Birth_date date,
SOC_SEC_NUM VARCHAR2 (9),
State_code char (2),
ConsTRAINT FK_DEPTNO FOREIGN Key (DePTNO)
References de PEPT (DePTNO),
ConsTRAINT FK_STATACODE Foreign Key (State_code)
References State (state_code)
)
Partition by Range (DePTNO)
Subphartition by hash (name)
Subpartitions 10
(Partition Part1 Values Less Than (11) TableSpace Part1_TS,
Partition Part2 Values Less Than (21) TableSpace Part2_TS,
Partition Part3 Values Less Than (31) TableSpace Part3_TS,
Partition Part4 Values Less Than (MaxValue) TABLESPACE Part 4_TS
);
