今天做EXCHANG TABLES TO PARTITION测试时，发现ORACLE交换分区时不检查数据有效性，很可能会导致数据重复，而且10g、11g都存在这个问题。实验过程如下：
1.创建测试表及索引

SQL> create table stream_part(id number,name varchar2(20),type varchar2(20))
  2  partition by list(type)
  3  (partition part1 values('TABLE'),
  4  partition part2 values(default));
表已创建。
SQL> create table stream_t1 as select * from stream_part where 1=2;
表已创建。
SQL> create table stream_t2 as select * from stream_part where 1=2;
表已创建。
SQL> CREATE INDEX IND_PART ON STREAM_PART(TYPE, ID) LOCAL;
索引已创建。
SQL> CREATE INDEX IND_T1 ON STREAM_T1(TYPE, ID);
索引已创建。
SQL> CREATE INDEX IND_T2 ON STREAM_T2(TYPE, ID);
索引已创建。
SQL> ALTER TABLE STREAM_PART ADD PRIMARY KEY(TYPE,ID)
  2  USING INDEX IND_PART;
表已更改。
SQL> ALTER TABLE STREAM_T1 ADD PRIMARY KEY(TYPE,ID)
  2  USING INDEX IND_T1;
表已更改。
SQL> ALTER TABLE STREAM_T2 ADD PRIMARY KEY(TYPE,ID)
  2  USING INDEX IND_T2;
表已更改。

2.向STREAM_T1和STREAM_T2表中插入测试数据。

SQL> insert into stream_t1 values(1,'DBA_USERS','VIEW');
1 row created.
SQL> insert into stream_t1 values(2,'TAB$','TABLE');
1 row created.
SQL> insert into stream_t1 values(3,'COL$','TABLE');
1 row created.
SQL> insert into stream_t2 values(1,'DBA_USERS','VIEW');
1 row created.
SQL> insert into stream_t2 values(2,'TAB$','TABLE');
1 row created.
SQL> insert into stream_t2 values(3,'COL$','TABLE');
1 row created.
SQL> commit;
Commit complete.

3.交换数据

SQL> alter table stream_part exchange partition part1 with table stream_t1
  2  including indexes without validation;
Table altered.
SQL> alter table stream_part exchange partition part2 with table stream_t2
  2  including indexes without validation;
alter table stream_part exchange partition part2 with table stream_t2
*
ERROR at line 1:
ORA-00001: unique constraint (STREAM.SYS_C008911) violated

由于违反了唯一性约束，报错了，此时分区表STREAM_PART已经有了STREAM_T1的数据，而。STREAM_T1已经成为空表。

SQL> select * from stream_part;
        ID NAME                 TYPE
---------- -------------------- --------------------
         1 DBA_USERS            VIEW
         2 TAB$                 TABLE
         3 COL$                 TABLE
SQL> select * from stream_t1;
未选定行

而交换到STREAM_PART表的数据并没有按照分区规则分配数据，所有STREAM_T1的数据均存到了PART1分区。

SQL> SELECT * FROM STREAM_PART PARTITION(PART1);
        ID NAME                 TYPE
---------- -------------------- --------------------
         1 DBA_USERS            VIEW
         2 TAB$                 TABLE
         3 COL$                 TABLE
SQL> SELECT * FROM STREAM_PART PARTITION(PART2);
未选定行

4.修改STREAM_T2表的TYPE字段的值为VIEW

SQL> UPDATE STREAM_T2 SET TYPE='VIEW';
3 row updated.
SQL> COMMIT;
Commit complete.

5.再次尝试将STREAM_T2表的数据交换到STREAM_PART表的PART2分区

SQL> alter table stream_part exchange partition part2 with table stream_t2
  2  including indexes without validation;
表已更改。

查看数据后会发现，ID为1的数据主键是重复的。

SQL> select * from stream_part order by id;
        ID NAME                 TYPE
---------- -------------------- -------------
         1 DBA_USERS            VIEW
         1 DBA_USERS            VIEW
         2 TAB$                 VIEW
         2 TAB$                 TABLE
         3 COL$                 VIEW
         3 COL$                 TABLE
已选择6行。

但是竟然可以交换成功， 那么为什么之前交换操作会由于违反唯一性约束而失败，这次的交换也违反唯一性约束就可以成功呢，仔细观察就会发现，这两次操作的不同点是TYPE列的值不一样，而分区表STREAM_PART是LIST分区，TYPE的值为TABLE的数据存放到PART1分区，TYPE的值为VIEW的数据存在PART2分区，虽然交换分区时只将整个源表的数据放到一个指定的分区里而不会按照分区规则存放数据，但是，在交换分区时，还是会检查分区列的值，例如第一次交换失败这次实验，交换分区时检测到STREAM_T2表的ID为2和3的记录按照分区规则会被存放到PART1分区，而这两条记录违反PART1分区的主键约束，因此会交换失败，第二次交换之所以会成功，是由于STREAM_T2表的TYPE列的值都被修改为VIEW，而TYPE的值为VIEW的数据按照分区规则是存在PART2分区，而第二次交换分区时，PART2分区没有数据，而我们指定将STREAM_T2的数据交换到PART2分区，这时，ORACLE就不会去检查STREAM_T2表的数据是否和PART1分区的数据有冲突，所以第二次交换分区即使存在唯一键约束也可以成功，（以上均为本人猜测，不具有权威性，需谨慎参考）。
6.下面在看下INSERT操作是否可以插入主键重复的数据。
先删除PART2分区的ID为1的重复数据。

