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;
--成功
----以上创建普通表的索引时,都是跟分区表的字段相对应
最新文章
- 隐私泄露杀手锏 —— Flash 权限反射
- zt:Linux查看程序端口占用情况
- Native code on Windows Phone 8(转)
- TextView展开和收回
- CSS + DIV 让页脚始终保持在页面底部
- 移动端(IOS)iframe监听不到 onscroll 事件
- 配置数据库连接池,Tomcat6.0 连接池的配置
- BZOJ 1449 JSOI2009 球队收益 费用流
- Swift 了解(1)
- 来杯咖啡看Pecan
- 【测试】Gunicorn , uWSGI同步异步测试以及应用场景总结
- ASP.NET Core - 关于Tag Helper值得了解的五点
- CSS布局学习(三) - Normal Flow 正常布局流(官网直译)
- redis 乐观锁实践秒杀
- 转载 html div三列布局占满全屏(左右两列定宽或者百分比、中间自动适应,div在父div中居底)
- ubuntu18.04虚拟机突然不能上网问题解决办法
- 移动平台的WebApp之Meta标签
- java继承-final关键词用法
- .NET 中字符文本中的字符太多 错误处理
- 嵌入式:FreeRTOS的使用(未完)
热门文章
- Ubuntu和Win7双系统,ubuntu被删,重新启动之后显示,no such partition
- 2011 ACM-ICPC 成都赛区A题 Alice and Bob (博弈动规)
- HDU 5371 Manacher
- 【bzoj2748】[HAOI2012]音量调节
- shell 爬虫 从日志分析到数据采集与分析
- 【Dairy】2016.11.5
- [HNOI2005]星际贸易
- [2017SEERC]Divide and Conquer
- C#面向过程之类型转换、算术运算符、关系运算符、逻辑运算符、if-else语句、switch-case、循环结构(while、for)、三元表达式
- js滚轮事件需要注意的兼容性问题