oracle分区交换(exchange)技术

需求:一个几亿条数据的分区表,要把一些老数据分出来,放一个新建的表上。按时间划分,指定时间以前的放在老数据表,指定时间以后的数据保留在原表,然后把备份老数据表空间truncate掉。按照分区操作分区交换是最好的解决办法。

10年的郸城网站建设经验,针对设计、前端、开发、售后、文案、推广等六对一服务,响应快,48小时及时工作处理。全网营销推广的优势是能够根据用户设备显示端的尺寸不同,自动调整郸城建站的显示方式,使网站能够适用不同显示终端,在浏览器中调整网站的宽度,无论在任何一种浏览器上浏览网站,都能展现优雅布局与设计,从而大程度地提升浏览体验。创新互联建站从事“郸城网站设计”,“郸城网站推广”以来,每个客户项目都认真落实执行。

分区交换技术可以实现数据快速转移,所以在数据加载提速,历史数据清理等方面特别有用。分区交换技术实际上只修改了数据字典中的数据物理段位置,而不是实际的移动数据,所以速度很快。

创建分区表:
create table t_exchange
(
sno number not null primary key,
oitime date DEFAULT sysdate,
word  varchar2(100)
)
PARTITION BY range(sno)
( PARTITION p1 VALUES LESS THAN (10000) tablespace TBS_MING,
 PARTITION p2 VALUES LESS THAN (20000) tablespace TBS_MING,
 PARTITION p3 VALUES LESS THAN (maxvalue) tablespace TBS_MING
) tablespace TBS_MING;

历史分区表:
create table t_exchange_his
(
sno number not null primary key,
oitime date DEFAULT sysdate,
word  varchar2(100)
)
PARTITION BY range(sno)
( PARTITION p1 VALUES LESS THAN (10000) tablespace TBS_MING,
 PARTITION p2 VALUES LESS THAN (20000) tablespace TBS_MING,
 PARTITION p3 VALUES LESS THAN (maxvalue) tablespace TBS_MING
) tablespace TBS_MING;

插入数据:
begin
   for i in 1..30000 loop
     insert into t_exchange values(i,sysdate,lpad('mingshuo',100,'x'));
   end loop;
dbms_output.put_line('success!');
commit;
end; 
/

创建一些不同类型的索引,观察分区交换是不是会对索引产生影响:
源表:
创建全局分区索引:
create index t_exchange_idx_01 on t_exchange(sno,oitime,word)
GLOBAL  partition by  range(sno)
(
 partition p01 values less than(10000) tablespace TBS_MING,
 partition p02 values less than(20000) tablespace TBS_MING,
 partition p03 values less than(MAXVALUE) tablespace TBS_MING
);

创建本地非前缀索引:
create index t_exchange_idx_02 on t_exchange(word) local;

创建唯一索引:
create unique index t_exchange_idx_03 on t_exchange(sno,oitime);

历史表:
创建全局分区索引:
create index t_exchange_his_idx_01 on t_exchange_his(sno,oitime,word)
GLOBAL  partition by  range(sno)
(
 partition p01 values less than(10000) tablespace TBS_MING,
 partition p02 values less than(20000) tablespace TBS_MING,
 partition p03 values less than(MAXVALUE) tablespace TBS_MING
);

创建本地非前缀分区索引:
create index t_exchange__his_idx_02 on t_exchange_his(word) local;

源表与历史表的区别在于没有创建唯一索引。
创建中间表:
create table t_exchange_tmp as select * from t_exchange where 1=2; SQL> !ora ddl ming table t_exchange_tmp
\n=============Fri Jun 22 19:31:13 CST 2018===================

Session altered. DBMS_METADATA.GET_DDL(UPPER('TABLE'),UPPER('T_EXCHANGE_TMP'),UPPER('MING'))
-------------------------------------------------------------------------------

 CREATE TABLE "MING"."T_EXCHANGE_TMP"
  (    "SNO" NUMBER NOT NULL ENABLE,
       "OITIME" DATE,
       "WORD" VARCHAR2(100)
  ) SEGMENT CREATION DEFERRED
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
 TABLESPACE "TBS_MING"
