添加、删除分区及本地索引的变化

针对mytest这个表，注意创建分区的方式，有maxvalue关键字。

添加分区：一定要注意，插入一个表分区，插入时必须是在最大的表分区住下插入

此时需要split 

示例如下：

SQL> alter table MYTEST split partition T3 at (3000) into (partition T4, partition T3);

Table altered.

SQL> select table_name,partition_name,high_value,tablespace_name from user_tab_partitions where table_name='MYTEST' order by partition_position;

TABLE_NAME                     PARTITION_NAME

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

HIGH_VALUE

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

TABLESPACE_NAME

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

MYTEST                         T1

100

MYTABLESPACE_1

MYTEST                         T2

1000

MYTABLESPACE_2

TABLE_NAME                     PARTITION_NAME

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

HIGH_VALUE

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

TABLESPACE_NAME

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

MYTEST                         T4

3000

MYTABLESPACE_3

MYTEST                         T3

MAXVALUE

TABLE_NAME                     PARTITION_NAME

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

HIGH_VALUE

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

TABLESPACE_NAME

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

MYTABLESPACE_3

看添加分区之后，索引没变化。

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 = 'MYTEST';   

TABLE_NAME                     PARTITI INDEX_NAME

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

HIGH_VALUE

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

STATUS

--------

MYTEST00                       RANGE   GLOBAL_INDEX_MYTEST00

100

USABLE

MYTEST00                       RANGE   GLOBAL_INDEX_MYTEST00

MAXVALUE

USABLE

TABLE_NAME                     PARTITI INDEX_NAME

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

HIGH_VALUE

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

STATUS

--------

MYTEST00                       RANGE   GLOBAL_INDEX_MYTEST00

1000

USABLE

删除分区：

SQL> alter table MYTEST drop partition T4;

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 = 'MYTEST';  

TABLE_NAME                     PARTITI INDEX_NAME

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

HIGH_VALUE

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

STATUS

--------

MYTEST00                       RANGE   GLOBAL_INDEX_MYTEST00

100

USABLE

MYTEST00                       RANGE   GLOBAL_INDEX_MYTEST00

MAXVALUE

USABLE

TABLE_NAME                     PARTITI INDEX_NAME

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

HIGH_VALUE

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

STATUS

--------

MYTEST00                       RANGE   GLOBAL_INDEX_MYTEST00

1000

USABLE

还是可用的。

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

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