说明

本文将包含如下内容：

ORACLE 19.5 测试ALTER TABLE ... MODIFY转换非分区表为分区表

创建测试表

CREATETABLETEST_MODIFY(IDNUMBER,NAMEVARCHAR2(30),STATUSVARCHAR2(10));插入30万数据

declarev1number;beginforiin1..300000loopexecuteimmediate'insertintotest_modifyvalues(:v1,''czh'',''Y'')'usingi;endloop;commit;end;/添加主键约束与索引

ALTERTABLETEST_MODIFYADDCONSTRAINTPK_TEST_MODIFYPRIMARYKEY(ID);CREATEINDEXIDX_TEST_MODIFYONTEST_MODIFY(CASESTATUSWHEN'N'THEN'N'END);收集统计信息

execdbms_stats.gather_table_stats(OWNNAME=>'CZH',TABNAME=>'TEST_MODIFY',cascade=>TRUE);查询索引状态

14:56:06CZH@czhpdb>selectINDEX_NAME,NUM_ROWS,LEAF_BLOCKS,statusfromuser_indexeswhereindex_namein('IDX_TEST_MODIFY','PK_TEST_MODIFY');INDEX_NAMENUM_ROWSLEAF_BLOCKSSTATUS--------------------------------------------------------------------------------------------------------------IDX_TEST_MODIFY00VALIDPK_TEST_MODIFY300000626VALID转换ALTER TABLE ... MODIFY

ALTERTABLETEST_MODIFYMODIFYPARTITIONBYRANGE(ID)(PARTITIONP1VALUESLESSTHAN(100000),PARTITIONP2VALUESLESSTHAN(200000),PARTITIONP3valueslessthan(maxvalue))ONLINEUPDATEINDEXES;查询索引状态

14:57:11CZH@czhpdb>selectINDEX_NAME,NUM_ROWS,LEAF_BLOCKS,statusfromuser_indexeswhereindex_namein('IDX_TEST_MODIFY','PK_TEST_MODIFY');INDEX_NAMENUM_ROWSLEAF_BLOCKSSTATUS--------------------------------------------------------------------------------------------------------------IDX_TEST_MODIFY00VALIDPK_TEST_MODIFY300000626N/A/*PK_TEST_MODIFY状态N/A说明有索引子分区，说明pk索引转换成了local，普通索引转换成了globalindex*/索引转换官方文档说明

If you do not specify the INDEXES clause or the INDEXES clause does not specify all

the indexes on the original non-partitioned table, then the following default

behavior applies for all unspecified indexes.

– Global partitioned indexes remain the same and retain the original partitioning

shape.

– Non-prefixed indexes become global nonpartitioned indexes.

Prefixed indexes are converted to local partitioned indexes.

Prefixed means that the partition key columns are included in the index

definition, but the index definition is not limited to including the partitioning

keys only.

– Bitmap indexes become local partitioned indexes, regardless whether they are

prefixed or not.

Bitmap indexes must always be local partitioned indexes.

• The conversion operation cannot be performed if there are domain indexes

参考文档：

Oracle® DatabaseVLDB and Partitioning Guide