插入数据之前开启10046事件跟踪:
EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(50, 419, true);
将数据装载到中间表:
alter table t_exchange exchange partition p1 with table t_exchange_tmp; 此时的数据:
SQL> select count(*) from t_exchange_tmp;

 COUNT(*)
----------
     9999

SQL> select count(*) from t_exchange partition(p1);

 COUNT(*)
----------
        0 将数据加载到历史表:
alter table t_exchange_his exchange partition p1 with table t_exchange_tmp;

SQL> alter table t_exchange_his exchange partition p1 with table t_exchange_tmp;
alter table t_exchange_his exchange partition p1 with table t_exchange_tmp
*
ERROR at line 1:
ORA-14130: UNIQUE constraints mismatch in ALTER TABLE EXCHANGE PARTITION

创建唯一索引:
create unique index t_exchange_his_idx_03 on t_exchange_his(sno,oitime); 再次加载数据,还是同样地错。
观察一下历史表和中间表的定义,unique约束只能是sno的主键约束了,这个说法不对,只是说明可能是主键约束的缘故,中间表增加主键约束:
alter table t_exchange_tmp add primary key (sno);

再次加载数据:
SQL> alter table t_exchange_his exchange partition p1 with table t_exchange_tmp;

Table altered.
成功! 关闭10046事件:
EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(50, 419, false); 数据已经成功从中间表到了历史表:
SQL> select count(*) from t_exchange_tmp;

 COUNT(*)
----------
        0

SQL> select count(*) from t_exchange_his partition(p1);

 COUNT(*)
----------
     9999 检查索引:  
SQL> !ora unusable
\n=============Fri Jun 22 19:55:13 CST 2018===================

Session altered. UNUSABLE_INDEXES
-------------------------------------------------------------------------
ALTER INDEX MING.SYS_C0012382 REBUILD ONLINE;             --中间表主键索引
ALTER INDEX MING.T_EXCHANGE_HIS_IDX_03 REBUILD ONLINE;    --历史表的唯一索引
ALTER INDEX MING.SYS_C0012380 REBUILD ONLINE;             --历史表主键索引
ALTER INDEX MING.T_EXCHANGE_IDX_03 REBUILD ONLINE;        --源表的唯一索引
ALTER INDEX MING.SYS_C0012378 REBUILD ONLINE;             --源表主键索引
ALTER INDEX MING.T_EXCHANGE__HIS_IDX_02 REBUILD PARTITION P1 ONLINE;   --历史表的本地非前缀分区索引
ALTER INDEX MING.T_EXCHANGE_HIS_IDX_01 REBUILD PARTITION P03 ONLINE;   --历史表的全局分区索引
ALTER INDEX MING.T_EXCHANGE_HIS_IDX_01 REBUILD PARTITION P02 ONLINE;
ALTER INDEX MING.T_EXCHANGE_IDX_01 REBUILD PARTITION P01 ONLINE;       --源表的全局分区索引
ALTER INDEX MING.T_EXCHANGE_IDX_02 REBUILD PARTITION P1 ONLINE;        --源表的本地非前缀分区索引
ALTER INDEX MING.T_EXCHANGE_HIS_IDX_01 REBUILD PARTITION P01 ONLINE;
ALTER INDEX MING.T_EXCHANGE_IDX_01 REBUILD PARTITION P03 ONLINE;
ALTER INDEX MING.T_EXCHANGE_IDX_01 REBUILD PARTITION P02 ONLINE;

13 rows selected.  
 
 
总结:
1.从10046中可以看到exchange的过程会以独占模式(exclusive)锁住两张表,不过执行速度很快,也不用担心阻塞业务dml语句。
2.10046中还考虑ogg和dataguard的影响,都是一些对系统表的修改。
3.装载到历史表的时候,需要两端的表上的约束都要一致。其实也很好理解,这个过程修改的数据字典中的物理位置指向,那么必然要满足约束的要求,否则加载过去的数据违反了表上的主键约束或者唯一约束,那就没有意义了。
4.所有的索引都失效了,可见这种方法的弊端是虽然数据加载快速,但是索引需要重建,这个就比较致命了。如果表很大的话,分区的可用性会变差,日常交易性能衰退,恢复需要的时间长。


名称栏目:oracle分区交换(exchange)技术
URL链接:http://scyanting.com/article/jejjhh.html