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

说明拆分成功。

实验告一段落了。。。

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




















评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值