添加、删除分区及全局索引的变化

针对mytest00这个表

添加分区：

SQL> ALTER TABLE mytest00 ADD partition T4 values  less than (3000) tablespace users;

Table altered.

SQL> insert into MyTest00 values (2001,'f1112');

1 row created.

SQL> commit;

Commit complete.

SQL>  select * from myTest00 partition(T4);

        ID

----------

NAME

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

      2001

f1112

看全局索引的变化
SQL> select a.table_name,a.partitioning_type,a.index_name,b.high_value,status

from user_part_indexes a

left join user_ind_partitions b

on a.index_name = b.index_name

where a.table_name = 'MYTEST00'; 

TABLE_NAME                     PARTITI INDEX_NAME

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

HIGH_VALUE

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

STATUS

--------

MYTEST00                       RANGE   GLOBAL_INDEX_MYTEST00

100

USABLE

MYTEST00                       RANGE   GLOBAL_INDEX_MYTEST00

1000

USABLE

TABLE_NAME                     PARTITI INDEX_NAME

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

HIGH_VALUE

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

STATUS

--------

MYTEST00                       RANGE   GLOBAL_INDEX_MYTEST00

MAXVALUE

USABLE

删除分区：

SQL> alter table MYTEST00 drop partition T2;

Table altered.

SQL> select a.table_name,a.partitioning_type,a.index_name,b.high_value,status

from user_part_indexes a

left join user_ind_partitions b

on a.index_name = b.index_name

where a.table_name = 'MYTEST00';  2    3    4    5 

TABLE_NAME                     PARTITI INDEX_NAME

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

HIGH_VALUE

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

STATUS

--------

MYTEST00                       RANGE   GLOBAL_INDEX_MYTEST00

100

UNUSABLE

MYTEST00                       RANGE   GLOBAL_INDEX_MYTEST00

1000

UNUSABLE

TABLE_NAME                     PARTITI INDEX_NAME

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

HIGH_VALUE

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

STATUS

--------

MYTEST00                       RANGE   GLOBAL_INDEX_MYTEST00

MAXVALUE

UNUSABLE

看到了吧，分区索引不可用，只能重建

SQL> alter index GLOBAL_INDEX_MYTEST00 rebuild partition T1;

Index altered.

SQL> alter index GLOBAL_INDEX_MYTEST00 rebuild partition T2;

Index altered.

SQL> alter index GLOBAL_INDEX_MYTEST00 rebuild partition T3;

Index altered.

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

转载于:http://blog.itpub.net/31448824/viewspace-2137545/