ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle Vs MsSQL 之交换分区

Oracle Vs MsSQL 之交换分区

原创 Linux操作系统 作者:cow977 时间:2012-02-20 12:10:10 2 删除 编辑

近日,看到网友[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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-02

  • 博文量
    629
  • 访问量
    686471