oracle10g索引不生效 oracle 10g分区表索引失效测试

一、测试环境

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

CORE10.2.0.3.0Production

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;