oracle 表分区

 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