6.3、分區表、分區表索引--Oracle模式對象，6.3--oracle

6.3、分區表、分區表索引--Oracle模式對象，6.3--oracle

分區：

(1).一種分區技術，可以在創建表時應用分區技術，將數據以分區形式保存。

(2).可以將巨型表或索引分割成相對較小的、可獨立管理的部分。

(3).表分區時必須為表中的每一條記錄指定所屬分區。

對表進行分區優點：

增強可用性；

維護方便；

均衡I/O；

改善查詢性能。

創建分區表

分區方法：范圍分區、散列分區、列表分區、組合范圍散列分區和組合范圍列表分區；

1、范圍分區：是對數據表中某個值的范圍，使用partition by range子句進行分區。

 1 create table testscore(
 2 id      number primary key,
 3 name    varchar2(8),
 4 subject varchar2(10),
 5 score   number
 6 )
 7 --指定按照score列，進行范圍分區
 8 partition by range(score)
 9 (
10 --將表分成3個區，分別為range1、range2、range3,並存在不同的三個表空間上
11 partition range1 values less than(60) tablespace AAA,
12 partition range2 values less than(80) tablespace BBB,
13 partition range3 values less than(maxvalue) tablespace CCC
14 );

--向表testscoe中插入數據

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

--查詢分區range1的結果如下：

select * from testscore partition(range1)；

--查詢分區range2的結果如下：

select * from testscore partition(range2)；

2、散列分區：通過哈希hash算法均勻分布數據，通過在I/O設備上進行散列分區，可以使得分區的大小一致。使用partition by hash子句。

 1 create table testid(
 2 id      number primary key,
 3 name    varchar2(8),
 4 subject  varchar2(10),
 5 score     number
 6 )
 7 --指定按照id列，進行散列分區
 8 partition by hash(id)
 9 (
10 partition part1 tablespace AAA,
11 partition part2 tablespace BBB,
12 partition part3 tablespace BBB
13 );

3、列表分區：適用於分區列的值為非數字或日期數據類型，並且分區列的取值范圍較少時，使用partition by list子句。

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

 1 create table testsubject(
 2 id      number primary key,
 3 name    varchar2(8),
 4 subject varchar2(10),
 5 score   number
 6 )
 7 --指定按照id列，進行散列分區
 8 partition by list(subject)
 9 (
10 partition part1 values('maths','yuwen') tablespace AAA,
11 partition part2 values('english','wuli') tablespace BBB,
12 partition part3 values('huaxue') tablespace CCC
13 );

--向表testsubject中插入數據

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

--查詢分區part1的結果如下：

select * from testsubject partition(part1);

--查詢分區part2的結果如下：

select * from testsubject partition(part2);

--查詢分區part3的結果如下：

select * from testsubject partition(part3);

4、組合范圍散列分區：結合范圍分區和散列分區

 1 create table testRanHas(
 2 id      number primary key,
 3 name    varchar2(8),
 4 subject varchar2(10),
 5 score   number
 6 )
 7 --指定按照score列范圍分區分成3個分區
 8 partition by range(score)
 9 --然後，再使用subpartition by子句進行散列分區
10 subpartition by hash(id)
11 subpartitions 2 store in (AAA,BBB)
12 (
13 partition range1 values less than(60),
14 partition range2 values less than(80),
15 partition range3 values less than(maxvalue)
16 );

5、組合范圍列表分區：結合范圍分區和列表分區

 1 create table testRanLis(
 2 id      number primary key,
 3 name    varchar2(8),
 4 subject varchar2(10),
 5 score   number
 6 )
 7 partition by range(score)
 8 subpartition by list(subject)
 9 (
10 partition range1 values less than(60)
11      (
12       subpartition part1_1 values('maths','yuwen') tablespace AAA,
13       subpartition part1_2 values('english','wuli') tablespace BBB
14       ),
15 partition range2 values less than(80)
16       (
17       subpartition part2_1 values('maths','yuwen') tablespace AAA,
18       subpartition part2_2 values('huaxue') tablespace CCC
19       ),
20 partition range3 values less than(maxvalue)
21       (
22        subpartition part3_1 values('maths','yuwen') tablespace AAA,
23        subpartition part3_2 values('english','wuli') tablespace BBB,
24        subpartition part3_3 values('huaxue') tablespace CCC
25        )
26 );

分區表索引：3種類型，局部分區索引、全局分區索引和全局非分區索引

局部分區索引：

為分區表的各個分區單獨建立的索引，各個分區索引之間相互獨立的。

--先創建一個范圍分區表

 1 create table testscore(
 2 id      number primary key,
 3 name    varchar2(8),
 4 subject varchar2(10),
 5 score   number
 6 )
 7 partition by range(score)
 8 (
 9 partition range1 values less than(60) tablespace AAA,
10 partition range2 values less than(80) tablespace BBB,
11 partition range3 values less than(maxvalue) tablespace CCC
12 );

--再創建局部分區索引

1 create index index_testscore
2 on testscore(name) local
3 (
4 partition index1 tablespace AAA,
5 partition index2 tablespace BBB,
6 partition index3 tablespace CCC
7 );

全局分區索引：

對整個分區表建立的索引，全局分區索引的各個分區之間不是相互對立的。

1 create index global_index_testscore
2 on testscore(score)
3 Global partition by range(score)
4 (
5 partition range1 values less than(60) tablespace AAA,
6 partition range2 values less than(80) tablespace BBB,
7 partition range3 values less than(maxvalue) tablespace CCC
8 );

注意：使用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

1 alter table test
2 split partition range3 at(90)
3 into(
4      partition range5 tablespace AAA,
5      partition range6 tablespace BBB
6      );

為散列分區表增加分區：

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;