分区表的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.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-716631/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-716631/