tbwshc

          HASH分區(qū)新增分區(qū)對(duì)索引狀態(tài)的影響

          一直認(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ù)的遷移。

          posted on 2012-09-05 11:43 chen11-1 閱讀(1262) 評(píng)論(0)  編輯  收藏

          My Links

          Blog Stats

          常用鏈接

          留言簿(4)

          隨筆分類

          隨筆檔案

          文章分類

          文章檔案

          新聞檔案

          tbw淘寶商城-首頁

          搜索

          最新評(píng)論

          閱讀排行榜

          評(píng)論排行榜

          主站蜘蛛池模板: 嘉定区| 昭苏县| 佛冈县| 贺州市| 敦煌市| 武功县| 桂东县| 汉阴县| 泰州市| 绥阳县| 余姚市| 远安县| 嵩明县| 酉阳| 奉贤区| 肇东市| 三台县| 堆龙德庆县| 嵩明县| 高唐县| 山东省| 监利县| 台州市| 文水县| 丰台区| 洪泽县| 两当县| 绥阳县| 湖口县| 德令哈市| 叶城县| 上犹县| 霸州市| 老河口市| 高碑店市| 方城县| 丹棱县| 定边县| 勃利县| 湛江市| 交口县|