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
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31439444/viewspace-2686040/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 將非分割槽錶轉換為分割槽表
- 將mysql非分割槽錶轉換為分割槽表MySql
- Oracle分割槽表(Partition Table)Oracle
- 分割槽表PARTITION table(轉)
- 非分割槽錶轉換成分割槽表
- 批次轉換分割槽表為普通表
- 批量轉換分割槽表為普通表
- 將一個非分割槽錶轉換為分割槽表
- 非分割槽錶轉換為分割槽表和partition indexIndex
- oracle分割槽表和分割槽表exchangeOracle
- Oracle 將普通錶轉換為分割槽表Oracle
- 普通錶轉換為分割槽表
- 分割槽表PARTITION table
- 【分割槽】如何將一個普通錶轉換為分割槽表
- oracle分割槽表和非分割槽表exchangeOracle
- Oracle分割槽表及分割槽索引Oracle索引
- ORACLE 11g 範圍分割槽錶轉換INTERVAL分割槽表Oracle
- 將普通錶轉換為分割槽表
- ORACLE分割槽表管理[轉]Oracle
- Oracle 表分割槽Oracle
- oracle分割槽表Oracle
- oracle表分割槽Oracle
- Oracle 分割槽表Oracle
- 簡單ORACLE分割槽表、分割槽索引Oracle索引
- oracle將表配置為分割槽表Oracle
- [引用分割槽表]Oracle 11g新特性之引用分割槽表Oracle
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- 非分割槽錶轉換成分割槽表以及注意事項
- oracle分割槽表總結(轉)Oracle
- 【轉】Oracle分割槽表維護Oracle
- ORACLE將不同表改為分割槽表Oracle
- Oracle分割槽表基礎運維-06分割槽表索引Oracle運維索引
- oracle 分割槽表move和包含分割槽表的lob moveOracle
- 分割槽表概念 partitioning table
- Oracle查詢分割槽表的最後一個分割槽值Oracle
- 堆錶轉換成分割槽表
- Oracle 建立分割槽表Oracle
- ORACLE分割槽表管理Oracle