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約束條件來實現普通表和表分割槽之間的快速資料交換。