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;
yang@ORACL> select count(1) from yang_tmp;
yang@ORACL> rename yang_tmp to yang_part;
yang@ORACL> insert into yang_part select id,time from yang_old;
yang@ORACL> select count(1) from yang_tmp partition (p2010);
85142
yang@ORACL> select count(1) from yang_tmp partition (p201101);
14858
yang@ORACL> select count(1) from yang_tmp partition (p201102);
yang@ORACL> select count(1) from yang_tmp partition (p201103);
yang@ORACL> select count(1) from yang_tmp partition (p201104);
優點:方法簡單易用,由于采用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
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(*)
已用時間: 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;
10000
已用時間: 00: 00: 00.00
yang@rac1>select count(1) from yang_part partition (p201104);
yang@rac1>select count(1) from yang_part partition (p201103);
yang@rac1>rename yangtmp to yangtmp2;
已用時間: 00: 00: 00.16
yang@rac1>rename yang_part to yangtmp;
yang@rac1>select count(1) from yangtmp;
yang@rac1>select count(1) from yangtmp2;
優點:隻是對資料字典中分區和表的定義進行了修改,沒有資料的修改或複制,效率最高。如果對資料在分區中的分布沒有進一步要求的話,實作比較簡單。在執行完RENAME操作後,可以檢查T_OLD中是否存在資料,如果存在的話,直接将這些資料插入到T中,可以保證對T插入的操作不會丢失。
不足:仍然存在一緻性問題,交換分區之後RENAME T_NEW TO T之前,查詢、更新和删除會出現錯誤或通路不到資料。如果要求資料分布到多個分區中,則需要進行分區的SPLIT操作,會增加操作的複雜度,效率也會降低。
适用于包含大資料量的表轉到分區表中的一個分區的操作。應盡量在閑時進行操作。
yang@rac1>create table tabyang ( id number primary key ,time date);
yang@rac1>insert into tabyang select rownum id,sysdate-dbms_random.value(2,500) time
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 (
yang@rac1>EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'TABYANG', 'YANGPART', 'ID ID, TIME TIME', DBMS_REDEFINITION.CONS_USE_PK);
已用時間: 00: 00: 02.60
yang@rac1>EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'TABYANG', 'YANGPART');
已用時間: 00: 00: 01.10
yang@rac1>SELECT COUNT(*) FROM YANGPART;
yang@rac1>SELECT COUNT(*) FROM TABYANG;
已用時間: 00: 00: 00.28
yang@rac1>SELECT COUNT(*) FROM YANGPART PARTITION (P20110401);
SELECT COUNT(*) FROM YANGPART PARTITION (P20110401)
*
ORA-14501: 對象未分區
已用時間: 00: 00: 00.18
yang@rac1>SELECT COUNT(*) FROM YANGPART PARTITION (P201104);
SELECT COUNT(*) FROM YANGPART PARTITION (P201104)
yang@rac1>SELECT COUNT(*) FROM YANGPART PARTITION (P201103);
SELECT COUNT(*) FROM YANGPART PARTITION (P201103)
yang@rac1>SELECT COUNT(*) FROM YANGPART PARTITION (P201101);
SELECT COUNT(*) FROM YANGPART PARTITION (P201101)
yang@rac1>SELECT COUNT(*) FROM YANGPART PARTITION (p201101);
SELECT COUNT(*) FROM YANGPART PARTITION (p201101)
yang@rac1>SELECT COUNT(*) FROM TABYANG PARTITION (p201101);
1580
yang@rac1>SELECT COUNT(*) FROM TABYANG PARTITION (p201102);
yang@rac1>SELECT COUNT(*) FROM TABYANG PARTITION (p201103);
yang@rac1>SELECT COUNT(*) FROM TABYANG PARTITION (p201104);
yang@rac1>SELECT COUNT(*) FROM TABYANG PARTITION (p2010);
8420
優點:保證資料的一緻性,在大部分時間内,表TABYANG都可以正常進行DML操作。隻在切換的瞬間鎖表,具有很高的可用性。這種方法具有很強的靈活性,對各種不同的需要都能滿足。而且,可以在切換前進行相應的授權并建立各種限制,可以做到切換完成後不再需要任何額外的管理操作。
不足:實作上比上面兩種略顯複雜。