oracle有四种表分区的方法:
1、区间分区
create table range_partition (
object_id number,
object_type VARCHAR2(19),
object_name VARCHAR2(30)
)partition by range(object_id)
(
partition range_p1 values less than (1000) tablespace users,
partition range_p2 values less than (5000) tablespace users,
partition range_p3 values less than (maxvalue) tablespace users
);
--插入数据
insert into range_partition
(select object_id,object_type,object_name from all_objects where object_id<10000);
--查询数据
select count(1) from range_partition partition(range_p1);
select count(1) from range_partition partition(range_p2);
select count(1) from range_partition partition(range_p3);
2、散列分区:oracle会对分区键应用一个散列函数,以此确定数据应该放在N个分区中的哪一个分区,oracle建议N是2的一个幂(2、4、8、16等)。
--创建表
create table hash_partition (
object_id number,
object_type VARCHAR2(19),
object_name VARCHAR2(30)
)partition by hash(object_id)
(
partition p1 tablespace users,
partition p2 tablespace users,
partition p3 tablespace users
);
--插入数据
insert into hash_partition
(select object_id,object_type,object_name from all_objects where object_id<10000);
--查询数据
select count(1) from hash_partition partition(p1); --2395
select count(1) from hash_partition partition(p2); --4846
select count(1) from hash_partition partition(p3); --2450
可以看出当N不为2的幂,分布很不均匀。
3、列表分区
--创建表
create table list_partition (
object_id number,
object_type VARCHAR2(19),
object_name VARCHAR2(30)
)partition by list(object_type)
(
partition p1 values ('TABLE','QUEUE','JOB','VIEW') tablespace users,
partition p2 values('PROCEDURE','LOB','PACKAGE','PROGRAM') tablespace users,
partition p3 values(default) tablespace users
);
--插入数据
insert into list_partition
(select object_id,object_type,object_name from all_objects where object_id<10000 );
--查询数据
select count(1) from list_partition partition(p1); --3990
select count(1) from list_partition partition(p2); --565
select count(1) from list_partition partition(p3); --5136
4、组合分区:是区间分区和散列分区的一种组合,或者是区间分区和列表分区的组合。
--创建表
create table composite_partition (
object_id number,
object_type VARCHAR2(19),
object_name VARCHAR2(30)
)partition by range(object_id)
subpartition by list(object_type)
(
partition p1 values less than (5000)
(
subpartition p1_1 values ('TABLE','QUEUE','JOB','VIEW') tablespace users,
subpartition p1_2 values('PROCEDURE','LOB','PACKAGE','PROGRAM') tablespace users,
subpartition p1_3 values(default) tablespace users
)
,partition p2 values less than (maxvalue)
(
subpartition p2_1 values ('TABLE','QUEUE','JOB','VIEW') tablespace users,
subpartition p2_2 values('PROCEDURE','LOB','PACKAGE','PROGRAM') tablespace users,
subpartition p2_3 values(default) tablespace users
)
);
--插入数据
insert into composite_partition
(select object_id,object_type,object_name from all_objects where object_id<10000 );
--查询数据
select count(1) from composite_partition partition(p1); --4924
select count(1) from composite_partition partition(p2); --4767
select count(1) from composite_partition subpartition(p1_1); --2533
select count(1) from composite_partition subpartition(p1_2); --94
select count(1) from composite_partition subpartition(p1_3); --2297