一、测试环境
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio
NLSRTL Version 10.2.0.3.0 - Production
二、测试步骤
1.全局索引测试步骤
create table tab (num number,name char(2))
partition by range(num)
(partition a values less than(10),
partition b values less than(20),
partition c values less than(30));
insert into tab values(1,'a');
insert into tab values(11,'a');
insert into tab values(21,'a');
commit;
create index g_idx on tab (num);
----------------------- add 测试 ----------------------------
alter table tab add partition d values less than (40);
alter table tab add partition e values less than (maxvalue);
----------------------- drop 测试 ---------------------------
insert into tab values(31,'a');
commit;
alter table tab drop partition e;
alter table tab drop partition d;
alter index g_idx rebuild;
----------------------- truncate 测试 ----------------------------
alter table tab add partition d values less than (40);
alter table tab add partition e values less than (maxvalue);
insert into tab values(31,'a');
commit;
alter table tab truncate partition e;
alter table tab truncate partition d;
alter index g_idx rebuild;
----------------------- split 测试 ---------------------------
alter table tab add partition d values less than (maxvalue);
alter table tab split partition d at (40) into (partition d,partition e);
insert into tab values(45,'a');
commit;
alter table tab split partition e at (50) into (partition e,partition f);
insert into tab values(65,'a');
commit;
alter table tab split partition f at (60) into (partition f,partition g);
insert into tab values(75,'a');
commit;
alter table tab split partition g at (70) into (partition g,partition h);
alter index g_idx rebuild;
----------------------- 查询语句 ---------------------------
select index_name,status from dba_indexes where wner='TEST'
2.本地索引测试步骤
create table tab (num number,name char(2))
partition by range(num)
(partition a values less than(10),
partition b values less than(20),
partition c values less than(30));
insert into tab values(1,'a');
insert into tab values(11,'a');
insert into tab values(21,'a');
commit;
create index l_idx on tab (num) local;
----------------------- add 测试 ----------------------------
alter table tab add partition d values less than (40);
alter table tab add partition e values less than (maxvalue);
----------------------- drop 测试 ---------------------------
insert into tab values(31,'a');
commit;
alter table tab drop partition e;
alter table tab drop partition d;
----------------------- truncate 测试 ----------------------------
alter table tab add partition d values less than (40);
alter table tab add partition e values less than (maxvalue);
insert into tab values(31,'a');
commit;
alter table tab truncate partition e;
alter table tab truncate partition d;
----------------------- split 测试 ---------------------------
alter table tab add partition d values less than (maxvalue);
alter table tab split partition d at (40) into (partition d,partition e);
insert into tab values(45,'a');
commit;
alter table tab split partition e at (50) into (partition e,partition f);
insert into tab values(65,'a');
commit;
alter table tab split partition f at (60) into (partition f,partition g);
insert into tab values(75,'a');
commit;
alter table tab split partition g at (70) into (partition g,partition h);
alter index l_idx rebuild partition h;
alter index l_idx rebuild partition g;
----------------------- 查询语句 ---------------------------
select index_name,partition_name,status from dba_ind_partitions where index_owner='TEST'
三、测试结论
|
|
add partition |
drop partition |
truncate partition | ||
|
分区表中无数据 |
分区表中有数据 |
分区表中无数据 |
分区表中有数据 | ||
|
global index |
VALID |
VALID |
UNUSABLE |
VALID |
UNUSABLE |
|
local index |
USABLE |
USABLE |
USABLE |
USABLE |
USABLE |
|
|
split partition ( split partition a into a and b ) | |||
|
|
a,b分区均无数据 |
a分区有数据 b分区无数据 |
a分区无数据 b分区有数据 |
a,b分区均有数据 |
|
global index |
VALID |
VALID |
VALID |
UNUSABLE |
|
local index |
USABLE |
USABLE |
USABLE |
UNUSABLE |
所查数据字典：
全局索引：dba_indexes
本地索引：dba_ind_partitions
rebuild index语句：
全局索引：alter index g_idx rebuild;
本地索引：alter index l_idx rebuild partition h;