Oracle大資料表的分表處理

標籤：ble   ora   rac   建立   number   logs   分數   不同的   nbsp   

1.首先給大資料表建立rownum序號

--增加序號欄位alter table TEST add xlh number;--以數列填滿號update TEST set xlh = rownum;

2.通過xlh欄位進行分資料到不同的表中(以建表的方式進行處理)

create table hik_1001 as select clm1 as hik_clm1,clm2 as hik_clm2,clm3 as hik_clm3 from TEST where   xlh>=1 and xlh <=5000000;create table hik_1002 as select clm1 as hik_clm1,clm2 as hik_clm2,clm3 as hik_clm3 from TEST where   xlh>=5000001 and xlh <=10000000;create table hik_1003 as select clm1 as hik_clm1,clm2 as hik_clm2,clm3 as hik_clm3 from TEST where   xlh>=10000001 and xlh <=15000000;create table hik_1004 as select clm1 as hik_clm1,clm2 as hik_clm2,clm3 as hik_clm3 from TEST where   xlh>=15000001;

3.給建立的表分別建立主鍵或者索引

create index hik_1001_index on HIK_1001  (HIK_CLM2)alter table HIK_1001  add constraint HIK_1001_PKEY primary key (HIK_CLM1)

如果資料有重複的話,只需要建立索引就可以.

4.然後對分表後的資料進行處理

 

Oracle大資料表的分表處理