一直認(rèn)為Oracle對(duì)于所有分區(qū)的操作都是一樣的,只有數(shù)據(jù)的改變才會(huì)導(dǎo)致分區(qū)狀態(tài)的失效,沒想到HASH分區(qū)的實(shí)現(xiàn)方式并不相同。
HASH分區(qū)表增加新的分區(qū)的一點(diǎn)研究:
看一個(gè)范圍分區(qū)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
可以看到,對(duì)于范圍分區(qū)而言,即使是SPLIT包含數(shù)據(jù)的分區(qū),只要沒有真正導(dǎo)致數(shù)據(jù)發(fā)生變化,就不會(huì)導(dǎo)致索引的失效。這里將PMAX分區(qū)SPLIT成P2和P3兩個(gè)分區(qū),其中PMAX中的所有數(shù)據(jù)都進(jìn)入P2分區(qū),而P3分區(qū)為空,這種情況下沒有數(shù)據(jù)的改變,因此所有分區(qū)索引的狀態(tài)都不會(huì)變?yōu)閁NUSABLE。
但是HASH分區(qū)的ADD PARTITION并沒有遵守這個(gè)規(guī)則,事實(shí)上對(duì)于每次ADD分區(qū),都會(huì)導(dǎo)致一個(gè)分區(qū)的數(shù)據(jù)發(fā)生分裂,而分裂的結(jié)果不管原分區(qū)的數(shù)據(jù)是否發(fā)生變化,都會(huì)導(dǎo)致原分區(qū)索引狀態(tài)變?yōu)閁NUSABLE,至于新增分區(qū)的索引狀態(tài),則取決于是否有數(shù)據(jù)的改變。
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
下面新增一個(gè)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中并沒有任何的數(shù)據(jù),也就是說沒有任何的數(shù)據(jù)從P1遷移到P5中,但是查詢分區(qū)索引的狀態(tài)發(fā)現(xiàn),P1對(duì)應(yīng)的分區(qū)索引狀態(tài)已經(jīng)變?yōu)閁NUSABLE。這和范圍分區(qū)的處理方式完全不同。而P5分區(qū)由于沒有任何數(shù)據(jù),因此分區(qū)狀態(tài)是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
為了更好的說明這個(gè)問題,在增加PARTITION P7之前,刪除了ID為5的記錄,這是增加分區(qū)后可以發(fā)現(xiàn),原有的P3已經(jīng)不包含任何的數(shù)據(jù),全部的記錄都進(jìn)入到新增的P7分區(qū),但是無論是P3還是P7,狀態(tài)都是UNUSABLE。這證明了前面提到的,只要是新增HASH分區(qū),就會(huì)導(dǎo)致源分區(qū)索引狀態(tài)變?yōu)閁NUSABLE,除非是一種情況:源分區(qū)本身就沒有數(shù)據(jù):
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.
事實(shí)上,對(duì)于HASH分區(qū)的ADD PARTITION操作,Oracle基本上還是秉承了沒有數(shù)據(jù)變化就不會(huì)導(dǎo)致索引失效的思路。唯一的差別在于,對(duì)于源分區(qū)包含記錄的情況,Oracle并沒有最后去驗(yàn)證,是否真的發(fā)生了數(shù)據(jù)的遷移。