批量删除数据

有条件的分步删除数据表中的记录删除时，在使用delete语句数据库是要做日志记录的，以便将来可以恢复数据，
删除上百万条数据时，十分缓慢 ,对于删除大批量的数据总结了以下几种方式进行批量删除：
方法一：
create or replace procedure delete_table
is
i number(10);
begin
  for x in (select * from emp where DEPTNO like 'a%')
  loop
      delete emp where emp.id = x.id
      i:=i+1;
      if i>1000 then
         commit;
         i:=0;
      end if;
  end loop;
exception
    when others then
         dbms_out.put_line(sqlcode);
         rollback;
end delete_table;

方法二:
DECLARE
CNT NUMBER(10):=0;
I NUMBER(10);
BEGIN
SELECT COUNT(*) INTO CNT FROM emp where DEPTNO like 'a%'

FOR I IN 1..TRUNC(CNT/500)+1 LOOP
DELETE FROM emp where DEPTNO like 'a%' AND ROWNUM<=1000;
COMMIT;
END LOOP;
END;

方法三：
create or replace procedure deleteTab
  /**
   ** Usage: run the script to create the proc deleteTab
   **        in SQL*PLUS, type "exec deleteTab('Foo','ID>=1000000','3000');"
   **        to delete the records in the table "Foo", commit per 3000 records.
   **       Condition with default value '1=1' and default Commit batch is 10000.
   **/
  (
    p_TableName    in    varchar2,    -- The TableName which you want to delete from
    p_Condition    in    varchar2 default '1=1',    -- Delete condition, such as "id>=100000"
    p_Count        in    varchar2 default '10000'    -- Commit after delete How many records
  )
  as
   pragma autonomous_transaction;
   n_delete number:=0;
  begin
   while 1=1 loop
     EXECUTE IMMEDIATE
       'delete from '||p_TableName||' where '||p_Condition||' and rownum <= :rn'
     USING p_Count;
     if SQL%NOTFOUND then
     exit;
     else
          n_delete:=n_delete + SQL%ROWCOUNT;
     end if;
     commit;
   end loop;
   commit;
   DBMS_OUTPUT.PUT_LINE('Finished!');
   DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!');
  end;
  /
方法四：把要保留的数据放在一个临时表里,truncate table后再放回来
       举例（省略）

删除重复记录
方法一：delete from table1 a where rowid!=(select max(rowid) from table1 b where a.id=b.id );
方法二：delete from table1
          where rowid in (select rowid
                              from (select rowid ,
                                    row_number() over(partition by field1,field2.... order by rowid )rn from table1 ) where rn<>1);

方法三：创建临时表把不重复的数据DISTINCT 后放在临时表里，然后DROP 掉‘原表’，再 RENAME ‘临时表’ TO ‘原表’ 就OK了！

其中方法一适合数据量小的删除，方法二适合数据量稍大表的删除，方法三适合重复的数据量特别大的删除。方法二还可以根据某个字段进行删除，只保留