oracle之表分区

分区元素： 字符串, 日期时间或间隔文字, 数字或 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)