oracle上亿条记录大表delete

[dongxnbz]

有一张表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')；不是分区表，有索引，因执行时间太长，看高手有什么好的办法？请高手指点！

[oO寒枫Oo]

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;

[dongxnbz]

非常感谢！您上面说的方法我用过了，效果不是多理想！

[yangqm22]

删除数据是挺长。如果要快就是把表改名字重建。导入数据这样快多了。但是如果是生产库。这样做就不合适。

[luoyoumou]

用 索引 + ROWID 去删除!

[luoyoumou]

[Quote=引用 6 楼 luoyoumou 的回复:]

用 索引 + ROWID 去删除!
[/Quote]

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

[dongxnbz]

因为是生产上的表，不能动生产的表，这张情况我也试过了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;

[oO寒枫Oo]

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;

看这个能不能想办法优化下

[christwei]

alter session enable parallel dml;
delete /*+ PARALLEL(table ,4)*/ from table where ...

尝试后告诉我结果

[dongxnbz]

具体怎么做说清楚点好不吧！多谢！

[007-x]

[Quote=引用 13 楼 dongxnbz 的回复:]
具体怎么做说清楚点好不吧！多谢！
[/Quote]
类似下面这样

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;

[oO寒枫Oo]

不知道这样会不会快些 把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;

[dongxnbz]

14楼用的方法我还没有用过，我用一下看看效果；然后给您回复！谢谢！

[dongxnbz]

10楼说的我试了效果不是多明显！多谢！

[007-x]

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

[dongxnbz]

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

[coolkisses]

分区表的话，就加一层分区进行循环。

for 分区
for i in 0.. 9

[cutebear2008]

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

[dongxnbz]

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

[dongxnbz]

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

[鹅卵石X]

这个问题应该从系统设计上来解决。