Oracle 12.2之后ALTER TABLE .. MODIFY轉換非分區表為分區表

說明

本文將包含如下內容：

ORACLE 19.5 測試ALTER TABLE ... MODIFY轉換非分區表為分區表

創建測試表

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

插入30萬數據

declare
v1 number;
begin
for i in 1..300000
loop
execute immediate 'insert into test_modify values(:v1,''czh'',''Y'')' using i;
end loop;
commit;
end;
/

添加主鍵約束與索引

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);

收集統計信息

exec dbms_stats.gather_table_stats(OWNNAME=>'CZH',TABNAME=>'TEST_MODIFY',cascade=>TRUE);

查詢索引狀態

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                                             0                                        0 VALID
PK_TEST_MODIFY                                         300000                                      626 VALID

轉換ALTER TABLE ... MODIFY

ALTER TABLE TEST_MODIFY MODIFY
PARTITION BY RANGE (ID)
( PARTITION P1 VALUES LESS THAN (100000),
PARTITION P2 VALUES LESS THAN (200000),
PARTITION P3 values less than (maxvalue)
) ONLINE
UPDATE INDEXES;

查詢索引狀態

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                                             0                                        0 VALID
PK_TEST_MODIFY                                         300000                                      626 N/A
/* PK_TEST_MODIFY狀態N/A說明有索引子分區，說明pk索引轉換成了local，普通索引轉換成了global index */

索引轉換官方文檔說明

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? Database VLDB and Partitioning Guide