使用分割槽表，要非常注意索引。因為刪除、move分割槽都會造成索引失效。小表的索引和非主鍵、唯一建索引還好，重建即可。

如果是幾十G的表，重建索引花費巨大，會嚴重影響查詢效能;

如果是主鍵或者唯一鍵索引，索引失效會插入重複的資料，帶來的麻煩更大。

所以，我們一定要重視分割槽表操作，避免引起索引失效。

哪些操作會引起索引失效？

ADD PARTITION | SUBPARTITION (hash)

COALESCE PARTITION | SUBPARTITION (hash)

DROP PARTITION | SUBPARTITION

EXCHANGE PARTITION | SUBPARTITION

MERGE PARTITION | SUBPARTITION

MOVE PARTITION | SUBPARTITION

SPLIT PARTITION | SUBPARTITION

TRUNCATE PARTITION | SUBPARTITION

上面所有操作都會引起global索引失效;

其中下面的操作會引起local索引失效。

MERGE PARTITION

MOVE PARTITION

SPLIT PARTITION

COALESCE PARTITION

EXCHANGE PARTITION

怎麼解決索引失效問題，那就是加上update indexes

加上update indexes，以上任何操作不會引起glocal索引失效;

加上update indexes，以上操作中除了EXCHANGE PARTITION操作以外，不會引起local 索引失效。

*EXCHANGE PARTITION操作是個很特殊的操作，加上update indexes引數，EXCHANGE PARTITION依然會造成local 索引失效。

需要注意的是，如果分割槽中不含資料，上面的操作都不會引起索引失效（EXCHANGE PARTITION除外）。

下面是實驗過程：

注意：

本實驗都是range分割槽

準備基表,id列作為分割槽key

create table part_test(id number,name varchar2(10))

partition by range (id)

(

partition p1 values less than (5),

partition p2 values less than (10),

partition p3 values less than (15)

);

SQL> select table_name,partition_name,HIGH_VALUE from dba_tab_partitions where table_name='PART_TEST' order by table_name,partition_name;

TABLE_NAME PARTITION_NAME HIGH_VALUE

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

PART_TEST P1 5

PART_TEST P2 10

PART_TEST P3 15

一、準備基表及索引

global和local資訊不在同一個資料字典中;

global索引資訊==>dba_indexes

local索引資訊==>dba_ind_partitions/user_ind_partitions

*可以透過dba_indexes判斷索引型別，如果status等於VALID或者UNUSABLE，索引型別是global;

status等於N/A，索引則是local索引。

1.建索引

建立global索引

create index ind_id on part_test(id);

建立local索引

create index ind_name on part_test(name) local;

PART_TEST上兩個索引名、表名、及索引狀態值

SQL> select INDEX_NAME,TABLE_OWNER,STATUS from dba_indexes where table_name='PART_TEST';

INDEX_NAME TABLE_OWNER STATUS

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

IND_ID DOWNLOAD VALID --global index

IND_NAME DOWNLOAD N/A --local index

2.索引狀態值

檢視global索引ind_id 資訊

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

INDEX_NAME TABLE_OWNER STATUS

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

IND_ID DOWNLOAD VALID

檢視local索引IND_NAME資訊

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

二、add partition

1.not plus "update indexes"

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

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 P4 20

2. plus "update indexes"

SQL> alter table part_test add partition p5 values less than (25) 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

IND_NAME P4 20 USABLE

IND_NAME P5 25 USABLE

結論：add partition時，不加update indexes不會破壞local及global索引的可用性;當然，加上update indexes更好更保險。

三、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引數能夠避免這個問題。

五、truncate partition

1.分割槽含有資料，不加update indexes

SQL> alter table part_test truncate partition p3;

Table truncated.

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 P3 15 USABLE

IND_NAME P4 20 USABLE

2.分割槽含有資料，加update indexes

SQL> insert into part_test values(11,'lucy'); --落在p3分割槽

1 row created.

SQL> commit;

Commit complete.

重建global索引

SQL> alter index IND_ID rebuild;

Index altered.

SQL> alter table part_test truncate partition p3 update indexes;

Table truncated.

加上update indexes後，global索引不再失效。

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

小結：分割槽表中含有資料，truncate partition會造成global索引失效;truncate partition加上update indexes可用避免這種情況。

六、SPLIT PARTITION（一個分割槽分裂為多個分割槽)

準備實驗分割槽

SQL> alter table part_test add partition max_part values less than(maxvalue);

Table altered.

SQL> insert into part_test values(21,'john');

1 row created.

SQL> insert into part_test values(30,'dog');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from part_test partition(MAX_PART);

ID NAME

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

21 john

30 dog

1.不加update indexes

SQL> alter table part_test split partition max_part at (25) into (partition p5,partition max_part );

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 P3 15 USABLE

IND_NAME P4 20 USABLE

IND_NAME P5 25 UNUSABLE

IND_NAME MAX_PART MAXVALUE UNUSABLE

2.加update indexes

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

IND_NAME MAX_PART MAXVALUE USABLE

SQL> alter table part_test split partition max_part at (25) into (partition p5,partition max_part ) 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

IND_NAME P5 25 USABLE

IND_NAME MAX_PART MAXVALUE USABLE

小結：split partition操作，會使global索引和local索引的原分割槽和分裂出的新分割槽都不可用。加上update indexes解決這個問題。

七、merge partition(合併分割槽)

現有分割槽

SQL> select table_name,partition_name,HIGH_VALUE from dba_tab_partitions where table_name='PART_TEST' order by PARTITION_NAME;

TABLE_NAME PARTITION_NAME HIGH_VALUE

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

