Exchanging Partitions 小测

普通索引和Global分区索引存在时都不能在交换时including indexes，即不能带索引交换，此时如存在Local分区索引，则索引状态为Unusable
UPDATE GLOBAL INDEXES选项对普通索引和Global分区索引进行rebuild，但对Local分区索引无效,非分区表的索引也都将UNUSABLE
WITHOUT VALIDATION选项将会把不符合分区规则的数据强制交换过来，这种情况就会出现交换后分区中的数据存在违背分区条件的情况
不带索引交换时也需要检查是否存在主键 如有则也需要在中间表创建，否则无法进行正常地交换。原因是当有数据的segment交换到对方，而对方存在主键导致无法检验约束

--测试过程！！！

insert into test_p
select * from test_p;
----
SQL> select count(*) from test_p;

COUNT(*)
----------
2788920
----
create table test_pt
( ID                  NUMBER not null,
RATER_TYPE          NUMBER(1) default 0)
PARTITION BY RANGE (rater_type)
(
PARTITION p_rated VALUES LESS THAN
( 1 ) ,
PARTITION p_rater VALUES LESS THAN
( 2 )
);
----
SQL> select count(*) from test_pt;
COUNT(*)         0
----

15:53:57 SQL> insert /*+ append */ into test_pt select * from test_p;
2788896 rows created.
Elapsed: 00:00:24.62

15:58:30 SQL> select count(*) from test_pt partition(p_rated);
COUNT(*)
----------
1292320

create table TEST_PT_RATED
( ID                  NUMBER not null,
RATER_TYPE          NUMBER(1) default 0
);

create table TEST_PT_RATER
( ID                  NUMBER not null,
RATER_TYPE          NUMBER(1) default 0
);

alter table TEST_PT
exchange partition p_rated
with table TEST_PT_RATED
without validation;

Table altered.
Elapsed: 00:00:00.03
--使用without validation一般会更快，实际做的动作只是更新了一下数据字典而已

alter table TEST_PT
exchange partition p_rater
with table TEST_PT_RATER;

Table altered.
Elapsed: 00:00:00.04
--不带without validation一样迅速，新表并不存在索引之类的玩意儿

SEGMENT_NAME                   segment_size(G) SEGMENT_TYPE
------------------------------ --------------- ------------------
TEST_PT                                      0 TABLE PARTITION
TEST_PT_RATER                              .45 TABLE
TEST_PT_RATED                              .47 TABLE

可以看到EXCHANGE PARTITION是非常迅速，而且并不需要额外的表空间！

create index idx_pt_id on TEST_PT(id);

create index idx_pt_rater_id on TEST_PT_RATER(id);

create index idx_pt_rated_id on TEST_PT_RATED(id);

16:56:11 SQL> alter table TEST_PT
16:57:41   2 exchange partition p_rated
16:57:41   3 with table TEST_PT_RATED
16:57:41   4 including indexes
16:57:41   5 without validation;
with table TEST_PT_RATED
*
ERROR at line 3:
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
--存在index名字不一样的索引是不允许带including indexes交换

Elapsed: 00:00:00.02
16:57:42 SQL> alter table TEST_PT
16:58:05   2 exchange partition p_rated
16:58:05   3 with table TEST_PT_RATED
16:58:05   4 without validation;
Table altered.
Elapsed: 00:00:00.05

alter index IDX_PT_RATED_ID rebuild;

---
drop index idx_pt_rater_id;

alter table TEST_PT
exchange partition p_rater
with table TEST_PT_RATER
including indexes
without validation;

Table altered.
Elapsed: 00:00:00.03

----
以前的记录： http://hi.baidu.com/dbaeyes/blog/item/83408bf2d9b05011b07ec5cc.html
ORACLE DOC http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/partiti.htm#sthref2762

Exchanging Partitions

You can convert a partition (or subpartition) into a non-partitioned table, and a non-partitioned table into a partition (or subpartition) of a partitioned table by exchanging their data segments. You can also convert a hash-partitioned table into a partition of a range-hash partitioned table, or convert the partition of the range-hash partitioned table into a hash-partitioned table. Similarly, you can convert a list-partitioned table into a partition of a range-list partitioned table, or convert the partition of the range-list partitioned table into a list-partitioned table

Exchanging table partitions is most useful when you have an application using non-partitioned tables that you want to convert to partitions of a partitioned table. For example, in data warehousing environments exchanging partitions facilitates high-speed data loading of new, incremental data into an already existing partitioned table. Generically, OLTP as well as data warehousing environments benefit from exchanging old data partitions out of a partitioned table. The data is purged from the partitioned table without actually being deleted and can be archived separately afterwards.

When you exchange partitions, logging attributes are preserved. You can optionally specify if local indexes are also to be exchanged (INCLUDING INDEXESclause), and if rows are to be validated for proper mapping (WITH VALIDATION clause).

ALTER TABLE table_name 
     DISABLE CONSTRAINT constraint_name KEEP INDEX