tbwshc

HASH分区新增分区对索引状态的影响

一直认为Oracle对于所有分区的操作都是一样的,只有数据的改变才会导致分区状态的失效,没想到HASH分区的实现方式并不相同。

HASH分区表增加新的分区的一点研究: 

 

看一个范围分区SPLIT的例子:

SQL> CREATE TABLE T_PART
 2 (ID NUMBER, NAME VARCHAR2(30))
 3 PARTITION BY RANGE (ID)
 4 (PARTITION P1 VALUES LESS THAN (10),
 5 PARTITION PMAX VALUES LESS THAN (MAXVALUE));

Table created.

SQL> INSERT INTO T_PART
 2 SELECT ROWNUM, TNAME 
 3 FROM TAB;

12 rows created.

SQL> CREATE INDEX IND_T_PART_ID ON T_PART(ID) LOCAL;

Index created.tb

SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS
 2 FROM USER_IND_PARTITIONS
 3 WHERE INDEX_NAME = 'IND_T_PART_ID';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IND_T_PART_ID                  P1                             USABLE
IND_T_PART_ID                  PMAX                           USABLE

SQL> SELECT COUNT(*) FROM T_PART PARTITION (PMAX);

 COUNT(*)
----------
         3

SQL> ALTER TABLE T_PART SPLIT PARTITION PMAX AT (20)
 2 INTO (PARTITION P2, PARTITION P3);

Table altered.

SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS
 2 FROM USER_IND_PARTITIONS
 3 WHERE INDEX_NAME = 'IND_T_PART_ID';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IND_T_PART_ID                  P2                             USABLE
IND_T_PART_ID                  P3                             USABLE
IND_T_PART_ID                  P1                             USABLE

可以看到,对于范围分区而言,即使是SPLIT包含数据的分区,只要没有真正导致数据发生变化,就不会导致索引的失效。这里将PMAX分区SPLIT成P2和P3两个分区,其中PMAX中的所有数据都进入P2分区,而P3分区为空,这种情况下没有数据的改变,因此所有分区索引的状态都不会变为UNUSABLE。

但是HASH分区的ADD PARTITION并没有遵守这个规则,事实上对于每次ADD分区,都会导致一个分区的数据发生分裂,而分裂的结果不管原分区的数据是否发生变化,都会导致原分区索引状态变为UNUSABLE,至于新增分区的索引状态,则取决于是否有数据的改变。

SQL> CREATE TABLE T_HASH
 2 (ID NUMBER)
 3 PARTITION BY HASH (ID)
 4 (PARTITION P1,
 5 PARTITION P2,
 6 PARTITION P3,
 7 PARTITION P4);

Table created.

SQL> CREATE INDEX IND_T_HASH_ID ON T_HASH(ID) LOCAL;

Index created.

SQL> INSERT INTO T_HASH SELECT ROWNUM FROM TAB;

12 rows created.

SQL> COMMIT;

Commit complete.

SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS
 2 FROM USER_IND_PARTITIONS
 3 WHERE INDEX_NAME = 'IND_T_HASH_ID';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IND_T_HASH_ID                  P1                             USABLE
IND_T_HASH_ID                  P2                             USABLE
IND_T_HASH_ID                  P3                             USABLE
IND_T_HASH_ID                  P4                             USABLE

SQL> SELECT * FROM T_HASH PARTITION (P1);

        ID
----------
         6
        11

SQL> SELECT * FROM T_HASH PARTITION (P2);

        ID
----------
         9
        10
        12

SQL> SELECT * FROM T_HASH PARTITION (P3);

        ID
----------
         2
         5
         8

SQL> SELECT * FROM T_HASH PARTITION (P4);

        ID
----------
         1
         3
         4
         7

下面新增一个PARTITION P5:

SQL> ALTER TABLE T_HASH ADD PARTITION P5;

Table altered.

SQL> SELECT * FROM T_HASH PARTITION (P5);

