用Partition Exchange(分区交换)卸载数据-优快云博客

本文介绍了一种使用分区交换技术优化大规模数据迁移的方法，避免了传统存储过程带来的性能瓶颈。通过对源表、中间表和历史表的设计及索引匹配，实现了高效的数据迁移。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

我们有个应用每天操作相关的一张核心表t_ms_mdeia_task，此表是按天来做的List分区,分区键为monthday，列值类似于‘mmdd’,共有366个分区。
每天的数据量在3千万以上，应用的要求是只保存31天，之前的全部迁移到历史表中，用于查询。也就是说每天要把31天前那一天的数据迁移到hist表里面,
当然hist表也是分区的。
应用侧原先通过存储过程来大批量的进行删除和insert,对于这样的操作,而且过程搞的非常复杂且影响数据库性能。

看提交的的过程：

CREATE OR REPLACE PROCEDURE P_MS_MOVEMEDIATASK(
   i_time DATE
)
AS
  v_time        DATE;
  v_beginTime   DATE;
  v_monthday    VARCHAR2(4);
  v_month       VARCHAR2(2);
  v_count       NUMBER;
  v_sql         VARCHAR2(5000);
  v_ret         VARCHAR2(10);
  v_errMsg      VARCHAR2(500);
  v_total       NUMBER;
BEGIN
  v_beginTime := SYSDATE;
  --转移31天前的数据
  v_time := i_time;
  --取转移数据的monthday
  v_monthday := to_char(v_time,'mmdd');
  --取转移数据的月份，按月分转移到不同的表里时用
  v_month := to_char(v_time,'mm');
  --
  SELECT COUNT(1)
        INTO v_count
        FROM T_MS_MEDIA_TASK T
        WHERE T.MONTHDAY = v_monthday;
  v_total := v_count;
  WHILE(v_count > 0) LOOP
     --将要转移的数据RESERVE79字段置0
      UPDATE T_MS_MEDIA_TASK T
        SET T.RESERVE79 = '0'
        WHERE T.MONTHDAY = v_monthday
        AND ROWNUM <= 50000; --每次最多提交50000条

      --将待转移数据插入历史表中  按月插入不同历史表 动态SQL
      v_sql := 'insert into t_ms_media_task_his'||v_month
 ...
 ...
      EXECUTE IMMEDIATE v_sql;

      --删除转移过的数据
      DELETE FROM T_MS_MEDIA_TASK T
        WHERE T.MONTHDAY = v_monthday
        AND T.RESERVE79 = '0';
      COMMIT;
      --获取需要转移的数据条数
      SELECT COUNT(1)
        INTO v_count
        FROM T_MS_MEDIA_TASK T
        WHERE T.MONTHDAY = v_monthday;
  END LOOP;
  EXCEPTION
   ...
END P_MS_MOVEMEDIATASK;
/

就这个操作，每次的select都要扫描单分区，几千万的数据,以上过程中每50000条提交一次，后面还有大量的delete。
SELECT COUNT(1)
INTO v_count
FROM T_MS_MEDIA_TASK T
WHERE T.MONTHDAY = v_monthday;
处于各方面的考虑，我们没有允许这样的操作在数据库中正式上线,最后和他们经过沟通之后，确定了如下的方案：
采用partition exchange来达到目的，大致思路如下：
1,源表是list分区表,表上无global index,全部是local index。
2,创建一张中间表,结构和源表,目标端的hist表一致。
3,在中间表上创建与源表一致的索引。
4,hist(分区表)中的结构索引和源表中一致。
5,先把源分区交换到中间表，在把中间表交换到历史表中。

我的测试代码如下：

