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
参考：http://forums.oracle.com/forums/thread.jspa?threadID=430452
另外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.

ITPub博客

在范围分区表上分区维护操作对全局及本地索引状态的影响(2)

最新文章