操作分区表对global和local索引的影响 (二）

精选转载

bfc99 2014-07-09 20:50:08 博主文章分类：数据库

文章标签 分区表操作本地全局索引影响 文章分类 数据库

以下转自：http://blog.chinaunix.net/uid-23284114-id-3421922.html 作者：十字螺丝钉

操作分区表对global和local索引的影响 (一）请参考 http://bfc99.blog.51cto.com/265386/1436441

三、drop partition

1.删除没有数据的分区

<1>not plus "update indexes" ，同时分区中没有任何数据

SQL> alter table part_test drop partition p4;

Table altered.

删除分区后，索引依然可用。

SQL> select INDEX_NAME,TABLE_OWNER,STATUS from dba_indexes where index_name='IND_ID';

INDEX_NAME TABLE_OWNER STATUS

------------------------------ ------------------------------ --------

IND_ID DOWNLOAD VALID

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_NAME';

INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_NAME P1 5 USABLE

IND_NAME P2 10 USABLE

IND_NAME P3 15 USABLE

IND_NAME P5 25 USABLE

<2>plus "update indexes" ，同时分区中没有任何数据

SQL> alter table part_test drop partition p5 update indexes;

Table altered.

删除分区后，索引依然可用。

SQL> select INDEX_NAME,TABLE_OWNER,STATUS from dba_indexes where index_name='IND_ID';

INDEX_NAME TABLE_OWNER STATUS

------------------------------ ------------------------------ --------

IND_ID DOWNLOAD VALID

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_NAME';

INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_NAME P1 5 USABLE

IND_NAME P2 10 USABLE

IND_NAME P3 15 USABLE

小结：分区中没有数据，不加update index ，drop该索引也不会引起local及global索引失效。

2.删除含有数据的分区

insert into part_test values(1,'tom'); --落在p1分区

insert into part_test values(6,'lucy'); --落在p2分区

insert into part_test values(11,'lucy'); --落在p3分区

SQL> select * from part_test partition(p1);

ID NAME

---------- ----------

1 tom

SQL>

SQL> select * from part_test partition(p2);

ID NAME

---------- ----------

6 lucy

SQL> select * from part_test partition(p3);

ID NAME

---------- ----------

11 lucy

<1>not plus "update indexes"

SQL> alter table part_test drop partition p3;

Table altered.

global索引失效

SQL> select INDEX_NAME,TABLE_OWNER,STATUS from dba_indexes where index_name='IND_ID';

INDEX_NAME TABLE_OWNER STATUS

------------------------------ ------------------------------ --------

IND_ID DOWNLOAD UNUSABLE

local索引正常

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_NAME';

INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_NAME P1 5 USABLE

IND_NAME P2 10 USABLE

小结：分区中含有数据，删除分区会造成global索引失效;而local 索引正常。

<2>plus "update indexes"

首先修复global索引IND_ID,通过rebuild

alter index IND_ID rebuild;

再添加几个实验分区

alter table part_test add partition p2 values less than(10);

alter table part_test add partition p3 values less than(15);

alter table part_test add partition p4 values less than(20);

insert into part_test values(6,'lucy'); --落在p2分区

insert into part_test values(11,'lucy'); --落在p3分区

SQL> alter table part_test drop partition p2 update indexes;

Table altered.

SQL> select INDEX_NAME,TABLE_OWNER,STATUS from dba_indexes where index_name='IND_ID';

INDEX_NAME TABLE_OWNER STATUS

------------------------------ ------------------------------ --------

IND_ID DOWNLOAD VALID

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_NAME';

INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_NAME P1 5 USABLE

IND_NAME P3 15 USABLE

IND_NAME P4 20 USABLE

小结：分区中含有数据，不加update indexes，会造成global索引失效，而local索引不受影响。

四、move partition

1.分区含有数据，不加update indexes

SQL> alter table part_test move partition p3;

Table altered.

global索引失效

SQL> select INDEX_NAME,TABLE_OWNER,STATUS from dba_indexes where index_name='IND_ID';

INDEX_NAME TABLE_OWNER STATUS

------------------------------ ------------------------------ --------

IND_ID DOWNLOAD UNUSABLE

分区对应的local索引失效，而其他分区的local索引不受影响。

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_NAME';

INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_NAME P1 5 USABLE

IND_NAME P3 15 UNUSABLE

IND_NAME P4 20 USABLE

恢复失效索引

global 索引

SQL> alter index IND_ID rebuild;

Index altered.

local索引:rebuild 失效的lcoal索引即可。

SQL> alter index IND_NAME rebuild partition p3;

Index altered.

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_NAME';

INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_NAME P1 5 USABLE

IND_NAME P3 15 USABLE

IND_NAME P4 20 USABLE

2.分区含有数据，加update indexes

SQL> alter table part_test move partition p3 update indexes;

Table altered.

加上update indexes后，两种索引都正常。

SQL> select INDEX_NAME,TABLE_OWNER,STATUS from dba_indexes where index_name='IND_ID';

INDEX_NAME TABLE_OWNER STATUS

------------------------------ ------------------------------ --------

IND_ID DOWNLOAD VALID

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_NAME';

INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_NAME P1 5 USABLE

IND_NAME P3 15 USABLE

IND_NAME P4 20 USABLE

小结：分区中含有数据，move分区会造成global和分区对应local索引失效；加上update indexes参数能够避免这个问题。

操作分区表对global和local索引的影响 (三）请参考 http://bfc99.blog.51cto.com/265386/1436445