SQL> delete from stream_part partition(part2) where id=1;
已删除 1 行。
SQL> commit;
提交完成。

当前STREAM_PART表的数据如下：

SQL> select * from stream_part;
        ID NAME                 TYPE
---------- -------------------- -------
         1 DBA_USERS            VIEW
         2 TAB$                 TABLE
         3 COL$                 TABLE
         2 TAB$                 VIEW
         3 COL$                 VIEW

然后依次插入和主键重复的TYPE为TABLE、VIEW的两条记录。

SQL> insert into stream_part values(2,'TAB$','TABLE');
insert into stream_part values(2,'TAB$','TABLE')
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (STREAM.SYS_C0014456)

SQL> insert into stream_part values(2,'TAB$','VIEW');
insert into stream_part values(2,'TAB$','VIEW')
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (STREAM.SYS_C0014456)

SQL> insert into stream_part values(1,'DBA_USERS','VIEW');
已创建 1 行。
SQL> commit;

可见前2条INSERT操作都由于违反唯一性约束而失败了，但是第3条操作是可以成功的，这也验证了我之前的猜测，由于第一条SQL语句TYPE值为TABLE，这条记录需要被插入到PART1分区，而这条记录违反PART1分区的主键约束，所以INSERT失败，同样第二条操作的记录需要被插入到PART2分区，同样违反PART2分区的主键约束，也失败了，而第三条操作的记录会被插入到PART2分区，但是PART2分区的主键并不存在ID=1，TYPE=VIEW的记录（这条INSERT操作之前，STREAM_PART表的ID=1，TYPE=VIEW存放在PART1分区，详见上文），由于ORACLE不验证由交换分区操作来的数据的有效性，所以虽然这个INSERT操作违反STREAM_PART表的主键约束，但是不违反PART2单个分区的主键约束，所以INSERT操作成功了（以上还是本人猜测，不具有权威性，参考需谨慎）。
下面看下，对分区表STREAM_PART的查询结果是否会有影响。

SQL> select * from stream_part;
        ID NAME                 TYPE
---------- -------------------- ---------
         1 DBA_USERS            VIEW
         2 TAB$                 TABLE
         3 COL$                 TABLE
         2 TAB$                 VIEW
         3 COL$                 VIEW
         1 DBA_USERS            VIEW
已选择6行。

可以看到ID为1的数据完全重复，进一步查看可以看到，这两条记录在PART1和PART2分区，各有一条。

SQL> select * from stream_part partition(part1);
        ID NAME                 TYPE
---------- -------------------- ----------------
         1 DBA_USERS            VIEW
         2 TAB$                 TABLE
         3 COL$                 TABLE

SQL> select * from stream_part partition(part2);
        ID NAME                 TYPE
---------- -------------------- ----------------
         2 TAB$                 VIEW
         3 COL$                 VIEW
         1 DBA_USERS            VIEW

下面验证下，全表扫描和主键扫描对查询的影响，首先看下全表扫描。

SQL>  select * from stream_part where id=1;
        ID NAME                 TYPE
---------- -------------------- --------------------
         1 DBA_USERS            VIEW
         1 DBA_USERS            VIEW

执行计划

可以看到，全表扫描时是可以看到这条主键重复的记录，下面在看下索引扫描。

SQL>  select * from stream_part where id=1 and type='VIEW';
        ID NAME                 TYPE
---------- -------------------- --------------------
         1 DBA_USERS            VIEW
执行计划

----------------------------------------------------------
Plan hash value: 1735537232
------------------------------------------------------------------------------------------
|Id| Operation                       | Name    |Rows|Bytes|Cost(%CPU)| Time  |Pstart|Pstop|
------------------------------------------------------------------------------------------
|0 | SELECT STATEMENT                |         |   1|   37|   0   (0)|00:00:01|     |     |
|1 |PARTITION LIST SINGLE            |         |   1|   37|   0   (0)|00:00:01| KEY | KEY |
|2 |TABLE ACCESS BY LOCAL INDEX ROWID|STREAM_PART| 1|   37|   0   (0)|00:00:01|   2 |   2 |
|*3|    INDEX RANGE SCAN             | IND_PAR |  1 |     |   0   (0)|00:00:01|   2 |   2 |
-------------------------------------------------------------------------------------------

可以看到，走主键之后，只可以显示1条记录，这种主键重复的数据，就需要手动指定分区进行删除了。

本文固定链接: https://www.dbdream.com.cn/2012/11/%e5%88%86%e5%8c%ba%e8%a1%a8%e7%9a%84exchange%e4%ba%a4%e6%8d%a2%e5%88%86%e5%8c%ba%e4%b8%8d%e5%85%a8%e9%83%a8%e6%a3%80%e6%9f%a5%e6%95%b0%e6%8d%ae%e6%9c%89%e6%95%88%e6%80%a7%ef%bc%8c%e5%8f%af%e8%83%bd/ | 信春哥，系统稳，闭眼上线不回滚！