oracle巨大表的数据删除的方法,20分钟搞定
来源:互联网 发布:java权限管理系统demo 编辑:程序博客网 时间:2024/05/20 22:39
oracle巨大表的数据删除的方法,20分钟搞定 一个客户的日志表,已经有3000多千万的记录了,容量大约30G,打算维护一下,看了一下字段,发现日志是按日期记录的,打算只保留3个月的日志就好了。
第一个思路:
按条件查出来,直接DELETE
试了一下
delete from NTLS_LOGS where to_char(START_TIMES,'yyyy-mm-dd hh:mi:ss') <'2008-08-01 00:00:00';
时间会非常长,不知道多少天能跑完,反正一个晚上没跑完,UNDO会增长到一个可怕的数字
这个思路是不行的
研究了一下,发现大多数人的思路是做个循环,每5000条左右提交一次,做了个测试语句试了一下,每小时能删除80-90万条,晕倒需要删除的数据是2700万条,猴年马月才能完成。
询问客户,发现这个业务日志表不是什么时候都要写的,每天晚上10点到第二天早上6点是没有人会使用的,很好,我立刻考虑了一种方法,思路是将需要的数据提取出来到一个新的表,然后将原表删除,将这个提取出来的表重命名为旧的日志表名。
呵呵,试一下这种方法的威力,生成新的数据整个过程只用了172S,也就是3分钟左右,表是生成了,但是没有索引,那就照抄原表的索引吧,呵呵最后花了18分21秒搞定了,其实原理就是只处理必须保留的数据这些数据远远小于要删掉的数据,跳过不需要的数据,采用CREAT SELECT AS可以充分使用索引,而且不会生存UNDO,因此速度大大加快,该方法的弊端是,必须要有一点时间,没有人使用这个表才行。
于是我写了一个SQL,将这个放到每月的自动执行计划里定期执行,定期维护这个表,避免表异常膨大,说了原理,就该上SQL了。
drop table new_log_temp;
create table new_log_temp as select * from NTLS_log where timestamp > to_date(to_char(SYSDATE-90,'yyyy-mm-dd'),'yyyy-mm-dd');
drop NTLS_LOG;
alter table new_log_temp rename to NTLS_LOG;
create index IDX_NTLS_LOG on NTLS_LOG (RECORD)
tablespace INDEXES
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index IDX_NTLS_LOG_OPERATE_ID on NTLS_LOG (OPERATE)
tablespace INDEXES
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index IDX_NTLS_LOG_START_SCN on NTLS_LOG (START_SCN)
tablespace INDEXES
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index IDX_T_LOG_START_TIMESTAMP on NTLS_LOG (TIMESTAMP)
tablespace INDEXES
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index INDEX_NTLS_LOG_UID on NTLS_LOG (UID)
tablespace INDEXES
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create unique index PK_NTLS_LOG on NTLS_LOG (OPERATE,UID, TABLE_NAME, TABLE_OWNER, ROW_ID)
tablespace INDEXES
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
最后来一句总结吧,条条大路通北京,不一定非要走路,飞机不更好。
第一个思路:
按条件查出来,直接DELETE
试了一下
delete from NTLS_LOGS where to_char(START_TIMES,'yyyy-mm-dd hh:mi:ss') <'2008-08-01 00:00:00';
时间会非常长,不知道多少天能跑完,反正一个晚上没跑完,UNDO会增长到一个可怕的数字
这个思路是不行的
研究了一下,发现大多数人的思路是做个循环,每5000条左右提交一次,做了个测试语句试了一下,每小时能删除80-90万条,晕倒需要删除的数据是2700万条,猴年马月才能完成。
询问客户,发现这个业务日志表不是什么时候都要写的,每天晚上10点到第二天早上6点是没有人会使用的,很好,我立刻考虑了一种方法,思路是将需要的数据提取出来到一个新的表,然后将原表删除,将这个提取出来的表重命名为旧的日志表名。
呵呵,试一下这种方法的威力,生成新的数据整个过程只用了172S,也就是3分钟左右,表是生成了,但是没有索引,那就照抄原表的索引吧,呵呵最后花了18分21秒搞定了,其实原理就是只处理必须保留的数据这些数据远远小于要删掉的数据,跳过不需要的数据,采用CREAT SELECT AS可以充分使用索引,而且不会生存UNDO,因此速度大大加快,该方法的弊端是,必须要有一点时间,没有人使用这个表才行。
于是我写了一个SQL,将这个放到每月的自动执行计划里定期执行,定期维护这个表,避免表异常膨大,说了原理,就该上SQL了。
drop table new_log_temp;
create table new_log_temp as select * from NTLS_log where timestamp > to_date(to_char(SYSDATE-90,'yyyy-mm-dd'),'yyyy-mm-dd');
drop NTLS_LOG;
alter table new_log_temp rename to NTLS_LOG;
create index IDX_NTLS_LOG on NTLS_LOG (RECORD)
tablespace INDEXES
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index IDX_NTLS_LOG_OPERATE_ID on NTLS_LOG (OPERATE)
tablespace INDEXES
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index IDX_NTLS_LOG_START_SCN on NTLS_LOG (START_SCN)
tablespace INDEXES
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index IDX_T_LOG_START_TIMESTAMP on NTLS_LOG (TIMESTAMP)
tablespace INDEXES
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index INDEX_NTLS_LOG_UID on NTLS_LOG (UID)
tablespace INDEXES
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create unique index PK_NTLS_LOG on NTLS_LOG (OPERATE,UID, TABLE_NAME, TABLE_OWNER, ROW_ID)
tablespace INDEXES
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
最后来一句总结吧,条条大路通北京,不一定非要走路,飞机不更好。
- oracle巨大表的数据删除的方法,20分钟搞定
- oracle删除大表的数据的方法
- Oracle中大批量删除数据的方法
- Oracle中大批量删除数据的方法
- Oracle中删除一列数据的方法
- Oracle中大批量删除数据的方法
- ORACLE误删除数据的恢复方法
- 转帖 oracle删除重复数据的方法
- Oracle中大批量删除数据的方法
- Oracle中大批量删除数据的方法
- Oracle 删除数据的几种方法
- ORACLE误删除数据的恢复方法
- oracle误删除数据的恢复方法
- oracle误删除数据的恢复方法
- oracle误删除数据的恢复方法
- Oracle中大批量删除数据的方法
- oracle误删除数据的恢复方法
- ORACLE大批量数据最快的删除方法
- ORACLE 10.204密码重试次数问题
- 新年第一篇
- modules.dep文件损坏无法找到网卡的处理
- 如何从EXPDP导出的文件里获得用户授权的SQL语句
- 继续摘抄:Can't connect to local MySQL server through socket '/tmp/mysql.sock'
- oracle巨大表的数据删除的方法,20分钟搞定
- dm9000驱动移植 之二
- CS8900 base address的确定方法
- 文本关键词的提取算法实验
- 从ORACLE的表里生成DBF的简单办法
- ORACLE下如何获得全部的索引创建语句
- Eclipse中java.lang.OutOfMemoryError: Java heap space 的问题解决方法
- Android上的skype
- 请看下面的文章,请不要试图读,中文真牛