接着上一篇文章:

一亿行删除或修改一千万行的思路

思路二: CTAS + RENAME


因为要在对数据库及其他应用影响最小的情况下做

如果直接对表进行删除则会出现应用因为等待数据释放锁而等待

而且常规的DELETE语句,会生成大量的UNDO和REDO

而且表上如果有大量索引的话,导致删除还会更慢

而且可能会造成大量的空数据块,如果有扫描大量行的操作,空块太多,也会导致性能下降


如果可以停止业务,防止对此表的更新

那么CTAS + RENAME也是个不赖的选择

这种方法只适用于没有业务发生的情况,对于大多数OLTP系统还是不适用的

由于CTAS是DDL语句,基本没有太多UNDO的生成,如果CTAS失败了,重新在执行一遍即可


大致过程如下

比如:对象表为test_01,我想要删去日期(deal_date)为20120301的数据


① CTAS的方法创建一个新表,建表条件为删除条件的对立条件,可考虑nologging+并行

例如:create table test_02 as select * from test_01 where deal_Date<>'20120301'


② 在新表上建立相关索引和约束


③ 重命名新表为老表名字

例如:ALTER TABLE test_02 RENAME TO test_01


④ 如果没问题 删除老表


⑤ 最后查看和该表相关的存储过程、函数等,重新编译


如果用CTAS在建表的时候会对原表建立一致性读的

如果这时候对原表有插入或更新,就需要访问undo表空间然后完成一致读

而且一旦失败需要全部回滚,数据量这么大,这样做的风险是巨大的

我们可以访问dba_extents视图,通过rowid将原表划分成许多不重叠的区域

然后起多个job,job中通过rowid访问然后删除数据

删除完后的区域写日志,这样即使中途失败通过划分的rowid区域和日志都是可以重启的

而且速度要比bulkcollect好,因为bulk collect是缓存100到200条一delete,一个区域直接一个delete

但是这种方式最好需要先在表上加读取锁,不然有insert和update会破坏原来的rowid划分


oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html