在範圍分割槽表上分割槽維護操作對索引狀態的影響

引用自：http://space.itpub.net/18922393/viewspace-755143

測試結論：針對範圍分割槽索引：
（1）ADD PARTITION
==>增加分割槽不影響本地、全域性索引狀態
（2）DROP/TRUNCATE PARTITION
==>在分割槽內無資料時不影響本地、全域性索引狀態；
==>在分割槽內有資料時全域性索引unusable，本地索引不受影響；
   使用update indexes或update global indexes都可以避免全域性索引unusable
（3）SPLIT PARTITION
==>在分割槽內無資料時不影響本地、全域性索引狀態；
==>在分割槽內有資料時,如改操作涉及資料移動，本地和全域性索引都變為unusable；本地索引需要使用alter index idx_name rebuild partition part_name方式重建。
   使用update indexes可以避免本地和全域性索引unusable；
   使用update global indexes可以避免全域性索引unusable，但本地索引變為unusable

1,建立測試表
create table test(id int,x varchar2(100))
partition by range(id)
(
partition p1 values less than (100),
partition p2 values less than (200),
partition p3 values less than (300)
);

create index idx_test_id on test(id) local;

create index idx_test_x on test(x);

--使用者物件
set linesize 200
col object_name for a30
col subobjet_name for a15
col object_id for 999999999999
col data_object_id for 99999999999
col object_type for a15
select object_name,subobject_name,object_id,data_object_id,object_type from user_objects;
OBJECT_NAME                    SUBOBJECT_NAME                     OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ------------------------------ ------------- -------------- ---------------
TEST                           P3                                    156124         156124 TABLE PARTITION
TEST                           P2                                    156123         156123 TABLE PARTITION
TEST                           P1                                    156122         156122 TABLE PARTITION
TEST                                                                 156121                TABLE
IDX_TEST_ID                    P3                                    156128         156128 INDEX PARTITION
IDX_TEST_ID                    P2                                    156127         156127 INDEX PARTITION
IDX_TEST_ID                    P1                                    156126         156126 INDEX PARTITION
IDX_TEST_ID                                                          156125                INDEX
IDX_TEST_X                                                           156129         156129 INDEX

9 rows selected.

--索引資訊
col index_name for a30
col index_type for a15
col table_name for a30
col status for a20
col partitioned for a10
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

--分割槽索引資訊
col partitioning_type for a10
col partition_count for 9999
col locality for a15
col alignment for a25
select index_name,table_name,partitioning_type,partition_count,locality,alignment from user_part_indexes;
INDEX_NAME                     TABLE_NAME                     PARTITIONI PARTITION_COUNT LOCALITY        ALIGNMENT
------------------------------ ------------------------------ ---------- --------------- --------------- -------------------------
IDX_TEST_ID                    TEST                           RANGE                    3 LOCAL           PREFIXED

--索引分割槽狀態
col partition_name for a15
col partition_position for 999
col status for a20
select index_name,partition_name,partition_position,status from user_ind_partitions;
INDEX_NAME                     PARTITION_NAME  PARTITION_POSITION STATUS
------------------------------ --------------- ------------------ --------------------
IDX_TEST_ID                    P3                               3 USABLE
IDX_TEST_ID                    P1                               1 USABLE
IDX_TEST_ID                    P2                               2 USABLE

2，ADD PARTITION
alter table test
  add partition p4 values less than(400);

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                    P3                               3 USABLE
IDX_TEST_ID                    P1                               1 USABLE
IDX_TEST_ID                    P2                               2 USABLE
IDX_TEST_ID                    P4                               4 USABLE

==>ADD PARTITION不影響本地和全域性索引的狀態

3，DROP PARTITION
3.1分割槽內無資料（從未插入）
alter table test
  drop partition p2;

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                    P3                               2 USABLE
IDX_TEST_ID                    P1                               1 USABLE
IDX_TEST_ID                    P4                               3 USABLE
==>drop PARTITION在分割槽內無資料的情況下不影響本地和全域性索引的狀態

3.2分割槽內無資料（插入後刪除）
insert into test values(290,'p3');
commit;

