文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

oracle中如何删除亿级数据

2023-02-15 11:30

关注

oracle删除亿级数据

近期遇到一个日志表记录了几年数据,数据量较大(几亿数据),导致查询等操作较慢,为了提高效率,决定删除部分历史数据,想了三种方法如下:

通用步骤(每种方法之前先运行以下备份脚本):

1、备份 2021年之前数据

create table table_log_b2020 as select * from table_log a where a.logtime<date'2021-1-1';

2、备份2021年数据

create table table_log_2021 as select * from table_log a where a.logtime>=date'2021-1-1';

1、drop table ,然后再create table,插入数据

1、drop table

drop table table_log

2、create table table_log

3、插入2021年数据

insert into table_log select * from table_log_b2021;

2、使用delete批量删除

1、先去掉表索引,因为索引可能会影响删除效率

2、批量执行删除语句,也可以以存过方式直接运行,按照数量删除

delete from table_log a where a.logtime<date'2021-1-1' and rownum<5000000

每500万一次,没去索引之前大约耗时85秒,删掉索引之后大约耗时50

3、使用truncate table ,然后再插入数据

1、truncate table

truncate table table_log  耗时0.5s

2、插入2021年数据

insert into table_log  select * from table_log_b2021; 耗时 0.095s

第一种涉及到删表结构,公司相关规定不可这么做,第二种效率较为低

综合考虑使用了第三种,效率较为可观 ,秒秒钟把数据干干净。

oracle数据库亿级数据量清理SQL优化常识

#ORACLE #SQL #亿级数据量清理优化

说到删除数据,想到的

第一种方法就是DELETE

确实对于清理数据量比较少的表来说,DELETE是简单直接的方法,但是对于上亿条数据的表来说,这种方式肯定是行不通的,会造成undo表空间的不足。

表数据量如图所示

第二种方法就是truncate

但是我们这里做的是删除表中的部分数据,而不是清空表数据,所以也不能直接truncate,既然不能全部truncate,我们一开始想到的方式是先创建一个临时表,把需要保留的数据拿出来,然后清空表数据只会,再把需要保留的数据放回去,最后删除临时表,这种方式就不会造成undo表空间不足了,但是这样效率很慢,时间很长,从二十亿的数据量中删除一半左右的数据需要八个小时,当然这个和服务器的性能等硬件也有关系,如下如所示的SQL,虽然能够删除成功,但是速度很慢。

 CREATE TABLE SYSADM.PS_GPCN_PAYEE_TBL2022 AS 
 SELECT * 
  FROM SYSADM.PS_GPCN_PAYEE_TBL 
 WHERE EMPLID IN ( 
 SELECT EMPLID3 
  FROM PS_C_PAY_TBL)/ COMMIT/ TRUNCATE TABLE SYSADM.PS_GPCN_PAYEE_TBL/ 
 INSERT INTO SYSADM.PS_GPCN_PAYEE_TBL NOLOGGING 
 SELECT * 
  FROM SYSADM.PS_GPCN_PAYEE_TBL2022/ COMMIT/ 
 DROP TABLE SYSADM.PS_GPCN_PAYEE_TBL2022/ COMMIT/ 

删除的第三种方式就是DROP

直接把表干掉,drop的速度要比truncate更快,如果drop掉表之后再create,表还不会生成索引,所以如果采用drop的方式,就还要创建索引,这种方式只需要一个小时左右的时间

 CREATE TABLE SYSADM.PS_GP_ITER_TRGR2022 AS 
 SELECT * 
  FROM SYSADM.PS_GP_ITER_TRGR 
 WHERE EMPLID IN ( 
 SELECT EMPLID3 
  FROM PS_C_PAY_TBL)/ COMMIT/ 
 DROP TABLE SYSADM.PS_GP_ITER_TRGR/ 
 CREATE TABLE SYSADM.PS_GP_ITER_TRGR AS 
 SELECT * 
  FROM SYSADM.PS_GP_ITER_TRGR2022 / 
 CREATE INDEX PSAGP_ITER_TRGR ON PS_GP_ITER_TRGR (CAL_RUN_ID 
 , EMPLID 
 , ITER_TRGR_STATUS) tablespace PSINDEX pctfree 10 initrans 2 maxtrans 255 storage ( initial 48K NEXT 112K minextents 1 maxextents unlimited )/ 
 CREATE INDEX PS_GP_ITER_TRGR ON PS_GP_ITER_TRGR (EMPLID 
 , CAL_RUN_ID 
 , ITER_TRGR_STATUS) tablespace PSINDEX pctfree 10 initrans 2 maxtrans 255 storage ( initial 80K NEXT 112K minextents 1 maxextents unlimited )/ 
 DROP TABLE SYSADM.PS_GP_ITER_TRGR2022/ COMMIT/

总结

以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