在範圍分割槽表上分割槽維護操作對全域性及本地索引狀態的影響(2)

5，SPLIT PARTITION
5.1分割槽內無資料（從未插入）
alter table test
   split partition p8 at (750) into (partition p81,partition p82);

select index_name,index_type,table_name,status,partitioned from user_indexes;
INDEX_NAME                     INDEX_TYPE      TABLE_NAME                     STATUS               PARTITIONE
------------------------------ --------------- ------------------------------ -------------------- ----------
IDX_TEST_X                     NORMAL          TEST                           VALID                NO
IDX_TEST_ID                    NORMAL          TEST                           N/A                  YES

select index_name,partition_name,partition_position,status from user_ind_partitions;
INDEX_NAME                     PARTITION_NAME  PARTITION_POSITION STATUS
------------------------------ --------------- ------------------ --------------------
IDX_TEST_ID                    P7                               2 USABLE
IDX_TEST_ID                    P1                               1 USABLE
IDX_TEST_ID                    P9                               5 USABLE
IDX_TEST_ID                    P81                              3 USABLE
IDX_TEST_ID                    P82                              4 USABLE

5.2分割槽內無資料（插入後刪除）
insert into test values(720,'x');
insert into test values(730,'x');
commit;

select * from test partition(p81);
        ID X
---------- ------------------------------
       720 x
       730 x

delete from test where id in (720,730);
commit;

alter table test
   split partition p81 at (725) into (partition p81a,partition p81b);

select index_name,index_type,table_name,status,partitioned from user_indexes;
INDEX_NAME                     INDEX_TYPE      TABLE_NAME                     STATUS               PARTITIONE
------------------------------ --------------- ------------------------------ -------------------- ----------
IDX_TEST_X                     NORMAL          TEST                           VALID                NO
IDX_TEST_ID                    NORMAL          TEST                           N/A                  YES

select index_name,partition_name,partition_position,status from user_ind_partitions;
INDEX_NAME                     PARTITION_NAME  PARTITION_POSITION STATUS
------------------------------ --------------- ------------------ --------------------
IDX_TEST_ID                    P7                               2 USABLE
IDX_TEST_ID                    P1                               1 USABLE
IDX_TEST_ID                    P81A                             3 USABLE
IDX_TEST_ID                    P81B                             4 USABLE
IDX_TEST_ID                    P9                               6 USABLE
IDX_TEST_ID                    P82                              5 USABLE

5.3分割槽內有資料-需要移動資料
5.3.1 split partition
insert into test values(770,'x');
insert into test values(780,'x');
commit;

select * from test partition(p82);
        ID X
---------- ------------------------------
       770 x
       780 x

alter table test
   split partition p82 at (775) into (partition p82a,partition p82b);

select * from test partition(p82a);
        ID X
---------- ------------------------------
       770 x

select * from test partition(p82b);
        ID X
---------- ------------------------------
       780 x

select index_name,index_type,table_name,status,partitioned from user_indexes;
INDEX_NAME                     INDEX_TYPE      TABLE_NAME                     STATUS               PARTITIONE
------------------------------ --------------- ------------------------------ -------------------- ----------
IDX_TEST_X                     NORMAL          TEST                           UNUSABLE             NO
IDX_TEST_ID                    NORMAL          TEST                           N/A                  YES

select index_name,partition_name,partition_position,status from user_ind_partitions;
INDEX_NAME                     PARTITION_NAME  PARTITION_POSITION STATUS
------------------------------ --------------- ------------------ --------------------
IDX_TEST_ID                    P7                               2 USABLE
IDX_TEST_ID                    P1                               1 USABLE
IDX_TEST_ID                    P81A                             3 USABLE
IDX_TEST_ID                    P81B                             4 USABLE
IDX_TEST_ID                    P82A                             5 UNUSABLE
IDX_TEST_ID                    P82B                             6 UNUSABLE
IDX_TEST_ID                    P9                               7 USABLE

