oracle 分区表

Oracle分区表分为四类：范围分区表，列表分区表，哈希分区表，组合分区表

范围分区

创建一个按字段数据范围分区的表，分区置于指定的不同表空间中。

创建分区

create tablespace emp_part1_space datafile 'C:\oracle\product\10.2.0\oradata\orcl\emp_part1_space.dbf' size 50m;
create tablespace emp_part2_space datafile 'C:\oracle\product\10.2.0\oradata\orcl\emp_part2_space.dbf' size 50m;
create tablespace emp_part3_space datafile 'C:\oracle\product\10.2.0\oradata\orcl\emp_part3_space.dbf' size 50m;
create tablespace emp_part4_space datafile 'C:\oracle\product\10.2.0\oradata\orcl\emp_part4_space.dbf' size 50m;
create tablespace emp_part5_space datafile 'C:\oracle\product\10.2.0\oradata\orcl\emp_part5_space.dbf' size 50m;
create tablespace emp_part6_space datafile 'C:\oracle\product\10.2.0\oradata\orcl\emp_part6_space.dbf' size 50m;
create tablespace emp_part7_space datafile 'C:\oracle\product\10.2.0\oradata\orcl\emp_part7_space.dbf' size 50m;
create tablespace emp_part8_space datafile 'C:\oracle\product\10.2.0\oradata\orcl\emp_part8_space.dbf' size 50m;
create tablespace emp_part9_space datafile 'C:\oracle\product\10.2.0\oradata\orcl\emp_part9_space.dbf' size 50m;
create tablespace emp_part10_space datafile 'C:\oracle\product\10.2.0\oradata\orcl\emp_part10_space.dbf' size 50m;

create table emp
(
  emp_id int primary key,
  deptno int not null
)
partition by range(emp_id)
(
  partition emp_part1 values less than(100000) tablespace emp_part1_space,
  partition emp_part2 values less than(200000) tablespace emp_part2_space,
  partition emp_part3 values less than(300000) tablespace emp_part3_space,
  partition emp_part4 values less than(400000) tablespace emp_part4_space,
  partition emp_part5 values less than(500000) tablespace emp_part5_space,
  partition emp_part6 values less than(600000) tablespace emp_part6_space,
  partition emp_part7 values less than(700000) tablespace emp_part7_space,
  partition emp_part8 values less than(800000) tablespace emp_part8_space,
  partition emp_part9 values less than(900000) tablespace emp_part9_space,
  partition emp_part10 values less than(1000001) tablespace emp_part10_space
);

插入数据

declare
l_deptno int;
begin
  for i in 1..1000000 loop
    l_deptno := i mod 4;
    insert into emp values (i, l_deptno);
  end loop;
  commit;
end;

查询某个分区

select count(*) from emp partition(emp_part1);

散列分区

在下面这种情况下，使用hash分区比range分区更好：

事先不知道需要将多少数据映射到给定范围的时候分区的范围大小很难确定，或者很难平衡的时候Range分区使数据得到不希望的聚集时性能特性，如并行DML、分区剪枝和分区连接很重要的时候。

create table tb_hash
(
  create_time timestamp not null,
  hash_name varchar2(20) null
)
partition by hash(create_time)
(
  partition p1,
  partition p2
);

插入数据

declare
l_name varchar2(20);
begin
  for i in 1..10000 loop
    select dbms_random.string('U', 20) into l_name from dual;
    insert into tb_hash values (sysdate, l_name);
  end loop;
  commit;
end;

查询

select count(*) from tb_hash partition(p1);

列表分区

一般用于数据可枚举，有限个值，可以考虑列表分区，例如国家名字，按州来分区。创建list分区表，我们按国家来分别存放在不同的州，每个州是一个分区。

create table tb_list
(
  city_id integer not null,
  city_name varchar2(30) not null,
  city_state varchar2(20) not null,
  city_amount integer not null
)
partition by list (city_name)
(
partition Asia values('China','Japan'),
partition Europe values ('Germany','Italy'),
partition Africa values('Egypt','Kenya'),
partition Other values(default)
);

插入数据

insert into tb_list values(1,'China','Asia',100);
insert into tb_list values(2,'Germany','Europe',101);
insert into tb_list values(3,'Egypt','Africa',102);
insert into tb_list values(4,'other city','other',103);
commit;

查询

select * from tb_list partition(Asia);

组合分区

先range，再hash。

create table tb_composite
(
sales_id int primary key,
sales_name varchar2(30) not null,
sales_amount int not null,
sales_date date not null
)
partition by range(sales_date)
subpartition by hash(sales_id)
subpartitions 4
(
  partition sales_jan2014 values less than(to_date('02/01/2014','DD/MM/YYYY'))
  (
    subpartition sales_jan2014_sub1,
    subpartition sales_jan2014_sub2
  ),
  partition sales_feb2014 values less than(to_date('03/01/2014','DD/MM/YYYY'))
  (
    subpartition sales_feb2014_sub1,
    subpartition sales_feb2014_sub2
  ),
  partition sales_mar2014 values less than(to_date('04/01/2014','DD/MM/YYYY'))
  (
    subpartition sales_mar2014_sub1,
    subpartition sales_mar2014_sub2
  )
);

查询

select * from tb_composite subpartition(sales_jan2014_sub1);

分区的好处

分区技术实质可以把数据分摊到不同的物理位置，增加I/O负载，提高检索效率。
可用性：分区表可以跨越表空间，而普通表则不然，好处就是如果表的一个分区损坏，其他分区不会受到影响我们只需要修复损坏的分区即可