ITPub博客

首页 > Linux操作系统 > Linux操作系统 > EXCHANGE分区导致主键重复

EXCHANGE分区导致主键重复

原创 Linux操作系统 作者:yangtingkun 时间:2012-02-15 23:17:07 0 删除 编辑

分区表的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索引,而临时表上的对应列也建立了索引并添加了主键。

随后向临时表中添加记录,除了三条TYPETABLE的记录外,还增加了一条TYPEVIEW的记录。

然后执行分区交换操作:

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

上一篇: 语句级并行提示
请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10366772