PART_TEST MAX_PART MAXVALUE

PART_TEST P1 5

PART_TEST P3 15

PART_TEST P4 20

PART_TEST P5 25

p4和p5分割槽都含有資料，把他們合併為p6分割槽

1.不加update indexes

SQL> alter table PART_TEST merge partitions p4,p5 into partition p6;

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

新生成的分割槽的索引是失效的。

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 P6 25 UNUSABLE

IND_NAME MAX_PART MAXVALUE USABLE

2.加update indexes

SQL> alter table PART_TEST merge partitions p4,p5 into partition p6 update indexes;

Table altered.

global和local索引都正常。

SQL> 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 P6 25 USABLE

IND_NAME MAX_PART MAXVALUE USABLE

八、COALESCE PARTITION （收縮表分割槽）

僅能應用在hash 分割槽表，執行一次就少一個分割槽，如一個hash分割槽表含有3個分割槽，COALESCE PARTITION 一次就成了2個分割槽。

create table part_hash(id number,name nvarchar2(20))

partition by hash(id)

(

partition p1,

partition p2,

partition p3,

partition p4

);

create index ind_hash_id on part_hash(id);

create index ind_hash_name on part_hash(name) local;

insert into part_hash values(1,'tom');

insert into part_hash values(2,'lucy');

insert into part_hash values(3,'john');

insert into part_hash values(4,'kate');

索引資訊

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

INDEX_NAME TABLE_OWNER STATUS

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

IND_HASH_ID DOWNLOAD VALID

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

INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS

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

IND_HASH_NAME P1 USABLE

IND_HASH_NAME P2 USABLE

IND_HASH_NAME P3 USABLE

IND_HASH_NAME P4 USABLE

表資訊

SQL> select table_name,partition_name,HIGH_VALUE from dba_tab_partitions where table_name='PART_HASH' order by table_name,partition_name;

TABLE_NAME PARTITION_NAME HIGH_VALUE

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

PART_HASH P1

PART_HASH P2

PART_HASH P3

PART_HASH P4

1.下面進行coalesce partiton操作,不加update index

SQL> alter table PART_HASH coalesce partition;

Table altered.

操作引起global和local索引失效。

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

INDEX_NAME TABLE_OWNER STATUS

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

IND_HASH_ID DOWNLOAD UNUSABLE

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

INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS

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

IND_HASH_NAME P1 USABLE

IND_HASH_NAME P2 UNUSABLE

IND_HASH_NAME P3 USABLE

修復索引

alter index IND_HASH_ID rebuild;

alter index IND_HASH_NAME rebuild partition p2;

2.加update indexes

SQL> alter table PART_HASH coalesce partition update indexes;

Table altered.

操作後，索引沒有失效。

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

INDEX_NAME TABLE_OWNER STATUS

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

IND_HASH_ID DOWNLOAD VALID

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

INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS

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

IND_HASH_NAME P1 USABLE

IND_HASH_NAME P2 USABLE

九、exchange partition

exchange partition是表與表，表與分割槽，分割槽與分割槽資料交換的命令，不是將錶轉換成分割槽，或者分割槽轉換成表

依然用上面的part_test分割槽表

SQL> select * from PART_TEST partition(p6);

ID NAME

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

18 ok

21 john

21 jodan

新建一個表exchange_test

create table exchange_test(id number,name varchar2(10));

下面把PART_TEST partition(p6)中的資料exchange到exchange_test中

SQL> alter table PART_TEST exchange partition p6 with table exchange_test;

Table altered.

p6分割槽沒有資料了

SQL> select * from PART_TEST partition(p6);

no rows selected

資料跑到了exchange_test中。

SQL> select * from exchange_test;

ID NAME

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

18 ok

21 john

21 jodan

看分割槽表索引情況，global索引失效，p6上的local索引失效。

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

INDEX_NAME TABLE_OWNER STATUS

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

IND_ID DOWNLOAD UNUSABLE

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 P6 25 UNUSABLE

IND_NAME MAX_PART MAXVALUE USABLE

恢復索引

alter index IND_ID rebuild;

alter index IND_NAME rebuild partition p6;

2.加update indexes

SQL> alter table PART_TEST exchange partition p6 with table exchange_test update indexes;

資料又回去了

SQL> select * from exchange_test;

no rows selected

SQL> select * from PART_TEST partition(p6);

ID NAME

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

18 ok

21 john

21 jodan

看索引情況

global索引正常

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

INDEX_NAME TABLE_OWNER STATUS

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

IND_ID DOWNLOAD VALID

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 USABLE

IND_NAME P6 25 UNUSABLE

IND_NAME MAX_PART MAXVALUE USABLE

小結：加上update indexes，global索引不失效，但是local 索引依然失效。

十、重新命名分割槽和local索引名字都不會影響索引狀態

SQL> alter table part_test rename partition P6 to p5;

SQL> alter index IND_NAME rename partition P6 to p5;

注意：

local索引不能整個rebuild，要rebuild索引分割槽。

SQL> alter index IND_NAME rebuild;

alter index IND_NAME rebuild

*

ERROR at line 1:

ORA-14086: a partitioned index may not be rebuilt as a whole

SQL> alter index IND_NAME rebuild partition MAX_PART;

Index altered.

如果local索引大面積不可用，那麼用動態sql生成重語句吧

SQL> select 'alter index INDEX_NAME rebuild partition '||PARTITION_NAME||';' from user_ind_partitions where INDEX_NAME='INDEX_NAME' and STATUS='UNUSABLE';

出處:http://blog.chinaunix.net/uid-23284114-id-3421922.html

操作分割槽表對global和local索引的影響

相關文章