本文将包含如下内容：
ORACLE 19.5 测试ALTER TABLE ... MODIFY转换非分区表为分区表
计算
安全
数据库
网络和加速
企业服务
CREATE TABLE TEST_MODIFY(ID NUMBER,NAME VARCHAR2(30),STATUS VARCHAR2(10));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 VALIDALTER 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
亿速云「云服务器」，即开即用、新一代英特尔至强铂金CPU、三副本存储NVMe SSD云盘，价格低至29元/月。点击查看>>
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
原文链接：http://blog.itpub.net/31439444/viewspace-2686040/
