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