分区表的EXCHANGE交换分区不检查数据有效性，可能导致LOCAL主键索引出现重复值。
通过一个简单的例子来说明这个问题：
SQL> CREATE TABLE T_PART_EXCHANGE (ID NUMBER, NAME VARCHAR2(30), TYPE VARCHAR2(18)) 2 PARTITION BY LIST (TYPE) 3 (PARTITION P1 VALUES ('TABLE'), 4 PARTITION P2 VALUES (DEFAULT)); TABLE created. SQL> CREATE INDEX IND_PART_EXCHANGE_TYPEID ON T_PART_EXCHANGE(TYPE, ID) LOCAL; INDEX created. SQL> ALTER TABLE T_PART_EXCHANGE ADD PRIMARY KEY (TYPE, ID) 2 USING INDEX IND_PART_EXCHANGE_TYPEID; TABLE altered. SQL> CREATE TABLE T_EXCHANGE_TEMP 2 AS SELECT * FROM T_PART_EXCHANGE; TABLE created. SQL> CREATE INDEX IND_EXCHANGE_TEMP_TYPEID ON T_EXCHANGE_TEMP(TYPE, ID); INDEX created. SQL> ALTER TABLE T_EXCHANGE_TEMP ADD PRIMARY KEY (TYPE, ID) 2 USING INDEX IND_EXCHANGE_TEMP_TYPEID; TABLE altered. SQL> INSERT INTO T_EXCHANGE_TEMP 2 SELECT ROWNUM, TABLE_NAME, 'TABLE' 3 FROM USER_TABLES; 3 ROWS created. SQL> SELECT * FROM T_EXCHANGE_TEMP; ID NAME TYPE ---------- ------------------------------ ------------------ 1 T_EXCHANGE_TEMP TABLE 2 T TABLE 3 T_PART_EXCHANGE TABLE SQL> INSERT INTO T_EXCHANGE_TEMP VALUES (4, 'V_T', 'VIEW'); 1 ROW created. SQL> COMMIT; Commit complete. |
建立一个分区表，一个临时表用来交换数据，分区表上的主键使用LOCAL索引，而临时表上的对应列也建立了索引并添加了主键。
随后向临时表中添加记录，除了三条TYPE为TABLE的记录外，还增加了一条TYPE为VIEW的记录。
然后执行分区交换操作：
SQL> ALTER TABLE T_PART_EXCHANGE EXCHANGE PARTITION P1 WITH TABLE T_EXCHANGE_TEMP 2 INCLUDING INDEXES WITHOUT VALIDATION; TABLE altered. SQL> SELECT * FROM T_PART_EXCHANGE PARTITION (P1); ID NAME TYPE ---------- ------------------------------ ------------------ 1 T_EXCHANGE_TEMP TABLE 2 T TABLE 3 T_PART_EXCHANGE TABLE 4 V_T VIEW SQL> INSERT INTO T_PART_EXCHANGE VALUES (4, 'V_T', 'VIEW'); 1 ROW created. SQL> COMMIT; Commit complete. SQL> SELECT * FROM T_PART_EXCHANGE; ID NAME TYPE ---------- ------------------------------ ------------------ 1 T_EXCHANGE_TEMP TABLE 2 T TABLE 3 T_PART_EXCHANGE TABLE 4 V_T VIEW 4 V_T VIEW |
由于Oracle不检测EXCHANGE进去的数据是否合法，就造成了数据重复的现场。这时如果通过主键访问，只会返回一条记录，而如果全表扫描则会返回两条记录：
SQL> SET AUTOT ON EXP SQL> SELECT * FROM T_PART_EXCHANGE WHERE ID = 4 AND TYPE = 'VIEW'; ID NAME TYPE ---------- ------------------------------ ------------------ 4 V_T VIEW Execution Plan ---------------------------------------------------------- Plan hash VALUE: 3202076975 ---------------------------------------------------------------------------------------- |Id|Operation |Name |ROWS|Cost|Pstart|Pstop| ---------------------------------------------------------------------------------------- | 0|SELECT STATEMENT | | 1| 1| | | | 1| PARTITION LIST SINGLE | | 1| 1| KEY | KEY| | 2| TABLE ACCESS BY LOCAL INDEX ROWID|T_PART_EXCHANGE | 1| 1| 2 | 2| |*3| INDEX RANGE SCAN |IND_PART_EXCHANGE_TYPEID| 1| 1| 2 | 2| ---------------------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 3 - access("TYPE"='VIEW' AND "ID"=4) SQL> SELECT * FROM T_PART_EXCHANGE WHERE ID = 4; ID NAME TYPE ---------- ------------------------------ ------------------ 4 V_T VIEW 4 V_T VIEW Execution Plan ---------------------------------------------------------- Plan hash VALUE: 820685725 ------------------------------------------------------------------------------------------- | Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| Pstart| Pstop | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 82 | 4 (0)| | | | 1 | PARTITION LIST ALL| | 2 | 82 | 4 (0)| 1 | 2 | |* 2 | TABLE ACCESS FULL| T_PART_EXCHANGE | 2 | 82 | 4 (0)| 1 | 2 | ------------------------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 2 - FILTER("ID"=4) Note ----- - dynamic sampling used FOR this statement |
即使扫描全表，查询的结果仍然可能是错误的：
SQL> SELECT ID, TYPE, COUNT(*) 2 FROM T_PART_EXCHANGE 3 GROUP BY ID, TYPE; ID TYPE COUNT(*) ---------- ------------------ ---------- 4 VIEW 1 1 TABLE 1 3 TABLE 1 2 TABLE 1 4 VIEW 1 Execution Plan ---------------------------------------------------------- Plan hash VALUE: 2336647613 ------------------------------------------------------------------------------------------ |Id|Operation |Name |ROWS|Bytes| Cost (%CPU)|Pstart|Pstop| ------------------------------------------------------------------------------------------ | 0|SELECT STATEMENT | | 5| 120| 3 (34)| | | | 1| PARTITION LIST ALL | | 5| 120| 3 (34)| 1 | 2| | 2| HASH GROUP BY | | 5| 120| 3 (34)| | | | 3| INDEX FAST FULL SCAN|IND_PART_EXCHANGE_TYPEID| 5| 120| 2 (0)| 1 | 2| ------------------------------------------------------------------------------------------ Note ----- - dynamic sampling used FOR this statement SQL> SELECT ID, TYPE, COUNT(*) 2 FROM T_PART_EXCHANGE 3 GROUP BY ID, TYPE 4 ORDER BY ID; ID TYPE COUNT(*) ---------- ------------------ ---------- 1 TABLE 1 2 TABLE 1 3 TABLE 1 4 VIEW 2 Execution Plan ---------------------------------------------------------- Plan hash VALUE: 98113653 ------------------------------------------------------------------------------------------ |Id|Operation |Name |ROWS|Bytes| Cost (%CPU)|Pstart|Pstop| ------------------------------------------------------------------------------------------ | 0|SELECT STATEMENT | | 5| 120| 3 (34)| | | | 1| SORT GROUP BY | | 5| 120| 3 (34)| | | | 2| PARTITION LIST ALL | | 5| 120| 2 (0)| 1 | 2| | 3| INDEX FAST FULL SCAN|IND_PART_EXCHANGE_TYPEID| 5| 120| 2 (0)| 1 | 2| ------------------------------------------------------------------------------------------ Note ----- - dynamic sampling used FOR this statement |
可以看到如果采用HASH GROUP BY，则GROUP BY被推到分区操作内部，因此完全相同的记录被计算两次。而加上ORDER BY语句，则Oracle采用SORT GROUP BY操作，这时GROUP BY在分区操作之外，因此得到的结果是正常的。
其实针对这个错误，倒是很容易解决，指定分区进行删除即可：
SQL> DELETE T_PART_EXCHANGE PARTITION (P1) WHERE ID = 4; 1 ROW deleted. Execution Plan ---------------------------------------------------------- Plan hash VALUE: 2501039200 ----------------------------------------------------------------------------------------- |Id|Operation |Name |ROWS|Bytes|Cost (%CPU)|TIME |Pstart|Pstop| ----------------------------------------------------------------------------------------- | 0|DELETE STATEMENT | | 1| 24| 3 (0)|00:00:01| | | | 1| DELETE |T_PART_EXCHANGE| | | | | | | | 2| PARTITION LIST SINGLE| | 1| 24| 3 (0)|00:00:01| KEY | KEY| |*3| TABLE ACCESS FULL |T_PART_EXCHANGE| 1| 24| 3 (0)|00:00:01| 1 | 1| ----------------------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 3 - FILTER("ID"=4) Note ----- - dynamic sampling used FOR this statement SQL> SET AUTOT OFF SQL> SELECT * FROM T_PART_EXCHANGE; ID NAME TYPE ---------- ------------------------------ ------------------ 1 T_EXCHANGE_TEMP TABLE 2 T TABLE 3 T_PART_EXCHANGE TABLE 4 V_T VIEW SQL> INSERT INTO T_PART_EXCHANGE VALUES (4, 'V_T', 'VIEW'); INSERT INTO T_PART_EXCHANGE VALUES (4, 'V_T', 'VIEW') * ERROR at line 1: ORA-00001: UNIQUE CONSTRAINT (TEST.SYS_C007282) violated SQL> COMMIT; Commit complete. |