exchange partition

exchange partition提供了快速转换普通成分区表的方法，它通过更新数据字典来实现分区与普通表的置换，所以速度相当快。[@more@]

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/60217/viewspace-890796/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/60217/viewspace-890796/