--用列做分区,分区原则是列的内容
create table custaddr
(
id varchar2(15 byte) not null,
areacode varchar2(4 byte)
)
partition by list (areacode)
(
partition t_list025 values ('025'),
partition t_list372 values ('372') ,
partition t_list510 values ('510'),
partition p_other values (default)
);
commit;
select * from custaddr;
select * from custaddr partition(t_list025) ;
select * from custaddr partition(t_list372) ;
select * from custaddr partition(t_list510) ;
select * from custaddr partition(p_other) ;
--范围分区
create table graderecord
(
sno varchar2(10),
sname varchar2(20),
dormitory varchar2(3),
grade int
)
partition by range(grade)
(
partition bujige values less than(60), --不及格
partition jige values less than(85), --及格
partition youxiu values less than(maxvalue) --优秀
);
commit;
select * from graderecord;
select * from graderecord partition(bujige);
select * from graderecord partition(jige);
select * from graderecord partition(youxiu);
---散列分区,计算字段值的hashcode进行分区,可以是单独一个字段hash,也可以是多个字段
create table graderecord1
(
sno varchar2(10),
sname varchar2(20),
dormitory varchar2(3),
grade int
)
partition by hash(sno)
(
partition p1,
partition p2,
partition p3
);
commit;
select * from graderecord1 partition(p1);
select * from graderecord1 partition(p2);
select * from graderecord1 partition(p3);
--复合分区(范围-散列分区,范围-列表分区)
--子分区如果是通过hashcode分区,可以是单个字段,也可以多个字段
create table graderecord4
--先通过范围分区,然后再联合sname和sno进行复合分区,其实就是子分区
(
sno varchar2(10),
sname varchar2(20),
dormitory varchar2(3),
grade int
)
partition by range(grade)
subpartition by hash(sno) --subpartition by hash(sno,sname)
(
partition p1 values less than(75)
(
subpartition sp1 --, subpartition sp2
),
partition p2 values less than(maxvalue)
(
subpartition sp3 --, subpartition sp4
)
);
select * from graderecord4 partition(p1);
select * from graderecord4 partition(p2);
select * from graderecord4 subpartition(sp1);
--select * from graderecord subpartition(sp2);
select * from graderecord4 subpartition(sp3);
--select * from graderecord subpartition(sp4);
--复合分区之范围-列表分区(无模板)
create table MobileMessage
(
ACCT_MONTH VARCHAR2(6), -- 帐期 格式:年月 YYYYMM
AREA_NO VARCHAR2(10), -- 地域号码
DAY_ID VARCHAR2(2), -- 本月中的第几天 格式 DD
S