col x for a30
select * from test partition(p3);
        ID X
---------- ----------
       290 p3

delete from test where id=290;
commit;

alter table test
  drop partition p3;

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                    P1                               1 USABLE
IDX_TEST_ID                    P4                               2 USABLE
==>drop PARTITION在分割槽內無資料的情況下不影響本地和全域性索引的狀態

3.3分割槽內有資料
--為了建立同樣的刪除情況，建立一些分割槽
alter table test
  add partition p5 values less than(500);
alter table test
  add partition p6 values less than(600);

3.3.1 直接刪除分割槽
insert into test values(390,'p4');
commit;

col x for a30
select * from test partition(p4);
        ID X
---------- ------------------------------
       390 p4

alter table test
  drop partition p4;

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                    P5                               2 USABLE
IDX_TEST_ID                    P6                               3 USABLE
IDX_TEST_ID                    P1                               1 USABLE
==>drop PARTITION在分割槽內有資料的情況下不影響本地索引狀態，但全域性索引狀態變為UNUSABLE

--需要rebuild全域性索引
alter index IDX_TEST_X rebuild;
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

3.3.2 刪除分割槽+update indexes
insert into test values(490,'p5');
commit;

select * from test partition(p5);
        ID X
---------- ------------------------------
       490 p5

alter table test
  drop partition p5 update indexes;

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                    P6                               2 USABLE
IDX_TEST_ID                    P1                               1 USABLE

==>drop PARTITION+update indexes在分割槽內有資料的情況下不影響本地和全域性索引的狀態

3.3.3 刪除分割槽+update global indexes
--為了建立同樣的刪除情況，建立一些分割槽
alter table test
  add partition p7 values less than(700);
alter table test
  add partition p8 values less than(800);
alter table test
  add partition p9 values less than(900);

insert into test values(590,'p6');
commit;

select * from test partition(p6);
        ID X
---------- ------------------------------
       590 p6

alter table test
  drop partition p6 update global indexes;

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                    P8                               3 USABLE
IDX_TEST_ID                    P9                               4 USABLE

7 rows selected.

==>drop PARTITION+update global indexes在分割槽內有資料的情況下不影響本地和全域性索引的狀態

4，TRUNCATE PARTITION
4.1分割槽內無資料（從未插入）
alter table test
  truncate partition p7;

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                    P8                               3 USABLE
IDX_TEST_ID                    P9                               4 USABLE
==>truncate PARTITION在分割槽內無資料的情況下不影響本地和全域性索引的狀態

4.2分割槽內無資料（插入後刪除）
insert into test values(690,'p7');
commit;

delete from test where id=690;
commit;

select * from test partition(p7);
no rows selected

alter table test
  truncate partition p7;

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                    P8                               3 USABLE
IDX_TEST_ID                    P9                               4 USABLE

7 rows selected.

--本地、全域性索引狀態不變

4.3分割槽內有資料
4.3.1 直接TRUNCATE分割槽
insert into test values(690,'p7');
commit;

select * from test partition(p7);
        ID X
---------- ------------------------------
       690 p7

alter table test
  truncate partition p7;

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                    P8                               3 USABLE
IDX_TEST_ID                    P9                               4 USABLE

7 rows selected.

--全域性索引狀態變為UNUSABLE，需要rebuild

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

4.3.2 TRUNCATE分割槽+update indexes

insert into test values(690,'p7');
commit;

select * from test partition(p7);
        ID X
---------- ------------------------------
       690 p7

alter table test
  truncate partition p7 update indexes;

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                    P8                               3 USABLE
IDX_TEST_ID                    P9                               4 USABLE

7 rows selected.

--本地、全域性索引狀態不變

4.3.3 TRUNCATE分割槽+update global indexes
insert into test values(690,'p7');
commit;

select * from test partition(p7);
        ID X
---------- ------------------------------
       690 p7

alter table test
  truncate partition p7 update global indexes;

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                    P8                               3 USABLE
IDX_TEST_ID                    P9                               4 USABLE

7 rows selected.