在oracle9i中 ,oracle支持多种类型分区:范围分区,散列分区,列表分区和混合分区
要创建一个范围分区,必须将用于分区的范围值作为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 dept(deptno),
constraint fk_statecode foreign key (state_code)
references state(state_code)
);
--如果要在emp 表中存储大量记录,可能希望将emp的行分到多个表中,若要按范围划分表的记录,可以使用
create table 命令的 partition by range 字句,这些范围确定存储在每个分区的值
用作分区逻辑基础的列很少是表的主键,最长用的划分基础是表中的一个外健.
例:
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 dept(deptno),
constraint fk_statecode 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 PART4_TS
);
--直接从分区表中查询
select * from emp partition (PART2)
where deptno between 11 and 20 ;
--创建基于列表的复合分区表
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 VALUES ('b'),
SUBPARTITION ORG_C VALUES ('c'),
SUBPARTITION ORG_D VALUES ('d'),
SUBPARTITION ORG_E VALUES ('e'),
SUBPARTITION ORG_F VALUES ('f'),
SUBPARTITION ORG_G VALUES ('g'),
SUBPARTITION ORG_OTHER VALUES (DEFAULT)
)
(---复合分区开始
PARTITION USER_Y VALUES LESS THAN ('y') --复合分区主分区条件设置
( SUBPARTITION USER_Y_ORG_A VALUES ('a') TABLESPACE USERS,
SUBPARTITION USER_Y_ORG_B VALUES ('b') TABLESPACE USERS,
SUBPARTITION USER_Y_ORG_C VALUES ('c') TABLESPACE USERS,
SUBPARTITION USER_Y_ORG_D VALUES ('d') TABLESPACE USERS,
SUBPARTITION USER_Y_ORG_E VALUES ('e') TABLESPACE USERS,
SUBPARTITION USER_Y_ORG_F VALUES ('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 THAN ('z')
( SUBPARTITION USER_Z_ORG_A VALUES ('a') TABLESPACE USERS,
SUBPARTITION USER_Z_ORG_B VALUES ('b') TABLESPACE USERS,
SUBPARTITION USER_Z_ORG_C VALUES ('c') TABLESPACE USERS,
SUBPARTITION USER_Z_ORG_D VALUES ('d') TABLESPACE USERS,
SUBPARTITION USER_Z_ORG_E VALUES ('e') TABLESPACE USERS,
SUBPARTITION USER_Z_ORG_F VALUES ('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 VALUES ('a') TABLESPACE USERS,
SUBPARTITION USER_MAX_ORG_B VALUES ('b') TABLESPACE USERS,
SUBPARTITION USER_MAX_ORG_C VALUES ('c') TABLESPACE USERS,
SUBPARTITION USER_MAX_ORG_D VALUES ('d') TABLESPACE USERS,
SUBPARTITION USER_MAX_ORG_E VALUES ('e') TABLESPACE USERS,
SUBPARTITION USER_MAX_ORG_F VALUES ('f') TABLESPACE USERS,
SUBPARTITION USER_MAX_ORG_G VALUES ('g') TABLESPACE USERS,
SUBPARTITION USER_MAX_ORG_OTHER VALUES (DEFAULT) TABLESPACE USERS )
)-----复合分区结束
或者
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 VALUES ('b'),
SUBPARTITION ORG_C VALUES ('c'),
SUBPARTITION ORG_D VALUES ('d'),
SUBPARTITION ORG_E VALUES ('e'),
SUBPARTITION ORG_F VALUES ('f'),
SUBPARTITION ORG_G VALUES ('g'),
SUBPARTITION ORG_OTHER VALUES (DEFAULT)
)
(
PARTITION USER_Y VALUES LESS THAN ('y') ,
PARTITION USER_Z VALUES LESS THAN ('z'),
PARTITION USER_MAX VALUES LESS THAN (MAXVALUE)
)
--创建基于散列子分区的复合分区
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 dept(deptno),
constraint fk_statecode foreign key (state_code)
references state(state_code)
)
partition by range(deptno)
SUBPARTITION 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 PART4_TS
);
要创建一个范围分区,必须将用于分区的范围值作为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 dept(deptno),
constraint fk_statecode foreign key (state_code)
references state(state_code)
);
--如果要在emp 表中存储大量记录,可能希望将emp的行分到多个表中,若要按范围划分表的记录,可以使用
create table 命令的 partition by range 字句,这些范围确定存储在每个分区的值
用作分区逻辑基础的列很少是表的主键,最长用的划分基础是表中的一个外健.
例:
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 dept(deptno),
constraint fk_statecode 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 PART4_TS
);
--直接从分区表中查询
select * from emp partition (PART2)
where deptno between 11 and 20 ;
--创建基于列表的复合分区表
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 VALUES ('b'),
SUBPARTITION ORG_C VALUES ('c'),
SUBPARTITION ORG_D VALUES ('d'),
SUBPARTITION ORG_E VALUES ('e'),
SUBPARTITION ORG_F VALUES ('f'),
SUBPARTITION ORG_G VALUES ('g'),
SUBPARTITION ORG_OTHER VALUES (DEFAULT)
)
(---复合分区开始
PARTITION USER_Y VALUES LESS THAN ('y') --复合分区主分区条件设置
( SUBPARTITION USER_Y_ORG_A VALUES ('a') TABLESPACE USERS,
SUBPARTITION USER_Y_ORG_B VALUES ('b') TABLESPACE USERS,
SUBPARTITION USER_Y_ORG_C VALUES ('c') TABLESPACE USERS,
SUBPARTITION USER_Y_ORG_D VALUES ('d') TABLESPACE USERS,
SUBPARTITION USER_Y_ORG_E VALUES ('e') TABLESPACE USERS,
SUBPARTITION USER_Y_ORG_F VALUES ('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 THAN ('z')
( SUBPARTITION USER_Z_ORG_A VALUES ('a') TABLESPACE USERS,
SUBPARTITION USER_Z_ORG_B VALUES ('b') TABLESPACE USERS,
SUBPARTITION USER_Z_ORG_C VALUES ('c') TABLESPACE USERS,
SUBPARTITION USER_Z_ORG_D VALUES ('d') TABLESPACE USERS,
SUBPARTITION USER_Z_ORG_E VALUES ('e') TABLESPACE USERS,
SUBPARTITION USER_Z_ORG_F VALUES ('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 VALUES ('a') TABLESPACE USERS,
SUBPARTITION USER_MAX_ORG_B VALUES ('b') TABLESPACE USERS,
SUBPARTITION USER_MAX_ORG_C VALUES ('c') TABLESPACE USERS,
SUBPARTITION USER_MAX_ORG_D VALUES ('d') TABLESPACE USERS,
SUBPARTITION USER_MAX_ORG_E VALUES ('e') TABLESPACE USERS,
SUBPARTITION USER_MAX_ORG_F VALUES ('f') TABLESPACE USERS,
SUBPARTITION USER_MAX_ORG_G VALUES ('g') TABLESPACE USERS,
SUBPARTITION USER_MAX_ORG_OTHER VALUES (DEFAULT) TABLESPACE USERS )
)-----复合分区结束
或者
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 VALUES ('b'),
SUBPARTITION ORG_C VALUES ('c'),
SUBPARTITION ORG_D VALUES ('d'),
SUBPARTITION ORG_E VALUES ('e'),
SUBPARTITION ORG_F VALUES ('f'),
SUBPARTITION ORG_G VALUES ('g'),
SUBPARTITION ORG_OTHER VALUES (DEFAULT)
)
(
PARTITION USER_Y VALUES LESS THAN ('y') ,
PARTITION USER_Z VALUES LESS THAN ('z'),
PARTITION USER_MAX VALUES LESS THAN (MAXVALUE)
)
--创建基于散列子分区的复合分区
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 dept(deptno),
constraint fk_statecode foreign key (state_code)
references state(state_code)
)
partition by range(deptno)
SUBPARTITION 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 PART4_TS
);