oracle 入门札记-分区表的分区交换
本文参考来自作者：蓝紫
详细内容请阅读原文 ： http://www.cnblogs.com/lanzi/archive/2013/01/24/2875838.html
在oracle 11.2环境下测试
--drop table tab_a purge;
--创建分区表
create table tab_a
(
r_id number(19) primary key,
r_name varchar2(300),
r_pat integer
)
partition by list (r_pat)
(
partition p_value1 values(1),
partition p_value2 values(2),
partition p_value3 values(3),
partition p_def values(default)
)
;
--创建普遍表
create table tab_b as select * from tab_a;
--创建主键
alter table tab_b add primary key(r_id);
---插入测试数据
insert into tab_a
(r_id,r_name,r_pat)
select a.OBJECT_ID,a.OBJECT_NAME,1 from all_objects a;
insert into tab_a select 99199999,'test',1 from dual;
commit;
--测试分区交换
----1.分区表，创建了主键索引，普通表没有创建，则
alter table tab_a exchange partition p_value1 with table tab_b including indexes without validation
--ORA-14097: ALTER TABLE EXCHANGE PARTITION 中的列类型或大小不匹配
---2.分区表,创建了主键索引，普通表也创建全局索引则
alter table tab_b add primary key(r_id);
alter table tab_a exchange partition p_value1 with table tab_b including indexes without validation update global indexes;
--ORA-14098: ALTER TABLE EXCHANGE PARTITION 中的表索引不匹配
--3.分区表,创建了主键索引，普通表也创建主键索引则,且不包含索引交换
alter table tab_a exchange partition p_value1 with table tab_b /*including indexes*/ without validation /*update global indexes*/;
--成功---
--但是 insert into tab_a select 99199999,'test',1 from dual;
--ORA-01502: 索引 'SCOTT.SYS_C0012090' 或这类索引的分区处于不可用状态
---这时需要重建索引才能更新数据 ----
alter index SCOTT.SYS_C0012090 rebuild;
insert into tab_a select 99399999,'test',1 from dual;
---成功，，，
--4.分区表,创建了主键索引，普通表也创建主键索引则,且不包含索引交换，更新全局索引
truncate table tab_a;(/*清理分区不行*/)
insert into tab_a select 99199999,'test',1 from dual;
commit;
alter table tab_a exchange partition p_value1 with table tab_b /*including indexes*/ without validation update global indexes;
insert into tab_a select 99399999,'test',1 from dual;
---成功，，，
---但是更新普通表数据时，insert into tab_b select 99399999,'test',1 from dual 时，
--ORA-01502: 索引 'SCOTT.SYS_C0012090' 或这类索引的分区处于不可用状态
--------------------------
---5.在分区表的非分区表键上建立全局索引，普通表也建立全局索引
alter table tab_a drop primary key;
alter table tab_b drop primary key;
create index idx_a_r_id on tab_a(r_id) ;
create index idx_b_r_id on tab_b(r_id) ;
alter table tab_a exchange partition p_value1 with table tab_b including indexes without validation;
-----ORA-14098: ALTER TABLE EXCHANGE PARTITION 中的表索引不匹配
---6.在分区表的非分区表键上建立全局索引，普通表不建立全局索引
drop index idx_b_r_id;
alter table tab_a exchange partition p_value1 with table tab_b including indexes without validation;
---成功
-----7.在分区表的非分区键上建立本地索引，普通表不创建索引
drop index idx_a_r_id;
create index idx_a_r_id on tab_a(r_id) local;
alter table tab_a exchange partition p_value1 with table tab_b including indexes without validation;
--ORA-14098: ALTER TABLE EXCHANGE PARTITION 中的表索引不匹配
-----8.在分区表的非分区键上建立本地索引，普通表创建索引
create index idx_b_r_id on tab_b(r_id) ;
alter table tab_a exchange partition p_value1 with table tab_b including indexes without validation;
---成功
---9.在分区表的分区键上创建全局索引，普通表创建索引
drop index idx_a_r_id;
drop index idx_b_r_id;
create index idx_a_r_pat on tab_a(r_pat) ;
create index idx_b_r_pat on tab_b(r_pat) ;
alter table tab_a exchange partition p_value1 with table tab_b including indexes without validation;
--ORA-14098: ALTER TABLE EXCHANGE PARTITION 中的表索引不匹配
---10.在分区表的分区键上创建全局索引，普通表不创建索引
drop index idx_a_r_pat;
drop index idx_b_r_pat;
create index idx_a_r_pat on tab_a(r_pat) ;
alter table tab_a exchange partition p_value1 with table tab_b including indexes without validation;
---成功
-----11.在分区表的分区键上创建本地索引，普通表不创建索引
drop index idx_a_r_pat;
create index idx_a_r_pat on tab_a(r_pat) local;
alter table tab_a exchange partition p_value1 with table tab_b including indexes without validation;
--ORA-14098: ALTER TABLE EXCHANGE PARTITION 中的表索引不匹配
----12.在分区表的分区键上创建本地索引，普通表创建索引
create index idx_b_r_pat on tab_b(r_pat) ;
alter table tab_a exchange partition p_value1 with table tab_b including indexes without validation;
---成功
---
---13.在分区表上的分区键和非分区键上创建全局索引，普通表上创建索引
drop index idx_a_r_id;
drop index idx_b_r_id;
create index idx_a_r_id on tab_a (r_id,r_pat);
create index idx_b_r_id on tab_b (r_id,r_pat);
alter table tab_a exchange partition p_value1 with table tab_b including indexes without validation;
--报错
---14.在分区表上的分区键和非分区键上创建全局索引，普通表不创建索引
drop index idx_b_r_id;
alter table tab_a exchange partition p_value1 with table tab_b including indexes without validation;
---成功
--15.在分区表上的分区键和非分区键上创建本地索引，普通表不创建索引
drop index idx_a_r_id;
create index idx_a_r_id on tab_a(r_id,r_pat) local;
alter table tab_a exchange partition p_value1 with table tab_b including indexes without validation;
---报错
--16.在分区表上的分区键和非分区键上创建本地索引，普通表创建索引
create index idx_b_r_id on tab_b(r_id,r_pat) ;
alter table tab_a exchange partition p_value1 with table tab_b including indexes without validation;
---成功
---17.在分区表上的分区键和非分区键上创建本地索引a，同时又在非分区键上创全局建索引b,普通表对应字段上都创建索引
drop index idx_a_r_id;
drop index idx_b_r_id;
create index idx_a_r_id on tab_a(r_id);
create index idx_b_r_id on tab_b(r_id);
create index idx_a_r_id_loc on tab_a(r_id,r_pat) local;
create index idx_b_r_id_loc on tab_b(r_id,r_pat) ;
alter table tab_a exchange partition p_value1 with table tab_b including indexes without validation;
--报错
---18.在分区表上的分区键和非分区键上创建本地索引a，同时又在非分区键上创建全局索引b,
--在普通表对应的分区表上的本地索引a的字段上创建索引，同时分区表的全局索引对应的字段上不创建索引
drop index idx_a_r_id;
drop index idx_b_r_id;
drop index idx_a_r_id _loc;
drop index idx_b_r_id _loc;
create index idx_a_r_id on tab_a(r_id);
create index i idx_a_r_id_loc on tab_a (r_id,r_pat) local;
create index idx_b_r_id _loc on tab_b (r_id,r_pat) ;
alter table tab_a exchange partition p_value1 with table tab_b including indexes without validation;
--成功
----以上创建普通表的索引时，都是跟分区表的字段相对应