SQL> create table test1
  2  (vid number ,
  3  v_date number,
  4  v_data varchar2(100))
  5  partition by range(v_date)
  6  (partition day_1 values less than (2 ),
  7  partition day_2 values less than (3 ),
  8  partition day_3 values less than (4 ),
  9  partition day_4 values less than (5 ),
 10  partition day_5 values less than (6 ),
 11  partition day_6 values less than (7 ),
 12  partition day_7 values less than (8 ),
 13  partition day_8 values less than (9 ),
 14  partition day_9 values less than (10),
 15  partition day_10 values less than (11),
 16  partition day_11 values less than (12),
 17  partition day_12 values less than (13),
 18  partition day_13 values less than (14),
 19  partition day_14 values less than (15),
 20  partition day_15 values less than (16),
 21  partition day_16 values less than (17),
 22  partition day_17 values less than (18),
 23  partition day_18 values less than (19),
 24  partition day_19 values less than (20),
 25  partition day_20 values less than (21),
 26  partition day_21 values less than (22),
 27  partition day_22 values less than (23),
 28  partition day_23 values less than (24),
 29  partition day_24 values less than (25),
 30  partition day_25 values less than (26),
 31  partition day_26 values less than (27),
 32  partition day_27 values less than (28),
 33  partition day_28 values less than (29),
 34  partition day_29 values less than (30),
 35  partition day_30 values less than (31),
 36  partition day_31 values less than (32), 
 37  partition day_max values less than (maxvalue)
 38 );   

Table created.

SQL> SQL> insert into test1 
  2  (vid,
  3  v_date,
  4  v_data)
  5  select 
  6  level ,
  7  mod(level,31)+1,
  8  lpad(level,60,'0')
  9  from dual 
 10  connect by level<=100000;

100000 rows created.

SQL>commit; 
Commit complete.

SQL> create index idx_1 on test1(vid) local;
SQL>create index idx_2 on test1(vid,v_date) local;

SQL> create table test2 as select*from test1 where 1=2;
SQL> create index idx_3 on test2(vid,v_date);

SQL> create table test3
  2  (vid number ,
  3  v_date number,
  4  v_data varchar2(100))
  5  partition by range(v_date)
  6  (partition day_1 values less than (2 ),
  7  partition day_2 values less than (3 ),
  8  partition day_3 values less than (4 ),
  9  partition day_4 values less than (5 ),
 10  partition day_5 values less than (6 ),
 11  partition day_6 values less than (7 ),
 12  partition day_7 values less than (8 ),
 13  partition day_8 values less than (9 ),
 14  partition day_9 values less than (10),
 15  partition day_10 values less than (11),
 16  partition day_11 values less than (12),
 17  partition day_12 values less than (13),
 18  partition day_13 values less than (14),
 19  partition day_14 values less than (15),
 20  partition day_15 values less than (16),
 21  partition day_16 values less than (17),
 22  partition day_17 values less than (18),
 23  partition day_18 values less than (19),
 24  partition day_19 values less than (20),
 25  partition day_20 values less than (21),
 26  partition day_21 values less than (22),
 27  partition day_22 values less than (23),
 28  partition day_23 values less than (24),
 29  partition day_24 values less than (25),
 30  partition day_25 values less than (26),
 31  partition day_26 values less than (27),
 32  partition day_27 values less than (28),
 33  partition day_28 values less than (29),
 34  partition day_29 values less than (30),
 35  partition day_30 values less than (31),
 36  partition day_31 values less than (32), 
 37  partition day_max values less than (maxvalue)
 38  );

Table created.

SQL>  alter table test1 exchange partition day_1 with table test2 including indexes;
 alter table test1 exchange partition day_1 with table test2 including indexes
                                                       *
ERROR at line 1:
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION


SQL> !oerr ora 14098
14098, 00000, "index mismatch for tables in ALTER TABLE EXCHANGE PARTITION"
// *Cause:  The two tables specified in the EXCHANGE have indexes which are
//          not equivalent
// *Action: Ensure that the indexes for the two tables have indexes which
//          follow this rule
//          For every non partitioned index for the non partitioned table,
//          there has to be an identical LOCAL index on the partitioned 
//          table and vice versa. By identical, the column position, type
//          and size have to be the same.

SQL> create index idx_4 on test2(vid);

Index created.

SQL> alter table test1 exchange partition day_1 with table test2 including indexes;

Table altered.

SQL> select DISTINCT index_name,status 
  2  From dba_ind_partitions 
  3  where index_name IN('IDX_1','IDX_2','IDX_3','IDX_4');

INDEX_NAME                     STATUS
------------------------------ --------
IDX_1                          USABLE
IDX_2                          USABLE

