Oracle分区表和索引的创建与管理(一)

今天用到了Oracle表的分区，就顺便写几个例子把这个表的分区说一说：

一、创建分区表

1、范围分区

根据数据表字段值的范围进行分区

举个例子，根据学生的不同分数对分数表进行分区，创建一个分区表如下：

create table range_fraction
  (
  id number(8),
  name varchar2(20),
  fraction number(3),
  grade number(2)
)
partition by range(fraction)
(
  partition fraction_60 values less than(60), --不及格
  partition fraction_80 values less than(85), --及格
  partition fraction_100 values less than(maxvalue) --优秀
)

创建完分区表后向表中添加一些数据：

declare 
  name     varchar2(10);
  fraction number(5);
  grade    number(5);
  i        number(8):=1;
begin
  for i in 1..100000 LOOP
  SELECT CHR (ROUND (DBMS_RANDOM.VALUE (97, 122))) INTO NAME FROM DUAL;
  SELECT ABS(MOD(DBMS_RANDOM.RANDOM,101)) into fraction FROM DUAL;
  SELECT ABS(MOD(DBMS_RANDOM.RANDOM,10))+1 into grade FROM DUAL;
  insert into range_fraction values(seq_range_fraction.nextval ,name,fraction,grade);
  END LOOP;
end;

--分别查询所有的，不及格的，中等的，优秀的成绩
select *  from  range_fraction;
select *  from  range_fraction partition(fraction_60) ;
select *  from  range_fraction partition(fraction_80) ;
select *  from  range_fraction partition(fraction_100) ;

当我们的查询语句不指定分区的时候，如果分区字段出现在where条件之后，Oracle会自动根据字段值的范围扫描响应的分区：

select * from range_fraction where fraction<30; 这句SQL执行的时候只会扫描不及格的分区

select * from range_fraction where fraction<80; 这句SQL执行的时候会扫描不及格和中等两个分区

2、散列分区

在范围分区中，分区字段的连续值通常出现在一个分区内，而在散列分区中，连续的字段值不一定存储在相同的分区中。散列分区把记录分布在比范围分区更多的分区上，这减少了I/O争用的可能性。

为了创建一个散列分区，应该用partition by hash语句代替partition by range子句，如下所示：

第一种为各个分区指定不同的表空间，表空间数量不用等于分区数量，当表空间数量大于分区数量的时候会循环写入各个表空间：

create table range_fraction1
  (
  id number(8),
  name varchar2(20),
  fraction number(3),
  grade number(2)
)
partition by hash(fraction)
partitions 8
store in (users,tbs_haicheng)

create table range_fraction1
  (
  id number(8),
  name varchar2(20),
  fraction number(3),
  grade number(1)
)
partition by hash(fraction)
(
   partition p1 tablespace tbs_haicheng ,
   partition p2 tablespace users
);

3、列表分区

还可以使用列表分区代替范围分区和散列分区。在列表分区中，告诉Oracle所有可能的值，并指定应当插入相应行的分区。

我们将1、2、3、4班级的数据放在一个分区，将6、7、8的数据放在一个分区，将其他的再放在一个分区，建表如下：

create table range_fraction1
  (
  id number(8),
  name varchar2(20),
  fraction number(3),
  grade number(2)
)
partition by list(grade)
(
   partition p1 values(1,2,3,4) tablespace tbs_haicheng ,
   partition p2 values(5,6,7,8) tablespace users,

   partition p3 values(default)

);

4、组合分区(创建子分区)

即分区的分区。例如可以先进行范围分区，再对各个范围分区创建列表分区。

对于非常大的表来说，这种组合分区是一种把数据分成可管理和可调整的组成部分的有效方法。

举个例子：按照分数范围分区后再将ID散列分区：

create table range_fraction1
  (
  id number(8),
  name varchar2(20),
  fraction number(3),
  grade number(1)
)
partition by range(fraction)
subpartition by hash(id)
subpartitions 4

(
  partition fraction_60 values less than(60), --不及格
  partition fraction_80 values less than(85), --及格
  partition fraction_100 values less than(maxvalue) --优秀
)

在分区表上可以建立三种类型的索引：1和普通表一样的全局索引；2.全局分区索引；3.本地分区索引

create index index_fraction on range_fraction(fraction);

create index  local_index_fraction on range_fraction(fraction) local;

create index global_index_fraction on range_fraction(fraction)
GLOBAL partition by  range(fraction)
(
 part_01 values less than(1000),
 part_02 values less than(MAXVALUE)
);

三、管理