表分区:允许用户将一个表分成多个分区
作用:
1.用户可以执行查询,只访问表中的特定分区
2.将不同的分区存储在不同的磁盘,提高访问性能和安全性
3.可以独立地备份和恢复每个分区
不可分区的情况:
1.如果一个表中包含非结构性数据,如:LOBS、LONG RAW或对象类型,就不能对表分区
2.索引组织的表不能分区
#范围分区:当数据在范围内均匀分布时,性能最好
1.以表中的一个列或一组列的值的范围分区
2.只能指定范围的下限,不能指定上限
3.可以指定分区存在的表空间,不指定则默认存在系统表空间SYSTEM中
4.系统表user_tab_partitions存放所有表分区信息
create table studentTest(
s_id number(3) primary key,
s_name varchar2(10),
s_sex char(2),
s_age number(3)
)
partition by range(s_age)(
partition p1 values less than(200),
partition p2 values less than(400),
partition p3 values less than(maxvalue) --分区列中的最大值
);
注意:向表中插入的数据如果列值超过了范围分区的最大值,则插入失败
insert into studentTest values (111,'张三','男',9);
insert into studentTest values (222,'张二','男',9);
insert into studentTest values (333,'张一','男',10);
insert into studentTest values (444,'李一','男',20);
insert into studentTest values (555,'网一','男',30);
select * from studentTest;
select * from studentTest partition(p1);
select * from studentTest partition(p2);
select * from studentTest partition(p3);
drop table studentTest;
#列表分区:允许用户将不相关的数据组织在一起
create table studentTest2(
s_id number(3) primary key,
s_name varchar2(10)
)
partition by list(s_name)(
partition p1 values ('张三','张二'),
partition p2 values ('张一','网一'),
partition p3 values (default) --Oracle9i不支持,Oracle10G才支持
);
insert into studentTest2 values (1,'张三');
insert into studentTest2 values (2,'张二');
insert into studentTest2 values (3,'张一');
insert into studentTest2 values (4,'李一');
insert into studentTest2 values (5,'网一');
drop table studentTest2;
select * from studentTest2;
select * from studentTest2 partition(p1);
select * from studentTest2 partition(p2);
select * from studentTest2 partition(p3);
#散列分区
1.允许用户对不具有逻辑范围的数据进行分区
2.只关注分几个区,不关注值存在哪个区
3.将数据随机地分布到不同的分区
create table studentTest3(
s_id number(3) primary key,
s_name varchar2(10)
)
partition by hash(s_name)(
partition s1,
partition s2,
partition s3,
partition s4,
partition s5
);
insert into studentTest3 values (1,'张三');
insert into studentTest3 values (2,'张二');
insert into studentTest3 values (3,'张一');
insert into studentTest3 values (4,'李一');
insert into studentTest3 values (5,'网一');
insert into studentTest3 values (6,'张三');
insert into studentTest3 values (7,'张二');
insert into studentTest3 values (8,'张一');
insert into studentTest3 values (9,'李一');
insert into studentTest3 values (10,'网一');
insert into studentTest3 values (11,'张三');
insert into studentTest3 values (12,'张二');
insert into studentTest3 values (13,'张一');
insert into studentTest3 values (14,'李一');
insert into studentTest3 values (15,'网一');
select * from studentTest3;
select * from studentTest3 partition(s1);
select * from studentTest3 partition(s2);
select * from studentTest3 partition(s3);
select * from studentTest3 partition(s4);
select * from studentTest3 partition(s5);
#复合分区
范围分区与散列分区或列表分区的组合