oracle 分区，range-list，范围－列表复合分区

由于工作原因，需要了解一下oracle 分区，发现oracle 11g之后分区的种类变得更多了。

三种原始分区。

（1）范围分区（range）；

（2）哈希分区（hash）；

（3）列表分区（list）；

Range分区例子：

create table pdba (id number, time date) partition by range (time)

(

partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')),

partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')),

partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')),

partition p4 values less than (maxvalue)

)

Hash分区例子：

create table test

(

transaction_id number primary key,

item_id number(8) not null

)

partition by hash(transaction_id)

(

partition part_01 tablespace tablespace01,

partition part_02 tablespace tablespace02,

partition part_03 tablespace tablespace03

);

List分区例子：

create table custaddr
(

id varchar2(15 byte) not null,

areacode varchar2(4 byte)
)

partition by list (areacode)
( partition t_list025 values ('025'),
partition t_list372 values ('372') ,
partition t_list510 values ('510'),

partition p_other values (default)

)

oralce 11g在原始分区的基础上，可以实现父子分区，在三种原始分区的基础上，可以实现 3*3=9种，父子分区。

自主学习，创建分区和子分区

由于公司需要为表增加分区，我采用的形式range-list，因为此表是按照工作日进行增加数据。

做了一个例子：

create table bep_part_test
(zzzid varchar2(100) primary key,
work_date date,  
csbh varchar2(2)
)

partition by range (work_date)subpartition by list (csbh)

(partition bep_2006_07 values less than (to_date('2006-7-1','yyyy-mm-dd'))

(
subpartition bep_2006_07_10 values ('10'),
subpartition bep_2006_07_20 values ('20'),
subpartition bep_2006_07_30 values ('30'),
subpartition bep_2006_07_40 values ('40'),
subpartition bep_2006_07_50 values ('50'),
subpartition bep_2006_07_default values (default)
),

partition bep_2007_01 values less than (to_date('2007-1-1','yyyy-mm-dd'))

(
subpartition bep_2007_01_10 values ('10'),
subpartition bep_2007_01_20 values ('20'),
subpartition bep_2007_01_30 values ('30'),
subpartition bep_2007_01_40 values ('40'),
subpartition bep_2007_01_50 values ('50'),
subpartition bep_2007_01_default values (default)
),

partition bep_maxvalue values less than (maxvalue)
);
commit;

开始折腾子分区

我现在想在父分区bep_2007_01中增加一个字分区subpartition bep_2007_01_60 values ('60')；

alter table bep_part_test 
modify partition bep_2007_01 
add subpartition bep_2007_01_60 values ('60');
commit;

oracle 11g 这时报错了： ORA-14621:在DEFAULT子分区已存在时无法添加子分区

这时，第一个想法就是将默认子分区删除，并且重新构60的子分区，并且重新创建默认子分区。

哪如果默认子分区中存在数据。那数据将会去哪里？

先增加一条数据:

insert into bep_part_test values('111',to_date('21-9-2006','dd-mm-yyyy'),'60');
commit;

先查询父分区，

select * from bep_part_test partition(bep_2007_01)

查询出来数据
1    111    06/9/21    60

再查询子分区

select * from bep_part_test subpartition(bep_2007_01_default)

查询出来数据
1    111    06/9/21    60

删除子分区

alter table bep_part_test drop subpartition bep_2007_01_default; 
commit; 

这时查询父分区

select * from bep_part_test partition(bep_2007_01);

数据不见了。。。

然后查询表的所有数据

select * from bep_part_test

数据不见了。。。

这说明删除分区的同时会将分区内的数据同时删除。

这时，我再将删除的默认子分区加回去。

alter table bep_part_test modify partition bep_2007_01 add subpartition bep_2007_01_default values (default);
commit;

成功。

然后将刚刚那条记录插入回去。

insert into bep_part_test values('111',to_date('21-9-2006','dd-mm-yyyy'),'60');
commit;

这时，数据库报错了。

ORA-01502:索引'TIGER.SYS_C00248918'或这类索引的分区处于不可用状态。

还以为连数据都删除了呢。结果竟然插入不进去。

再查询无效索引

select <code class="sql plain">index_name ,status </code> from user_indexes where Status = 'UNUSABLE';

再将无效索引重铸

