Oracle,删除表中千万条数据

Posted by Alei Blog on October 25, 2017

场景

  • 当表中存在数百万条以上的数据,sql执行的时间急剧增加
  • 我的一次实践,依次删除某张表中的部分数据,然后统计该表的数据量。5200万条,查询耗时120.3秒;2800万条,查询耗时45.6秒;488万条,查询耗时10.9秒;177万条,查询耗时0.17秒
  • 为提升性能,决定删除该表其中大部分过期的数据。业务需要,仍保留近期的数据
  • 因为该表存在多个索引,删除数据耗时远超过查询耗时,直接删除其中的几千万条数据,数据库几个小时都未返回执行结果,删除失败

操作

  • 创建临时表,保存需要的数据
    create table tmp_table as select * from big_table where xxxx;
    
  • 清空大表
    truncate table big_table;
    
  • 减少大表中不必要的索引,提升增删改的性能(可选操作)
    drop index i_big_table_xxx;
    
  • 恢复数据到大表
    insert into big_table select * from tmp_table
    
  • 删除其中的几千万条数据,操作总计耗时1小时