no rows selected

SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS
 2 FROM USER_IND_PARTITIONS
 3 WHERE INDEX_NAME = 'IND_T_HASH_ID';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IND_T_HASH_ID                  P5                             USABLE
IND_T_HASH_ID                  P1                             UNUSABLE
IND_T_HASH_ID                  P2                             USABLE
IND_T_HASH_ID                  P3                            USABLE
IND_T_HASH_ID                  P4                             USABLE

新增的PARTITION P5中并没有任何的数据,也就是说没有任何的数据从P1迁移到P5中,但是查询分区索引的状态发现,P1对应的分区索引状态已经变为UNUSABLE。这和范围分区的处理方式完全不同。而P5分区由于没有任何数据,因此分区状态是USABLE。

SQL> ALTER TABLE T_HASH ADD PARTITION P6;

Table altered.

SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS
 2 FROM USER_IND_PARTITIONS
 3 WHERE INDEX_NAME = 'IND_T_HASH_ID';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IND_T_HASH_ID                  P5                             USABLE
IND_T_HASH_ID                  P6                             UNUSABLE
IND_T_HASH_ID                  P1                             UNUSABLE
IND_T_HASH_ID                  P2                             UNUSABLE
IND_T_HASH_ID                  P3                             USABLE
IND_T_HASH_ID                  P4                             USABLE

6 rows selected.

SQL> DELETE T_HASH WHERE ID = 5;        

1 row deleted.

SQL> COMMIT;

Commit complete.

SQL> ALTER TABLE T_HASH ADD PARTITION P7;

Table altered.

SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS
 2 FROM USER_IND_PARTITIONS
 3 WHERE INDEX_NAME = 'IND_T_HASH_ID';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IND_T_HASH_ID                  P5                             USABLE
IND_T_HASH_ID                  P6                             UNUSABLE
IND_T_HASH_ID                  P7                             UNUSABLE
IND_T_HASH_ID                  P1                             UNUSABLE
IND_T_HASH_ID                  P2                             UNUSABLE
IND_T_HASH_ID                  P3                             UNUSABLE
IND_T_HASH_ID                  P4                             USABLE

7 rows selected.

SQL> SELECT * FROM T_HASH PARTITION (P3);

no rows selected

SQL> SELECT * FROM T_HASH PARTITION (P7);

        ID
----------
         2
         8

为了更好的说明这个问题,在增加PARTITION P7之前,删除了ID为5的记录,这是增加分区后可以发现,原有的P3已经不包含任何的数据,全部的记录都进入到新增的P7分区,但是无论是P3还是P7,状态都是UNUSABLE。这证明了前面提到的,只要是新增HASH分区,就会导致源分区索引状态变为UNUSABLE,除非是一种情况:源分区本身就没有数据:

SQL> ALTER TABLE T_HASH ADD PARTITION P8;

Table altered.

SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS                         
 2 FROM USER_IND_PARTITIONS
 3 WHERE INDEX_NAME = 'IND_T_HASH_ID';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IND_T_HASH_ID                  P5                             USABLE
IND_T_HASH_ID                  P6                             UNUSABLE
IND_T_HASH_ID                  P7                             UNUSABLE
IND_T_HASH_ID                  P1                             UNUSABLE
IND_T_HASH_ID                  P2                             UNUSABLE
IND_T_HASH_ID                  P3                             UNUSABLE
IND_T_HASH_ID                  P4                             USABLE
IND_T_HASH_ID                  P8                             USABLE

8 rows selected.

事实上,对于HASH分区的ADD PARTITION操作,Oracle基本上还是秉承了没有数据变化就不会导致索引失效的思路。唯一的差别在于,对于源分区包含记录的情况,Oracle并没有最后去验证,是否真的发生了数据的迁移。

posted on 2012-09-05 11:43 chen11-1 阅读(1254) 评论(0)  编辑  收藏