Oracle交换分区

交换分区注意事项：

• 要交换的分区表中不能含有自增列，否则可能无法交换分区；

• exchange的过程会以独占模式（exclusive）锁住两张表，不过执行速度很快，也不用担心阻塞业务dml语句。

• 还考虑ogg和dataguard的影响，都是一些对系统表的修改。

• 装载到历史表的时候，需要两端的表上的约束都要一致。这个过程修改的数据字典中的物理位置指向，那么必然要满足约束的要求，否则加载过去的数据违反了表上的主键约束或者唯一约束。

• 所有的索引都失效了，可见这种方法的弊端是虽然数据加载快速，但是索引需要重建（加上update global indexes也是重建），这个就比较致命了。如果表很大的话，分区的可用性会变差，日常交易性能衰退，恢复需要的时间长。

业务需求：

数据按财政年度分区，而且最后两年的数据在线。这个表有两个索引：一个是LOCAL索引，另一个是GLOBAL索引。现在正处于年末，我们想做下面的工作：

（1） 删除最旧的财政年度数据。我们不想永远地丢掉这个数据，而只是希望它老化，并将其归档。

（2） 增加最新的财政年度数据。加载、转换、建索引等工作需要一定的时间。我们想做这个工作，但是希望尽可能不影响当前数据的可用性。

创建partitioned表：

CREATE TABLE partitioned
( timestamp date,

id int)PARTITION BY RANGE (timestamp)
(
PARTITION fy_2004 VALUES LESS THAN( to_date('01-jan-2005','dd-mon-yyyy') ) ,
PARTITION fy_2005 VALUES LESS THAN( to_date('01-jan-2006','dd-mon-yyyy') ));

插入数据，

insert into partitioned partition(fy_2004)select to_date('31-12-2004', 'dd-mm-yyyy')-mod(rownum,360), object_id from all_objects;

insert into partitioned partition(fy_2005)select to_date('31-12-2005', 'dd-mm-yyyy')-mod(rownum,360), object_idfrom all_objects;

commit;

创建索引：

SQL> create index partitioned_idx_local on partitioned(id) LOCAL;


SQL> create index partitioned_idx_global on partitioned(timestamp) GLOBAL;

SQL> select ui.table_name, ui.index_name, ui.status  from user_indexes ui where ui.table_name = 'PARTITIONED';

TABLE_NAME                   INDEX_NAME                  STATUS

------------------------------ ------------------------------ --------

PARTITIONED                   PARTITIONED_IDX_LOCAL            N/A

PARTITIONED                   PARTITIONED_IDX_GLOBAL            VALID

分区操作：

为2004财政年度建立fy_2004空表并将使用这个表，与partitioned分区表中的FY_2004分区交换。

SQL> create table fy_2004 ( timestamp date, id int );

Table created.

SQL> create index fy_2004_idx on fy_2004(id);

分区前：

SQL> select count(*) from fy_2004;

  COUNT(*)

----------

       0

SQL> select count(*) from partitioned partition(fy_2004);

  COUNT(*)

----------

     72002

执行交换分区：

alter table partitioned exchange partition fy_2004 with table fy_2004 including indexes without validation;

exchange为DDL操作，要比DML大大的提升性能。分区fy_2004和空表fy_2004实现了交换，数据得到了交换。

注意：partitioned表的全局索引失效，需要重建索引。

SQL> alter index PARTITIONED_IDX_GLOBAL rebuild online;

将表交换进来，同时包括表的索引也交换进来，这个工作会立即完成；这是通过简单的数据字典更新实现的。增加空分区几乎不需要多少时间来处理。然后，将新创建的空分区与表交换，这个操作也会很快完成。新数据是在线的。

交换分区后：分区数据和分区的local索引都会被交换。

SQL> select count(*) from fy_2004;

  COUNT(*)

----------

     72002

SQL> select count(*) from partitioned partition(fy_2004);

  COUNT(*)

----------

       0

#交换分区后可以删除原表分区

SQL> alter table partitioned drop partition fy_2004 ;

将分区交换出去，可以删除旧的分区，同时分区交换时候也将分区索引交换出去。

查看索引，

SQL> select ui.table_name, ui.index_name, ui.status  from user_indexes ui where ui.table_name = 'PARTITIONED';

TABLE_NAME                   INDEX_NAME                  STATUS

------------------------------ ------------------------------ --------

PARTITIONED                   PARTITIONED_IDX_LOCAL            N/A

PARTITIONED                   PARTITIONED_IDX_GLOBAL            UNUSABLE

到此为止整个过程几乎不会带来任何停机时间，但是在我们重建全局索引时，如果分区数据量很大，需要相当长的时间才能完成。

SQL> alter index PARTITIONED_IDX_GLOBAL rebuild online;

如果查询依赖于这些索引，在此期间它们的运行时查询性能就会受到负面影响，可能根本不会运行，也可能运行时得不到索引提供的好处。所有数据都必须扫描，而且要根据数据重建整个索引。如果表的大小为数百GB，这会占用相当多的资源。

交换分区索引维护：UPDATE GLOBAL INDEXES

从Oracle9i开始，对于分区维护又增加了另一个选项：可以在分区操作期间使用UPDATE GLOBAL INDEXES子句来维护全局索引。

在你删除一个分区、分解一个分区以及在分区上执行任何必要的操作时，Oracle会对全局索引执行必要的修改，保证它是最新的。

由于大多数分区操作都会导致全局索引无效，这个特征对于需要提供数据连续访问的系统来说是一个大福音。

你会发现，通过牺牲分区操作的速度，可以换取100%的数据可用性（尽管分区操作的总体响应时间会更慢）。

简单地说，如果数据仓库不允许有停机时间，而且必须支持数据的滑入滑出等数据仓库技术，这个特性就再合适不过了，但是你必须了解它带来的影响。

执行交换分区操作，增加UPDATE GLOBAL INDEXES子句

SQL> alter table partitioned exchange partition fy_2004 with table fy_2004 including indexes without validation UPDATE GLOBAL INDEXES

删除分区操作，增加UPDATE GLOBAL INDEXES子句

SQL> alter table partitioned drop partition fy_2004 UPDATE GLOBAL INDEXES

引用URL:Oracle分区表的分区交换 - 墨天轮 (modb.pro)