测试结论：针对范围分区索引：
（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.
--本地、全局索引状态不变
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-755143/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/18922393/viewspace-755143/
1426
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
