普通錶轉換為分割槽表
yang@ORACL> create table yangtmp ( id number, time date );
表已建立。
yang@ORACL> insert into yangtmp select rownum id ,sysdate-dbms_random.value(1,500) time
2 from dual
3 connect by level <=1e5;
已建立100000行。
yang@ORACL> select count(1) from yangtmp;
COUNT(1)
----------
100000
yang@ORACL> commit;
提交完成。
yang@ORACL> create table yang_part (
2 id number,
3 time date
4 )
5 partition by range (time) (
6 partition p2010 values less than (to_date('20110101','yyyymmdd')),
7 partition p201101 values less than (to_date('20110401','yyyymmdd')),
8 partition p201102 values less than (to_date('20110701','yyyymmdd')),
9 partition p201103 values less than (to_date('20111001','yyyymmdd')),
10 partition p201104 values less than (to_date('20120101','yyyymmdd'))
11 );
表已建立。
yang@ORACL> rename yangtmp to yang_old;
表已重新命名。
yang@ORACL> rename yang_part to yang_tmp;
表已重新命名。
yang@ORACL> select count(1) from yang_old;
COUNT(1)
----------
100000
yang@ORACL> select count(1) from yang_tmp;
COUNT(1)
----------
0
yang@ORACL> rename yang_tmp to yang_part;
表已重新命名。
yang@ORACL> insert into yang_part select id,time from yang_old;
已建立100000行。
yang@ORACL> rename yang_part to yang_tmp;
表已重新命名。
yang@ORACL> select count(1) from yang_old;
COUNT(1)
----------
100000
yang@ORACL> select count(1) from yang_tmp;
COUNT(1)
----------
100000
yang@ORACL> select count(1) from yang_tmp partition (p2010);
COUNT(1)
----------
85142
yang@ORACL> select count(1) from yang_tmp partition (p201101);
COUNT(1)
----------
14858
yang@ORACL> select count(1) from yang_tmp partition (p201102);
COUNT(1)
----------
0
yang@ORACL> select count(1) from yang_tmp partition (p201103);
COUNT(1)
----------
0
yang@ORACL> select count(1) from yang_tmp partition (p201104);
COUNT(1)
----------
0
優點：方法簡單易用，由於採用DDL語句，不會產生UNDO，且只產生少量REDO，效率相對較高，而且建表完成後資料已經在分佈到各個分割槽中了。
不足：對於資料的一致性方面還需要額外的考慮。由於幾乎沒有辦法透過手工鎖定T表的方式保證一致性，在執行CREATE TABLE語句和RENAME T_NEW TO T語句直接的修改可能會丟失，如果要保證一致性，需要在執行完語句後對資料進行檢查，而這個代價是比較大的。另外在執行兩個RENAME語句之間執行的對T的訪問會失敗。
適用於修改不頻繁的表，在閒時進行操作，表的資料量不宜太大。
yang@rac1>create table yangtmp (id number primary key ,time date);
表已建立。
已用時間: 00: 00: 00.44
yang@rac1>insert into yangtmp select rownum id,sysdate -dbms_random.value(2,500) time
2 from dual
3 connect by level <=1e4;
已建立10000行。
已用時間: 00: 00: 00.29
yang@rac1>commit;
提交完成。
已用時間: 00: 00: 00.02
yang@rac1>create table yang_part (
2 id number,
3 time date
4 )
5 partition by range (time) (
6 partition p2010 values less than (to_date('20110101','yyyymmdd')),
7 partition p201101 values less than (to_date('20110401','yyyymmdd')),
8 partition p201102 values less than (to_date('20110701','yyyymmdd')),
9 partition p201103 values less than (to_date('20111001','yyyymmdd')),
10 partition p201104 values less than (to_date('20120101','yyyymmdd')) );
表已建立。
已用時間: 00: 00: 00.09
yang@rac1>select count(*) from yang_part;
COUNT(*)
----------
0
已用時間: 00: 00: 00.01
yang@rac1>alter table yang_part exchange partition p201104 with table yangtmp;
alter table yang_part exchange partition p201104 with table yangtmp
*
第 1 行出現錯誤:
ORA-14099: 未對指定分割槽限定表中的所有行
yang@rac1>alter table yang_part exchange partition p201104 with table yangtmp without validation;
表已更改。
已用時間: 00: 00: 00.11
yang@rac1>select count(1) from yang_part;
COUNT(1)
----------
10000
已用時間: 00: 00: 00.00
yang@rac1>select count(1) from yang_part partition (p201104);
COUNT(1)
----------
10000
已用時間: 00: 00: 00.01
yang@rac1>select count(1) from yang_part partition (p201103);
COUNT(1)
----------
0
已用時間: 00: 00: 00.00
yang@rac1>rename yangtmp to yangtmp2;
表已重新命名。
已用時間: 00: 00: 00.16
yang@rac1>rename yang_part to yangtmp;
表已重新命名。
已用時間: 00: 00: 00.00
yang@rac1>select count(1) from yangtmp;
COUNT(1)
----------
10000
已用時間: 00: 00: 00.01
yang@rac1>select count(1) from yangtmp2;
COUNT(1)
----------
0
已用時間: 00: 00: 00.00
優點：只是對資料字典中分割槽和表的定義進行了修改，沒有資料的修改或複製，效率最高。如果對資料在分割槽中的分佈沒有進一步要求的話，實現比較簡單。在執行完RENAME操作後，可以檢查T_OLD中是否存在資料，如果存在的話，直接將這些資料插入到T中，可以保證對T插入的操作不會丟失。
不足：仍然存在一致性問題，交換分割槽之後RENAME T_NEW TO T之前，查詢、更新和刪除會出現錯誤或訪問不到資料。如果要求資料分佈到多個分割槽中，則需要進行分割槽的SPLIT操作，會增加操作的複雜度，效率也會降低。
適用於包含大資料量的錶轉到分割槽表中的一個分割槽的操作。應儘量在閒時進行操作。
yang@rac1>create table tabyang ( id number primary key ,time date);
表已建立。
已用時間: 00: 00: 00.01
yang@rac1>insert into tabyang select rownum id,sysdate-dbms_random.value(2,500) time
2 from dual
3 connect by level <=1e4;
已建立10000行。
已用時間: 00: 00: 00.11
yang@rac1>commit;
提交完成。
已用時間: 00: 00: 00.02
yang@rac1>EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'TABYANG', DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL 過程已成功完成。
已用時間: 00: 00: 00.21
yang@rac1>create table yangpart (
2 id number,
3 time date
4 )
5 partition by range (time) (
6 partition p2010 values less than (to_date('20110101','yyyymmdd')),
7 partition p201101 values less than (to_date('20110401','yyyymmdd')),
8 partition p201102 values less than (to_date('20110701','yyyymmdd')),
9 partition p201103 values less than (to_date('20111001','yyyymmdd')),
10 partition p201104 values less than (to_date('20120101','yyyymmdd')) );
表已建立。
已用時間: 00: 00: 00.02
yang@rac1>EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'TABYANG', 'YANGPART', 'ID ID, TIME TIME', DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL 過程已成功完成。
已用時間: 00: 00: 02.60
yang@rac1>EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'TABYANG', 'YANGPART');
PL/SQL 過程已成功完成。
已用時間: 00: 00: 01.10
yang@rac1>SELECT COUNT(*) FROM YANGPART;
COUNT(*)
----------
10000
已用時間: 00: 00: 00.00
yang@rac1>SELECT COUNT(*) FROM TABYANG;
COUNT(*)
----------
10000
已用時間: 00: 00: 00.28
yang@rac1>SELECT COUNT(*) FROM YANGPART PARTITION (P20110401);
SELECT COUNT(*) FROM YANGPART PARTITION (P20110401)
*
第 1 行出現錯誤:
ORA-14501: 物件未分割槽
已用時間: 00: 00: 00.18
yang@rac1>SELECT COUNT(*) FROM YANGPART PARTITION (P201104);
SELECT COUNT(*) FROM YANGPART PARTITION (P201104)
*
第 1 行出現錯誤:
ORA-14501: 物件未分割槽
已用時間: 00: 00: 00.00
yang@rac1>SELECT COUNT(*) FROM YANGPART PARTITION (P201103);
SELECT COUNT(*) FROM YANGPART PARTITION (P201103)
*
第 1 行出現錯誤:
ORA-14501: 物件未分割槽
已用時間: 00: 00: 00.00
yang@rac1>SELECT COUNT(*) FROM YANGPART PARTITION (P201101);
SELECT COUNT(*) FROM YANGPART PARTITION (P201101)
*
第 1 行出現錯誤:
ORA-14501: 物件未分割槽
已用時間: 00: 00: 00.00
yang@rac1>SELECT COUNT(*) FROM YANGPART PARTITION (p201101);
SELECT COUNT(*) FROM YANGPART PARTITION (p201101)
*
第 1 行出現錯誤:
ORA-14501: 物件未分割槽
已用時間: 00: 00: 00.00
yang@rac1>SELECT COUNT(*) FROM TABYANG PARTITION (p201101);
COUNT(*)
----------
1580
已用時間: 00: 00: 00.00
yang@rac1>SELECT COUNT(*) FROM TABYANG PARTITION (p201102);
COUNT(*)
----------
0
已用時間: 00: 00: 00.00
yang@rac1>SELECT COUNT(*) FROM TABYANG PARTITION (p201103);
COUNT(*)
----------
0
已用時間: 00: 00: 00.00
yang@rac1>SELECT COUNT(*) FROM TABYANG PARTITION (p201104);
COUNT(*)
----------
0
已用時間: 00: 00: 00.01
yang@rac1>SELECT COUNT(*) FROM TABYANG PARTITION (p2010);
COUNT(*)
----------
8420
已用時間: 00: 00: 00.01
優點：保證資料的一致性，在大部分時間內，表TABYANG都可以正常進行DML操作。只在切換的瞬間鎖表，具有很高的可用性。這種方法具有很強的靈活性，對各種不同的需要都能滿足。而且，可以在切換前進行相應的授權並建立各種約束，可以做到切換完成後不再需要任何額外的管理操作。
不足：實現上比上面兩種略顯複雜。
表已建立。
yang@ORACL> insert into yangtmp select rownum id ,sysdate-dbms_random.value(1,500) time
2 from dual
3 connect by level <=1e5;
已建立100000行。
yang@ORACL> select count(1) from yangtmp;
COUNT(1)
----------
100000
yang@ORACL> commit;
提交完成。
yang@ORACL> create table yang_part (
2 id number,
3 time date
4 )
5 partition by range (time) (
6 partition p2010 values less than (to_date('20110101','yyyymmdd')),
7 partition p201101 values less than (to_date('20110401','yyyymmdd')),
8 partition p201102 values less than (to_date('20110701','yyyymmdd')),
9 partition p201103 values less than (to_date('20111001','yyyymmdd')),
10 partition p201104 values less than (to_date('20120101','yyyymmdd'))
11 );
表已建立。
yang@ORACL> rename yangtmp to yang_old;
表已重新命名。
yang@ORACL> rename yang_part to yang_tmp;
表已重新命名。
yang@ORACL> select count(1) from yang_old;
COUNT(1)
----------
100000
yang@ORACL> select count(1) from yang_tmp;
COUNT(1)
----------
0
yang@ORACL> rename yang_tmp to yang_part;
表已重新命名。
yang@ORACL> insert into yang_part select id,time from yang_old;
已建立100000行。
yang@ORACL> rename yang_part to yang_tmp;
表已重新命名。
yang@ORACL> select count(1) from yang_old;
COUNT(1)
----------
100000
yang@ORACL> select count(1) from yang_tmp;
COUNT(1)
----------
100000
yang@ORACL> select count(1) from yang_tmp partition (p2010);
COUNT(1)
----------
85142
yang@ORACL> select count(1) from yang_tmp partition (p201101);
COUNT(1)
----------
14858
yang@ORACL> select count(1) from yang_tmp partition (p201102);
COUNT(1)
----------
0
yang@ORACL> select count(1) from yang_tmp partition (p201103);
COUNT(1)
----------
0
yang@ORACL> select count(1) from yang_tmp partition (p201104);
COUNT(1)
----------
0
優點：方法簡單易用，由於採用DDL語句，不會產生UNDO，且只產生少量REDO，效率相對較高，而且建表完成後資料已經在分佈到各個分割槽中了。
不足：對於資料的一致性方面還需要額外的考慮。由於幾乎沒有辦法透過手工鎖定T表的方式保證一致性，在執行CREATE TABLE語句和RENAME T_NEW TO T語句直接的修改可能會丟失，如果要保證一致性，需要在執行完語句後對資料進行檢查，而這個代價是比較大的。另外在執行兩個RENAME語句之間執行的對T的訪問會失敗。
適用於修改不頻繁的表，在閒時進行操作，表的資料量不宜太大。
yang@rac1>create table yangtmp (id number primary key ,time date);
表已建立。
已用時間: 00: 00: 00.44
yang@rac1>insert into yangtmp select rownum id,sysdate -dbms_random.value(2,500) time
2 from dual
3 connect by level <=1e4;
已建立10000行。
已用時間: 00: 00: 00.29
yang@rac1>commit;
提交完成。
已用時間: 00: 00: 00.02
yang@rac1>create table yang_part (
2 id number,
3 time date
4 )
5 partition by range (time) (
6 partition p2010 values less than (to_date('20110101','yyyymmdd')),
7 partition p201101 values less than (to_date('20110401','yyyymmdd')),
8 partition p201102 values less than (to_date('20110701','yyyymmdd')),
9 partition p201103 values less than (to_date('20111001','yyyymmdd')),
10 partition p201104 values less than (to_date('20120101','yyyymmdd')) );
表已建立。
已用時間: 00: 00: 00.09
yang@rac1>select count(*) from yang_part;
COUNT(*)
----------
0
已用時間: 00: 00: 00.01
yang@rac1>alter table yang_part exchange partition p201104 with table yangtmp;
alter table yang_part exchange partition p201104 with table yangtmp
*
第 1 行出現錯誤:
ORA-14099: 未對指定分割槽限定表中的所有行
yang@rac1>alter table yang_part exchange partition p201104 with table yangtmp without validation;
表已更改。
已用時間: 00: 00: 00.11
yang@rac1>select count(1) from yang_part;
COUNT(1)
----------
10000
已用時間: 00: 00: 00.00
yang@rac1>select count(1) from yang_part partition (p201104);
COUNT(1)
----------
10000
已用時間: 00: 00: 00.01
yang@rac1>select count(1) from yang_part partition (p201103);
COUNT(1)
----------
0
已用時間: 00: 00: 00.00
yang@rac1>rename yangtmp to yangtmp2;
表已重新命名。
已用時間: 00: 00: 00.16
yang@rac1>rename yang_part to yangtmp;
表已重新命名。
已用時間: 00: 00: 00.00
yang@rac1>select count(1) from yangtmp;
COUNT(1)
----------
10000
已用時間: 00: 00: 00.01
yang@rac1>select count(1) from yangtmp2;
COUNT(1)
----------
0
已用時間: 00: 00: 00.00
優點：只是對資料字典中分割槽和表的定義進行了修改，沒有資料的修改或複製，效率最高。如果對資料在分割槽中的分佈沒有進一步要求的話，實現比較簡單。在執行完RENAME操作後，可以檢查T_OLD中是否存在資料，如果存在的話，直接將這些資料插入到T中，可以保證對T插入的操作不會丟失。
不足：仍然存在一致性問題，交換分割槽之後RENAME T_NEW TO T之前，查詢、更新和刪除會出現錯誤或訪問不到資料。如果要求資料分佈到多個分割槽中，則需要進行分割槽的SPLIT操作，會增加操作的複雜度，效率也會降低。
適用於包含大資料量的錶轉到分割槽表中的一個分割槽的操作。應儘量在閒時進行操作。
yang@rac1>create table tabyang ( id number primary key ,time date);
表已建立。
已用時間: 00: 00: 00.01
yang@rac1>insert into tabyang select rownum id,sysdate-dbms_random.value(2,500) time
2 from dual
3 connect by level <=1e4;
已建立10000行。
已用時間: 00: 00: 00.11
yang@rac1>commit;
提交完成。
已用時間: 00: 00: 00.02
yang@rac1>EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'TABYANG', DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL 過程已成功完成。
已用時間: 00: 00: 00.21
yang@rac1>create table yangpart (
2 id number,
3 time date
4 )
5 partition by range (time) (
6 partition p2010 values less than (to_date('20110101','yyyymmdd')),
7 partition p201101 values less than (to_date('20110401','yyyymmdd')),
8 partition p201102 values less than (to_date('20110701','yyyymmdd')),
9 partition p201103 values less than (to_date('20111001','yyyymmdd')),
10 partition p201104 values less than (to_date('20120101','yyyymmdd')) );
表已建立。
已用時間: 00: 00: 00.02
yang@rac1>EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'TABYANG', 'YANGPART', 'ID ID, TIME TIME', DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL 過程已成功完成。
已用時間: 00: 00: 02.60
yang@rac1>EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'TABYANG', 'YANGPART');
PL/SQL 過程已成功完成。
已用時間: 00: 00: 01.10
yang@rac1>SELECT COUNT(*) FROM YANGPART;
COUNT(*)
----------
10000
已用時間: 00: 00: 00.00
yang@rac1>SELECT COUNT(*) FROM TABYANG;
COUNT(*)
----------
10000
已用時間: 00: 00: 00.28
yang@rac1>SELECT COUNT(*) FROM YANGPART PARTITION (P20110401);
SELECT COUNT(*) FROM YANGPART PARTITION (P20110401)
*
第 1 行出現錯誤:
ORA-14501: 物件未分割槽
已用時間: 00: 00: 00.18
yang@rac1>SELECT COUNT(*) FROM YANGPART PARTITION (P201104);
SELECT COUNT(*) FROM YANGPART PARTITION (P201104)
*
第 1 行出現錯誤:
ORA-14501: 物件未分割槽
已用時間: 00: 00: 00.00
yang@rac1>SELECT COUNT(*) FROM YANGPART PARTITION (P201103);
SELECT COUNT(*) FROM YANGPART PARTITION (P201103)
*
第 1 行出現錯誤:
ORA-14501: 物件未分割槽
已用時間: 00: 00: 00.00
yang@rac1>SELECT COUNT(*) FROM YANGPART PARTITION (P201101);
SELECT COUNT(*) FROM YANGPART PARTITION (P201101)
*
第 1 行出現錯誤:
ORA-14501: 物件未分割槽
已用時間: 00: 00: 00.00
yang@rac1>SELECT COUNT(*) FROM YANGPART PARTITION (p201101);
SELECT COUNT(*) FROM YANGPART PARTITION (p201101)
*
第 1 行出現錯誤:
ORA-14501: 物件未分割槽
已用時間: 00: 00: 00.00
yang@rac1>SELECT COUNT(*) FROM TABYANG PARTITION (p201101);
COUNT(*)
----------
1580
已用時間: 00: 00: 00.00
yang@rac1>SELECT COUNT(*) FROM TABYANG PARTITION (p201102);
COUNT(*)
----------
0
已用時間: 00: 00: 00.00
yang@rac1>SELECT COUNT(*) FROM TABYANG PARTITION (p201103);
COUNT(*)
----------
0
已用時間: 00: 00: 00.00
yang@rac1>SELECT COUNT(*) FROM TABYANG PARTITION (p201104);
COUNT(*)
----------
0
已用時間: 00: 00: 00.01
yang@rac1>SELECT COUNT(*) FROM TABYANG PARTITION (p2010);
COUNT(*)
----------
8420
已用時間: 00: 00: 00.01
優點：保證資料的一致性，在大部分時間內，表TABYANG都可以正常進行DML操作。只在切換的瞬間鎖表，具有很高的可用性。這種方法具有很強的靈活性，對各種不同的需要都能滿足。而且，可以在切換前進行相應的授權並建立各種約束，可以做到切換完成後不再需要任何額外的管理操作。
不足：實現上比上面兩種略顯複雜。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30496894/viewspace-2039956/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 非分割槽錶轉換成分割槽表
- 非分割槽錶轉換成分割槽表以及注意事項
- 【ASK_ORACLE】使用insert語句將普通錶轉換成分割槽表Oracle
- 線上重定義的操作步驟__普通錶轉換為分割槽表的實驗案例
- LightDB 22.4 新特性之相容Oracle線上轉換普通表為分割槽表Oracle
- Oracle 12C新特性-線上把非分割槽錶轉為分割槽表Oracle
- Oracle 12.2之後ALTER TABLE .. MODIFY轉換非分割槽表為分割槽表Oracle
- oracle 普通表-分割槽表改造流程Oracle
- 線上重定義與普通表改為分割槽表
- 線上重定義方式將普通表修改為分割槽表
- PG的非分割槽表線上轉分割槽表
- oracle 線上重新定義，普通表改變分割槽表，分割槽表可以更改型別、分割槽欄位等Oracle型別
- oracle將表配置為分割槽表Oracle
- oracle分割槽表和分割槽表exchangeOracle
- oracle分割槽表和非分割槽表exchangeOracle
- 增加表分割槽時，為local分割槽索引指定不同表空間的方法索引
- MySQL線上轉分割槽表（以及TiDB）MySqlTiDB
- PostgreSQL/LightDB 分割槽表之分割槽裁剪SQL
- 【MYSQL】 分割槽表MySql
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- linux交換分割槽Linux
- Oracle分割槽表基礎運維-07增加分割槽(3列表分割槽)Oracle運維
- oracle 分割槽表move和包含分割槽表的lob moveOracle
- 移動分割槽表和分割槽索引的表空間索引
- Oracle分割槽表基礎運維-04列表分割槽Oracle運維
- Oracle分割槽表基礎運維-07增加分割槽(2 HASH分割槽)Oracle運維
- MySQL 分割槽表探索MySql
- 分割槽表-實戰
- 指令碼：線上重定義，從普通表到分割槽表，redefinition_table.sh指令碼
- unbuntu新增交換分割槽
- Oracle分割槽表基礎運維-06分割槽表索引Oracle運維索引
- 【Linux】MBR磁碟分割槽表只能有四個分割槽？Linux
- Oracle分割槽表基礎運維-07增加分割槽(1範圍分割槽)Oracle運維
- Oracle分割槽表基礎運維-01分割槽表分類Oracle運維
- Linux 分割槽擴容（根分割槽擴容，SWAP 分割槽擴容，掛載新分割槽為目錄）Linux
- Oracle分割槽表基礎運維-09刪除分割槽Oracle運維
- Oracle分割槽表基礎運維-05組合分割槽Oracle運維
- Oracle分割槽表基礎運維-02範圍分割槽Oracle運維
- Oracle分割槽表基礎運維-03HASH分割槽Oracle運維