Oracle Vs MsSQL 之交换分区

近日，看到网友[yangtingkun]的“EXCHANGE分区导致主键重复”（http://yangtingkun.itpub.net/post/468/526325），因为MsSQL仅支持RANGE分区，故作了修改，下面先重现一下问题：

SQL> create table t_part (id number, name varchar2(30))

  2  partition by range (id)

  3  (partition p1 values less than (10),

  4  partition p2 values less than (100),

  5  partition p3 values less than (maxvalue));

 

SQL> create index idx_t_part on t_part (id) local;

 

SQL> alter table t_part add primary key (id) using index idx_t_part;

 

SQL> create table t_exchange as select * from t_part;

 

SQL> create index idx_t_exchange on t_exchange(id);

 

SQL> alter table t_exchange add primary key (id) using index idx_t_exchange;

 

SQL> insert into t_exchange values (1,'P1');

SQL> insert into t_exchange values (10,'P2');

SQL> insert into t_exchange values (30,'P2');

SQL> insert into t_exchange values (100,'P3');

SQL> commit;

 

SQL> select * from t_exchange;

        ID NAME

---------- ------------------------------

         1 P1

        30 P2

        10 P2

       100 P3

 

SQL> alter table t_part exchange partition p2 with table t_exchange including indexes without validation;

 

Table altered.

 

SQL> select * from t_part;

        ID NAME

---------- ------------------------------

         1 P1

        30 P2

        10 P2

       100 P3

 

SQL> select * from t_part partition (p2);

        ID NAME

---------- ------------------------------

         1 P1

        30 P2

        10 P2

       100 P3

 

SQL> insert into t_part values (1,'P1');

1 row created.

 

SQL> commit;

Commit complete.

 

SQL> select * from t_part;

        ID NAME

---------- ------------------------------

         1 P1

         1 P1

        30 P2

        10 P2

       100 P3

 

下面再看下MsSQL：

CREATE PARTITION FUNCTION [Pfn_Number](int) AS RANGE RIGHT

 FOR VALUES (10,100)

 

CREATE PARTITION SCHEME [Psc_Number] AS PARTITION [Pfn_Number]

 TO ([Primary], [Primary], [Primary])

 

create table t_part (id int, name varchar(30)) on [Psc_Number](id)

 

select * into t_exchange from t_part;

 

insert into t_exchange values (1,'P1');

insert into t_exchange values (10,'P2');

insert into t_exchange values (30,'P2');

insert into t_exchange values (100,'P3');

 

 

select * from t_exchange

id          name

----------- ------------------------------

1           P1

10          P2

30          P2

100         P3

 

alter table t_exchange switch to t_part partition 2

消息4982，级别16，状态1，第1 行

ALTER TABLE SWITCH 语句失败。源表'demo.dbo.t_exchange' 的检查约束所允许的值对于目标表'demo.dbo.t_part' 中分区2 定义的范围是不允许的。

 

可见，在MsSQL中，并不是通过检查具体数据，而是通过CHECK约束条件来实现普通表和表分区之间的快速数据交换。

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

转载于:http://blog.itpub.net/81227/viewspace-716791/