6.3、分区表、分区表索引--Oracle模式对象

分区：

(1).一种分区技术，可以在创建表时应用分区技术，将数据以分区形式保存。

(2).可以将巨型表或索引分割成相对较小的、可独立管理的部分。

(3).表分区时必须为表中的每一条记录指定所属分区。

 

对表进行分区优点：

增强可用性；

维护方便；

均衡I/O；

改善查询性能。

 

创建分区表

分区方法：范围分区、散列分区、列表分区、组合范围散列分区和组合范围列表分区；

 

1、范围分区：是对数据表中某个值的范围，使用partition by range子句进行分区。

create table testscore(
id      number primary key,
name    varchar2(8),
subject varchar2(10),
score   number
)
--指定按照score列，进行范围分区
partition by range(score)
(
--将表分成3个区，分别为range1、range2、range3,并存在不同的三个表空间上
partition range1 values less than(60) tablespace AAA,
partition range2 values less than(80) tablespace BBB,
partition range3 values less than(maxvalue) tablespace CCC
);

--向表testscoe中插入数据

insert into testscore values (1,'tyou','maths',50);
insert into testscore values (2,'zhangsa','maths',90);
insert into testscore values (3,'thm','english',76);
insert into testscore values (4,'tya','maths',98);
insert into testscore values (5,'jionjion','yuwen',32);
insert into testscore values (6,'haha','wuli',66);
commit;

--查询分区range1的结果如下：

select * from testscore partition(range1)；

--查询分区range2的结果如下：

select * from testscore partition(range2)；

 

2、散列分区：通过哈希hash算法均匀分布数据，通过在I/O设备上进行散列分区，可以使得分区的大小一致。使用partition by hash子句。

create table testid(
id      number primary key,
name    varchar2(8),
subject  varchar2(10),
score     number
)
--指定按照id列，进行散列分区
partition by hash(id)
(
partition part1 tablespace AAA,
partition part2 tablespace BBB,
partition part3 tablespace BBB
);

 

3、列表分区：适用于分区列的值为非数字或日期数据类型，并且分区列的取值范围较少时，使用partition by list子句。

--例如，成绩表中的subject科目列取值较少。

create table testsubject(
id      number primary key,
name    varchar2(8),
subject varchar2(10),
score   number
)
 7 --指定按照id列，进行散列分区
partition by list(subject)
(
partition part1 values('maths','yuwen') tablespace AAA,
partition part2 values('english','wuli') tablespace BBB,
partition part3 values('huaxue') tablespace CCC
);

--向表testsubject中插入数据

insert into testsubject values (1,'tyou','maths',50);
insert into testsubject values (2,'zhangsa','maths',90);
insert into testsubject values (3,'thm','english',76);
insert into testsubject values (4,'tya','maths',98);
insert into testsubject values (5,'jionjion','yuwen',32);
insert into testsubject values (6,'haha','wuli',66);
insert into testsubject values (7,'hehe','huaxue',66);
commit;

--查询分区part1的结果如下：

select * from testsubject partition(part1);

--查询分区part2的结果如下：

select * from testsubject partition(part2);

--查询分区part3的结果如下：

select * from testsubject partition(part3);

 

4、组合范围散列分区：结合范围分区和散列分区

create table testRanHas(
id      number primary key,
name    varchar2(8),
subject varchar2(10),
score   number
)
--指定按照score列范围分区分成3个分区
partition by range(score)
--然后，再使用subpartition by子句进行散列分区
subpartition by hash(id)
subpartitions 2 store in (AAA,BBB)
(
partition range1 values less than(60),
partition range2 values less than(80),
partition range3 values less than(maxvalue)
);

 

5、组合范围列表分区：结合范围分区和列表分区

create table testRanLis(
id      number primary key,
name    varchar2(8),
subject varchar2(10),
score   number
)
partition by range(score)
subpartition by list(subject)
(
partition range1 values less than(60)
     (
      subpartition part1_1 values('maths','yuwen') tablespace AAA,
      subpartition part1_2 values('english','wuli') tablespace BBB
      ),
partition range2 values less than(80)
      (
      subpartition part2_1 values('maths','yuwen') tablespace AAA,
      subpartition part2_2 values('huaxue') tablespace CCC
      ),
partition range3 values less than(maxvalue)
      (
       subpartition part3_1 values('maths','yuwen') tablespace AAA,
       subpartition part3_2 values('english','wuli') tablespace BBB,
       subpartition part3_3 values('huaxue') tablespace CCC
       )
);

 

分区表索引：3种类型，局部分区索引、全局分区索引和全局非分区索引

局部分区索引：

为分区表的各个分区单独建立的索引，各个分区索引之间相互独立的。

--先创建一个范围分区表

create table testscore(
id      number primary key,
name    varchar2(8),
subject varchar2(10),
score   number
)
partition by range(score)
(
partition range1 values less than(60) tablespace AAA,
partition range2 values less than(80) tablespace BBB,
partition range3 values less than(maxvalue) tablespace CCC
);

--再创建局部分区索引

create index index_testscore
on testscore(name) local
(
partition index1 tablespace AAA,
partition index2 tablespace BBB,
partition index3 tablespace CCC
);

 

全局分区索引：

对整个分区表建立的索引，全局分区索引的各个分区之间不是相互对立的。

create index global_index_testscore
on testscore(score)
Global partition by range(score)
(
partition range1 values less than(60) tablespace AAA,
partition range2 values less than(80) tablespace BBB,
partition range3 values less than(maxvalue) tablespace CCC
);

注意：使用global建立全局分区索引，只能用于range分区！！！
 

全局非分区索引：

对整个分区表建立索引，但未对索引进行分区。

create index nopart_index_testscore

on testscore(subject);

 

分区表管理：

1、增加分区

为范围分区表增加分区：

--先创建一个test范围分区表

create table test(
id      number primary key,
name    varchar2(8),
subject varchar2(10),
score   number
)
partition by range(score)
(
partition range1 values less than(60) tablespace AAA,
partition range2 values less than(80) tablespace BBB,
partition range3 values less than(100) tablespace CCC
);

--在最后一个分区之后增加分区

alter table test
add partition range4 values less than(150)

 

--在分区中间或开始出增加分区

--在80-100之间，增加一个90分开成两个分区，即range3分成range5和range6

alter table test
split partition range3 at(90)
into(
     partition range5 tablespace AAA,
     partition range6 tablespace BBB
     );

 

为散列分区表增加分区：

alter table test2

add partition range4 tablespace AAA;

 

为列表分区表增加分区：

alter table test3

add partition range4 values('shengwu') tablespace AAA;

 

2、合并分区：

--将之前增加的分区合并

alter table test

merge partitions range5,range6 into partition range3;

 

3、删除分区：

alter table test

drop partition range4;