SQL> select INDEX_NAME,STATUS 
  2  from dba_indexes 
  3  where index_name IN('IDX_1','IDX_2','IDX_3','IDX_4');

INDEX_NAME                     STATUS
------------------------------ --------
IDX_4                          VALID
IDX_1                          N/A
IDX_2                          N/A
IDX_3                          VALID

SQL> create index idx_5 on test3(vid) local;

Index created.

SQL> create index idx_6 on test3(vid,v_date) local;

Index created.

SQL> alter table test3 exchange partition day_1 with table test2 including indexes;

Table altered.

SQL> select DISTINCT index_name,status 
  2  From dba_ind_partitions 
  3  where index_name IN('IDX_1','IDX_2','IDX_3','IDX_4','IDX_5','IDX_6');

INDEX_NAME                     STATUS
------------------------------ --------
IDX_1                          USABLE
IDX_2                          USABLE
IDX_5                          USABLE
IDX_6                          USABLE

SQL> select INDEX_NAME,STATUS 
  2  from dba_indexes 
  3  where index_name IN('IDX_1','IDX_2','IDX_3','IDX_4','IDX_5','IDX_6');

INDEX_NAME                     STATUS
------------------------------ --------
IDX_6                          N/A
IDX_4                          VALID
IDX_5                          N/A
IDX_1                          N/A
IDX_2                          N/A
IDX_3                          VALID

简单的测试完后，在生产测试环境中又测试一番，可以实施成功.方案确定，通过编写存储过程，建立job来定时完成任务

CREATE OR REPLACE PROCEDURE P_MS_MOVEMEDIATASK
AS
  v_time        DATE;
  v_month       VARCHAR2(2);
  v_day         VARCHAR2(2);
  v_count       NUMBER;
  v_sql         VARCHAR2(5000);
  v_ret         VARCHAR2(10);
  v_errMsg      VARCHAR2(500);
BEGIN
  --转移31天前的数据
  v_time := sysdate-31; 
  --取转移数据的月份，按月分转移到不同的表里时用
  v_month := to_char(v_time,'mm');
  v_day   := to_char(v_time,'dd');
  ...
  --如果历史表该天分区中有数据，则不做转移
  v_sql := 'SELECT COUNT(1) INTO :v_count FROM T_MS_MEDIA_TASK_HIS'||v_month||' PARTITION (P'||v_month||'_'||v_day||') WHERE ROWNUM=1';
  --dbms_output.put_line(v_sql);
  EXECUTE IMMEDIATE v_sql into v_count;
  
  IF v_count > 0 THEN
    v_ret := '99999';
    v_errMsg := '历史表T_MS_MEDIA_TASK_HIS'||v_month||'中P'||v_month||'_'||v_day||'分区存在数据,'||v_month||v_day||'数据未进行转移';
    INSERT INTO T_MS_DBSTATE(CREATEDATE, ERRORCODE, ERRORINFO, MODULENAME)
           VALUES(SYSDATE, v_ret, v_errMsg, 'P_MS_MOVEMEDIATASK');
    COMMIT;
    RETURN; --终止存储过程
  END IF;
  
  --进行分区交换 T_MS_MEDIA_TASK <==> T_MS_MEDIA_TASK_CEN
  v_sql := 'ALTER TABLE T_MS_MEDIA_TASK EXCHANGE PARTITION P'||v_month||'_'||v_day||' WITH TABLE T_MS_MEDIA_TASK_CEN INCLUDING INDEXES';
  --dbms_output.put_line(v_sql);
  EXECUTE IMMEDIATE v_sql;
  
  --进行分区交换T_MS_MEDIA_TASK_HIS?? <==> T_MS_MEDIA_TASK_CEN
  v_sql := 'ALTER TABLE T_MS_MEDIA_TASK_HIS'||v_month||' EXCHANGE PARTITION P'||v_month||'_'||v_day||' WITH TABLE T_MS_MEDIA_TASK_CEN INCLUDING INDEXES';
  --dbms_output.put_line(v_sql);
  EXECUTE IMMEDIATE v_sql;
  ...

  EXCEPTION
   ...
END P_MS_MOVEMEDIATASK;
/