oracle 分区表的学习

最新推荐文章于 2022-08-28 17:17:52 发布

玩火的小孩

最新推荐文章于 2022-08-28 17:17:52 发布

阅读量296

点赞数

本文链接：https://blog.youkuaiyun.com/u012952442/article/details/47752459

版权

对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)
);