说明 本文将包含如下内容: oracle 19.5 测试ALTER TABLE ... MODIFY转换非分区表为分区表 创建测试表 CREATE TABLE TEST_MODIF
本文将包含如下内容:
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 VALID
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
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
--结束END--
本文标题: Oracle 12.2之后ALTER TABLE .. MODIFY转换非分区表为分区表
本文链接: https://www.lsjlt.com/news/46451.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-04-29
2024-04-29
2024-04-29
2024-04-29
2024-04-29
2024-04-29
2024-04-29
2024-04-29
2024-04-29
2024-04-28
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0