對一個大表進行分區操作
===========================================================
作者: 西門吹牛(http://.itpub.net)
發表于: 2004.09.06 17:23
分類: 數據庫優化
出處: http://.itpub.net/post/306/1471
---------------------------------------------------------------
對一個大表進行分區操作
一：使用分區表的前提
以system身份登陸數據庫，查看 v$option 視圖，如果其中 Partitioning 為TRUE，則支持分區功能；
select value from v$option where parameter='Partitioning';
否則不支持。
二：使用分區表的背景
目前有一個大表 T_MPN_OUTDATE表，有記錄 8千萬 條，查詢速度比較慢，為了優化想做成一個分區表，按照范圍分區，這個表的happen_time字段最合適，因為它是按照時間來區分的，目前的數據是從今年5月份到現在（9月份）。不過這個字段雖然是8位的，但是里面的記錄可能有的是6位的到月的記錄。
這個表的結構和索引情況如下
CREATE TABLE sms.t_mpn_outdate
(
mobile VARCHAR2 (11)
, happen_time VARCHAR2 (8)
, prov VARCHAR2 (5)
, state NUMBER
)
ORGANIZATION HEAP
NOMONITORING
PARALLEL
(
DEGREE 1
INSTANCES 1
)
NOCACHE
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE
(
INITIAL 131072
NEXT 131072
MINEXTENTS 1
MAXEXTENTS unlimited
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL default
)
LOGGING
TABLESPACE users
;
CREATE INDEX sms.idx_t_mpn_outdate ON sms.t_mpn_outdate
(
mobile
)
PARALLEL
(
DEGREE 1
INSTANCES 1
)
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE
(
INITIAL 131072
NEXT 131072
MINEXTENTS 1
MAXEXTENTS unlimited
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL default
)
LOGGING
TABLESPACE users
;
三：步驟
1、查看本地硬盤的空間還有多少，USERS表空間容量是多少，查看需要分區的表的占用空間是多少
查看users表空間的容量
select
b.tablespace_name ,
(b.bytes)/(1024*1024) content ,
(b.bytes-sum(nvl(a.bytes,0)))/(1024*1024) used,
sum(nvl(a.bytes,0))/(1024*1024) remain
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id and b.tablespace_name = 'USERS'
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id;
TABLESPACE_NAME CONTENT USED REMAIN
------------------------------ ---------- ---------- ----------
USERS 22.5 21.5078125 .9921875
查看本地 t_mpn_outdate 表占用空間的大小
select segment_name,bytes/(1024*1024) as content from user_segments where segment_name='T_MPN_OUTDATE';
SEGMENT_NAME CONTENT
----------------------------------------------------
T_MPN_OUTDATE .125
查看索引占用的空間大小
select segment_name,bytes/(1024*1024) as content from user_segments where segment_name='IDX_T_MPN_OUTDATE';
SEGMENT_NAME CONTENT
----------------------------------------------------
IDX_T_MPN_OUTDATE .125
查看本地硬盤下面是否足夠容納表和表索引的大小
(略)
然后以system 身份創建獨立的表空間(大小可以根據數據量的多少而定，路徑根據實際情況而定)，我做實驗的例子是同一個表空間，沒有使用獨立的表空間。建立表空間的相關的語法是
create tablespace happen_time_200405 datafile '/home/oradata/oradata/test/happen_time_200405.dbf' size 50M default storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0);
create tablespace happen_time_200406 datafile '/home/oradata/oradata/test/happen_time_200406.dbf' size 50M default storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0);
create tablespace happen_time_200407 datafile '/home/oradata/oradata/test/happen_time_200407.dbf' size 50M default storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0);
=====================================================
方案一
2、基于舊表創建一個新表(這一步對原來的表可能會有影響，最好放在晚上做)
set timing on
set time on
CREATE TABLE T_MPN_OUTDATE2
PARTITION BY RANGE (happen_time)
( PARTITION happen_time_200405
VALUES LESS THAN ('200406')
TABLESPACE users,
PARTITION happen_time_200406
VALUES LESS THAN ('200407')
TABLESPACE users ,
PARTITION happen_time_200407
VALUES LESS THAN ('200408')
TABLESPACE users ,
PARTITION happen_time_200408
VALUES LESS THAN ('200409')
TABLESPACE users ,
PARTITION happen_time_200409
VALUES LESS THAN ('200410')
TABLESPACE users
)
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE
(
INITIAL 131072
NEXT 131072
MINEXTENTS 1
MAXEXTENTS unlimited
PCTINCREASE 0
FREELISTS 3
FREELIST GROUPS 1
BUFFER_POOL default
)
LOGGING
AS SELECT * FROM T_MPN_OUTDATE;
注意：freelists 參數我修改為3，因為這個表有很多insert操作，加大這個參數對性能有優化作用。
看看插入記錄是否成功
select * from T_MPN_OUTDATE2 where rownum < 90;
選擇其中的一個分區
select * from T_MPN_OUTDATE2 partition (happen_time_200409) where rownum < 90;
3、新舊兩個表改名稱（時間需要測試一下）
先測試改一個表的名稱需要多少時間，把這個時間加倍就是系統對前端程序沒有反應的時間。
set timing on
set time on
RENAME T_MPN_OUTDATE2 TO T_MPN_OUTDATE3;
然后再正式修改后面兩個表，這時間就是系統對前端程序沒有反應的時間。
RENAME T_MPN_OUTDATE TO T_MPN_OUTDATE2;
RENAME T_MPN_OUTDATE3 TO T_MPN_OUTDATE;
方案一結束，下面繼續步驟4
=====================================================
方案二
2 、用EXPORT工具把舊數據備份在 T_MPN_OUTDATE.DMP中；用戶名密碼和保存路徑需要按照實際情況修改一下
exp sms/sms file=c:aaT_MPN_OUTDATE.DMP tables=T_MPN_OUTDATE
原來的舊表改名，從這個時候起，數據庫就對前端程序沒有反應了
alter table t_mpn_outdate rename to t_mpn_outdate_old2 nowait;
以 sms 身份創建分區的表
CREATE TABLE sms.t_mpn_outdate
(
mobile VARCHAR2 (11)
, happen_time VARCHAR2 (8)
, prov VARCHAR2 (5)
, state NUMBER )
PARTITION BY RANGE (happen_time)
( PARTITION happen_time_200405
VALUES LESS THAN ('200406')
TABLESPACE users,
PARTITION happen_time_200406
VALUES LESS THAN ('200407')
TABLESPACE users ,
PARTITION happen_time_200407
VALUES LESS THAN ('200408')
TABLESPACE users ,
PARTITION happen_time_200408
VALUES LESS THAN ('200409')
TABLESPACE users ,
PARTITION happen_time_200409
VALUES LESS THAN ('200410')
)
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE
(
INITIAL 131072
NEXT 131072
MINEXTENTS 1
MAXEXTENTS unlimited
PCTINCREASE 0
FREELISTS 3
FREELIST GROUPS 1
BUFFER_POOL default
)
LOGGING
TABLESPACE users
;
IMPORT導入數據，參數ignore=y
%imp sms/sms file=c:aaT_MPN_OUTDATE.DMP tables=(t_mpn_outdate) commit=y buffer=10240000 ignore=y
方案二結束，下面繼續步驟4
=====================================================
注意：系統修改表名的時候必需沒有其他的事務對表進行操作，否則修改表名稱的時候如果有未提交的事務，報告
ERROR 位于第 1 行:
ORA-00054: 資源正忙，要求指定 NOWAIT
如果加上了nowait選項，又會報錯
ERROR 位于第 1 行:
ORA-14048: 分區維護操作不可以與其它操作組合
最好是關閉數據庫重新啟動的時候進入restrict過程。
=====================================================
4、寫SQL語句把第三步中遺漏的數據補充到新表中。
這一步的原理是重新把最近的一個月的記錄插入到分區表中，然后再刪除重復的記錄，所以建議創建分區表的時候最好在某個月的月初做，按照本例，最好是在9月上旬做分區，這樣記錄數還不是很多。
先建立一個臨時表把9月份所有的記錄都包括
create table T_MPN_OUTDATE_TMP as select * from T_MPN_OUTDATE2 where happen_time like '200409%' ;
把這個表的記錄插入到分區表
insert into T_MPN_OUTDATE select * from T_MPN_OUTDATE_TMP;
刪除臨時表
drop table T_MPN_OUTDATE_TMP ;
把分區表中9月份相同的記錄刪掉。
DELETE FROM T_MPN_OUTDATE partition (happen_time_200409) E
WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM T_MPN_OUTDATE partition (happen_time_200409) x
WHERE X.mobile = E.mobile
and X.happen_time = E.happen_time
and X.prov = E.prov
and X.state = E.state
);
5、建立索引
CREATE INDEX sms.idx_t_mpn_outdate_partition2 ON sms.t_mpn_outdate
(
mobile
)
PARALLEL
(
DEGREE 1
INSTANCES 1
)
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE
(
INITIAL 131072
NEXT 131072
MINEXTENTS 1
MAXEXTENTS unlimited
PCTINCREASE 0
FREELISTS 3
FREELIST GROUPS 1
BUFFER_POOL default
)
LOGGING
LOCAL
TABLESPACE users
;
備注：索引加local選項，否則truncate分區，索引將失效，所有用到索引的查詢都無法進行。
另外 freelists 參數我修改為3，因為這個表有很多insert操作，加大這個參數對性能有優化作用。
6、分區表的相關維護操作
1)分區表的擴容：
到了2004 年10月份，建立新的表空間：(或者仍然用原來的USER表空間,這一步就不需要了)
create tablespace happen_time_200405 datafile '/home/oradata/oradata/test/happen_time_200405.dbf' size 50m default storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 1);
為表添加新分區和表空間：
alter table T_MPN_OUTDATE add partition happen_time_200410
VALUES LESS THAN ('200411')
tablespace users
storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0);
2)刪除不必要的分區
將2004年05月的數據備份（備份方法見 3)EXPORT 分區），將2004年05月的分區刪除。
alter table T_MPN_OUTDATE drop PARTITION happen_time_200405;
刪除物理文件
%rm /home/oradata/oradata/test/happen_time_200405.dbf
3)EXPORT 分區：
% exp sms/sms tables=T_MPN_OUTDATE:happen_time_200405 rows=Y file=c:aahappen_time_200405.dmp
4)IMPORT分區：
例如用戶要查看2004年05月的數據，先創建表空間
create tablespace happen_time_200405 datafile '/home/oradata/oradata/test/happen_time_200405.dbf' size 50m default storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 1);
再導入數據
%imp sms/sms file=c:aahappen_time_200405.dmp tables=(T_MPN_OUTDATE:happen_time_200405) ignore=y
(說明：如果不指明導入的分區，imp會自動按分區定義的范圍裝載數據)
5)查看分區信息：
DBA要查看表的分區信息，可查看數據字典USER_EXTENTS,操作如下：
SELECT * FROM user_extents WHERE SEGMENT_NAME='T_MPN_OUTDATE';
感謝yangtingkun版主、pingshx、 ZALBB、Fenng等網友的支持
備注：如果是9iR2版本就可以使用 在線表格重定義 技術實現這個功能。不用常規方法（8i以前的方法）了。