发表于: 2008.01.29 17:31
分类: Oracle技术
出处: http://logzgh.itpub.net/post/3185/453705
---------------------------------------------------------------
在我们系统里面有张sample表格，目前单表已达106G，还在不断的逐步增长。
这张表格里面只保留最近三个月的数据。每天晚上有个JOB自动把3个月前的数据一一删掉。
现在这张表格里面空洞有不少，真正的数据量大概在60-70G之间。有没有什么办法能够重新整理一下这张表格呢？
在线重定义由于表太大，风险也较大，不是个好方法。
本文的例子提供一个思路。它是采用分区表做中转的。
1.创建一般的表格和索引：
c2c@C2C> create table sample1
2 (
3 GMT_CREATE DATE,
4 IS_DELETED CHAR(1) default 'n',
5 SITE VARCHAR2(32) default 'taobao' not null,
6 DOMAIN_SAMPLE_ID VARCHAR2(32) default ' ' not null,
7 DOMAIN_USER_ID VARCHAR2(32),
8 TITLE VARCHAR2(256),
9 GMT_MODIFIED DATE default SYSDATE not null
64 );
Table created.
c2c@C2C> create index SAMPLE_DMUID_ISD_IND3 on sample1 (DOMAIN_USER_ID, IS_DELETED);
Index created.
c2c@C2C> create index SAMPLE_DSPID_IND3 on sample1 (DOMAIN_SAMPLE_ID);
Index created.
2.插入数据
c2c@C2C> insert into sample1 select * from sample where rownum<100000;
99999 rows created.
c2c@C2C> commit;
Commit complete.
3.创建空的分区表，结构与sample1一样。
c2c@C2C> create table sample2
2 (
3 GMT_CREATE DATE,
4 IS_DELETED CHAR(1) default 'n',
5 SITE VARCHAR2(32) default 'taobao' not null,
6 DOMAIN_SAMPLE_ID VARCHAR2(32) default ' ' not null,
7 DOMAIN_USER_ID VARCHAR2(32),
8 TITLE VARCHAR2(256),
9 GMT_MODIFIED DATE default SYSDATE not null
64 )
65 partition by range (GMT_MODIFIED)
66 (
67 partition P1 values less than (TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
68 partition P2 values less than (MAXVALUE))
69 ;
Table created.
4.创建分区索引，注意这里一定要是local的分区索引，否则后面exchange时，会导致索引失效。
c2c@C2C> create index SAMPLE_DMUID_ISD_IND4 on sample2 (DOMAIN_USER_ID, IS_DELETED) local;
Index created.
c2c@C2C> create index SAMPLE_DSPID_IND4 on sample2 (DOMAIN_SAMPLE_ID) local;
Index created.
5.交换分区
c2c@C2C> alter table sample2 exchange partition p1 with table sample1 including indexes;
Table altered.
c2c@C2C> select count(*) from sample2;
COUNT(*)
----------
99999
1 row selected.
6.检查一下索引是否失效：
[oracle@c2c_pri ~]$ sqlplus "/as sysdba"
sys@C2C>
select">sys@C2C>select index_name,owner,table_name from dba_indexes where status!='VALID';
INDEX_NAME OWNER TABLE_NAME
------------------------------ ------------------------------ ------------------------------
SAMPLE_DMUID_ISD_IND4 C2C SAMPLE2
SAMPLE_DSPID_IND4 C2C SAMPLE2
select">sys@C2C>select index_name,status from dba_ind_partitions where index_name='SAMPLE_DSPID_IND4';
INDEX_NAME STATUS
------------------------------ --------
SAMPLE_DSPID_IND4 USABLE
SAMPLE_DSPID_IND4 USABLE
索引是正常的。
7.接下来需要做的工作就是将sample2表和索引重命名为sample1表和索引的名字。
8.这样2008-2-1以后的数据就会插入到新的分区中来，这样一直运行三个月。所有的最近三个月的数据都在新的分区里面。
而p1分区由于每天晚上自动删除数据将不存在。
9.三个月后，只需要将新的分区再换成一张普通的表格。
也许有人会问为什么不一直采用分区表呢？
因为这张表格上面的索引有不少，并且都无法建成prefix-local的索引。
也就是说只能建成global的索引或者non-prefixed的索引。
对global的索引，在以后的分区维护中增加不少困难，并且每次分区的维护都会导致相关的存过和包失效。
对non-prefixed的索引，在使用过程中性能将下降很多。
当然采用这种方法在过渡的这三个月内，性能是会有所下降的。
re: 如何使用分区表来对大表做重整 
