在范围分区表上分区维护操作对索引状态的影响

最新推荐文章于 2022-08-15 17:05:16 发布

cizongzhic79075

最新推荐文章于 2022-08-15 17:05:16 发布

阅读量217

点赞数

文章标签：数据库

引用自：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

==>ADD PARTITION不影响本地和全局索引的状态

3，DROP PARTITION
3.1分区内无数据（从未插入）
alter table test
drop partition p2;

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;

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

--需要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;

==>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;

7 rows selected.

==>drop PARTITION+update global indexes在分区内有数据的情况下不影响本地和全局索引的状态

4，TRUNCATE PARTITION
4.1分区内无数据（从未插入）
alter table test
truncate partition p7;

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;

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

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;

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;

7 rows selected.

--本地、全局索引状态不变

5，SPLIT PARTITION
5.1分区内无数据（从未插入）
alter table test
split partition p8 at (750) into (partition p81,partition p82);

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);

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,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,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博客 ” ，链接：http://blog.itpub.net/27042095/viewspace-755199/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27042095/viewspace-755199/

cizongzhic79075

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
在范围分区表上分区维护操作对索引状态的影响

引用自：http://space.itpub.net/18922393/viewspace-755143测试结论：针对范围分区索引：（1）ADD PARTITION==>增加分区不影响本地、全局索引状态（2）DR...
复制链接

扫一扫