將非分割槽錶轉換為分割槽表

xsdan發表於2006-09-18
很多資料庫在設計時未能很好的規劃資料庫的容量, 導致在某些情況下, 導致一個表中存放了數百萬, 數千萬甚至上億條記錄, 維護起來非常的不便, 下面介紹一種將非分割槽錶轉換為分割槽表的方法.[@more@]testtb_nopart是一個非分割槽表, 基本資訊如下:

SQL> desc testtb_nopart
Name Null? Type
----------------------------------------- -------- ----------------------------
TIMESTAMP DATE
OWNER VARCHAR2(30)
ACTION VARCHAR2(6)
OBJECT_NAME VARCHAR2(128)
MESSENG VARCHAR2(169)

SQL> select to_char(timestamp, 'mm-yyyy'), count(*) from testtb_nopart group by to_char(timestamp, 'mm-yyyy');

TO_CHAR COUNT(*)
------- ----------
12-2005 2058125
11-2005 1363431
08-2005 150951
10-2005 628149
07-2005 1006340
09-2005 730410

6 rows selected.

建立分割槽表testtb_part, 結構和testtb_nopart一致.

create table testtb_part (timestamp date, owner varchar2(30), action varchar2(6), object_name varchar2(128) , messeng varchar2(169))
tablespace testts
partition by range(timestamp)(
partition rest values less than (maxvalue))

交換testtb_nopart和testtb_part中part分割槽的資料

SQL> alter table testtb_part exchange partition rest with table testtb_nopart;

Table altered.

SQL> select to_char(timestamp, 'mm-yyyy'), count(*) from testtb_nopart group by to_char(timestamp, 'mm-yyyy');

no rows selected

SQL> c/nopart/part/
1* select to_char(timestamp, 'mm-yyyy'), count(*) from testtb_part group by to_char(timestamp, 'mm-yyyy')
SQL> /

TO_CHAR COUNT(*)
------- ----------
12-2005 2058125
11-2005 1363431
08-2005 150951
10-2005 628149
07-2005 1006340
09-2005 730410

6 rows selected.

testtb_nopart中大約有50萬條記錄, 佔用600m左右空間, 交換大約用了不到30秒鐘, testtb_part和testtb_nopart所在的表空間也沒有明顯增長.

對rest分割槽進行拆分

SQL> alter table testtb_part split partition rest at (to_date('200508','yyyymm'))
2 into (partition part08, partition rest);

Table altered.

SQL> alter table testtb_part split partition rest at (to_date('200509','yyyymm'))
2 into (partition part08, partition rest);

Table altered.

SQL> alter table testtb_part split partition rest at (to_date('200510','yyyymm'))
2 into (partition part09, partition rest);

Table altered.

SQL> alter table testtb_part split partition rest at (to_date('200511','yyyymm'))
2 into (partition part10, partition rest);

Table altered.

SQL> alter table testtb_part split partition rest at (to_date('200512','yyyymm'))
2 into (partition part11, partition rest);

Table altered.

SQL> alter table testtb_part split partition rest at (to_date('200601','yyyymm'))
2 into (partition part12, partition rest);

Table altered.

最後可以重新命名錶, 使得轉換過程不影響使用者程式.

alter table testtb_part rename to testtb_nopart

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/262387/viewspace-865781/，如需轉載，請註明出處，否則將追究法律責任。

相關文章