alter index  SYS_C00248918 rebuild ;
commit;

然后再插入了。成功了。

这时，我又将bep_2007_01_default 默认分区删除了。并且重铸了无效的索引。

再插入刚刚那个数据：

报错了：ORA-14400:插入的分区关键字未映射到任何分区。

说明没有默认分区，当超出子分区之后，还是会报错的。

我最开始的想法，是不应该以前的数据，并增加一个子分区，发现这样是不行的。

经过询问各种大神，找到办法了。现将原表还原。

然后插入数据。

再进行拆子分区。

alter table bep_part_test split 
subpartition bep_2007_01_default values ('60') into 
(subpartition bep_2007_01_60, subpartition bep_2007_01_default);
commit;

成功了。那么查询一下数据：

select * from bep_part_test subpartition(bep_2007_01_60)

数据出现了：
1    111    06/9/21    60

这样就在没有影响原有数据的情况下，拆分了子分区。

既然分区可以拆，那么分区应该也可以合并啊。

alter table bep_part_test merge subpartitions 
bep_2007_01_60,bep_2007_01_default into subpartition bep_2007_01_default;
commit;

合并成功查询数据：

select * from bep_part_test subpartition(bep_2007_01_default)

数据存在

再增加数据的时候

insert into bep_part_test values('11111',to_date('21-9-2006','dd-mm-yyyy'),'70');
commit;

报错了，之前的那个索引游离状态。需要重铸。

再次重新做合并的试验，发现合并后再插入同样出现索引游离状态，同样需要重铸一下。

为了不那么繁琐，可以直接在后面加上  update indexs

这样操作：

alter table bep_part_test merge subpartitions 
bep_2007_01_60,bep_2007_01_default into subpartition bep_2007_01_default  UPDATE INDEXES;
commit;

哇塞，是不是可以直接插入了。。不用重铸了。。。

这个子分区是折腾明白了。

开始折腾父分区吧

现在将默认的父分区进行拆开。

先插入两条数据：

insert into bep_part_test values('22',to_date('21-9-2007','dd-mm-yyyy'),'60');
commit;
insert into bep_part_test values('21',to_date('21-4-2007','dd-mm-yyyy'),'60');
commit;

查询一下数据：

select * from bep_part_test partition(bep_maxvalue);

数据两条

1    22    07/9/21    60
2    21    07/4/21    60

现在将父分区的最大默认分区，拆分成2007-07 和最大默认分区：

alter table bep_part_test split 
partition bep_maxvalue  at (to_date('2007-7-1','yyyy-mm-dd')) into 
(partition bep_2007_07, partition bep_maxvalue) UPDATE INDEXES;
commit;

拆分成功！

查询数据：

select * from bep_part_test partition(bep_maxvalue);

1    22    07/9/21    60

select * from bep_part_test partition(bep_2007_07);

1    21    07/4/21    60

数据显示正确！

最大值拆分没有问题。

现在要是拆分已有子分区的父分区，会成功吗？

先插入两条2006-08的数据

insert into bep_part_test values('281',to_date('22-8-2006','dd-mm-yyyy'),'60');
commit;
insert into bep_part_test values('282',to_date('21-8-2006','dd-mm-yyyy'),'60');
commit;

查询数据：

select * from bep_part_test partition(bep_2007_01);

1    1111    06/9/21    10
2    111    06/9/21    60
3    111211    06/9/21    60
4    11111    06/9/21    70
5    111111    06/9/21    70
6    281    06/8/22    60
7    282    06/8/21    60

有一些是之前做实验的数据。主要我是想从9月份进行拆这个父分区

拆分语句如下：

alter table bep_part_test split 
partition bep_2007_01  at (to_date('2006-9-1','yyyy-mm-dd')) into 
(partition bep_2006_09, partition bep_2007_01) UPDATE INDEXES;
commit;

查询数据：

select * from bep_part_test partition(bep_2007_01);

数据如下：

1    1111    06/9/21    10
2    111    06/9/21    60
3    111211    06/9/21    60
4    11111    06/9/21    70
5    111111    06/9/21    70

查询数据：

select * from bep_part_test partition(bep_2006_09);

数据如下：

1    281    06/8/22    60
2    282    06/8/21    60

说明拆分成功。

实验告一段落了。。。

喜欢这片文章的。。请点赞。。。