Oracle12.2之后ALTERTABLE..MODIFY转换非分区表为分区表
说明
本文将包含如下内容:
成都创新互联公司-云计算及IDC服务提供商,涵盖公有云、IDC机房租用、大邑服务器托管、等保安全、私有云建设等企业级互联网基础服务,欢迎联系:18982081108
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
网页名称:Oracle12.2之后ALTERTABLE..MODIFY转换非分区表为分区表
文章URL:http://scyanting.com/article/jdsisd.html