分区元素: 字符串, 日期时间或间隔文字, 数字或 MAXVALUE
查看表的分区,利用user_tab_partitions
select table_name,partition_name from user_tab_partitions where table_name='STUD';
1:范围分区
--按行分区,
create table stud(
sid number primary key,
age number,
birthday date,
sex varchar2(6)
)
partition by range(sid)(
partition part1 values less than(10000),
partition part2 values less than(20000),
partition part3 values less than(maxvalue)
);
--按时间分区
create table stud(
sid number primary key,
age number,
birthday date,
sex varchar2(6)
)
partition by range(birthday)(
partition part1 values less than(to_date('19950101','yyyymmdd')),
partition part2 values less than(to_date('19960101','yyyymmdd')),
partition part3 values less than(to_date('19970101','yyyymmdd')),
partition part4 values less than(maxvalue)
);
--hash分区
通过hash(参数)来分区,分区数量尽量为2的n次方,这样各分区数据分布更均匀
create table stud(
sid number primary key,
age number,
birthday date,
sex varchar2(6)
)
partition by hash(sid)
(
partition p1,
partition p2,
partition p3,
partition p4
);
--list分区
在分区时确定分区可能存在的值,依赖不在是一个范围,而是一个确定的值,如果实在无法完全确定
分区依赖列的所有可能值,可以用default关键字把剩余的情况归为一类
create table stud(
sid number primary key,
age number,
birthday date,
sex varchar2(6)
)
partition by list(sex)
(
partition p1 values('男'),
partition p2 values('女'),
partition p3 values('其他'),
partition p4 values(default)
);
--混合分区
rang+hash
create table stud(
sid number primary key,
age number,
birthday date,
sex varchar2(6)
)
partition by range(sid) subpartition by hash(age)
(
partition p1 values less than(10),
partition p2 values less than(20),
partition p3 values less than(30),
partition p4 values less than(maxvalue)
);
declare
psex varchar2(6);
random_chance number;
begin
for sid in 1..40 loop
random_chance:=dbms_random.value(0,100);
if(random_chance<40) then
psex:='女';
elsif(random_chance<90) then
psex:='男';
else
psex:='其他';
end if;
insert into stud(sid,sex) values(sid,psex);
end loop;
commit;
end;
--混合分区
range-list
create table stud(
sid number primary key,
age number,
birthday date,
sex varchar2(6)
)
partition by range(sid) subpartition by list(sex)
(
partition p1 values less than(10) tablespace tablespace_102
(subpartition p1sub1 values('男'),
subpartition p1sub2 values('女'),
subpartition p1sub3 values('其他')
),
partition p2 values less than(20) tablespace tablespace_102
(
subpartition p2sub1 values('男'),
subpartition p2sub2 values('女'),
subpartition p2sub3 values('其他')
),
partition p3 values less than(30) tablespace tablespace_102
(
subpartition p3sub1 values('男'),
subpartition p3sub2 values('女'),
subpartition p3sub3 values('其他')
),
partition p4 values less than(41) tablespace tablespace_102
(
subpartition p4sub1 values('男'),
subpartition p4sub2 values('女'),
subpartition p4sub3 values('其他')
)
);
查看指定分区数据:
select * from stud partition(p1/p2/p3/p4);
select * from stud subpartition(p1sub1/p1sub2/p1sub3...);
create table dep(deptno char(4) primary key);
insert into dep values('0001');
create table stud(
sid number primary key,
age number,
birthday date,
sex varchar2(6)
);
alter table stud add(deptno char(4) constraint fk_stud_dept_deptno references dep(deptno));
insert into stud(sid,deptno,sex) values(45,'0001','男');
在没有指定maxvalue的表中增加分区(原理通过id number分区)
alter table table_name add partition p_name values less than(50) tablespace tablespacename;
删除分区
alter table table_name drop partition p_name;
修改分区名(p1->p2)
alter table table_name rename partition p1 to p2;
移动分区数据到表空间(tablespace_u101)
alter table tablename move partition p1 to tablespace tablespace_u101;
分区拆分(拆分点为60)