对oracle分区表知识整理和学习
1、范围分区表
create table part_status(
id number(6),
name varchar2(30),
grade number(4),
birthday date
)partition by range(grade)(
partition p1 values less than(60),
partition p2 values less than(80),
partition p3 values less than(maxvalue)
);
创建学生表按成绩分区 60,80,80以上
写入数据
insert into part_status values(1,'a',30,to_date('2011-01-22','yyyy-mm-dd'));
insert into part_status values(1,'b',60,to_date('2011-03-22','yyyy-mm-dd'));
insert into part_status values(1,'c',70,to_date('2011-04-22','yyyy-mm-dd'));
insert into part_status values(1,'d',80,to_date('2011-05-22','yyyy-mm-dd'));
insert into part_status values(1,'e',90,to_date('2011-06-22','yyyy-mm-dd'));
insert into part_status values(1,'f',100,to_date('2011-07-22','yyyy-mm-dd'));
insert into part_status values(1,'g',0,to_date('2011-08-22','yyyy-mm-dd'));
查看全表
15:27:52 WHDXH@shgt > select * from part_status;
ID NAME GRADE BIRTHDAY
---------- ------------------------------ ---------- ---------
1 a 30 22-JAN-11
1 g 0 22-AUG-11
1 b 60 22-MAR-11
1 c 70 22-APR-11
1 d 80 22-MAY-11
1 e 90 22-JUN-11
1 f 100 22-JUL-11
7 rows selected.
指定分区查看
15:47:26 WHDXH@shgt > select * from part_status partition(p1);
ID NAME GRADE BIRTHDAY
---------- ------------------------------ ---------- ---------
1 a 30 22-JAN-11
1 g 0 22-AUG-11
改变分区
alter table part_status enable row movement;
16:33:50 WHDXH@shgt > update part_status t set t.grade=90 where id =1;
1 row updated.
16:33:59 WHDXH@shgt > select * from part_status partition(p1);
ID NAME GRADE BIRTHDAY
---------- ------------------------------ ---------- ---------
2 e 50 22-JUN-11
16:34:01 WHDXH@shgt > alter table part_status disable row movement;
Table altered.
15:49:30 WHDXH@shgt > select * from part_status partition(p2);
ID NAME GRADE BIRTHDAY
---------- ------------------------------ ---------- ---------
1 b 60 22-MAR-11
1 c 70 22-APR-11
15:49:53 WHDXH@shgt > select * from part_status partition(p3);
ID NAME GRADE BIRTHDAY
---------- ------------------------------ ---------- ---------
1 d 80 22-MAY-11
1 e 90 22-JUN-11
1 f 100 22-JUL-11
2、散列分区表 hash
通过hash算法均匀分布数据 时的分区大小一致(高并发性的解决方案)
create table part_status(
id number(6),
name varchar2(30),
grade number(4),
birthday date
)partition by hash(grade)(
partition p1 tablespace tbp1,
partition p2 tablespace tbp1,
partition p3 tablespace tbp1
);
3列表分区
create table part_status(
id number(6),
name varchar2(30),
grade number(4),
sex number(1)
)partition by list(sex)(
partition p1 values(1),
partition p2 values(2)
);