ITPub博客

首页 > Linux操作系统 > Linux操作系统 > HASH分区新增分区对索引状态的影响

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

原创 Linux操作系统 作者:yangtingkun 时间:2012-06-28 23:46:32 0 删除 编辑

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

HASH分区表增加新的分区的一点研究:http://yangtingkun.itpub.net/post/468/195510

 

 

看一个范围分区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.

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分区SPLITP2P3两个分区,其中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之前,删除了ID5的记录,这是增加分区后可以发现,原有的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并没有最后去验证,是否真的发生了数据的迁移。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-734265/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10354082