==>所涉及本地索引和全域性索引UNUSABLE

alter index idx_test_x rebuild;
INDEX_NAME                     INDEX_TYPE      TABLE_NAME                     STATUS               PARTITIONE
------------------------------ --------------- ------------------------------ -------------------- ----------
IDX_TEST_X                     NORMAL          TEST                           VALID                NO
IDX_TEST_ID                    NORMAL          TEST                           N/A                  YES

alter index idx_test_id rebuild partition p82a;
select index_name,partition_name,partition_position,status from user_ind_partitions;
INDEX_NAME                     PARTITION_NAME  PARTITION_POSITION STATUS
------------------------------ --------------- ------------------ --------------------
IDX_TEST_ID                    P7                               2 USABLE
IDX_TEST_ID                    P82A                             5 USABLE
IDX_TEST_ID                    P1                               1 USABLE
IDX_TEST_ID                    P81A                             3 USABLE
IDX_TEST_ID                    P81B                             4 USABLE
IDX_TEST_ID                    P82B                             6 UNUSABLE
IDX_TEST_ID                    P9                               7 USABLE

alter index idx_test_id rebuild;
SQL> alter index idx_test_id rebuild;
alter index idx_test_id rebuild
            *
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole

alter index idx_test_id rebuild partition p82b;
select index_name,partition_name,partition_position,status from user_ind_partitions;
INDEX_NAME                     PARTITION_NAME  PARTITION_POSITION STATUS
------------------------------ --------------- ------------------ --------------------
IDX_TEST_ID                    P7                               2 USABLE
IDX_TEST_ID                    P82A                             5 USABLE
IDX_TEST_ID                    P1                               1 USABLE
IDX_TEST_ID                    P81A                             3 USABLE
IDX_TEST_ID                    P81B                             4 USABLE
IDX_TEST_ID                    P82B                             6 USABLE
IDX_TEST_ID                    P9                               7 USABLE

5.3.2 split partition update indexes

insert into test values(760,'x');
commit;

select * from test partition(p82a);
        ID X
---------- ------------------------------
       770 x
       760 x

alter table test
   split partition p82a at (765) into (partition p82a1,partition p82a2) update indexes;

select * from test partition(p82a1);
        ID X
---------- ------------------------------
       760 x

select * from test partition(p82a2);
        ID X
---------- ------------------------------
       770 x

select index_name,index_type,table_name,status,partitioned from user_indexes;
INDEX_NAME                     INDEX_TYPE      TABLE_NAME                     STATUS               PARTITIONE
------------------------------ --------------- ------------------------------ -------------------- ----------
IDX_TEST_X                     NORMAL          TEST                           VALID                NO
IDX_TEST_ID                    NORMAL          TEST                           N/A                  YES

select index_name,partition_name,partition_position,status from user_ind_partitions;
INDEX_NAME                     PARTITION_NAME  PARTITION_POSITION STATUS
------------------------------ --------------- ------------------ --------------------
IDX_TEST_ID                    P7                               2 USABLE
IDX_TEST_ID                    P82A2                            6 USABLE
IDX_TEST_ID                    P1                               1 USABLE
IDX_TEST_ID                    P82A1                            5 USABLE
IDX_TEST_ID                    P81A                             3 USABLE
IDX_TEST_ID                    P81B                             4 USABLE
IDX_TEST_ID                    P82B                             7 USABLE
IDX_TEST_ID                    P9                               8 USABLE

==>本地和全域性索引都有效

5.3.3 split partition update global indexes
insert into test values(755,'x');
commit;

select * from test partition(p82a1);
        ID X
---------- ------------------------------
       760 x
       755 x

alter table test
   split partition p82a1 at (762) into (partition p82a1a,partition p82a1b) update global indexes;
            *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_INDPART_BOPART$) violated

附：split partition遭遇bug OS：redhat as 4
參考：
另外thomas zhang對該情況由一個比較詳細的分析：http://space.itpub.net/785478/viewspace-571007

