title: How to delete huge rows in oracle table using parallel sessions query quickly
body: <p>I am using mentioned query to delete 250 million plus rows from my table and it is taking more time</p>
  
  <p>I have tried with t_delete limit with up to 20000. </p>
  
  <p>Still slow deletion happening.</p>
  
  <p>Please suggest a few optimisations in the same code to done my job faster.</p>
  
  <pre><code>DECLARE
  TYPE tt_delete IS TABLE OF ROWID; t_delete tt_delete;
  CURSOR cIMAV IS SELECT ROWID FROM moc_attribute_value where id in (select 
  id from ORPHANS_MAV);
  
  total Number:=0;
  rcount Number:=0;
  Stmt1 varchar2(2000);
  Stmt2 varchar2(2000);
  BEGIN
  ---    CREATE TABLE orphansInconsistenDelProgress (currentTable 
  VARCHAR(100), deletedCount INT, totalToDelete INT);
  ---    INSERT INTO orphansInconsistenDelProgress (currentTable, 
  deletedCount,totalToDelete) values ('',0,0);
  Stmt1:='ALTER SESSION SET parallel_degree_policy = AUTO';
  Stmt2:='ALTER SESSION FORCE PARALLEL DML';
  EXECUTE IMMEDIATE Stmt1;
  EXECUTE IMMEDIATE Stmt2;
  ---   ALTER SESSION SET parallel_degree_policy = AUTO;
  ---   ALTER SESSION FORCE PARALLEL DML;
  COMMIT;
  
  --- MOC_ATTRIBUTE_VALUE
  
  SELECT count(*) INTO total FROM ORPHANS_MAV;
  UPDATE orphansInconsistenDelProgress SET currentTable='ORPHANS_MAV', 
  totalToDelete=total;
  rcount := 0;
  OPEN cIMAV;
  LOOP
      FETCH cIMAV BULK COLLECT INTO t_delete LIMIT 2000;
              EXIT WHEN t_delete.COUNT = 0;
              FORALL i IN 1..t_delete.COUNT
          DELETE moc_attribute_value WHERE ROWID = t_delete (i);
      COMMIT;
      rcount := rcount + 2000;
      UPDATE orphansInconsistenDelProgress SET deletedCount=rcount;
  END LOOP;
  CLOSE cIMAV;
  COMMIT;
  END;
  / 
  </code></pre>