ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 10g分区表索引失效测试

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

原创 Linux操作系统 作者:is.x 时间:2011-05-29 21:18:53 0 删除 编辑

一、测试环境

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;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20750200/viewspace-696586/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-04-27

  • 博文量
    73
  • 访问量
    265336