關於批量delete 的速度問題,高手進........

Question

表test裡有60W批數據，我下delete from test where rownum<=10000
如查表裡有索引就要三分鍾以上才能刪掉，如果沒有索引二分鍾左右就可以刪掉.
在delete時有沒有像insert那樣帶 /*+append*/ 的類擬寫法．望指點

Answer 1

delete from test
 where rowid in (select rd
                 from (select rowid rd,
                              row_number() over(order by rowid) rn from test)
                where rn <= 1000)

Answer 2

1万条不多．如果是海量删除,采用自制事务进行处理.
参考这个连接：http://www.eygle.com/archives/2005/04/oracleoeouaeeae.html

Answer 3

delete  /*+append*/ from test  where rownum<=1000

Answer 4

--是不是这个意思？delete from test where exists in (select 1 from test1 where ...);

Answer 5

貌似直接rownum<10000最快吗跟数据有关系，楼主自己测试
SQL> delete from SYMC_SIM_EVENT
  2   where rowid in (select rd
  3                   from (select rowid rd,
  4                                row_number() over(order by rowid) rn from SYMC_SIM_EVENT)
  5                  where rn <= 10000);
  
 
10000 rows deleted
 
Executed in 20.031 seconds
 
SQL> delete  /*+append*/ from SYMC_SIM_EVENT  where rownum<=10000;
 
10000 rows deleted
 
Executed in 13.672 seconds
 
SQL> delete from SYMC_SIM_EVENT  where rownum<=10000;
 
10000 rows deleted
 
Executed in 7.047 seconds
 
SQL> delete  /*+append*/ from SYMC_SIM_EVENT  where rownum<=10000;
 
10000 rows deleted
 
Executed in 9.578 seconds

Answer 6

清楚append工作原理就知道delete会不会有这种hint了。尽管加了这个hint也不报错，不过纯粹成了注释了。1。需要考虑的是delete时的锁引起别的会话等待的问题。
2。如果想提速，就考虑分批delete并commit,
或者delete数据量很大，可用ctas，这可减少log产生。然后结合truncate，insert /*+ append*/ into t select ... nologging不过这要看你的业务是否允许这种操作了。

Answer 7

给楼主的建议：
1.索引保留（或建立）；
2.写个存贮过程，此过程中应有删除记录和索引重建(rebuild)；
3.放在JOB中凌晨或空闲时间按期（天）执行。之所以“表裡有索引就要三分鍾以上才能刪掉，如果沒有索引二分鍾左右就可以刪掉”，
那是因为每次删除时产生了很多碎记录，使用位图索引代替B+索引倒是可以试试。

Answer 8

给你一个前段时间我给别人写的一个例子吧:表结构：
SQL> desc aa;
Name Type           Nullable Default Comments 
---- -------------- -------- ------- -------- 
ID   VARCHAR2(10)   Y                         
NAME VARCHAR2(1000) Y                         SQL> 
总的数据量SQL> select count(*) c from aa;c
-----
18546688Executed in 85.79 secondsSQL> --进行全表的删除，说明：如果有where条件，可将cursor后面的语句
--修改,这里主要通过rowid+bulk collect来删除数据，经我手头的测试
--速度是非常快的，但还要根据实际情况和环境来进行测量。这个脚本
--目前可以进行参考。
SQL> declare
  2    cursor l_cur is
  3      select rowid from aa;
  4  
  5    type ridArray is table of rowid index by binary_integer;
  6  
  7    l_rowid ridArray;
  8  begin
  9    open l_cur;
 10    loop
 11      fetch l_cur bulk collect
 12        into l_rowid limit 10000;
 13  
 14      forall i in 1 .. l_rowid.count
 15        delete from aa where rowid = l_rowid(i);
 16      commit;
 17  
 18      exit when l_cur%notfound;
 19    end loop;
 20    close l_cur;
 21  end;
 22  /PL/SQL procedure successfully completedExecuted in 797.39 seconds

Answer 9

如果是全表删除可能tuncate噻。

Answer 10

只要分批次删除和COMMIT就可以了

Answer 11

关于BlueskyWide建议创建bitmap索引我看是不必要的,要知道bitmap的应用场景是在OLAP环境,如果是DML事务频繁的OLTP系统恐怕是反受其害了.再说bitmap也并非适合创建在任何字段上.
另外,此处删除并没有用到索引带来的好处,反而需要付出维护索引的代价,因此禁用索引之后重构是必要的.
用CTAS的方法重构表我觉得倒是不错的办法,按照上面所说的情况,我估计你要分析一下段HWM.我估计,问题还是出在表存储管理上.

Answer 12

有道理。楼主执行下： 
sql>alter table 表名 move;再执行你的sql,看看有优化没有？

Answer 13

如果只是一次性清除数据，手动做还是程序后台运行都无所谓。
如果经常定期删除数据，还是建议使用后台存储过程，用JOB来做。
至于数据处理比较慢的问题，也就能对索引简单处理一下，没有太好的办法。
如果删除数据远大于保留在表中的数据，倒是可以反向将保留数据插入到新表，truncate或drop旧表。

Answer 14

处理建议:1.考虑通过分区truncate
        2.非要执行delete的话,可以考虑批量删除,但不要使用分析函数,因为它会所有记录排序,使用min或max即可,其次权衡每次删除的记录数大一点,总体上可以减少IO,从而提高性能

Answer 15

搜一下forall语句看看有没有用