After Oracle 12.2, ALTER TABLE.. MODIFY converts non-partitioned tables to partitioned tables

Description

This article will include the following:

ORACLE 19.5 Test ALTER TABLE... MODIFY converts non-partitioned tables to partitioned tables

Create a test table

CREATE TABLE TEST_MODIFY (ID NUMBER,NAME VARCHAR2 (30), STATUS VARCHAR2 (10)); insert 300000 data

Declarev1 number;beginfor I in 1..300000loopexecute immediate 'insert into test_modify values (: v1 exec dbms_stats.gather_table_stats exec dbms_stats.gather_table_stats >' CZH',TABNAME= > 'TEST_MODIFY',cascade= > TRUE)' using ibot end loop;commit;end;/ adds primary key constraint and index ALTER TABLE TEST_MODIFY ADD CONSTRAINT PK_TEST_MODIFY PRIMARY KEY (ID); CREATE INDEX IDX_TEST_MODIFY ON TEST_MODIFY (CASE STATUS WHEN'N' THEN'N' END); and collects statistics information Query index status 14:56:06 CZH@czhpdb > select INDEX_NAME,NUM_ROWS,LEAF_BLOCKS,status from user_indexes where index_name in ('IDX_TEST_MODIFY','PK_TEST_MODIFY') INDEX_NAME NUM_ROWS LEAF_BLOCKS STATUS -IDX_TEST_MODIFY 00 VALIDPK_TEST_MODIFY 300000 626 VALID conversion ALTER TABLE... MODIFYALTER TABLE TEST_MODIFY MODIFYPARTITION BY RANGE (ID) (PARTITION P1 VALUES LESS THAN (100000), PARTITION P2 VALUES LESS THAN (200000), PARTITION P3 values less than (maxvalue)) ONLINEUPDATE INDEXES; query index status 14:57:11 CZH@czhpdb > select INDEX_NAME,NUM_ROWS,LEAF_BLOCKS,status from user_indexes where index_name in ('IDX_TEST_MODIFY','PK_TEST_MODIFY') INDEX_NAME NUM_ROWS LEAF_BLOCKS STATUS -IDX_TEST_MODIFY 00 VALIDPK_TEST_MODIFY 300000 626 N _ PK_TEST_MODIFY _ A _ A _ An indicates that there is an index subpartition It shows that the competitive index is converted to local, and the ordinary index is converted to global index * / the official document description of index conversion.

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

Reference documentation:

Oracle ®Database VLDB and Partitioning Guide