当前位置导航:炫浪网>>网络学院>>编程开发>>Oracle教程

关于exchange partition


  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
相关内容
赞助商链接