==〉同一環境下，執行了5次不出問題的操作，也不保證下一次不出問題！！！

 

---換個地方再分割槽：）
alter table test
   add partition p10 values less than(1000);

insert into test values(820,'x');
insert into test values(860,'x');
commit;

select * from test partition(p9);
        ID X
---------- ------------------------------
       820 x
       860 x

select index_name,index_type,table_name,status,partitioned from user_indexes;
INDEX_NAME                     INDEX_TYPE      TABLE_NAME                     STATUS               PARTITIONE
------------------------------ --------------- ------------------------------ -------------------- ----------
IDX_TEST_X                     NORMAL          TEST                           VALID                NO
IDX_TEST_ID                    NORMAL          TEST                           N/A                  YES

select index_name,partition_name,partition_position,status from user_ind_partitions;
INDEX_NAME                     PARTITION_NAME  PARTITION_POSITION STATUS
------------------------------ --------------- ------------------ --------------------
IDX_TEST_ID                    P81A                             3 USABLE
IDX_TEST_ID                    P82B                             7 USABLE
IDX_TEST_ID                    P7                               2 USABLE
IDX_TEST_ID                    P82A2                            6 USABLE
IDX_TEST_ID                    P82A1                            5 USABLE
IDX_TEST_ID                    P1                               1 USABLE
IDX_TEST_ID                    P81B                             4 USABLE
IDX_TEST_ID                    P10                              9 USABLE
IDX_TEST_ID                    P9                               8 USABLE

9 rows selected.

alter table test
    split partition p9 at (850) into (partition p91,partition p92) update global indexes;

select * from test partition(p91);
        ID X
---------- ------------------------------
       820 x

select * from test partition(p92);
        ID X
---------- ------------------------------
       860 x

SQL> select index_name,index_type,table_name,status,partitioned from user_indexes;

INDEX_NAME                     INDEX_TYPE      TABLE_NAME                     STATUS               PARTITIONE
------------------------------ --------------- ------------------------------ -------------------- ----------
IDX_TEST_X                     NORMAL          TEST                           VALID                NO
IDX_TEST_ID                    NORMAL          TEST                           N/A                  YES

select index_name,partition_name,partition_position,status from user_ind_partitions;
INDEX_NAME                     PARTITION_NAME  PARTITION_POSITION STATUS
------------------------------ --------------- ------------------ --------------------
IDX_TEST_ID                    P81A                             3 USABLE
IDX_TEST_ID                    P82B                             7 USABLE
IDX_TEST_ID                    P7                               2 USABLE
IDX_TEST_ID                    P82A2                            6 USABLE
IDX_TEST_ID                    P82A1                            5 USABLE
IDX_TEST_ID                    P1                               1 USABLE
IDX_TEST_ID                    P81B                             4 USABLE
IDX_TEST_ID                    P10                             10 USABLE
IDX_TEST_ID                    P91                              8 UNUSABLE
IDX_TEST_ID                    P92                              9 UNUSABLE

10 rows selected.

==〉本地索引狀態為UNUSABLE

alter index idx_test_id rebuild partition p91;
alter index idx_test_id rebuild partition p92;

select index_name,partition_name,partition_position,status from user_ind_partitions;
INDEX_NAME                     PARTITION_NAME  PARTITION_POSITION STATUS
------------------------------ --------------- ------------------ --------------------
IDX_TEST_ID                    P81A                             3 USABLE
IDX_TEST_ID                    P82B                             7 USABLE
IDX_TEST_ID                    P7                               2 USABLE
IDX_TEST_ID                    P82A2                            6 USABLE
IDX_TEST_ID                    P82A1                            5 USABLE
IDX_TEST_ID                    P1                               1 USABLE
IDX_TEST_ID                    P81B                             4 USABLE
IDX_TEST_ID                    P92                              9 USABLE
IDX_TEST_ID                    P10                             10 USABLE
IDX_TEST_ID                    P91                              8 USABLE

10 rows selected.