关于exchangepartition
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 10Gset timing on
SQL 10Galter table t
2
exchange partition part2003
3
with table t3
4
/
Table altered.
Elapsed: 00:00:00.07
SQL 10Galter table t
2
exchange partition part2005
3
with table t1
4
/
Table altered.
Elapsed: 00:00:00.07
SQL 10Galter 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 10Galter 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 10Gselect count(*) from "T1" where TBL$OR$IDX$PART$NUM("T", 0, 3,1048576,"DT") != 58626;
COUNT(*)
----------
0
Elapsed: 00:00:00.00
SQL 10Gselect count(*) from "T2" where TBL$OR$IDX$PART$NUM("T", 0, 3,1048576,"DT") != 58625;
COUNT(*)
----------
49496
Elapsed: 00:00:00.04
SQL 10Gselect 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