对一个大表进行分区操作
===========================================================
作者: 西门吹牛(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以前的方法）了。