oracle上亿条记录大表delete

Question

有一张表tabacc有9000多万条数据，每次delete大约100万条数据，每次delete的时间很长，我用的方法(在存储过程中实现)是--delete /*+ use_hash(a,b) parallel(a,15)*/ from tabacca where exists (select 1 from temptablea b where a.id=b.id and b.type='1')；不是分区表，有索引，因执行时间太长，看高手有什么好的办法？请高手指点！

Answer 1

有一张表tabacc有9000多万条数据，每次delete大约100万条数据，每次delete的时间很长，我用的方法(在存储过程中实现)是--delete /*+ use_hash(a,b) parallel(a,15)*/ from tabacca where exists (select 1 from temptablea b where a.id=b.id and b.type='1')；不是分区表，有索引，因执行时间太长，看高手有什么好的办法？请高手指点！ 是生产上的数据库

Answer 2

declare
maxrows number default 1000;
tb_row_id type%tabacca.row_id;
  cursor my_cur is
    select  a.rowid row_id
    from tabacca a where exists (select 1 from temptablea b where a.id=b.id and b.type='1')
  order by a.rowid;
begin
open my_cur;
loop
exit when my_cur%NOTFOUND;
fetch my_cur bulk collect into tb_row_id limit maxrows;
forall i in 1..tb_row_id.count
    delete tabacca  where rowid= tb_row_id(i);
      commit;
  end loop;
end;

Answer 3

用 索引 + ROWID 去删除!

Answer 4

-- 具体请参考：http://topic.csdn.net/u/20110805/09/87d9d494-9105-44f6-8971-a41aeb406922.html?seed=1796660687&r=74789817#r_74789817

Answer 5

因为是生产上的表，不能动生产的表，这张情况我也试过了CURSOR tmp_cur IS 
   SELECT /*+ use_hash(a,b) parallel(a,16) */ a.ROWID FROM tmpaaa a
WHERE EXISTS (SELECT 1 FROM temptable b WHERE a.tmpaaaid=b.appid and b.mType='1') ORDER BY a.ROWID;
 EXECUTE IMMEDIATE 'alter table tmpaaa storage(buffer_pool keep)';
 OPEN tmp_cur ;
 loop
 begin
FETCH tmp_cur BULK COLLECT INTO var_rowid LIMIT LimitRows;
FORALL i IN 1 .. var_rowid.COUNT 
  DELETE FROM tmpaaa WHERE ROWID = var_rowid(i);
  commit;
EXCEPTION WHEN OTHERS THEN ROLLBACK;
 end ;
 exit when tmp_cur%notfound;
 end loop;
 CLOSE tmp_cur;
 execute immediate 'alter table tmpaaa storage(buffer_pool default)';
 commit;

Answer 6

SELECT /*+ use_hash(a,b) parallel(a,16) */ a.ROWID FROM tmpaaa a
WHERE EXISTS (SELECT 1 FROM temptable b WHERE a.tmpaaaid=b.appid and b.mType='1') ORDER BY a.ROWID;看这个能不能想办法优化下

Answer 7

alter session enable parallel dml;
delete /*+ PARALLEL(table ,4)*/ from table where  ...尝试后告诉我结果

Answer 8

lz说的时间太长是有多长呢，毕竟要从上亿条记录中找出100w还是需要花时间的。
楼主可以试试多个job分工，同时进行。
比如开10个job，每个job分十万数据，这样可能会快点

Answer 9

类似下面这样
DECLARE
   x   NUMBER;
BEGIN
   FOR i IN 0 .. 9
   LOOP
      DBMS_JOB.submit (
         x,
         'begin DELETE   tabacca t1
 WHERE   EXISTS
            (SELECT   1
               FROM   (SELECT   id
                         FROM   (SELECT   SUBSTR (TO_CHAR (ROWNUM), -1) rn,
                                          id
                                   FROM   temptablea
                                  WHERE   TYPE = ''1'')
                        WHERE   rn = '''
         || i
         || ''') t2
              WHERE   t2.id = t1.id);COMMIT; end;'
      );
   END LOOP;
END;

Answer 10

不知道这样会不会快些 把where换个地方declare
maxrows number default 1000;
tb_row_id type%tabacca.row_id;
  cursor my_cur is
    select /*+ use_hash(a,b) parallel(a,16) */  a.rowid row_id
    from tabacca a 
  order by a.rowid;
begin
open my_cur;
loop
exit when my_cur%NOTFOUND;
fetch my_cur bulk collect into tb_row_id limit maxrows;
forall i in 1..tb_row_id.count
    delete tabacca where id in(select id from temptablea where type='1') and rowid= tb_row_id(i);
      commit;
  end loop;
end;

Answer 11

tx2730你好！我试了一下，分区表调用job有死锁存在，效果更差；不是分区表效果很好！请问一下分区表怎么处理？你的QQ或者邮箱有吗？谢谢！

Answer 12

分区表的话，就加一层分区进行循环。for 分区
  for i in 0.. 9

Answer 13

delete掉的每条数据要记log，所以能不能delete时指定nologging呢。

Answer 14

cutebear2008说的我早就试过了，去掉log对我来说几乎不起作用，不知道其他人怎么样？

Answer 15

我现在问题解决了，感谢上面发言的人，特别是tx2730；不是分区表就按tx2730的方法，分区按分区删除；今天才出来生产上的实际结果？tx2730你好我的qq号是397940646；