一直认为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 |
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分区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 |
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 |
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 |
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. |
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并没有最后去验证，是否真的发生了数据的迁移。
