根据我的经验，删除大量行的最快方法是：
解决方案1（Tom Kyte推荐）

`SET TRANSACTION USE ROLLBACK SEGMENT <your_seg>
 DELETE FROM <tab1> WHERE <cond>
 COMMIT`

字符串
或
解决方案2

`create table new_table unrecoverable as select * from old_table where ....;
drop table old_table;
rename new_table to old_table;
create index old_table_idx1 on old_table(c1,c2) unrecoverable parallel 5;
`

型
我在不同的情况下使用了第二种解决方案：删除大量行总是最快的。
另一种方法是将要删除的数据放在一个分区中，然后删除该分区（每个分区都有自己的回滚段，可以使用并行性，...）。

如果希望此查询运行得更快，请添加以下两个索引：

create index idx_persons_city_pid on persons(city, p_id);
 create index idx_orders_pid on orders(p_id);

字符串

DECLARE
 v_limit PLS_INTEGER :=100000;

CURSOR person_deleted_cur
IS 
 SELECT rowid 
   FROM Persons p
  WHERE City = 'ABC'
   AND NOT EXISTS
               (SELECT O_Id
                  FROM Orders o
                 WHERE p.P_Id = o.P_Id);

TYPE person_deleted_nt IS TABLE OF person_deleted_cur%ROWTYPE
        INDEX BY PLS_INTEGER;
BEGIN
  OPEN person_deleted_cur;
    LOOP
      FETCH person_deleted_cur 
        BULK COLLECT INTO person_deleted_nt LIMIT v_limit;

    FORALL indx IN 1 .. person_deleted_nt.COUNT 
      DELETE FROM Persons WHERE rowid=person_deleted_nt(indx);

    EXIT WHEN person_deleted_cur%NOTFOUND;

   END LOOP;

   CLOSE person_deleted_cur;
  COMMIT;
END;
/

字符串

解决方案1：下面的方法将一次删除1000条记录，并将提交删除的1000条记录。
--此集合将包含1000个要删除的ID
创建或替换数组名称类型为VARARRAY（1000）的编号;

DECLARE  
arrayNameType ARRAYNAMETYPE ;  
recordsToBeDeleted NUMBER; 
noOfRecordsDeleted NUMBER;
countDeleted NUMBER;      

CURSOR CURSOR1  
 IS  
   SELECT ID_COLUMN FROM TABLE with conditions;  

BEGIN   
    noOfRecordsDeleted  :=0;  
    countDeleted :=0;  
   --Taking the count of the total no of records that is planned to delete  
   SELECT COUNT(*) INTO recordsToBeDeleted from table_name with where condition  

     open CURSOR1;  
        LOOP   
          FETCH CURSOR1 BULK COLLECT INTO arrayNameType LIMIT 1000;  
          EXIT WHEN arrayNameType.count=0;  
            delete from table where id in (select * from table (arrayNameType));  
countDeleted:=SQL%ROWCOUNT;
noOfRecordsDeleted:= countDeleted+noOfRecordsDeleted
            commit;  

        END LOOP;  
CLOSE CURSOR1;  

end;

字符串
解决方案二：如果必须删除70%或以上的数据，则可以考虑以下方法
1)创建一个临时表，并将数据复制到该临时表中，该临时表不被删除2）截断原始表3）将临时表中的记录插入原始表4）删除临时表。5）需要注意的指标

另一种删除方式：

begin
  dbms_errlog.create_error_log('PERSONS');
end;
/

-- index on foreign key is useful thing in many cases, not only now
create index idx_orders_pid on orders(p_id); 

declare 
  min_id number;
  max_id number;
begin
  select min(p_id), max(p_id)
    into min_id, max_id
    from persons;

  for i in min_id..max_id loop
    delete from persons where p_id between i and i + 100000
    log errors into err$_persons reject limit unlimited;
  end loop;
end;
/

drop table err$_persons;

字符串