exchange partition提供了快速轉換普通成分區表的方法，它通過更新數據字典來實現分區與普通表的置換，所以速度相當快。
create table t1 as select sysdate dt, all_objects.* from all_objects;
create table t2 as select sysdate dt, all_objects.* from all_objects;
create table t3 as select add_months(sysdate,-24) dt, all_objects.* from all_objects;
create table t( dt, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED,
LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY )
partition by range(dt) (
partition part2003 values less than ( to_date( '01-jan-2004',
'dd-mon-yyyy') ),
partition part2004 values less than ( to_date( '01-jan-2005',
'dd-mon-yyyy') ),
partition part2005 values less than ( to_date( '01-jan-2006',
'dd-mon-yyyy') )
)
as
select sysdate dt, all_objects.* from all_objects where 1=0;
/
SQL 10G>set timing on
SQL 10G>alter table t
2 exchange partition part2003
3 with table t3
4 /
Table altered.
Elapsed: 00:00:00.07
SQL 10G>alter table t
2 exchange partition part2005
3 with table t1
4 /
Table altered.
Elapsed: 00:00:00.07
SQL 10G>alter table t
2 exchange partition part2004
3 with table t2
4 /
with table t2
*
ERROR at line 3:
ORA-14099: all rows in table do not qualify for specified partition
Elapsed: 00:00:00.00
因為t2不符合分區規則，所以當進行exchange將會報錯，我們可以指定without validation子句來強行禁止Oracle檢查合理性
SQL 10G>alter table t
2 exchange partition part2004
3 with table t2
4 without validation
5 /
Table altered.
Elapsed: 00:00:00.03
由于without validation不需要校驗數據的正確性，所以不會對t2做全表掃描，因此exchange的時間將會縮短，without validation子句在進行大表的exchange時效率將會非凡高，假如你能確定普通表數據的正確性，那么請大膽得使用without validation吧。
我們也可以看一下當驗證數據正確性的時候oracle內部是怎么做的
通過10046 trace events，我們可以看到當進行exchange partition with validation時
select 1 from "T1" where TBL$OR$IDX$PART$NUM("T", 0, 3,1048576,"DT") != 58626;
select 1 from "T2" where TBL$OR$IDX$PART$NUM("T", 0, 3,1048576,"DT") != 58625;
select 1 from "T3" where TBL$OR$IDX$PART$NUM("T", 0, 3,1048576,"DT") != 58624;
oracle通過TBL$OR$IDX$PART$NUM函數來判定是否普通表滿足分區置換的條件
SQL 10G>select count(*) from "T1" where TBL$OR$IDX$PART$NUM("T", 0, 3,1048576,"DT") != 58626;
COUNT(*)
----------
0
Elapsed: 00:00:00.00
SQL 10G>select count(*) from "T2" where TBL$OR$IDX$PART$NUM("T", 0, 3,1048576,"DT") !
= 58625;
COUNT(*)
----------
49496
Elapsed: 00:00:00.04
SQL 10G>select count(*) from "T3" where TBL$OR$IDX$PART$NUM("T", 0, 3,1048576,"DT") != 58624;
COUNT(*)
----------
0
t2是不符合分區條件的，所以exchange的時候會報
ORA-14099: all rows in table do not qualify for specified partition
